前天检查数据库,发现一天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个.
--可以发现,第1个执行计划走的是 INDEX FULL SCAN (MIN/MAX),很明显第1个执行计划逻辑读的数量取决于扫描索引的undrug_code的最大值到出现
满足SUBSTR (undrug_code, 1,1) = 'F'的键值停止的块数.而第2个执行计划走的是INDEX RANGE SCAN,逻辑读的数量取决于扫描索引的undrug_code
like 'F%'满足此条件的块数.
知道这些,加上取最大值的语句有一些特殊,根据以上分析,如果语句修改如下就能减少逻辑读:
不过作为第3方,不能修改语句呢?确实不好做,不过如果是11GR2,可以建立一个函数索引:
CREATE UNIQUE INDEX if_UNDRUG_INFO_undrug_code ON FSHIS.UNDRUG_INFO (substr(undrug_code,1,1),UNDRUG_CODE);
--如果这样代价有点大!
--可以发现可以使用函数索引,并且逻辑读减少.10G,留给大家测试,^_^.
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
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
SQL_ID 2yjg9nmgb6bt8, 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 | | 474 |
|* 3 | INDEX RANGE SCAN (MIN/MAX)| PK_UNDRUG_INFO | 474 |
----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("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
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,留给大家测试,^_^.
