CODEWITHSUNDEEP
sqloracle-databaseexists
RRUZ
RRUZ

Reputation: 136441

Fast way to determine if an field exist in a ORACLE table

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.

Upvotes: 8

Views: 30825

Answers (6)

Summer23 HV
Summer23 HV

Reputation: 1

Ez, fastest way is just create function like this:

  Create function exist(v_table in varchar2, v_col in  varchar2) 
 Return integer is
 Res integer:= 0;
 Begin
   Begin
      Execute immediate 'select ' || v_col || ' from '|| v_table;         
      Res:=1;
      Exception when other then null;
   End;
Return (res);
End;

Upvotes: 0

Ranveer Singh
Ranveer Singh

Reputation: 61

This SQL Query will give name of all the table having column 'NAVIGATION_ID' for the user 'DSGIDEV'

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

So, Change the column name with column you want to search and owner with your owner Id name.

Upvotes: 0

FerranB
FerranB

Reputation: 36827

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?

Upvotes: 1

Joseph Bui
Joseph Bui

Reputation: 1731

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

Upvotes: 2

LBushkin
LBushkin

Reputation: 131806

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.

Upvotes: 1

Vincent Malgrat
Vincent Malgrat

Reputation: 67772

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).

Upvotes: 9

Related Questions