SQL 快速确定字段是否存在于 ORACLE 表中的方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2276305/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Fast way to determine if an field exist in a ORACLE table
提问by RRUZ
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 有超过一百万条记录。
回答by Vincent Malgrat
the primary key of all_tab_columnsis owner, table_name, column_nameso looking for a particular owner will be faster (or use user_tab_columns).
的主键all_tab_columns是owner, table_name, column_name这样寻找特定所有者会更快（或使用user_tab_columns）。
回答by Joseph Bui
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
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:698008000346356376
回答by FerranB
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 wayand 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_COLUMNSdefinition 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?
无论如何，你为什么要检查这个？
回答by LBushkin
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.
数据字典缓存在内存中，应该能够很快满足查询。如果您知道表的实际架构所有者，您可能会获得稍微快一点的结果 - 这样您就不会产生搜索所有架构的成本。
回答by Ranveer Singh
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 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.
因此，使用您要搜索的列更改列名称，并使用您的所有者 ID 名称更改所有者。
回答by Summer23 HV
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;
