组合索引前导列问题请教！

ylw6006

RT，请问在11.2.0.3 版本的oracle，where子句中不包含组合索引的前导列的情况下，执行计划会选择走组合索引吗？

以下是测试过程，请帮忙分析下，谢谢！

一：10g版本
10.2.0.1的数据库，rhel5.4 32bit
SQL> create user test  identified by test;
User created.
SQL> grant connect,resource,select_catalog_role to test;
Grant succeeded.
SQL> conn test/test
Connected.
SQL> create table t1 as select * from dba_objects;
Table created.
SQL> desc t1;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
OWNER                                              VARCHAR2(30)
OBJECT_NAME                                        VARCHAR2(128)
SUBOBJECT_NAME                                     VARCHAR2(30)
OBJECT_ID                                          NUMBER
DATA_OBJECT_ID                                     NUMBER
OBJECT_TYPE                                        VARCHAR2(19)
CREATED                                            DATE
LAST_DDL_TIME                                      DATE
TIMESTAMP                                          VARCHAR2(19)
STATUS                                             VARCHAR2(7)
TEMPORARY                                          VARCHAR2(1)
GENERATED                                          VARCHAR2(1)
SECONDARY                                          VARCHAR2(1)
SQL> create index i_t1_mul1 on t1(object_id,object_type);
Index created.
SQL> set autot traceonly exp
SQL> select * from t1 where object_id < 100;
Execution Plan
----------------------------------------------------------
Plan hash value: 1186876071
---------------------------------------------------------------------------------------
--
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     
|
---------------------------------------------------------------------------------------
--
|   0 | SELECT STATEMENT            |           |    98 | 17346 |     4   (0)| 00:00:01
|
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |    98 | 17346 |     4   (0)| 00:00:01
|
|*  2 |   INDEX RANGE SCAN          | I_T1_MUL1 |    98 |       |     2   (0)| 00:00:01
|
---------------------------------------------------------------------------------------
--
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"<100)
Note
-----
   - dynamic sampling used for this statement

SQL> select * from t1 where object_type='INDEX';
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   531 | 93987 |   159   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T1   |   531 | 93987 |   159   (2)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_TYPE"='INDEX')
Note
-----
   - dynamic sampling used for this statement

SQL> set autot off
SQL> select count(*) from t1;
  COUNT(*)
----------
     50380
SQL> select object_type,count(*) from t1  group by object_type;
OBJECT_TYPE           COUNT(*)
------------------- ----------
CONSUMER GROUP               5
INDEX PARTITION            276
SEQUENCE                   143
QUEUE                       27
SCHEDULE                     1
TABLE PARTITION            128
RULE                         4
JAVA DATA                  306
PROCEDURE                   85
OPERATOR                    57
LOB PARTITION                1
OBJECT_TYPE           COUNT(*)
------------------- ----------
WINDOW                       2
LOB                        566
PACKAGE                    848
PACKAGE BODY               791
LIBRARY                    150
RULE SET                    19
PROGRAM                     12
TYPE BODY                  173
CONTEXT                      5
JAVA RESOURCE              770
XML SCHEMA                  26
OBJECT_TYPE           COUNT(*)
------------------- ----------
TRIGGER                    171
JOB CLASS                    2
UNDEFINED                    6
DIRECTORY                    9
DIMENSION                    5
MATERIALIZED VIEW            2
TABLE                     1636
INDEX                     1800
SYNONYM                  20026
VIEW                      3671
FUNCTION                   270
OBJECT_TYPE           COUNT(*)
------------------- ----------
WINDOW GROUP                 1
JAVA CLASS               16417
INDEXTYPE                   10
CLUSTER                     10
TYPE                      1926
RESOURCE PLAN                3
EVALUATION CONTEXT          14
JOB                          6
41 rows selected.
SQL> set autot traceonly exp
SQL> select /*+index(i_t1_mul1)*/ * from t1 where object_type='INDEX';
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   531 | 93987 |   159   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T1   |   531 | 93987 |   159   (2)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_TYPE"='INDEX')
Note
-----
   - dynamic sampling used for this statement

二：11g上测试，11.2.0.3 windows enterprise editon 32bit
SQL> create index idx_t1_mul5 on t1(password,signinid);
Index created.
SQL> select * from (select signinid,count(*) from t1 group by signinid) where rownum<10;
SIGNINID                                                       COUNT(*)
------------------------------------------------------------ ----------
000000yu                                                              1
0000410265269                                                         1
00006789                                                              1
00009746                                                              1
000113                                                                1
0001mwm                                                               1
0002081                                                               1
000317                                                                1
00032156688                                                           1
9 rows selected.
SQL> select * from t1 where signinid='000000yu';
Execution Plan
----------------------------------------------------------
Plan hash value: 2246799375
-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)|Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |  1296 |  3706   (1)|00:00:45 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1          |     1 |  1296 |  3706   (1)|00:00:45 |
|*  2 |   INDEX SKIP SCAN           | IDX_T1_MUL5 |     1 |       |  3705   (1)|00:00:45 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("SIGNINID"='000000yu')
       filter("SIGNINID"='000000yu')
SQL> select * from t1 where createddate < sysdate and signinid='000000yu';
Execution Plan
----------------------------------------------------------
Plan hash value: 2246799375
-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)|Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |  1296 |  3706   (1)|00:00:45 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T1          |     1 |  1296 |  3706   (1)|00:00:45 |
|*  2 |   INDEX SKIP SCAN           | IDX_T1_MUL5 |     1 |       |  3705   (1)|00:00:45 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("CREATEDDATE"<SYSDATE@!)
   2 - access("SIGNINID"='000000yu')
       filter("SIGNINID"='000000yu')

字段类型：
PASSWORD                                           RAW(255)
SIGNINID                                                 VARCHAR2(20 CHAR)

备注：在11g上做测试一，同样执行计划不选择走组合索引！

求真相！

vincent

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

SQL> create table wangxi1 as select * from dba_objects;

Table created.

SQL> update wangxi1 set status='INVALID' where owner='WANGXI';

5 rows updated.

SQL> commit;

Commit complete.

SQL> create index ind_wangxi1 on wangxi1(status);

Index created.

SQL> exec dbms_stats.gather_table_stats('wangxi','wangxi1',cascade=>true, method_opt=>'FOR ALL COLUMNS SIZE 2');

PL/SQL procedure successfully completed.

SQL> set autot traceonly exp
SQL> set linesize 140 pagesize 1400               
SQL> select * from wangxi1 where status='INVALID';

Execution Plan
----------------------------------------------------------
Plan hash value: 3434130650

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |    10 |   930 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| WANGXI1     |    10 |   930 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_WANGXI1 |    10 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("STATUS"='INVALID')

SQL>

ylw6006

11g:
SQL> exec dbms_stats.gather_table_stats('HR,'T11',CASCADE=>TRUE,method_opt=>'FOR ALL COLUMNS SIZE 2');
PL/SQL procedure successfully completed.

SQL>  select * from t11 where object_type='INDEX';
Execution Plan
----------------------------------------------------------
Plan hash value: 3387822805
--------------------------------------------------------------------------------
----------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)|
Time     |
--------------------------------------------------------------------------------
----------
|   0 | SELECT STATEMENT            |            |  1757 |   168K|   294   (0)|
00:00:04 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T11        |  1757 |   168K|   294   (0)|
00:00:04 |
|*  2 |   INDEX SKIP SCAN           | I_T11_MUL1 |  1757 |       |   265   (0)|
00:00:04 |
--------------------------------------------------------------------------------
----------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_TYPE"='INDEX')
       filter("OBJECT_TYPE"='INDEX')

10g:
SQL> exec dbms_stats.gather_table_stats('TEST','T1',CASCADE=>TRUE,METHOD_OPT=>'FOR ALL COLUMNS SIZE 2')
PL/SQL procedure successfully completed.
SQL> set autot traceonly exp

SQL> select * from t1 where object_type='INDEX';
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 12595 |  1143K|   160   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T1   | 12595 |  1143K|   160   (2)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_TYPE"='INDEX')

根据刘总指示: 收集完直方图信息后执行计划正常，感谢指导！
看来11g在这方面确实比10g先进了许多！

[ 本帖最后由 ylw6006 于 2012-7-9 15:40 编辑 ]

Maclean Liu(刘相兵

请参考这篇文章

CBO为什么不走索引？

http://www.oracledatabase12g.com ... BC%95%EF%BC%9F.html