[20120319]一條sql語句的優化.txt

lfree發表於2012-03-20
SQL優化
前天檢查資料庫,發現一天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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章