收集表和主键索引统计信息和直方图

exec SYS.DBMS_STATS.GATHER_TABLE_STATS('username','table_name');

exec SYS.DBMS_STATS.GATHER_index_stats('username','index_name');

SQL> exec dbms_stats.gather_table_stats('alex','alex_t01')

SQL> exec dbms_stats.gather_index_stats('alex','pk_alex_t01')

B树索引

由 根节点，分支节点，叶节点三部分组成

索引条目分布  包含索引条目头

                  键值长度

                  键值列

                  ROWID

 

 

全表扫描：全表扫描（FULL TABLE SCAN）就是在数据查询过程中，对整张表的全部低于高水位标记（High Water Mark，HWM）的数据块（Data Block）进行读取

强制全表扫描：select /*+full(alex_t00)*/ count(*) from alex_t00;

rowid扫描：oracle提供的伪列；

select rowid from alex_t00 where id=1;

AAAVAe        AAB            AAAWFp          AAA

           6位            3位               6位            3位 

        数据库对象编号  文件编号          块编号         行编号

通过 dbms_rowid包分解rowid；

            select dbms_rowid.rowid_object(rowid) object_id,

dbms_rowid.rowid_relative_fno(rowid) file_id,

dbms_rowid.rowid_block_number(rowid) block_id,

dbms_rowid.rowid_row_number(rowid) num

from alex_t00

where id = 1;

 

索引唯一扫描：

有一个特点，就是当唯一索引由多列组成，则至少要有组合索引的前导列参与查询；

索引范围扫描：根据索引列范围扫描表，结构有排序

索引全扫描：与全表扫描相比，其扫描过程进行排序，避免空块查询，单块读取；

索引快速全扫描：与索引全表扫描相比，其不同在于结过不排序，可以并行多块读取；

强制直接快速扫描

SQL> select /*+ index_ffs(alex_t01 idx_alex_t01_id_ab) */ a, b  from alex_t01 where b>600;

索引跳跃扫描：在复合索引发生，where条件中没有对目标索引的前导列指定查询条件但同时又对该索引的非前导列指定了查询条件的目标SQL依然可以用上该索引

              强制执行跳跃扫描

              select /*+ index_ss(alex_t01 idx_alex_t01_id_ab) */ a, b, name  from alex_t01 where b=600;

索引组合扫描：这个特性由_b_tree_bitmap_plans来控制；发生在where后面的两个条件，两个条件都是独立索引(其他索引扫描的补充)

 

                强制走一个索引

                select /*+index(alex_t01,idx_alex_t01_id_b)*/ *  from alex_t01 where b=600 and c=600;

索引联立扫描：和索引组合扫描相似，只是当查询返回的列包含该两个索引列，就不用回表取数，可以index_jion(其他索引扫描的补充)

 

             SQL> select /*+ index_join(alex_t01 idx_alex_t01_id_b idx_alex_t01_id_c) */ b, c from alex_t01 where b=600 and c=600;

 

索引与排序：

          索引扫描是可以排序的，（快速扫描除外）；

 

 

 

B树索引内部结构

降序索引：个人觉得没必要，因为优化器现在可以自动进行降序查询优化，不过在复合索引的降序是可以表现的如（order by a ase ,bdesc）

              

聚合查询min()和max()：同时查询最大最小值最优语句

                     Select (select max(id) from dual) as max_values, (select min(id) from dual) as min_values from dual

 

 

 

索引设计优化：

1. 索引选择度 ----越低越好

索引理想选择度=1/索引列的distinct数；

索引实际选择度=返回结果集行数/全表行数；

我们知道当一个表的NUM_DISTINCT 值越接近NUM_ROWS 值，则优化器越倾向于走

索引扫描。那么最优情况是NUM_DISTINCT=NUM_ROWS（典型代表为唯一索引，此类索引

为理想状态索引，扫描效率最高），最差情况则是NUM_DISTINCT=1（不考虑空表和Null

值）。引进一个比例NUM_ROWS/NUM_DISTINCT 来表示其关联性，该比例的值越小则越优。

第2 章　高效B 树索引41

通过一般情况与最差情况的对比，可以得到一般情况下的选择度，这个选择度就是一般情况

的理想选择度。

理想选择度＝ (NUM_ROWS/NUM_DISTINCT)/(NUM_ROWS/1) ＝ 1/NUM_DISTINCT

可以说理想选择度反映了优化器选择的倾向性。换而言之，如果希望优化器尽可能地选

择索引扫描，则索引设计上尽可能地参考理想选择度的原则，它给索引设计提供一定的指导

意义。

至于NUM_ROWS 和NUM_DISTINCT 的值可以通过扫描表获取，也可以通过如下方式

查询数据字典获取（必须收集统计信息）：

SQL> select num_rows from dba_tables;

SQL> select num_distinct from dba_tab_cols;

1. 数据分布影响

1. 均匀分布情况

这种情况的理想选择度和实际选择度是相等的，直方图信息是存放列的数据分布情况，当缺少索引列直方图信息，CBO会认为其是均匀分布，从而影响执行计划；

 

1. 非均匀分布情况

    

如果按照实际选择度来走的话，COL=2 走全表扫描，其他情况走索引扫描是一个比较理想的选择。但是，优化器的实际处理情况未必如此。这个时候，如果我们没有COL 列上的直方图，优化器会认为数据是均匀分布的，则理想选择度 = 1/NUM_DISTINCT = 20%，很有可能都走了全表扫描。这对于COL 不为2 的情况几乎是不能接受的。

收集索引列直方图对于大表来说，开销很大，在这情况下不建议建立索引，否则直方图信息收集时必要的；

Oracle 有一个绑定变量窥探的机制，SQL 语句第一次执行决定了以后同样的SQL 语句的执行计划。这对于绑定变量也不合适

1. 索引聚族因子

    

   这情况，当where col=1时候，右图性能比左图好；

   可以通过数据字典查看聚族因子信息：CLUSTERING_FACTOR越小越好

   select index_name,CLUSTERING_FACTOR from dba_indexes where table_name='　'

   综合以上内容，我们可以来总结一下了，一个设计优秀的索引应该具有以下特点：

   ??具有较好的选择度，能参考理想选择度来界定；

   ??索引列的数据分布足够趋于均匀化；

   ??具有较小的聚簇因子。

2. 复合索引

   复合索引的设计

   

   

    

 

1. 索引被无视

   要根据官方规则进行sql优化

   列与列的对比

   存在NULL 条件

   NOT 条件

   LIKE 前置通配符

   条件列上使用函数

   高区分度前导列的复合索引后置列查询

    

 

 

索引分裂：  完全理想状态（索引聚簇因子无限接近于数据块数）

1. 造成5-5分裂

??左侧节点发生新值插入时（新值小于索引中的最大值）；

??     发生 DML 操作，索引块上没有足够空间分配新的 ITL 槽；

       select event, total_waits from v$system_event

 where event in ('enq: TX - allocate ITL entry', 'enq: TX - index contention');

 

select n.name, s.value from v$sysstat s, v$statname n

where s.statistic# = n.statistic#  and n.name like '%split%';

?      ?新值待插入的索引块上存在其他未提交的事务。

 

 

数据根据索引顺序往后插，操作9-1分裂

   可以使用10224跟踪事件记录索引分裂过程

   Alter session set events ‘10224 trace name context forever,level 1’;

   alter session set events '10224 trace name context off';

通过数据字典的查询，可以看到该会话制造索引分裂情况

select s.sid, n.name, s.value

 from v$sesstat s, v$statname n

 where s.statistic# = n.statistic#

 and sid in (select sid from v$mystat)

 and value > 0

 and n.name like '%split%';

分析索引结构 -------这分析过程代价高，执行时间长，锁表

analyze index idx_alex_t05_id validate structure;

查看索引优劣

select height  as 索引高度,

round((del_lf_rows_len/lf_rows_len)*100,2)||'%'   as ratio碎片率,

pct_used 索引空间使用率 from index_stats where name= 'PK_ALEX_T01';

Pct_free 在索引创建时起作用，使用时往往忽略

 

反向健可以一定程度可以解决索引分裂情况，但对查询读取就增加了额外IO消耗

 

 

索引维护：

为何重建索引

索引空间使用率低，

劣质索引影响性能

何时重建索引

??索引树高度过高，如：height>=4；

??叶节点碎片率过多，如： DEL_LF_ROWS/LF_ROWS>20%；

??叶节点使用率低下，如：PCT_USAGE<20%。

如何重建索引

??离线重组：alter index idx_alex_t05_id shrink space    回收空间不彻底

??在线重组：alter index idx_alex_t05_id coalesce     不回收索引上的空闲空间

??离线重建：alter index idx_alex_t05_id rebuild

??在线重建：alter index idx_alex_t05_id rebuild online