想得到O_EMPLOYEES表中的唯一约束的名称,约束列和约束列在表中的序号,执行如下的sql语句:
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';
执行结果正确,但足足执行了2分钟,请教高手如何来优化这条sql语句?
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';
执行结果正确,但足足执行了2分钟,请教高手如何来优化这条sql语句?
create table O_EMPLOYEES(o_id int,
o_num int,
o_name varchar(50),
o_depart varchar(100));
alter table O_EMPLOYEES add constraint uuu1 unique(o_id);
alter table O_EMPLOYEES add constraint uuu2 unique(o_num);
o_num int,
o_name varchar(50),
o_depart varchar(100));
alter table O_EMPLOYEES add constraint uuu1 unique(o_id);
alter table O_EMPLOYEES add constraint uuu2 unique(o_num);insert into O_EMPLOYEES values ( 1,1,'1','1' );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';0 rows selected in 0.031 seconds.
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 /*+ ordered use_hash(cons cols tcols) */ 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';
你两个方法都试试看