“通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描. 注意, 以上规则只针对多个索引列有效. 如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低.”
“如果你坚持要用OR, 那就需要返回记录最少的索引列写在最前面.”
以上两段话是别人总结SQL性能优化的一段话,通过测试我觉得上面的总结有点不对,也许是版本问题吧.
1. 对索引列用OR不会造成全表扫描
2. 用union all 替代or不一定高效
3. 用OR的话,应该是把返回记录最多的记录写在前面
以下是我的测试:ORACLE版本(10.1)
在表PRD的CODE列上有唯一性索引,在GRP上有唯一性索引
SQL> SELECT COUNT(CODE)
2 FROM PRD
3 WHERE CODE='03AC00051';
COUNT(CODE)
-----------
1
已用时间: 00: 00: 00.07
SQL> SELECT COUNT(CODE)
2 FROM PRD
3 WHERE GRP='MIX';
COUNT(CODE)
-----------
2844
已用时间: 00: 00: 00.07
SQL> set timing on;
SQL> set autotrace traceonly;
SQL> select code,lname,cname /*1ST*/
2 from prd
3 where code='03AC00051'
4 or grp='MIX';
已选择2845行。
已用时间: 00: 00: 01.07
执行计划
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=ALL_ROWS (Cost=38 Card=67 Bytes=5
695)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'PRD' (CLUSTER) (Cost=38
Card=67 Bytes=5695)
2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP OR
4 3 BITMAP CONVERSION (FROM ROWIDS)
5 4 INDEX (RANGE SCAN) OF 'PRD$IDX1' (INDEX) (Cost=1)
6 3 BITMAP CONVERSION (FROM ROWIDS)
7 6 INDEX (RANGE SCAN) OF 'PRD$PK' (INDEX) (Cost=1)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
723 consistent gets
408 physical reads
0 redo size
273622 bytes sent via SQL*Net to client
2591 bytes received via SQL*Net from client
191 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2845 rows processed
SQL> select code,lname,cname /*2ND*/
2 from prd
3 where grp='MIX'
4 or
5 code='03AC00051';
已选择2845行。
已用时间: 00: 00: 00.53
执行计划
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=ALL_ROWS (Cost=38 Card=67 Bytes=5
695)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'PRD' (CLUSTER) (Cost=38
Card=67 Bytes=5695)
2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP OR
4 3 BITMAP CONVERSION (FROM ROWIDS)
5 4 INDEX (RANGE SCAN) OF 'PRD$IDX1' (INDEX) (Cost=1)
6 3 BITMAP CONVERSION (FROM ROWIDS)
7 6 INDEX (RANGE SCAN) OF 'PRD$PK' (INDEX) (Cost=1)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
723 consistent gets
0 physical reads
0 redo size
273622 bytes sent via SQL*Net to client
2591 bytes received via SQL*Net from client
191 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2845 rows processed
SQL> select code,lname,cname /*3th*/
2 from prd
3 where code='03AC00051'
4 union all
5 select code,lname,cname
6 from prd
7 where grp='MIX';
已选择2845行。
已用时间: 00: 00: 00.59
执行计划
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=ALL_ROWS (Cost=26 Card=67 Bytes=5
690)
1 0 UNION-ALL
2 1 TABLE ACCESS (CLUSTER) OF 'PRD' (CLUSTER) (Cost=2 Card=1
Bytes=80)
3 2 INDEX (UNIQUE SCAN) OF 'PRD$CLUSTER$IDX' (INDEX) (Cost
=1 Card=1)
4 1 TABLE ACCESS (BY INDEX ROWID) OF 'PRD' (CLUSTER) (Cost=2
4 Card=66 Bytes=5610)
5 4 INDEX (RANGE SCAN) OF 'PRD$IDX1' (INDEX) (Cost=1 Card=
66)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
910 consistent gets
1 physical reads
0 redo size
273622 bytes sent via SQL*Net to client
2591 bytes received via SQL*Net from client
191 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2845 rows processed
SQL>
SQL> select code,lname,cname /*4th*/
2 from prd
3 where code='03AC00051'
4 or grp='MIX';
已选择2845行。
已用时间: 00: 00: 00.75
执行计划
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=ALL_ROWS (Cost=38 Card=67 Bytes=5
695)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'PRD' (CLUSTER) (Cost=38
Card=67 Bytes=5695)
2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP OR
4 3 BITMAP CONVERSION (FROM ROWIDS)
5 4 INDEX (RANGE SCAN) OF 'PRD$IDX1' (INDEX) (Cost=1)
6 3 BITMAP CONVERSION (FROM ROWIDS)
7 6 INDEX (RANGE SCAN) OF 'PRD$PK' (INDEX) (Cost=1)
统计信息
----------------------------------------------------------
2148 recursive calls
0 db block gets
1342 consistent gets
0 physical reads
0 redo size
273622 bytes sent via SQL*Net to client
2591 bytes received via SQL*Net from client
191 SQL*Net roundtrips to/from client
23 sorts (memory)
0 sorts (disk)
2845 rows processed
第一次执行因为很多数据不在缓冲区中,有708 phisical reads是可以理解的,主要对比第二三四次执行,可以看到把返回记录最多的索引放在前面的查询(2th)明显比返回记录最少的索引放在前面的查询(4th)的效果好得多,主要对比recursive calls和consistent gets.
2th:
recursive calls:1
consistent gets:723
4th:
recursive calls:2148
consistent gets:1342
而用union all呢(3th)跟2th差不多.
Recursive calls:1
Consistent gets:910
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13387766/viewspace-520743/，如需转载，请注明出处，否则将追究法律责任。
