sql的cbo 执行计划 是无法控制的 条件查出的记录数与总记录的占比有关,
oracle有时会认为用索引比不用索引的成本还高,此时可能cbo的性能要比rbo差。我们只能加hint改变执行计划
可能加一个不是很重要的条件就会改变整个的计划
-- Create table
create table T1
(
ID VARCHAR2(32) not null,
NAME VARCHAR2(62),
AGE VARCHAR2(62)
);
alter table T1
add constraint PK_T1 primary key (ID);
create index IDX_T1_NAME on T1 (NAME);
-- Create table
create table T2
(
ID VARCHAR2(32) not null,
FID VARCHAR2(32),
NAME VARCHAR2(55),
ADDRESS VARCHAR2(32)
);
alter table T2
add constraint PK_T2 primary key (ID);
create index IDX_T2_NAME on T2 (NAME);
alter table T2
add constraint fk_t2_ref_t1 foreign key (FID)
references T1 (ID);
是按name 1:1的两个表
插入数据
begin
for i in 1..10000 loop
insert into t1(id,name,age) values (sys_guid(),'tom'||sys_guid(),'44');
end loop;
end;
插入数据
insert into t2(id,fid,name,address) (select sys_guid() ,id ,name, sys_guid() from t1)
begin
for i in 1..10 loop
insert into t1(id,name,age) values (sys_guid(),'zhang','44');
end loop;
end;
select * from t1,t2 where t1.name=t2.name and t1.name='zhang'
begin
for i in 1..400 loop
insert into t1(id,name,age) values (sys_guid(),'zhang','44');
end loop;
end;
select * from t1,t2 where t1.name=t2.name and t1.name='zhang'
select /*+rule*/* from t1,t2 where t1.name=t2.name and t1.name='zhang'
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12548713/viewspace-229973/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/12548713/viewspace-229973/