Fast way to determine if an field exist in a ORACLE table
Solution 1
the primary key of all_tab_columns
is owner, table_name, column_name
so looking for a particular owner will be faster (or use user_tab_columns
).
Solution 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:0::::P11_QUESTION_ID:698008000346356376
Solution 3
Querying the Oracle data dictionary - as you example indeed does, is probably the fastest way.
The data dictionary is cached in memory and should be able to satisfy the query pretty quickly. You may be able to get slightly faster results if you know the actual schema owner of the table - so that you don't incur the cost of searching against all schemas.
Solution 4
This query is enough:
SELECT null
FROM user_tab_columns
WHERE table_name = 'MYTABLE' and column_name = 'MYCOLUMN'
The only fastest way is to query directly from the internal tables which is not a recommended way 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 query is taken from the USER_TAB_COLUMNS
definition and it can change over different releases (10gR2 on my case). On this query I've cut the references to information not requested by you.
Anyway, why do you want to check this?
Comments
-
RRUZ almost 2 years
I am looking for a fast sql sentence for determine when a field exist or not in a table .
actually i am using 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 must be a fastest way to determine whether or not a column exist in ORACLE.
UPDATE
I'm optimizing a larger software system that makes multiple calls to this Query, I can not modify the source code ;( , only i can modify the query which is stored in an external file.
the Table all_tab_columns has over a million of records.