The importance of being aliased

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

I was writing a query returning the indexes to be monitored for a list of schemas for those tables having fewer than 5 indexes (not counting LOB indexes) and I was surprised to see that it returned no rows.
After checking what's wrong I realized that I had specified the wrong column for the included subquery, but how could it be that Oracle wasn't complaining about a non existing column?

with excluded as (select /*+ MATERIALIZE */ b.owner, b.index_name
                                      from dba_constraints b
                                     where b.constraint_type in ('P','U')
                                       and b.owner in ('A','B','C')
                                     ),
       included as (select /*+ MATERIALIZE */ owner, table_name
                                from dba_indexes
                               where owner in ('A','B','C') 
                                 and index_type != 'LOB' 
                               group by owner, table_name
                               having count(*) > 5
                            )
select 'ALTER INDEX ' || owner ||'.' || index_name || ' MONITORING USAGE;' as ddl
  from dba_indexes a
 where (a.owner, a.table_name) in (select owner, index_name
                                     from included
                              )
  and (a.owner, a.index_name) not in (select owner, index_name
                                        from excluded 
                                     )
  and a.owner in ('A','B','C')
  and a.index_type != 'LOB';

DDL

===

(no rows) 

Soon I realized not only that I had specified the wrong column (I should have written table_name instead) but also that I hadn't specified table aliases and index_name was not a value coming from included, but from the outer view dba_indexes, which is just fine from Oracle's point of view but totally wrong for my purposes.

In order to prevent these mistakes, the SQL above needs to be written using ALWAYS table aliases in the subqueries and applying them to each relevant column:

  with excluded as (select /*+ MATERIALIZE */ b.owner, b.index_name
                                      from dba_constraints b
                                     where b.constraint_type in ('P','U')
                                       and b.owner in ('A','B','C')
                                     ),
       included as (select /*+ MATERIALIZE */ owner, table_name
                                from dba_indexes
                               where owner in ('A','B','C') 
                                 and index_type != 'LOB' 
                               group by owner, table_name
                               having count(*) > 5
                            )
  select 'ALTER INDEX ' || owner ||'.' || index_name || ' MONITORING USAGE;' as ddl
  from dba_indexes a
 where (a.owner, a.table_name) in (select i.owner, i.table_name
                                     from included i
                              )
  and (a.owner, a.index_name) not in (select e.owner, e.index_name
                                        from excluded e
                                     )
  and a.owner in ('A','B','C')
  and a.index_type != 'LOB';

In this case it was easy to guess that something was wrong because the query returned no results and I knew it was nonsense, but in complex queries made up possibly of several subqueries one can easily overlook the problem should some results being returned.

Table aliases must be used at all times!