好吧,你自己都说了,优化器都不知道a=5和b=6应该先执行哪个,那你凭什么知道呢?
在a=1 and b=2 and c=3 这种绝对平衡的where条件中(所谓绝对平衡是指数据分布,返回结果,执行方式等效率全部一样),CBO怎么执行的,有必要去考虑吗,怎么执行的结果都一样。如果where条件不平衡,同样不用考虑先后顺序,因为CBO会把所有where条件平等看待,无所谓先后。
建表;
create table t_testcolstart as select * from dba_objects;
插入数据:
insert into t_testcolstart select * from t_testcolstart;
commit;
insert into t_testcolstart select * from t_testcolstart;
commit;
insert into t_testcolstart select * from t_testcolstart;
commit;
insert into t_testcolstart select * from t_testcolstart;
commit;
insert into t_testcolstart select * from t_testcolstart;
commit;
insert into t_testcolstart select * from t_testcolstart;
commit;
SQL> select count(*) from t_testcolstart;
COUNT(*)
----------
807088
--目前表中以及包含近百万数据
--构造条件,测试数据
数据一:
SQL> select * from t_testcolstart a where a.owner = 'HAHA' and a.object_name = 'HAHA';
已用时间: 00: 00: 00.06
SQL> select * from t_testcolstart a where a.object_name = 'HAHA' and a.owner = 'HAHA';
已用时间: 00: 00: 00.07
SQL> select * from t_testcolstart a where a.owner = 'HAHA';
已用时间: 00: 00: 00.06
SQL> select * from t_testcolstart a where a.object_name = 'HAHA';
已用时间: 00: 00: 00.06
几乎看不出什么差距
测试二:
SQL> select * from t_testcolstart a where a.owner = 'HAHA' and instr(a.object_name,'123') = 1; b1
已用时间: 00: 00: 00.07
SQL> select * from t_testcolstart a where instr(a.object_name,'123') = 1 and a.owner = 'HAHA'; b2
已用时间: 00: 00: 00.06
SQL> select * from t_testcolstart a where instr(a.object_name,'123') = 1; b3