索引小测试

一.索引的隐式转换
我们都知道索引一旦做了隐式转换，就会失效，但是请注意下面这两个例子：
先把结论放上来：number型，隐式转换相当于a =to_number('1')；反之，如果是varchar2类型，a=1;则在左边转换
15:44:48 sys@FOX> desc t
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ----------------
 A                                                     NOT NULL NUMBER
 B                                                     NOT NULL VARCHAR2(10)
 C                                                              DATE
create index idx_a on t(a);

理论上where a ='1'相当于隐式转换了，可是结果却走了索引
15:43:48 sys@FOX> select * from t where a ='1';

128 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=64 Bytes=1088)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=64 Bytes=1088)
   2    1     INDEX (RANGE SCAN) OF 'IDX_A' (NON-UNIQUE) (Cost=1 Card=64)


16:39:44 sys@FOX> analyze table t delete statistics;

Table analyzed.

这次走rule方式，照旧能走索引
16:40:45 sys@FOX> select * from t where a ='1';

128 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T'
   2    1     INDEX (RANGE SCAN) OF 'IDX_A' (NON-UNIQUE)

16:51:40 sys@FOX> create table s as  select * from t where b='1';

Table created.

16:52:45 sys@FOX> create index idx_s_b on s(b);

Index created.

16:53:09 sys@FOX> select * from s where b='1';


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'S'
   2    1     INDEX (RANGE SCAN) OF 'IDX_S_B' (NON-UNIQUE)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        480  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
注意这里，b是varchar2型，做了隐式转换b=1相当于to_number(b)=1,走了全表
16:53:16 sys@FOX> select * from s where b=1;


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'S'




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        480  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

二.复合索引的顺序问题
drop index idx_a
drop index idx_b
create index on t(a,b)
这里查询条件不按复合列的顺序走，照旧能走索引
16:55:19 sys@FOX> select * from t where b='x' and a=1;

64 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T'
   2    1     INDEX (RANGE SCAN) OF 'IDX_A_B' (NON-UNIQUE)

只用b的条件缺少复合索引第一列的查询条件，就只能全表了
16:55:44 sys@FOX> select * from t where b='x' ;

64 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'T'