A quick way to determine if a field exists in an ORACLE table

Question

I am looking for a quick sql suggestion to determine if a field exists in a table or not.

actually i use this sentence

Select 1 from dual where exists (select 1 from all_tab_columns where table_name = 'MYTABLE' and column_name = 'MYCOLUMN') 

I think there should be the fastest way to determine if a column exists in ORACLE.

UPDATE

I am optimizing a large software system that makes several calls to this request; I cannot change the source code; (, only I can change the request, which is stored in an external file.

The all_tab_columns table has over a million records.

Answer 1

the primary key of all_tab_columns is owner, table_name, column_name , so finding a specific owner will be faster (or use user_tab_columns ).

Answer 2

I suggest reading this AskTom article. It explains that the fastest way to check is not to check at all.

http://asktom.oracle.com/pls/asktom/f?p=100:11:59::::P11_QUESTION_ID:698008000346356376

Answer 3

Querying an Oracle data dictionary - how you really do this is probably the fastest way.

The data dictionary is cached in memory and should satisfy requests fairly quickly. You can get slightly faster results if you know the actual owner of the table schema - so that you do not incur the cost of searching for all the schemas.

Answer 4

This request is enough:

  SELECT null FROM user_tab_columns WHERE table_name = 'MYTABLE' and column_name = 'MYCOLUMN' 

The fastest way is to directly query the internal tables, which are not recommended , and you need grants over sys objects:

 select null from sys.col$ c , sys.obj$ o , sys.obj$ ot where o.name = 'MYTABLE' and c.name = 'MYCOLUMN' and o.obj# = c.obj# and o.owner# = userenv('SCHEMAID') and ot.type#(+) = 13 and (o.type# in (3, 4) or (o.type# = 2 and not exists (select null from sys.tab$ t where t.obj# = o.obj# and (bitand(t.property, 512) = 512 or bitand(t.property, 8192) = 8192)))) 

This request is taken from the definition of USER_TAB_COLUMNS , and it can change in different versions (10gR2 in my case) . At this request, I have shortened links to information not requested by you.

Anyway, why do you want to check it out?

Answer 5

This SQL query will give the name of the entire table containing the column "NAVIGATION_ID" for user "DSGIDEV"

select * from all_tab_cols, where column_name = 'NAVIGATION_ID' and owner = 'DSGIDEV'

So, change the name of the column to the column you want to find, and the owner with the name of its owner.