ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [20120319]一条sql语句的优化.txt

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

原创 Linux操作系统 作者:lfree 时间:2012-03-20 15:38:28 0 删除 编辑
前天检查数据库,发现一天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<'G';

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<'G'

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"<'G')
       filter(SUBSTR("UNDRUG_CODE",1,1)='F')

或者这样:
SELECT MAX (undrug_code) FROM undrug_info WHERE undrug_code >='F' and  undrug_code<'G';

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
SQL_ID  ach8g0ct3r7k0, child number 0
-------------------------------------
SELECT MAX (undrug_code) FROM undrug_info WHERE undrug_code >='F' and undrug_code<'G'
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"<'G' AND "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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2286
  • 访问量
    6026285