select cons.constraint_name,cols.column_name,tcols.column_id
from all_constraints cons,all_cons_columns cols, all_tab_columns tcols
where
cons.owner=cols.owner and
cons.constraint_name=cols.constraint_name and
cons.table_name =tcols.table_name and
cols.column_name=tcols.column_name and
cons.CONSTRAINT_TYPE ='U' and
cons.Owner='DTS' and
cons.table_name='O_EMPLOYEES';
该语句执行结果正确,但特别慢,后来做了这样的修改:
select a.constraint_name,a.column_name,tcols.column_id from
(
select cons.constraint_name, cols.table_name, cols.column_name from
(select cons.owner, cons.constraint_name from all_constraints cons where cons.CONSTRAINT_TYPE ='U' and cons.Owner='DTS' and cons.table_name='O_EMPLOYEES' )
cons
LEFT join all_cons_columns cols on cons.owner=cols.owner and cons.constraint_name=cols.constraint_name
) a
LEFT join all_tab_columns tcols on a.table_name =tcols.table_name and a.column_name=tcols.column_name
速度提上去了,但有重复记录,请高人帮忙看一下问题所在
from all_constraints cons,all_cons_columns cols, all_tab_columns tcols
where
cons.owner=cols.owner and
cons.constraint_name=cols.constraint_name and
cons.table_name =tcols.table_name and
cols.column_name=tcols.column_name and
cons.CONSTRAINT_TYPE ='U' and
cons.Owner='DTS' and
cons.table_name='O_EMPLOYEES';
该语句执行结果正确,但特别慢,后来做了这样的修改:
select a.constraint_name,a.column_name,tcols.column_id from
(
select cons.constraint_name, cols.table_name, cols.column_name from
(select cons.owner, cons.constraint_name from all_constraints cons where cons.CONSTRAINT_TYPE ='U' and cons.Owner='DTS' and cons.table_name='O_EMPLOYEES' )
cons
LEFT join all_cons_columns cols on cons.owner=cols.owner and cons.constraint_name=cols.constraint_name
) a
LEFT join all_tab_columns tcols on a.table_name =tcols.table_name and a.column_name=tcols.column_name
速度提上去了,但有重复记录,请高人帮忙看一下问题所在
解决方案 »
- 请教如何在一个存储过程里面动态调用另外的存储过程
- dataguard broker 切换主备库问题
- 入门oracle问题,select变量出来,来人帮忙
- 关于dts连接oracle服务器问题,急等!!!
- 一个关于SQL语句的问题!
- 急~~~~~~~~~~~~~~~~~~~~~~~~~~关于oracle的jobs 时间间隔设置
- 请教各位,oracle中如何插入一个长字符串?
- 怎样用SQL语句查出一个约束的具体内容,比如:我想查unique constraint (MOBILE.SYS_C0042292)的具体内容。
- 急!9i中如何自定义数据类型,并在建表时使用它啊
- 急急急急急....我的存储过程错在哪儿???各位,能帮我看一下吗?
- oracle 触发器问题(精)
- BETWEEN...AND 和>= AND <==的效率
select cons.constraint_name,cols.column_name,tcols.column_id
from all_constraints cons,all_cons_columns cols, all_tab_columns tcols
where
cons.owner=cols.owner and
cons.constraint_name=cols.constraint_name and
cons.owner =tcols.owner and
cons.table_name =tcols.table_name and
cols.column_name=tcols.column_name and
cons.CONSTRAINT_TYPE ='U' and
cons.Owner='DTS' and
cons.table_name='O_EMPLOYEES';
from all_constraints cons,all_cons_columns cols, all_tab_columns tcols
where
cons.table_name='O_EMPLOYEES' and
cons.CONSTRAINT_TYPE ='U' and
cons.Owner='DTS' and
cons.owner=cols.owner and
cons.constraint_name=cols.constraint_name and
cons.table_name =tcols.table_name and
cols.column_name=tcols.column_name 把越苛刻的条件写得离where越近,这样可以优化,如果还比较慢,可以接合嵌套来解决