解析索引
参考《oracle DBA工作笔记》
在oracle中对于索引的存储是采用btree索引来实现。btree是树形结构，索引分为根节点，分支节点和叶子节点。最后叶子节点的数据和表的数据行就会有一个最终的映射。
1、关于索引的内部信息
我们创建一个表。
create table index_test as select *from user_objects;
然后创建一个索引。
create index inx_test on index_test(object_id);
使用analyze来分析索引的信息，尽管在新版本中我们建议使用dbms_stats来替代analyze,但是analyze validate structure这个功能时analyze独有的，dbms_stats在这方面还不能完全替代analyze.
analyze index inx_test validate structure;
select height,blocks,br_blks,lf_blks,lf_rows,del_lf_rows from index_stats; --高度 索引总块数 枝干块数 叶子块数 叶子内行数 叶子中被删除的行数
2、关于索引的访问方式
针对不同的使用场景使用不认同的访问方式。
2.1、索引唯一扫描
如下使用了INDEX UNIQUE SCAN，这是最快的索引访问方式,适用于'where object_id=258'。
SQL> create table a as select object_id,object_name,object_type from dba_objects;
Table created.
SQL> analyze table a compute statistics;
Table analyzed.
SQL> create unique index ind_a on a(object_id);
Index created.
SQL> set autotrace on
SQL> set linesize 150
SQL> select * from a where object_id=258;
OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------------------
OBJECT_TYPE
-------------------
258 PLSCOPE_IDENTIFIER$
TABLE
Execution Plan
----------------------------------------------------------
Plan hash value: 2087649606
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 36 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| A | 1 | 36 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | IND_A | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=258)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
568 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
2.2、快速索引全扫描
因为不涉及排序，所以快一些。
select object_id from a;
Execution Plan
----------------------------------------------------------
Plan hash value: 2248738933
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 86264 | 336K| 142 (0)| 00:00:02 |
| 1 | TABLE ACCESS FULL| A | 86264 | 336K| 142 (0)| 00:00:02 |
--------------------------------------------------------------------------
没有走索引，排除null干扰
SQL> alter table a modify(object_id not null);
Table altered.
SQL> set autot traceonly exp
SQL> select object_id from a;
Execution Plan
----------------------------------------------------------
Plan hash value: 672397539
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 86264 | 336K| 50 (0)| 00:00:01 |
| 1 | INDEX FAST FULL SCAN| IND_A | 86264 | 336K| 50 (0)| 00:00:01 |
------------------------------------------------------------------------------
2.3、索引全扫描
如果要对索引做排序，可以使用索引全扫描，适用于'order by'。
可以做个对比
SQL> select object_id from a order by object_id;
Execution Plan
----------------------------------------------------------
Plan hash value: 1197305525
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 86264 | 336K| 181 (1)| 00:00:03 |
| 1 | INDEX FULL SCAN | IND_A | 86264 | 336K| 181 (1)| 00:00:03 |
--------------------------------------------------------------------------
SQL> select object_id from a;
Execution Plan
----------------------------------------------------------
Plan hash value: 672397539
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 86264 | 336K| 50 (0)| 00:00:01 |
| 1 | INDEX FAST FULL SCAN| IND_A | 86264 | 336K| 50 (0)| 00:00:01 |
------------------------------------------------------------------------------
2.4、区间扫描
如果涉及索引列的区间值，可以用区间，适用于扫描between。
SQL> select * from a where object_id between 2000 and 3000;
Execution Plan
----------------------------------------------------------
Plan hash value: 1070634250
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 989 | 35604 | 11 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| A | 989 | 35604 | 11 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_A | 989 | | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID">=2000 AND "OBJECT_ID"<=3000)
2.5、跳跃索引扫描
索引有好几列，语句只用一列。
SQL> create index ind_a on a(object_type,object_id,object_name);
Index created.
SQL> analyze table a compute statistics for all indexed columns;
Table analyzed.
SQL> select * from a where object_id=258;
Execution Plan
----------------------------------------------------------
Plan hash value: 4117514
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 36 | 46 (0)| 00:00:01 |
|* 1 | INDEX SKIP SCAN | IND_A | 1 | 36 | 46 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_ID"=258)
filter("OBJECT_ID"=258)
3、索引和空值
创建索引会因为空值出现偏差；
空值不在索引中，查询会因为空值走不了索引。
如果需要输出非空的数据，加入is NOT NULL的过滤条件，索引就能正常启用。
4、约束和索引
用alter table test add constraint con_test_id_uq unique(id);添加约束，不要用modify。
约束和索引要尽量分开，因为约束disable的情况下，索引会连带删除，如果是分区表的全局索引那么影响也是全局性的。
索引和约束分离，索引列顺序不当会导致不走索引。
5、虚拟索引
使用虚拟索引，通过查看执行计划来比较前后的变化，如果提升幅度大，再来考虑创建对应的索引。
SQL> create table t as select *from dba_objects where object_id is not null and rownum<100000;
Table created.
正常情况
SQL> select *from t where object_id=8040;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 2898 | 336 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| T | 14 | 2898 | 336 (1)| 00:00:05 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=8040)
Note
-----
- dynamic sampling used for this statement (level=2)
创建虚拟索引
SQL> create unique index inx_t on t(object_id) nosegment;
Index created.
发现没有使用虚拟索引
SQL> select *from t where object_id=8040;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 207 | 336 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| T | 1 | 207 | 336 (1)| 00:00:05 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=8040)
Note
-----
- dynamic sampling used for this statement (level=2)
在会话级别启用虚拟索引
SQL> alter session set "_USE_NOSEGMENT_INDEXES" = true;
Session altered.
虚拟索引生效
SQL> select *from t where object_id=8040;
Execution Plan
----------------------------------------------------------
Plan hash value: 1855406669
-------------------------------------------------------------------------------------
| 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 | INX_T | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=8040)
Note
-----
- dynamic sampling used for this statement (level=2)
6、不可见索引
创建不可见索引会锁表，对业务有影响，对比虚拟索引，不可见索引有索引段，也有数据字典信息，这些虚拟索引没有。
SQL> create table t as select *from dba_objects where object_id is not null and rownum<100000;
Table created.
SQL> set autot trace exp stat
正常状态
SQL> select *from t where object_id=9445 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 2898 | 336 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| T | 14 | 2898 | 336 (1)| 00:00:05 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=9445)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
24 recursive calls
0 db block gets
1321 consistent gets
1464 physical reads
0 redo size
1628 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
创建不可见索引
SQL> create unique index inx_t on t(object_id) invisible;
Index created.
不可见索引没有生效
SQL> select *from t where object_id=9445 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1005 | 203K| 336 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| T | 1005 | 203K| 336 (1)| 00:00:05 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=9445)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1235 consistent gets
1231 physical reads
0 redo size
1628 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
使不可见索引生效
SQL> alter session set "optimizer_use_invisible_indexes"=true;
Session altered.
SQL> select *from t where object_id=9445 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 1855406669
-------------------------------------------------------------------------------------
| 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 | INX_T | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=9445)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
2 physical reads
0 redo size
1495 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
使不可见索引无效
SQL> alter session set "optimizer_use_invisible_indexes"=false;
Session altered.
将不可见索引变为正常索引
SQL> alter index inx_t visible;
Index altered.
SQL> select *from t where object_id=9445 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 1855406669
-------------------------------------------------------------------------------------
| 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 | INX_T | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=9445)
Statistics
----------------------------------------------------------
34 recursive calls
0 db block gets
20 consistent gets
0 physical reads
0 redo size
1495 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
扫一扫
10万+
07-25
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
