oracle之索引

索引

11.1 索引结构及特点

两大类:B树索引,2)位图索引

11.1.1 B树索引结构(图),介绍根节点,分支节点,叶子节点,以及表行,rowid,键值,双向链等概念。

考点:
1)叶块之间使用双向链连接,
2)删除表行时索引叶块也会更新,但只是逻辑更改,并不做物理的删除叶块。
3)索引叶块中不保存表行键值的null信息。

11.1.2 位图索引结构:

位图索引适用于离散度较低的列,它的叶块中存放key, start rowid-end rowid,并应用一个函数把位图中相应key值置1,位图索引在逻辑or时效率最高。

SQL>create bitmap index job_bitmap on emp1(job);

值/行              1  2  3  4  5  6  7  8  9  10 11 12 13 14
---------------------------------------------------------------
ANALYST     0  0  0  0  0  0  0  1  0  0  0  0  1  0    
CLERK               1  0  0  0  0  0  0  0  0  0  1  1  0  1
MANAGER             0  0  0  1  0  1  1  0  0  0  0  0  0  0
PRESIDENT          0  0  0  0  0  0  0  0  1  0  0  0  0  0
SALESMAN    0  1  1  0  1  0  0  0  0  0  0  0  0  0

SQL>select count(*) from emp1 where job = 'CLERK' or job = 'MANAGER';

值/行              1  2  3  4  5  6  7  8  9  10 11 12 13 14
---------------------------------------------------------------
CLERK               1  0  0  0  0  0  0  0  0  0  1  1  0  1
MANAGER             0  0  0  1  0  1  1  0  0  0  0  0  0  0
---------------------------------------------------------------
or的结果            1  0  0  1  0  1  1  0  0  0  1  1  0  1

以上操作使用autotrace可以看到优化器使用了bitmap,

B树索引要比位图索引应用更广泛,下面我们重点关注B树索引。

索引是与表相关的一个可选结构,在逻辑上和物理上都独立于表的数据,索引能优化查询,不能优化DML操作,Oracle自动维护索引,频繁的DML操作反而会引起大量的索引维护。

如果SQL语句仅访问被索引的列,那么数据库只需从索引中读取数据,而不用读取表,如果该语句同时还要访问除索引列之外的列,那么,数据库会使用rowid来查找表中的行。

11.2 B树索引和位图索引的适用环境

B树适合情况                         位图适合情况
--------------------------------------------------------------------------------------------
大表,返回行数<5%                        同左
经常使用where子句查询的列                    同左
离散度高的列                        离散度低的列
更新键值代价低                        更近键值代价高
                        
11.3 常用的B树索引类型:

唯一或非唯一索引(Unique or non_unique): 唯一索引指键值不重复。
SQL> create unique index empno_idx on emp1(empno);

SQL> create index empno_idx on emp1(empno);

组合索引(Composite):绑定了两个或更多列的索引。
SQL> create index job_deptno_idx on emp1(job,deptno);

反向键索引(Reverse):将字节倒置后组织键值。当使用序列产生主键索引时,可以防止叶节点出现热块现象(考点)。
SQL> create index mgr_idx on emp1(mgr) reverse;

函数索引(Function base):以索引列值的函数值为键值去组织索引
SQL> create index fun_idx on emp1(lower(ename));

压缩(Compress):重复键值只存储一次,就是说重复的键值在叶块中就存一次,后跟所有与之匹配的rowid字符串。
SQL> create index comp_idx on emp1(sal) compress;

升序或降序(Ascending or descending):叶节点中的键值排列默认是升序的。
SQL> create index deptno_job_idx on emp1(deptno desc, job asc);

可以更改索引属性:
alter index xxx ....

索引相关的数据字典

USER_INDEXES   //索引主要信息  
USER_IND_CULUMNS //索引列的信息

11.4 优化器使用索引的扫描方式

Oracle的执行计划常见的四种索引扫描方式:

1)索引唯一扫描(index unique scan)
通过唯一索引查找一个数值返回单个ROWID。对于唯一组合索引,要在where的谓词“=”后包含所有列的“布尔与”。

2)索引范围扫描(index range scan)
在非唯一索引上,可能返回多行数据,所以在非唯一索引上都使用索引范围扫描。
使用index rang scan的3种情况:
(a) 在唯一索引列上使用了range操作符(> < <> >= <= between)
(b) 在唯一组合索引上,对组合索引使用部分列进行查询(含引导列),导致查询出多行
(c) 对非唯一索引列上进行的任何查询。不含‘布尔或’

3)索引全扫描(index full scan)
对整个index进行扫描,并且顺序的读取其中数据。
CBO根据统计数值得知进行全Oracle索引扫描比进行全表扫描更有效时,才进行全Oracle索引扫描

4)索引快速扫描(index fast full scan)
扫描索引中的所有的数据块,fast full scan 在读取叶子块时的顺序完全由物理存储位置决定,并采取多块读,每次读取DB_FILE_MULTIBLOCK_READ_COUNT个块。

CBO能够使用索引全扫描和的索引快速扫描前提是:
所要的数据必须能从索引中可以直接得到,因此不再需要查询基表。

聚簇因子(CLUSTERING_FACTOR): 堆表的表行物理的存储在数据块是无序的,这与插入一行记录首选空闲块的策略有关,而索引的键值又是有序的,当这两者差异越大,聚簇因子的值就越高,索引成本也就越高。
举例这四种索引扫描方式:

如果你的scott不能使用autotrace,试做一下几步:
SQL> conn / as sysdba
SQL> @$ORACLE_HOME/rdbms/admin/utlxplan
SQL> @$ORACLE_HOME/sqlplus/admin/plustrce
SQL> grant plustrace to public;
SQL>

试试scott下能否使用autotrace

SQL> create table emp1 as select * from emp;

SQL> set autotrace traceonly explain;

SQL> select empno from emp1;

执行计划
----------------------------------------------------------
Plan hash value: 2226897347

--------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |    14 |   182 |     3   (0)           | 00:00:01 |
|   1 |  TABLE ACCESS FULL     | EMP1 |    14 |   182 |     3   (0)           | 00:00:01 |
--------------------------------------------------------------------------

例:索引唯一扫描(index unique scan)

SQL> create unique index emp1_idx on emp1(empno);

索引已创建。

SQL> select empno from emp1 where empno=7788;

执行计划
----------------------------------------------------------
Plan hash value: 1995401140

------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |     1 |    13  |     0   (0) | 00:00:01 |
|*  1 |  INDEX UNIQUE SCAN| EMP1_IDX |     1 |    13 |     0   (0) | 00:00:01 |
------------------------------------------------------------------------------

SQL> drop index emp1_idx;

例:索引范围扫描(index range scan)

SQL> create index emp1_idx on emp1(empno);

SQL> select empno from emp1 where empno=7788;

执行计划
----------------------------------------------------------
Plan hash value: 253836959

-----------------------------------------------------------------------------
| Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |             |     1 |    13 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| EMP1_IDX |     1 |    13 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------

3)索引全扫描(index full scan)

当你要查询出的数据全部可以从索引中直接得到,也就是说仅读索引块而不需要读表块,这时会选择index (fast) full scan

SQL> alter table emp1 modify (empno not null);    --因索引的叶子块不存空值,使empno字段非空。

SQL> select empno from emp1;            --数据库仅访问索引本身的数据。而无需访问表。

显示结果同上

例:索引快速扫描(index fast full scan)

SQL> insert into emp1 select * from emp1;

已创建14行。

SQL> /
.....

已创建14336行。

SQL> select count(empno) from emp1;

执行计划
----------------------------------------------------------
Plan hash value: 593157948

----------------------------------------------------------------------------------
| Id  | Operation                 | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                |        1 |     3 |    27   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |              |         1 |     3 |            |          |
|   2 |   INDEX FAST FULL SCAN| EMP1_IDX | 28672 | 86016 |    27   (0)| 00:00:01 |
----------------------------------------------------------------------------------

对比index full scan,当行数较多时,会使用index fast full scan

行数较多时index fast full scan 比index full scan计划成本要低的多,所以CBO优化器会优选 index fast full scan

两项再比较一下:

SQL> select empno from emp1;

执行计划
----------------------------------------------------------
Plan hash value: 1892048637

---------------------------------------------------------------------------------
| Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |              | 28033 |   355K|    29   (0)| 00:00:01 |
|   1 |  INDEX FAST FULL SCAN| EMP1_IDX | 28033 |   355K|    29   (0)| 00:00:01 |
---------------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement

可以加一行hint,强制oracle使用index full scan的执行计划,得到cost是100.

SQL>select /*+ index(emp1,emp1_idx) */ empno from emp1;        

执行计划
----------------------------------------------------------
Plan hash value: 4252953140

-----------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |              | 28033 |   355K|   100   (0)| 00:00:02 |
|   1 |  INDEX FULL SCAN | EMP1_IDX     | 28033 |   355K|   100   (0)| 00:00:02 |
-----------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

可以看出:CBO在满足一定条件时会先选index fast full scan,而不是index full scan,因为前者的cost是29,比后者100低许多。

CBO优化器何时决定INDEX FULL SCAN 与 INDEX FAST FULL SCAN:

共性:当仅从索引表中就可以得到所要的查询结果,省去了第二步扫描表块。
个性:INDEX FAST FULL SCAN可以使用多块读,多块读由参数db_file_multiblock_read_count指定,适用于表行多时IO效率更高,而对于索引列上order by之类的操作又几乎总是使用INDEX FULL SCAN。

SQL>

11.5 索引的碎片问题

由于对基表做DML操作,便导致对索引表块的自动更改操作,尤其是基表的delete操作会引起index表的index entries的逻辑删除,注意,只有当一个索引块中的全部index entry都被删除了,这个块才会被收回。如果update基表索引列,则索引块会发生entry delete,再entry insert,这都些动作都可能产生索引碎片。

SQL> create table t (id int);
SQL> create index ind_1 on t(id);
SQL>
begin
  for i in 1..1000000 loop
    insert into t values (i);
        if mod(i, 100)=0 then
        commit;
        end if;
  end loop;
end;
/

SQL> analyze index ind_1 validate structure;
SQL> select name,HEIGHT,PCT_USED,DEL_LF_ROWS/LF_ROWS from index_stats;
SQL> delete t where rownum<700000;
SQL> alter index ind_1 rebuild [online] [tablespace name];

在Oracle文档里并没有清晰的给出索引碎片的量化标准,Oracle建议通过Segment Advisor(段顾问)解决表和索引的碎片问题(053课程会涉及),如果你想自行解决,可以通过查看index_stats视图,当以下三种情形之一发生时,说明积累的碎片应该整理了(仅供参考)。

1.HEIGHT >=4   
2 PCT_USED< 50%   
3 DEL_LF_ROWS/LF_ROWS>0.2

联机重建索引通常比删除后再重建要更实用,Oracle9i和10g一直提供联机索引重建功能--rebuild online,但由于涉及到使用表的排他锁,通常一段时间内其他用户的DML操作需要等待。

在Oracle11g中有了改进,实现了最终意义上的联机索引重建(rebuild index online)的特性,它重新设计了锁的模式,使其他用户对基表的DML操作可以不受干扰。
SQL> alter index ind_1 coalesce;    //索引融合,比rebuild动作轻,可以合并一些块中的index entries;
                
11.6 索引不可用unusable,以及不可见(invisible)

索引被设定为 unusable的结果是:保存索引定义,不删除索引,也不更新索引)。视图user_indexes中status字段是unusable(考点)。
SQL> create table test (id int,name char(10));
SQL> create index test_idx on test(id)
SQL> alter index test_idx unusable;    

如想再次使用需要做rebuild。


SQL> alter index test_idx rebuild;
    
在11g里, Oracle提供了一个新特性来降低直接删除索引或禁用索引的风险,那就是索引不可见(Index Invisible)。我们可以在创建索引时指定invisible属性或者用alter语句来修改索引为invisible(visible)(考点)

SQL> alter index test_idx invisible;
SQL> select index_name,status,VISIBILITY from user_indexes;

查询索引的两个动态视图:

select * from dba_indexes;
select * from dba_ind_columns


posted on 2018-06-11 10:34  小孩没穿鞋  阅读(149)  评论(0编辑  收藏  举报

导航