Re: Simple query hangs !! help

In article <4jesls$mad_at_hubcap.clemson.edu>, Raj Rangarjan (raj_at_cs.clemson.edu) says...
!>
!>Hi,
!> Anyone know of a reson why this query hangs
!>spool flag.tmp
!>set head off
!>select distinct table_name from
!>sys.all_tab_columns a
!>where (owner='AIS' or owner='PDS')
!>and 'FLAG_SELF_SUPPORTING' in
!> (select column_name from sys.all_tab_columns
!> where table_name=a.table_name);
!>spool off
!>
!>whereas this one works fine
!>
!>spool flag.tmp
!>set head off
!>select distinct table_name from
!>sys.all_tab_columns a
!>where (owner='AIS' or owner='PDS')
!>and 'FLAG_SELF_SUPPORTING' not in
!> ^^^^^^^
!> (select column_name from sys.all_tab_columns
!> where table_name=a.table_name);
!>spool off
!>
!>All I am doing is selecting a list of tables which do have a particular
!>field_name in the first query and choosing the list of tables that do
!>have the field name. The field name in the above two queries are
!>'FLAG_SELF_SUPPORTING'
!>

I'm not surprised at all it takes a long time, all_tab_columns is a view. But if you want an answer fast:
select table_name from all_tab_columns where owner in ('AIS','PDS') and  column_name = 'FLAG_SELF_SUPPORTING'.

No need to hurt the poor database with a subselect, and a correlated one for which you didn't even bother to specify the owner. Shame on you.

Sorry, but I've been drinking again.

-- 
Kind reGards
     \ /   |
      X    |
     / \   s
     Gerard