Home > fundamental > non-leading index column

non-leading index column

对于复合索引,如果predicate条件只有非前导列(non-leading index column),并且查询列不在index中的话,oracle选择的路径要么index skip scan,要么全表扫。一直都觉得cbo在这一块做得不是特别好,因为如果非前导列选择性很好的话,可以考虑走index full scan来提高效率。而且这种情况也很常见吧。

SQL>--Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
SQL> create table n2 (id number,id2 number,name1 varchar(20),name2 char(2000));
Table created.

SQL> insert into n2 select level,mod(level,2),'qif'||level,'qifeng' from dual connect by level<=800000;

800000 rows created.

SQL> create index n2_ind on n2(id,name1);

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'N2',estimate_percent=>100,cascade=>true);

PL/SQL procedure successfully completed.

SQL> set serverout off

SQL> select /*+gather_plan_statistics*/id2 from n2 where name1='qif1';

ID2

----------

1

SQL> set timing on

SQL> select /*+gather_plan_statistics*/id2 from n2 where name1='qif1';

ID2

----------

1

Elapsed:00:00:01.45

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID  a6rf4gzr9raaq, child number 0

-------------------------------------

select /*+gather_plan_statistics*/id2 from n2 where name1='qif1'

Plan hash value: 1313880268

---------------------------------------------------------------------------------------------

| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |

---------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |      1 |        |      1 |00:00:00.01 |     114K|    114K|

|*  1 |  TABLE ACCESS FULL| N2   |      1 |      1 |      1 |00:00:00.01 |     114K|    114K|

---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("NAME1"='qif1')

18 rows selected.

Elapsed: 00:00:00.45

SQL> select /*+gather_plan_statistics index(n2)*/id2 from n2 where name1='qif1';

ID2

----------

1

Elapsed: 00:00:00.15

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID  cbd4pqpp950fu, child number 0

-------------------------------------

select /*+gather_plan_statistics index(n2)*/id2 from n2 where

name1='qif1'

Plan hash value: 663914138

---------------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |

---------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |        |      1 |        |      1 |00:00:00.01 |    1430 |      1 |

|   1 |  TABLE ACCESS BY INDEX ROWID| N2     |      1 |      1 |      1 |00:00:00.01 |    1430 |      1 |

|*  2 |   INDEX FULL SCAN           | N2_IND |      1 |      1 |      1 |00:00:00.01 |    1429 |      1 |

---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("NAME1"='qif1')

filter("NAME1"='qif1')

21 rows selected.

Elapsed: 00:00:00.04

SQL> select /*+gather_plan_statistics index(n2)*/id2 from n2 where name1='qif800000';

ID2

----------

0

Elapsed: 00:00:00.14

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID  58v3ua7hwpa68, child number 0

-------------------------------------

select /*+gather_plan_statistics index(n2)*/id2 from n2 where

name1='qif800000'

Plan hash value: 663914138

------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |        |      1 |        |      1 |00:00:00.14 |    1428 |

|   1 |  TABLE ACCESS BY INDEX ROWID| N2     |      1 |      1 |      1 |00:00:00.14 |    1428 |

|*  2 |   INDEX FULL SCAN           | N2_IND |      1 |      1 |      1 |00:00:00.14 |    1427 |

------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("NAME1"='qif800000')

filter("NAME1"='qif800000')

21 rows selected.

Elapsed: 00:00:00.03
Categories: fundamental Tags:
  1. No comments yet.
  1. No trackbacks yet.