[20120319]一条sql语句的优化.txt

前天检查数据库,发现一天sql语句执行如下:
SELECT MAX (undrug_code)   FROM undrug_info  WHERE SUBSTR (undrug_code, 1, 1) = 'F';

undrug_code是表undrug_info的主键.开始看见这个语句,感觉这样写不好,我想像的执行计划,全扫描索引,然后sort aggregate,找到最大值.

不如这样写:
SELECT MAX (undrug_code)   FROM undrug_info  WHERE undrug_code like 'F%';

对比两个执行计划,发现前面的语句逻辑读更少.仅仅20个逻辑读,而第2个语句逻辑读23个.

PLAN_TABLE_OUTPUT

-------------------------------------

SQL_ID  akhuqz90jahvq, child number 0

-------------------------------------

SELECT MAX (undrug_code) FROM undrug_info WHERE SUBSTR (undrug_code, 1,1) = 'F'

Plan hash value: 1995159169

---------------------------------------------------------------

| Id  | Operation                   | Name           | E-Rows |

---------------------------------------------------------------

|   1 |  SORT AGGREGATE             |                |      1 |

|   2 |   FIRST ROW                 |                |    234 |

|*  3 |    INDEX FULL SCAN (MIN/MAX)| PK_UNDRUG_INFO |    234 |

---------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - filter(SUBSTR("UNDRUG_CODE",1,1)='F')

PLAN_TABLE_OUTPUT

-------------------------------------

SQL_ID  bpb7sh9xq2rqw, child number 0

-------------------------------------

SELECT MAX (undrug_code) FROM undrug_info WHERE undrug_code like 'F%'

Plan hash value: 4281577230

-----------------------------------------------------

| Id  | Operation         | Name           | E-Rows |

-----------------------------------------------------

|   1 |  SORT AGGREGATE   |                |      1 |

|*  2 |   INDEX RANGE SCAN| PK_UNDRUG_INFO |    475 |

-----------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("UNDRUG_CODE" LIKE 'F%')

       filter("UNDRUG_CODE" LIKE 'F%')

--可以发现,第1个执行计划走的是 INDEX FULL SCAN (MIN/MAX),很明显第1个执行计划逻辑读的数量取决于扫描索引的undrug_code的最大值到出现
满足SUBSTR (undrug_code, 1,1) = 'F'的键值停止的块数.而第2个执行计划走的是INDEX RANGE SCAN,逻辑读的数量取决于扫描索引的undrug_code
like 'F%'满足此条件的块数.

知道这些,加上取最大值的语句有一些特殊,根据以上分析,如果语句修改如下就能减少逻辑读:

SELECT MAX (undrug_code)   FROM undrug_info  WHERE SUBSTR (undrug_code, 1, 1) = 'F' and undrug_code

PLAN_TABLE_OUTPUT

-------------------------------------

SQL_ID  8ndrgww40s2kc, child number 0

-------------------------------------

SELECT MAX (undrug_code)   FROM undrug_info  WHERE SUBSTR (undrug_code,

1, 1) = 'F' and undrug_code

Plan hash value: 550870910

----------------------------------------------------------------

| Id  | Operation                    | Name           | E-Rows |

----------------------------------------------------------------

|   1 |  SORT AGGREGATE              |                |      1 |

|   2 |   FIRST ROW                  |                |      5 |

|*  3 |    INDEX RANGE SCAN (MIN/MAX)| PK_UNDRUG_INFO |      5 |

----------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - access("UNDRUG_CODE"

       filter(SUBSTR("UNDRUG_CODE",1,1)='F')

或者这样:

SELECT MAX (undrug_code) FROM undrug_info WHERE undrug_code >='F' and  undrug_code

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------

SQL_ID  ach8g0ct3r7k0, child number 0

-------------------------------------

SELECT MAX (undrug_code) FROM undrug_info WHERE undrug_code >='F' and undrug_code

Plan hash value: 550870910

----------------------------------------------------------------

| Id  | Operation                    | Name           | E-Rows |

----------------------------------------------------------------

|   1 |  SORT AGGREGATE              |                |      1 |

|   2 |   FIRST ROW                  |                |    475 |

|*  3 |    INDEX RANGE SCAN (MIN/MAX)| PK_UNDRUG_INFO |    475 |

----------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - access("UNDRUG_CODE"='F')

或者这样:

SELECT MAX (undrug_code) FROM undrug_info WHERE undrug_code >='F' and  undrug_code<='F'||chr(255);

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------

SQL_ID  2yjg9nmgb6bt8, child number 0

-------------------------------------

SELECT MAX (undrug_code) FROM undrug_info WHERE undrug_code >='F' and

undrug_code<='F'||chr(255)

Plan hash value: 550870910

----------------------------------------------------------------

| Id  | Operation                    | Name           | E-Rows |

----------------------------------------------------------------

|   1 |  SORT AGGREGATE              |                |      1 |

|   2 |   FIRST ROW                  |                |    474 |

|*  3 |    INDEX RANGE SCAN (MIN/MAX)| PK_UNDRUG_INFO |    474 |

----------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - access("UNDRUG_CODE"<='F?' AND "UNDRUG_CODE">='F')

不过作为第3方,不能修改语句呢?确实不好做,不过如果是11GR2,可以建立一个函数索引:
CREATE UNIQUE INDEX if_UNDRUG_INFO_undrug_code ON FSHIS.UNDRUG_INFO (substr(undrug_code,1,1),UNDRUG_CODE);

--如果这样代价有点大!

SQL> select * from v$version ;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

SQL> CREATE TABLE test1 AS SELECT ROWNUM ID ,DBMS_RANDOM.STRING('U',10)||LPAD(ROWNUM,6,0) code ,LPAD('a',60,'a') vc FROM DUAL CONNECT BY LEVEL<=10000;

SQL> CREATE UNIQUE INDEX pk_test1 ON TEST1 (CODE) ;

SQL> ALTER TABLE TEST1 ADD CONSTRAINT pk_test1 PRIMARY KEY (CODE);

SQL> exec dbms_stats.gather_table_stats(ownname=>USER, tabname=>'test1');

SQL> set autotrace trace

SQL> select max(code) from test1 where substr(code,1,1)='F';

Execution Plan

----------------------------------------------------------

Plan hash value: 1164682198

----------------------------------------------------------------------------------------

| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |          |     1 |    17 |     2   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE             |          |     1 |    17 |            |          |

|   2 |   FIRST ROW                 |          |     1 |    17 |     2   (0)| 00:00:01 |

|*  3 |    INDEX FULL SCAN (MIN/MAX)| PK_TEST1 |     1 |    17 |     2   (0)| 00:00:01 |

----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - filter(SUBSTR("CODE",1,1)='F')

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

         31  consistent gets

          0  physical reads

          0  redo size

        541  bytes sent via SQL*Net to client

        520  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

SQL> CREATE UNIQUE INDEX if_test1_code ON TEST1 (substr(code,1,1),CODE) ;

Index created.

SQL> select max(code) from test1 where substr(code,1,1)='F';

Execution Plan

----------------------------------------------------------

Plan hash value: 1185817353

----------------------------------------------------------------------------------------------

| Id  | Operation                    | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |               |     1 |    20 |     2   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE              |               |     1 |    20 |            |          |

|   2 |   FIRST ROW                  |               |     1 |    20 |     2   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN (MIN/MAX)| IF_TEST1_CODE |     1 |    20 |     2   (0)| 00:00:01 |

----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - access(SUBSTR("CODE",1,1)='F')

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

          2  consistent gets

          0  physical reads

          0  redo size

        541  bytes sent via SQL*Net to client

        520  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

--可以发现可以使用函数索引,并且逻辑读减少.10G,留给大家测试,^_^.

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