5 ms·

A lot of the points in this article are far too sweeping to be true. For instance, the statement that using select * will cause too many columns to be read is

A lot of the points in this article are far too sweeping to be true. For instance, the statement that using select * will cause too many columns to be read is repeated over and over, but it mostly just isn't true. For instance, if I define a view that does select * from a join of two tables and then do a query that asks for two columns from the view, essentially every optimizer on the planet will push down the two columns and will never retrieve all the columns. The same applies to common table expressions. Moreover, putting a * in these views or common expressions is actually less error prone than putting in an explicit field list. What you are saying is that the view or expression is passing everything through from a join or filter operation. That's the right thing. This absolutist sort of generalization that select * needs to be repaired everywhere is just silly.

chasil 5y ago
(At least in Oracle) Rows are held in blocks, and the whole block must be read to retrieve a row within it. If the row entirely fits, there is no difference between selecting a subset of columns versus all of them, as the disk must do the same work. When columns are sharded over several servers, this would not hold true. Never use SELECT * within the context of an application, as column additions or removals can break the app. It pays to be specific in code. For interactive use, it's fine, especially when the exact spellings of the target columns is not known. The author might have more of a point on complex views.
- willcipriano 5y ago
  > Never use SELECT * within the context of an application, as column additions or removals can break the app. Use a dictionary cursor, or some other method to convert whatever is returned into a hashmap like so: https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursordict.html https://dev.mysql.com/doc/connector-python/en/connector-pyth...
  - sk5t 5y ago
    Not good enough, as 'select *' can in any event introduce ambiguity that is fatal to query processing.
    - ted_dunning 5y ago
      Do you have an example to back that up?
    - ted_dunning 5y ago
      It actually appears to go the other way. Explicitly referencing an ambiguous column causes the error while using select * avoids it, at least with duckdb (which is a very standard sort of SQL). This suggests to me that using select * in views is a very good practice. D create table a (amb integer, a1 integer, a2 integer); D create table b (amb varchar, b1 integer, b2 integer); D insert into a values (1,1,1); D insert into b values ('b',2,2); D insert into b values ('b',1,3); D select * from a, b where a.a1 = b.b1; ┌─────┬────┬────┬─────┬────┬────┐ │ amb │ a1 │ a2 │ amb │ b1 │ b2 │ ├─────┼────┼────┼─────┼────┼────┤ │ 1 │ 1 │ 1 │ b │ 1 │ 3 │ └─────┴────┴────┴─────┴────┴────┘ D select amb from a, b where a.a1 = b.b1; Error: Binder Error: Ambiguous reference to column name "amb" (use: "b.amb" or "a.amb")
      - listenallyall 5y ago
        The database query analyzer is doing you a favor by telling you there's an ambiguous column name and refusing to run, for the same reason a compiler won't allow you to define the same variable name twice.
  - RedShift1 5y ago
    How does that help against columns being added or removed?
    - willcipriano 5y ago
      If they are added they are just another key in the hashmap, no harm. If they are removed, I don't have a solution for that. Typically you don't remove columns of databases like that, but adding them is common.
- evanelias 5y ago
  > Never use SELECT * within the context of an application, as column additions or removals can break the app. To help with this problem, several databases now support a notion of "invisible columns", including Oracle, MySQL, and MariaDB. Invisible columns are excluded from SELECT *, but otherwise function like normal columns and can be queried explicitly by name.
- ted_dunning 5y ago
  I suspect that almost any sweeping statement that Oracle doesn't support some feature or other is wrong in the current day. Oracle can certainly handle columnar formatted data. The in-memory columnar format is just what it says, HCC provides columnar compression and Exadata has done columnar data access forever. I know little about Oracle specifically, but I do know that it is a huge beast with all kinds of feature that make generalization difficult.

chasil 5y ago

(At least in Oracle) Rows are held in blocks, and the whole block must be read to retrieve a row within it. If the row entirely fits, there is no difference between selecting a subset of columns versus all of them, as the disk must do the same work. When columns are sharded over several servers, this would not hold true. Never use SELECT * within the context of an application, as column additions or removals can break the app. It pays to be specific in code. For interactive use, it's fine, especially when the exact spellings of the target columns is not known. The author might have more of a point on complex views.

willcipriano 5y ago
> Never use SELECT * within the context of an application, as column additions or removals can break the app. Use a dictionary cursor, or some other method to convert whatever is returned into a hashmap like so: https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursordict.html https://dev.mysql.com/doc/connector-python/en/connector-pyth...
- sk5t 5y ago
  Not good enough, as 'select *' can in any event introduce ambiguity that is fatal to query processing.
  - ted_dunning 5y ago
    Do you have an example to back that up?
  - ted_dunning 5y ago
    It actually appears to go the other way. Explicitly referencing an ambiguous column causes the error while using select * avoids it, at least with duckdb (which is a very standard sort of SQL). This suggests to me that using select * in views is a very good practice. D create table a (amb integer, a1 integer, a2 integer); D create table b (amb varchar, b1 integer, b2 integer); D insert into a values (1,1,1); D insert into b values ('b',2,2); D insert into b values ('b',1,3); D select * from a, b where a.a1 = b.b1; ┌─────┬────┬────┬─────┬────┬────┐ │ amb │ a1 │ a2 │ amb │ b1 │ b2 │ ├─────┼────┼────┼─────┼────┼────┤ │ 1 │ 1 │ 1 │ b │ 1 │ 3 │ └─────┴────┴────┴─────┴────┴────┘ D select amb from a, b where a.a1 = b.b1; Error: Binder Error: Ambiguous reference to column name "amb" (use: "b.amb" or "a.amb")
    - listenallyall 5y ago
      The database query analyzer is doing you a favor by telling you there's an ambiguous column name and refusing to run, for the same reason a compiler won't allow you to define the same variable name twice.
- RedShift1 5y ago
  How does that help against columns being added or removed?
  - willcipriano 5y ago
    If they are added they are just another key in the hashmap, no harm. If they are removed, I don't have a solution for that. Typically you don't remove columns of databases like that, but adding them is common.
evanelias 5y ago
> Never use SELECT * within the context of an application, as column additions or removals can break the app. To help with this problem, several databases now support a notion of "invisible columns", including Oracle, MySQL, and MariaDB. Invisible columns are excluded from SELECT *, but otherwise function like normal columns and can be queried explicitly by name.
ted_dunning 5y ago
I suspect that almost any sweeping statement that Oracle doesn't support some feature or other is wrong in the current day. Oracle can certainly handle columnar formatted data. The in-memory columnar format is just what it says, HCC provides columnar compression and Exadata has done columnar data access forever. I know little about Oracle specifically, but I do know that it is a huge beast with all kinds of feature that make generalization difficult.