快速确定ORACLE表中是否存在字段的方法

I am looking for a fast sql sentence for determine when a field exist or not in a table .

我正在寻找一个快速的sql语句来确定表中是否存在字段。

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.

我认为必须有一种最快的方法来确定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.

表all_tab_columns有超过一百万条记录。

6 个解决方案

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

all_tab_columns的主键是owner,table_name,column_name,因此查找特定所有者将更快(或使用user_tab_columns)。

#2

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

我建议阅读这篇AskTom文章。它解释说,最快的检查方法是不检查。

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

#3

Querying the Oracle data dictionary - as you example indeed does, is probably the fastest way.

查询Oracle数据字典 - 正如您的示例确实所做的那样,可能是最快的方式。

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.

数据字典缓存在内存中,应该能够很快地满足查询。如果您知道表的实际架构所有者,则可能会获得稍快的结果 - 这样您就不会产生搜索所有架构的成本。

#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:

唯一最快的方法是直接从内部表中查询,这不是推荐的方式,您需要对sys对象进行授权:

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.

此查询取自USER_TAB_COLUMNS定义,它可以更改不同版本(在我的情况下为10gR2)。在此查询中,我删除了对您未请求的信息的引用。

Anyway, why do you want to check this?

无论如何,你为什么要检查这个?

#5

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

此SQL查询将为用户“DSGIDEV”提供具有“NAVIGATION_ID”列的所有表的名称

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

select_ from all_tab_cols其中column_name ='NAVIGATION_ID'且owner ='DSGIDEV'

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

因此,使用您的所有者ID名称更改要搜索的列的列名称和所有者。

#6

Ez, fastest way is just create function like this:

Ez,最快的方法就是创建这样的函数:

  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;

#1