17,086
社区成员
发帖
与我相关
我的任务
分享
create index idx_my_big_table on my_big_table(a, b);
select *
from my_big_table t1
where t1.a = 55654
union
select *
from my_big_table t1
where t1.b = 'b55654'
select *
from my_big_table t1
where t1.a = 55654
or t1.b = 'b55654';
select *
from my_big_table t1
where t1.a = 55654
union
select *
from my_big_table t1
where t1.b = 'b55654'
create table my_big_table as select level a, cast('b' || level as varchar2(8)) b from dual connect by level <= 200000;
create index idx_my_big_table on my_big_table(a, b);
下面两个查询是等效的,但是都不会走索引
select *
from my_big_table t1
where t1.a = 55654
or t1.b = 'b55654';
select *
from my_big_table t1
where t1.a = 55654
union
select *
from my_big_table t1
where t1.b = 'b55654'
再换一个正确的索引试试
create table my_big_table2 as select level a, cast('b' || level as varchar2(8)) b from dual connect by level <= 200000;
create index idx_my_big_table2_1 on my_big_table2(a);
create index idx_my_big_table2_2 on my_big_table2(b);
又可以看到优化器自作主张给转成了bitmap。
用hint强制修改执行计划
从这个执行计划可以看出确实改成union效率更高,但是优化器会自动帮你做这一步,并且这是根据统计信息做的智能判断,无需手动改成union。
再来一组数据奇葩的
create table my_big_table3 as select mod(level, 2) a, cast('b' || level as varchar2(8)) b from dual connect by level <= 200000;
create index idx_my_big_table3_1 on my_big_table3(a);
create index idx_my_big_table3_2 on my_big_table3(b);
select * from my_big_table3 t1 where t1.a = 1 or t1.b = 'b1235'; 或者说只要带上 t1.a = 1 这个条件,你再怎么折腾它都不会走索引。
再用我刚才工作时写的一个sql举个例子
select *
from aaa.tab1 t1
where t1.aaaa_date = '20200504'
and t1.day_id = substr('20200504', 7, 2)
and t1.aaaa_no = '01'
and (
(#{levelType} = '1')
or (#{levelType} = '2' and #{saleId} = t1.col1)
or (#{levelType} = '3' and #{saleId} = t1.col2)
)
aaa.tab1表一个分区虽然有40w数据,但是我本来就是要全表查询,为什么要改写?就算我只查几条数据也不会改写,因为这张表上没有索引,如果没有索引,上面所说的全部都会不成立