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

生产系统,遇到这样一条语句:
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<=1;

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<=10000;

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