[20130319]一条sql语句的优化.txt
生产系统,遇到这样一条语句:
SELECT MAX (LENGTH (pe_id)) FROM pe_master_index WHERE SUBSTR (pe_id, 1, 2) = 'TJ';
--真不知道开发人员如何想的,写出这样的语句.字段pe_id是主键.
--数据库版本
--建立 SUBSTR (pe_id, 1, 2),LENGTH (pe_id),这样的函数索引在这个版本下,会使用新建的索引,但是执行计划还是INDEX FAST FULL
--SCAN.优化效果不明显.好像10.2.0.4可以很好的使用这样的索引,并且执行计划走INDEX RANGE SCAN (MIN/MAX).
自己在11G下做一个测试:
--11G下可以使用这样的索引走INDEX RANGE SCAN (MIN/MAX).
参看
http://space.itpub.net/267265/viewspace-715313
http://space.itpub.net/267265/viewspace-715314
http://space.itpub.net/267265/viewspace-715315
http://space.itpub.net/267265/viewspace-715390
生产系统,遇到这样一条语句:
SELECT MAX (LENGTH (pe_id)) FROM pe_master_index WHERE SUBSTR (pe_id, 1, 2) = 'TJ';
--真不知道开发人员如何想的,写出这样的语句.字段pe_id是主键.
--数据库版本
SQL> select * from v$version where rownum
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
1 row selected.
SQL> SELECT MAX (LENGTH (pe_id)) FROM pe_master_index WHERE SUBSTR (pe_id, 1, 2) = 'TJ';
MAX(LENGTH(PE_ID))
------------------
10
SQL> @dpc ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID awfcmwrca41fc, child number 0
-------------------------------------
SELECT MAX (LENGTH (pe_id)) FROM pe_master_index WHERE SUBSTR (pe_id,1, 2) = 'TJ'
Plan hash value: 2553983512
-------------------------------------------------------------
| Id | Operation | Name | E-Rows |
-------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 |
|* 2 | INDEX FAST FULL SCAN| PK_PE_MASTER_INDEX | 7053 |
-------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_FFS(@"SEL$1" "PE_MASTER_INDEX"@"SEL$1"
("PE_MASTER_INDEX"."PE_ID"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(SUBSTR("PE_ID",1,2)='TJ')
--建立 SUBSTR (pe_id, 1, 2),LENGTH (pe_id),这样的函数索引在这个版本下,会使用新建的索引,但是执行计划还是INDEX FAST FULL
--SCAN.优化效果不明显.好像10.2.0.4可以很好的使用这样的索引,并且执行计划走INDEX RANGE SCAN (MIN/MAX).
自己在11G下做一个测试:
create table t1 as select 'tj'||lpad(rownum,8,'0') pe_id,lpad('x',100,'x') vc from dual connect by level
create unique index scott.pk_t1 on t1(pe_id) ;
alter table t1 add constraint pk_t1 primary key (pe_id);
exec dbms_stats.gather_table_stats(USER,'T1',METHOD_OPT => 'FOR ALL COLUMNS SIZE 1 ',No_Invalidate => FALSE);
SQL> SELECT MAX (LENGTH (pe_id)) FROM t1 WHERE SUBSTR (pe_id, 1, 2) = 'tj';
MAX(LENGTH(PE_ID))
------------------
10
SQL> @dpc '' ''
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
SQL_ID 02mdzdry4jpt9, child number 0
-------------------------------------
SELECT MAX (LENGTH (pe_id)) FROM t1 WHERE SUBSTR (pe_id, 1, 2) = 'tj'
Plan hash value: 1953966236
-------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
-------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 11 (100)|
| 1 | SORT AGGREGATE | | 1 | |
|* 2 | INDEX FAST FULL SCAN| PK_T1 | 100 | 11 (10)|
-------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(SUBSTR("PE_ID",1,2)='tj')
SQL> create index if_t1_pe_id on t1(SUBSTR (pe_id, 1, 2),LENGTH (pe_id)) ;
SQL> SELECT MAX (LENGTH (pe_id)) FROM t1 WHERE SUBSTR (pe_id, 1, 2) = 'tj';
MAX(LENGTH(PE_ID))
------------------
10
SQL> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 02mdzdry4jpt9, child number 0
-------------------------------------
SELECT MAX (LENGTH (pe_id)) FROM t1 WHERE SUBSTR (pe_id, 1, 2) = 'tj'
Plan hash value: 2812640901
--------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 (100)|
| 1 | SORT AGGREGATE | | 1 | |
| 2 | FIRST ROW | | 1 | 2 (0)|
|* 3 | INDEX RANGE SCAN (MIN/MAX)| IF_T1_PE_ID | 1 | 2 (0)|
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."SYS_NC00003$"='tj')
--11G下可以使用这样的索引走INDEX RANGE SCAN (MIN/MAX).
参看
http://space.itpub.net/267265/viewspace-715313
http://space.itpub.net/267265/viewspace-715314
http://space.itpub.net/267265/viewspace-715315
http://space.itpub.net/267265/viewspace-715390
