实例看反向索引的使用

wzf@TEST1> select * from t where x>5;

已选择800行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=4994 Bytes=29964)
1 0 INDEX (FAST FULL SCAN) OF 'T_XY_REV' (NON-UNIQUE) (Cost=5 Card=4994 Bytes=2996
4)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
68 consistent gets
9 physical reads
0 redo size
14049 bytes sent via SQL*Net to client
1086 bytes received via SQL*Net from client
55 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
800 rows processed

wzf@TEST1> select * from t where x>7;

已选择400行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=4992 Bytes=29952)
1 0 INDEX (FAST FULL SCAN) OF 'T_XY_REV' (NON-UNIQUE) (Cost=5 Card=4992 Bytes=2995
2)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
40 consistent gets
0 physical reads
0 redo size
7196 bytes sent via SQL*Net to client
789 bytes received via SQL*Net from client
28 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
400 rows processed

wzf@TEST1> create table t1 as select * from t;

表已创建。

wzf@TEST1> create index t1_xy on t1(x,y);

索引已创建。

wzf@TEST1> select * from t1 where x>7;

已选择400行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 INDEX (RANGE SCAN) OF 'T1_XY' (NON-UNIQUE)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
31 consistent gets
3 physical reads
0 redo size
7196 bytes sent via SQL*Net to client
789 bytes received via SQL*Net from client
28 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
400 rows processed
/*说明虽然反键索引不能用于范围,但可以用于fast full scan.道理非常简单,fast full scan 与full scan可以扫描*/

wzf@TEST1> create table t2 as select * from t;

表已创建。

wzf@TEST1> create index t2_xy on t2(x) reverse;

索引已创建。

wzf@TEST1> select * from t2 where x=5;

已选择200行。

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

Statistics
----------------------------------------------------------
29 recursive calls
0 db block gets
25 consistent gets
0 physical reads
0 redo size
3830 bytes sent via SQL*Net to client
646 bytes received via SQL*Net from client
15 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
200 rows processed

wzf@TEST1> analyze table t2 compute statistics for table
2 for all indexes for all indexed columns;

表已分析。

wzf@TEST1> select * from t2 where x=5;

已选择200行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=200 Bytes=2000)
1 0 TABLE ACCESS (FULL) OF 'T2' (Cost=3 Card=200 Bytes=2000)

Statistics
----------------------------------------------------------
27 recursive calls
0 db block gets
28 consistent gets
0 physical reads
0 redo size
3830 bytes sent via SQL*Net to client
646 bytes received via SQL*Net from client
15 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
200 rows processed

wzf@TEST1> select /*+ rule */ * from t2 where x=5;

已选择200行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 TABLE ACCESS (FULL) OF 'T2'

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
22 consistent gets
0 physical reads
0 redo size
3830 bytes sent via SQL*Net to client
646 bytes received via SQL*Net from client
15 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
200 rows processed

wzf@TEST1> select /* + index(T2,T2_XY) */ * from t2 where x=200;

未选定行

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=10)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T2' (Cost=2 Card=1 Bytes=10)
2 1 INDEX (RANGE SCAN) OF 'T2_XY' (NON-UNIQUE) (Cost=1 Card=1)

/*说明对于非唯一索引单字段反转是没有意义因为不能用于范围扫描*/

wzf@TEST1> desc t_char
名称 是否为空? 类型
----------------------------------------------------- -------- ------------------------------------
X NOT NULL VARCHAR2(20)
Y NOT NULL VARCHAR2(20)

wzf@TEST1> declare
2 a1 varchar(20);
3 a2 varchar(20);
4 begin
5 for i in 1..20000
6 loop
7 a1:=to_char(mod(i,300));
8 a2:=to_char(i);
9 insert into t_char values(a1,a2);
10 end loop;
11 commit;
12 end;
13 /

PL/SQL 过程已成功完成。

wzf@TEST1> select count(*) from t_char;

COUNT(*)
----------
20000

wzf@TEST1> create index t_char_rev on t_char(x,y) reverse;

索引已创建。

wzf@TEST1> select x,y from t_char where x='19';

wzf@TEST1> set autotrace traceonly
wzf@TEST1> select x,y from t_char where x='19';

已选择67行。

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

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
96 consistent gets
0 physical reads
0 redo size
1649 bytes sent via SQL*Net to client
547 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
67 rows processed

wzf@TEST1> desc t_char
名称 是否为空? 类型
----------------------------------------------------- -------- ------------------------------------
X NOT NULL VARCHAR2(20)
Y NOT NULL VARCHAR2(20)

wzf@TEST1> analyze table t_char compute statistics for table
2 for all indexes for all indexed columns;

表已分析。

wzf@TEST1> select x,y from t_char where x='19';

已选择67行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=67 Bytes=536)
1 0 INDEX (RANGE SCAN) OF 'T_CHAR_REV' (NON-UNIQUE) (Cost=2 Card=67 Bytes=536)

Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
12 consistent gets
0 physical reads
0 redo size
1649 bytes sent via SQL*Net to client
547 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
67 rows processed

wzf@TEST1> select x,y from t_char where x>='298';

已选择5291行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=15 Card=5333 Bytes=42664)
1 0 TABLE ACCESS (FULL) OF 'T_CHAR' (Cost=15 Card=5333 Bytes=42664)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
444 consistent gets
0 physical reads
0 redo size
93667 bytes sent via SQL*Net to client
4375 bytes received via SQL*Net from client
354 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5291 rows processed
/*说明多字段反转前缀字段相对于=的条件是可用于范围扫描*/

[@more@]

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

转载于:http://blog.itpub.net/42376/viewspace-927311/