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
/*说明多字段反转前缀字段相对于=的条件是可用于范围扫描*/
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/42376/viewspace-927311/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/42376/viewspace-927311/