Oracle Index 特性

最新推荐文章于 2024-08-23 15:42:13 发布

easy009

最新推荐文章于 2024-08-23 15:42:13 发布

阅读量266

点赞数

分类专栏： Oracle

本文链接：https://blog.csdn.net/u012654134/article/details/50541919

版权

Oracle 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

01.INDEX RANGE SCAN
02.INDEX UNIQUE SCAN
03.TABLE ACCESS BY USER ROWID
04.INDEX FULL SCAN
05.INDEX FAST FULL SCAN
06.INDEX FULL SCAN (MINMAX)
07.INDEX SKIP SCAN
08.TABLE ACCESS BY INDEX ROWID

Root –>Branch–>Leaf
Index entry –>Index entry header
–>Key column length
–>Key column value
–>RowId
索引黄金特性1：索引高度较低有6层。有几百亿数据。

01.INDEX RANGE SCAN

drop table t purge;
create table t as select * from dba_objects;
update t set object_id=rownum;
commit;
create  index idx_object_id on t(object_id);
set autotrace traceonly
set linesize 1000
exec dbms_stats.gather_table_stats(ownname => 'scott',tabname => 'T',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE) ;  
select object_id from t where t.object_id = 8;
执行计划
----------------------------------------------------------
Plan hash value: 1276437228
----------------------------------------------------------------------------------
| Id  | Operation     | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |        |   1 |   5 |  1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX_OBJECT_ID |   1 |   5 |  1   (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T"."OBJECT_ID"=8)

统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        528  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

02.INDEX UNIQUE SCAN

请注意这个INDEX UNIQUE SCAN扫描方式,在唯一索引情况下使用。

drop table t purge;
create table t as select * from dba_objects;
update t set object_id=rownum;
commit;
create unique index idx_object_id on t(object_id);
set autotrace traceonly
set linesize 1000
select * from t where object_id=8;
执行计划
---------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |         |    1 |   207 |  2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T        |    1 |   207 |  2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN     | IDX_OBJECT_ID |    1 |     |  1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------|
统计信息
---------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
       1298  bytes sent via SQL*Net to client
        404  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

03.TABLE ACCESS BY USER ROWID

-请注意这个TABLE ACCESS BY USER ROWID扫描方式,直接根据rowid来访问，最快的访问方式！

drop table t purge;
create table t as select * from dba_objects;
update t set object_id=rownum;
commit;
--注意，这里连索引都没建!
--create  index idx_object_id on t(object_id);
set autotrace off
select rowid from t where object_id=8; 
ROWID
------------------
AAAZxiAAGAAAB07AAH
set autotrace traceonly
set linesize 1000
select * from t where object_id=8 and rowid='AAAZxiAAGAAAB07AAH';
执行计划
-----------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |    |   1 |   219 | 1   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY USER ROWID| T   |   1 |   219 | 1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
       1391  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

04.INDEX FULL SCAN

—请记住这个INDEX FULL SCAN扫描方式，并体会与INDEX FAST FULL SCAN的区别

drop table t purge;
create table t as select * from dba_objects;
update t set object_id=rownum;
commit;
alter table T modify object_id not null;
create  index idx_object_id on t(object_id);
set autotrace traceonly
set linesize 1000
select * from t  order by object_id;
执行计划
---------------------------------------------------------------------------------------------
| Id  | Operation           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |         | 88780 |   17M|  1208   (1)| 00:00:15 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T       | 88780 |   17M|  1208   (1)| 00:00:15 |
|   2 |   INDEX FULL SCAN     | IDX_OBJECT_ID | 88780 |     |   164   (1)| 00:00:02 |
---------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      10873  consistent gets
          0  physical reads
          0  redo size
    8116181  bytes sent via SQL*Net to client
      54040  bytes received via SQL*Net from client
       4877  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      73130  rows processed

05.INDEX FAST FULL SCAN

—请记住这个INDEX FAST FULL SCAN扫描方式，并体会与INDEX FULL SCAN的区别

drop table t purge;
create table t as select * from dba_objects ;
update t set object_id=rownum;
commit;
alter table T modify object_id not null;
create  index idx_object_id on t(object_id);
set autotrace traceonly
set linesize 1000
select count(*) from t;
执行计划
-------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |     1 |    49   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |               |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IDX_OBJECT_ID | 88780 |    49   (0)| 00:00:01 |
-------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        170  consistent gets
          0  physical reads
          0  redo size
        425  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

06.INDEX FULL SCAN (MINMAX)

–请注意这个INDEX FULL SCAN (MIN/MAX)扫描方式

drop table t purge;
create table t as select * from dba_objects;
update t set object_id=rownum;
commit;
create  index idx_object_id on t(object_id);
set autotrace traceonly
set linesize 1000
select max(object_id) from t;
执行计划
--------------------------------------------------------------------------------------------
| Id  | Operation                  | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |               |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE            |               |     1 |    13 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| IDX_OBJECT_ID |     1 |    13 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        431  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

07.INDEX SKIP SCAN

–请记住这个INDEX SKIP SCAN扫描方式

drop table t purge;
create table t as select * from dba_objects;
update t set object_type='TABLE' ;
commit;
update t set object_type='VIEW' where rownum<=30000;
commit;
create  index idx_type_id on t(object_type,object_id);
exec dbms_stats.gather_table_stats(ownname => 'LJB',tabname => 'T',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE) ;  
set autotrace traceonly
set linesize 1000
select * from t where object_id=8;
执行计划
-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    94 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T           |     1 |    94 |     4   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN           | IDX_TYPE_ID |     1 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"=8)
       filter("OBJECT_ID"=8)

统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
       1401  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

08.TABLE ACCESS BY INDEX ROWID

–好好的体会前后两个试验，记住这个TABLE ACCESS BY INDEX ROWID

drop table t purge;
create table t as select * from dba_objects;
update t set object_id=rownum;
commit;
create  index idx_object_id on t(object_id);
set autotrace traceonly explain
set linesize 1000
select object_id from t where object_id=2 and object_type='TABLE';
---------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |    9 |   216 |   2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T       |    9 |   216 |   2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN     | IDX_OBJECT_ID |   12 |     |   1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

--在接下来的试验中，你会看到，哇塞，TABLE ACCESS BY INDEX ROWID消失了。
create  index idx_id_type on t(object_id,object_type);
select object_id from t where object_id=2 and object_type='TABLE';
执行计划
--------------------------------------------------------------------------------
| Id  | Operation     | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |    9 |  216 |  1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX_ID_TYPE |    9 |  216 |  1   (0)| 00:00:01 |
--------------------------------------------------------------------------------