Union all 与 or

“通常情况下, 用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  /*1^ST*/

  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   /*2^ND*/

  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  /*4^th*/

  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)明显比返回记录最少的索引放在前面的查询(4^th)的效果好得多,主要对比recursive calls和consistent gets.

2th:

recursive calls:1

consistent gets:723

4^th:

recursive calls:2148

consistent gets:1342

而用union all呢(3th)跟2th差不多.

Recursive calls:1

Consistent gets:910

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13387766/viewspace-520743/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13387766/viewspace-520743/