ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle联合索引在CBO下的分析

oracle联合索引在CBO下的分析

原创 Linux操作系统 作者:paulyibinyi 时间:2009-01-08 22:20:37 0 删除 编辑

SQL> set autot on
SQL> desc yibin_test;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------

 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                               NOT NULL VARCHAR2(128)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                 NOT NULL NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(18)
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)
对OBJECT_NAME  ,OBJECT_ID 建立主键联合索引,object_name在前

SQL> select index_name,table_name,column_name from user_ind_columns;

INDEX_NAME                     TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
-------------------------------------------------------------------------------

IDX_TEST                       YIBIN_TEST
OBJECT_ID

IDX_TEST                       YIBIN_TEST
OBJECT_NAME

并且分析索引

SQL> analyze table object_test compute statistics;

Table analyzed


看下面 两个字段都列上时,不管后面字段摆放顺序 都会用上 INDEX (UNIQUE SCAN) 索引,

后面两个字段对调位置

SQL> select * from yibin_test where  object_id=97 and object_name='access$' ;

未选定行


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=1 Bytes=76)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'YIBIN_TEST' (Cost=2 Card
          =1 Bytes=76)

   2    1     INDEX (UNIQUE SCAN) OF 'IDX_TEST' (UNIQUE) (Cost=1 Card=
          1)

 

 

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          1  physical reads
          0  redo size
        942  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

没调换位置

SQL> select * from yibin_test where  object_name='access$' and object_id=97
  2  ;

未选定行


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=1 Bytes=76)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'YIBIN_TEST' (Cost=2 Card
          =1 Bytes=76)

   2    1     INDEX (UNIQUE SCAN) OF 'IDX_TEST' (UNIQUE) (Cost=1 Card=
          1)

 

 

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        942  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL>

单独把object_name列出来 会用上索引范围扫描

SQL> select * from yibin_test where  object_name='access$' ;

未选定行


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=3 Card=1 Bytes=76)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'YIBIN_TEST' (Cost=3 Card
          =1 Bytes=76)

   2    1     INDEX (RANGE SCAN) OF 'IDX_TEST' (UNIQUE) (Cost=2 Card=1
          )

 

 

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        942  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

而只用后面这个时,不会用上索引,走全表扫描

SQL> select * from yibin_test where  object_id=97 ;

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------

SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- ------------------
CREATED    LAST_DDL_T TIMESTAMP           STATUS  T G S
---------- ---------- ------------------- ------- - - -
SYS
ACCESS$
                                       97             97 TABLE
26-10月-08 26-10月-08 2008-10-26:01:03:06 VALID   N N N

 

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=10 Card=1 Bytes=76)
   1    0   TABLE ACCESS (FULL) OF 'YIBIN_TEST' (Cost=10 Card=1 Bytes=
          76)

 

 

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         83  consistent gets
          0  physical reads
          0  redo size
       1167  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

 

总结:根据上面的分析,得出以下结论

CBO下联合索引:

要是where 后面联合索引字段都列上,不管先后顺序都会用上索引

要是where后面只列单独一个字段,那只有按照建立索引字段顺序排在最前的会用上索引,后面的不会用上

所以要根据应用来结合,建立合适的索引

经过测试 发现在rbo下也是和上面规律一样

如果大家对这个结果有问题,请提出来

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7199859/viewspace-533331/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
学习数据库

注册时间:2007-12-11

  • 博文量
    903
  • 访问量
    6606037