The weirdest reason to avoid SELECT *

A quick Google or Bing search and you’ll find no limit to the number of articles on databases that tell you that using “SELECT *” is a terrible terrible thing to do. Your code will be more fragile.  It is bad for your data dictionary.  You’ll end up with conflicts when you join, the list goes on. You can find a more reasoned argument from Markus Winand but ultimately for the majority of the time, the potential drawbacks outweigh any convenience benefits of using SELECT *.

(I say “majority” because PL/SQL is one of the few languages that can insulate you from a lot of the risks with its %ROWTYPE syntax and the VALUES / SET ROW clauses for DML. Yet another reason why PL/SQL is so cool).

But here’s probably the weirdest reason you’ll ever see for why you might want to steer clear of SELECT *. I’ll start with a simple table T, and I’ve named the columns CHILD and PARENT because I’m going to query the table using the recursive subquery factoring feature:

SQL> create table t (child,parent ) as select 2,1 from dual;

Table created.

With only a single row, the results from the recursive query are not particularly exciting

SQL> with recursive (chd,par) as (
  2    select child,parent
  3    from t
  4    union all
  5    select t.child, t.parent
  6    from t, recursive
  7    where recursive.chd = t.parent
  8  )
  9  select *
 10  from recursive;

       CHD        PAR
---------- ----------
         2          1

but the key thing to note here is line 2 and 5. As you would expect from any UNION ALL query, the number of columns in the first part of the UNION ALL must match the number of columns in the second part of the UNION ALL.

On line 5, I’m querying columns CHILD and PARENT from the table T, and savvy readers will have already spotted that this is all of the columns from T and in the same order at the definition of the table. Hence lets see what happens when I go against conventional wisdom and replace this with a SELECT *.

SQL> with recursive (chd,par) as (
  2    select child,parent
  3    from t
  4    union all
  5    select t.*
  6    from t, recursive
  7    where recursive.chd = t.parent
  8  )
  9  select *
 10  from recursive;
  select t.*
  *
ERROR at line 5:
ORA-01789: query block has incorrect number of result columns

To understand why this is the case, we need to take a careful look at the documentation for recursive subquery factoring, which states:

“The number of column aliases following WITH query_name and the number of columns in the SELECT lists of the anchor and recursive query blocks must be the same.”

The ORA-01789 error suggests this check is being done very early in the syntax processing before expansion of the asterisk into columns. Hence there is only a single item on the second part of the UNION ALL. Further weight to the hypothesis of the earliness of this check can be seen by putting gibberish in the SQL.

SQL> with recursive (chd,par) as (
  2    select child,parent
  3    from t
  4    union all
  5    select blahblahblah.*
  6    from t, recursive
  7    where recursive.chd = t.parent
  8  )
  9  select *
 10  from recursive;
  select blahblahblah.*
  *
ERROR at line 5:
ORA-01789: query block has incorrect number of result columns

Even with the reference to the non-existent “blahblahblah”, the first error we get is not about the reference but to the number of columns. The statement fails the first syntax check before getting anywhere near the data dictionary for further validation.

So when it comes to recursive query subfactoring, make sure you list those columns out loud and proud!