oracle 跳跃查询,跳跃式索引扫描的结构猜想

存在index(a,b)

当a的选择性不高的时候，oracle进行 跳跃式扫描的 优点明显

当a的选择性高的时候，跳跃式扫描失去了意义

既然有跳跃一说，就是说每次扫描的时候，会有一个入口进去，扫描，然后从新的入口进去，扫描……如此反复

这个入口，就是 a 的不同的值

SQL> create table t as select * from all_objects ;

表已创建。

SQL> create index t_index1 on t(owner,object_name);

索引已创建。

SQL> analyze table t compute statistics;

表已分析。

SQL> set autotrace on

SQL> select DATA_OBJECT_ID,created from t where object_name = 'DBA_EXTENTS';

DATA_OBJECT_ID CREATED

-------------- ----------

12-5月 -02

12-5月 -02

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=35 Card=2 Bytes=64)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=35 Card=2 Bytes

=64)

2 1 INDEX (SKIP SCAN) OF 'T_INDEX1' (NON-UNIQUE) (Cost=34 Ca

rd=1)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

26 consistent gets

0 physical reads

0 redo size

467 bytes sent via SQL*Net to client

425 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

2 rows processed

SQL> drop index t_index1;

索引已丢弃。

SQL> create index t_index1 on t (object_name,owner);

索引已创建。

SQL> analyze table t compute statistics;

表已分析。

SQL> select * from t where owner = 'TEST';

OWNER OBJECT_NAME

------------------------------ ------------------------------

SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE

------------------------------ ---------- -------------- ------------------

CREATED LAST_DDL_T TIMESTAMP STATUS T G S

---------- ---------- ------------------- ------- - - -

TEST A

30882 30882 TABLE

06-12月-02 06-12月-02 2002-12-06:17:35:38 VALID N N N

TEST B

33237 33237 TABLE

11-2月 -03 11-2月 -03 2003-02-11:10:07:49 VALID N N N

OWNER OBJECT_NAME

------------------------------ ------------------------------

SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE

------------------------------ ---------- -------------- ------------------

CREATED LAST_DDL_T TIMESTAMP STATUS T G S

---------- ---------- ------------------- ------- - - -

TEST DBEXPERT_PLAN1

30333 30333 TABLE

02-12月-02 02-12月-02 2002-12-02:17:26:29 VALID N N N

TEST LMT

33468 33468 TABLE

OWNER OBJECT_NAME

------------------------------ ------------------------------

SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE

------------------------------ ---------- -------------- ------------------

CREATED LAST_DDL_T TIMESTAMP STATUS T G S

---------- ---------- ------------------- ------- - - -

27-2月 -03 27-2月 -03 2003-02-27:09:22:18 VALID N N N

TEST LOGON_HISTORY

31284 TRIGGER

30-12月-02 30-12月-02 2002-12-30:19:02:08 VALID N N N

TEST MANLMT

OWNER OBJECT_NAME

------------------------------ ------------------------------

SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE

------------------------------ ---------- -------------- ------------------

CREATED LAST_DDL_T TIMESTAMP STATUS T G S

---------- ---------- ------------------- ------- - - -

33469 33469 TABLE

27-2月 -03 27-2月 -03 2003-02-27:10:07:10 VALID N N N

TEST PLAN_TABLE

33482 33482 TABLE

03-3月 -03 03-3月 -03 2003-03-03:10:49:09 VALID N N N

OWNER OBJECT_NAME

------------------------------ ------------------------------

SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE

------------------------------ ---------- -------------- ------------------

CREATED LAST_DDL_T TIMESTAMP STATUS T G S

---------- ---------- ------------------- ------- - - -

TEST SESSION_HISTORY

31287 31287 TABLE

30-12月-02 30-12月-02 2002-12-30:19:00:41 VALID N N N

TEST T

33485 33485 TABLE

03-3月 -03 03-3月 -03 2003-03-03:17:12:42 VALID N N N

OWNER OBJECT_NAME

------------------------------ ------------------------------

SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE

------------------------------ ---------- -------------- ------------------

CREATED LAST_DDL_T TIMESTAMP STATUS T G S

---------- ---------- ------------------- ------- - - -

TEST TEST

33483 33483 TABLE

03-3月 -03 03-3月 -03 2003-03-03:10:52:08 VALID N N N

TEST TEST_INDEX

33484 33484 INDEX

OWNER OBJECT_NAME

------------------------------ ------------------------------

SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE

------------------------------ ---------- -------------- ------------------

CREATED LAST_DDL_T TIMESTAMP STATUS T G S

---------- ---------- ------------------- ------- - - -

03-3月 -03 03-3月 -03 2003-03-03:10:52:34 VALID N N N

已选择11行。

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=43 Card=927 Bytes=79

722)

1 0 TABLE ACCESS (FULL) OF 'T' (Cost=43 Card=927 Bytes=79722)

Statistics

----------------------------------------------------------

71 recursive calls

0 db block gets

450 consistent gets

0 physical reads

0 redo size

2264 bytes sent via SQL*Net to client

425 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

11 rows processed