如何优化带or条件的sql----------本文章转自网络，互相学习，互相帮助以下为转帖内容：======================================================================================== ========今天在论坛上看到了一个帖子，问题如下：select * from ccwhere ((a1 ='ffff' and z1='mmmm') or (b1='sss' and z2='nnnn'))and c1 ='ggggg'其中表有30万行数据，返回的数据10行左右，怎样创建index访问最快。

按照别人的说法测试了一下，步骤如下：create table CC(A1 VARCHAR2(5),Z1 VARCHAR2(5),B1 VARCHAR2(5),Z2 VARCHAR2(5),C1 VARCHAR2(5))insert into cc values('dffd','dfsd','fdf','fdsfs','sfds');--重复插入2097152条，对查询时间可能有影响SQL> select count(*) from cc;COUNT(*)----------2097160SQL> set timing onSQL> edit已写入file afiedt.buf1 select * from cc2 where ((a1='ffff'and z1='mmmm') or (b1='sss' and z2='nnnn'))3* and c1='ggggg'--无索引情况下or查询SQL> /A1 Z1 B1 Z2 C1----- ----- ----- ----- -----ffff mmmmm sss nnnn gggggffff mmmmm sss nnnn gggggffff mmmmm sss nnnn gggggffff mmmmm sss nnnn gggggffff mmmmm sss nnnn gggggffff mmmmm sss nnnn gggggffff mmmmm sss nnnn gggggffff mmmmm sss nnnn ggggg已选择8行。

已用时间: 00: 00: 00.21SQL> edit已写入file afiedt.buf1 select * from cc2 where (a1='ffff'and z1='mmmm')3 and c1='ggggg'4 union5 select * from cc6 where (b1='sss' and z2='nnnn')7* and c1='ggggg'--无索引情况下union查询，注意与union all查询结果的区别SQL> /A1 Z1 B1 Z2 C1----- ----- ----- ----- -----ffff mmmmm sss nnnn ggggg已用时间: 00: 00: 00.33SQL> edit已写入file afiedt.buf1 select * from cc2 where (a1='ffff'and z1='mmmm')3 and c1='ggggg'4 union all5 select * from cc6 where (b1='sss' and z2='nnnn')7* and c1='ggggg'--无索引情况下union all查询SQL> /A1 Z1 B1 Z2 C1----- ----- ----- ----- -----ffff mmmmm sss nnnn gggggffff mmmmm sss nnnn gggggffff mmmmm sss nnnn gggggffff mmmmm sss nnnn gggggffff mmmmm sss nnnn gggggffff mmmmm sss nnnn gggggffff mmmmm sss nnnn gggggffff mmmmm sss nnnn ggggg已选择8行。

已用时间: 00: 00: 00.35SQL> create index cc_idx on cc(c1);索引已创建。

已用时间: 00: 00: 11.14SQL> edit已写入file afiedt.buf1 select * from cc2 where ((a1='ffff'and z1='mmmm') or (b1='sss' and z2='nnnn'))3* and c1='ggggg'--有索引or查询，注意，虽然没有列出执行计划，索引肯定用到了。

SQL> /A1 Z1 B1 Z2 C1----- ----- ----- ----- -----ffff mmmmm sss nnnn gggggffff mmmmm sss nnnn gggggffff mmmmm sss nnnn gggggffff mmmmm sss nnnn gggggffff mmmmm sss nnnn gggggffff mmmmm sss nnnn gggggffff mmmmm sss nnnn gggggffff mmmmm sss nnnn ggggg已选择8行。

已用时间: 00: 00: 00.01SQL> edit已写入file afiedt.buf1 select * from cc2 where (a1='ffff'and z1='mmmm')3 and c1='ggggg'4 union5 select * from cc6 where (b1='sss' and z2='nnnn')7* and c1='ggggg'--有索引union查询，注意与union all查询结果的区别SQL> /A1 Z1 B1 Z2 C1----- ----- ----- ----- -----ffff mmmmm sss nnnn ggggg已用时间: 00: 00: 00.00SQL> edit已写入file afiedt.buf1 select * from cc2 where (a1='ffff'and z1='mmmm')3 and c1='ggggg'4 union all5 select * from cc6 where (b1='sss' and z2='nnnn')7* and c1='ggggg'--有索引union all查询SQL> /A1 Z1 B1 Z2 C1----- ----- ----- ----- -----ffff mmmmm sss nnnn gggggffff mmmmm sss nnnn gggggffff mmmmm sss nnnn gggggffff mmmmm sss nnnn gggggffff mmmmm sss nnnn gggggffff mmmmm sss nnnn gggggffff mmmmm sss nnnn gggggffff mmmmm sss nnnn ggggg已选择8行。

已用时间: 00: 00: 00.01SQL>create index CC_IDX2 on CC (A1, Z1);SQL>create index CC_IDX3 on CC (B1, Z2);SQL>set autot onSQL> edit已写入file afiedt.buf1 select * from cc2 where ((a1='ffff'and z1='mmmm') or (b1='sss' and z2='nnnn'))3* and c1='ggggg'--3索引情况下or查询SQL> /A1 Z1 B1 Z2 C1----- ----- ----- ----- -----ffff mmmmm sss nnnn gggggffff mmmmm sss nnnn gggggffff mmmmm sss nnnn gggggffff mmmmm sss nnnn gggggffff mmmmm sss nnnn gggggffff mmmmm sss nnnn gggggffff mmmmm sss nnnn gggggffff mmmmm sss nnnn ggggg已选择8行。

已用时间: 00: 00: 00.60--时间明显比单索引扫描时间长执行计划----------------------------------------------------------Plan hash value: 1540710700---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2 | 40 | 4 (0)| 00:00:01 || 1 | CONCATENATION | | | | | ||* 2 | TABLE ACCESS FULL| CC | 1 | 20 | 2 (0)| 00:00:01 |--注意：从执行计划上可以看到，|* 3 | TABLE ACCESS FULL| CC | 1 | 20 | 2 (0)| 00:00:01 |--索引失效，全表扫描---------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------2 - filter("B1"='sss' AND "Z2"='nnnn' AND "C1"='ggggg')3 - filter("C1"='ggggg' AND "A1"='ffff' AND "Z1"='mmmm' AND(LNNVL("B1"='sss') OR LNNVL("Z2"='nnnn')))Note------ dynamic sampling used for this statement统计信息----------------------------------------------------------0 recursive calls0 db block gets17673 consistent gets405 physical reads0 redo size703 bytes sent via SQL*Net to client400 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)8 rows processedSQL> edit已写入file afiedt.buf1 select * from cc2 where (a1='ffff'and z1='mmmm')3 and c1='ggggg'4 union5 select * from cc6 where (b1='sss' and z2='nnnn')7* and c1='ggggg'--3索引情况下union查询，注意与union all查询结果的区别SQL> /A1 Z1 B1 Z2 C1----- ----- ----- ----- -----ffff mmmmm sss nnnn ggggg已用时间: 00: 00: 00.10--时间明显比单索引扫描时间长执行计划----------------------------------------------------------Plan hash value: 1185376162-----------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 4 | 80 | 10 (60)| 00:00:01 || 1 | SORT UNIQUE | | 4 | 80 | 10 (60)| 00:00:01 || 2 | UNION-ALL | | | | |||* 3 | TABLE ACCESS BY INDEX ROWID| CC | 2 | 40 | 4 (0)| 0 0:00:01 ||* 4 | INDEX RANGE SCAN | CC_IDX2 | 34 | | 3 (0)| 00:00:01 ||* 5 | TABLE ACCESS BY INDEX ROWID| CC | 2 | 40 | 4 (0)| 0 0:00:01 ||* 6 | INDEX RANGE SCAN | CC_IDX3 | 34 | | 3 (0)| 00:00:01 |-----------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------3 - filter("C1"='ggggg')4 - access("A1"='ffff' AND "Z1"='mmmm')5 - filter("C1"='ggggg')6 - access("B1"='sss' AND "Z2"='nnnn')Note------ dynamic sampling used for this statement统计信息----------------------------------------------------------9 recursive calls0 db block gets174 consistent gets7 physical reads0 redo size637 bytes sent via SQL*Net to client400 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client1 sorts (memory)--注意，进行了排序0 sorts (disk)1 rows processedSQL> edit已写入file afiedt.buf1 select * from cc2 where (a1='ffff'and z1='mmmm')3 and c1='ggggg'4 union all5 select * from cc6 where (b1='sss' and z2='nnnn')7* and c1='ggggg'--3索引情况下union all查询SQL> /A1 Z1 B1 Z2 C1----- ----- ----- ----- -----ffff mmmmm sss nnnn gggggffff mmmmm sss nnnn gggggffff mmmmm sss nnnn gggggffff mmmmm sss nnnn gggggffff mmmmm sss nnnn gggggffff mmmmm sss nnnn gggggffff mmmmm sss nnnn gggggffff mmmmm sss nnnn ggggg已选择8行。