《基于ORACLE SQL优化》读书笔记-访问索引的方法

访问B树索引：

索引分枝块两种类型的指针，IMC,索引行记录所记录的指针。

在同等条件下， 唯一性B树索引比非唯一性B树索引，省叶子节点的存储空间。

 

INDEX UNIQUE SCAN:针对UNIQUE INDEX，WHERE条件中为等号。

INDEX RANGE SCAN:在同等条件下，目标索引行的数量大于1时，索引范围扫描所用的逻辑读至少会比相应的唯一性扫描的逻辑读多1.（有试验证明EG1）

补充：INDEX RANGE SCAN 扫描结果也是有序的

INDEX FULL SCAN:通常使用单块读不并行，自带排序，通常不用回表。前提条件是有一个索引键值列属性是NOT NULL. （有序）

补充：对于MAX,MIN,走INDEX时，实际直接拿最后一个或是第一个有值块，效率高。（INDEX FULL SCAN (MIN/MAX)）不过也看过网上一个帖子，说有个坑爹的情况。就是程序一直在删除MIN的值，使索引碎片很多。以取MIN为例，本来直接拿第一个块就OK了，可拿的时候发现里边全是空的，再去找第二，发现还是空的，一直找直到找到有值的那个。找了很久。。。不记得是哪个帖子里写的了。

MAX/MIN 同时写时，会变成全表扫，或是加上ISNOT NULL,变成INDEX FAST FULL SCAN.如果拆开成两个SQL，可能有数据不同步。（试验证明EG2）

解决方案： select max,minfrom select max(object_id max from t a,(select min(object_id) min from t)b;

 

INDEXFAST FULL SCAN:多块读，可并行。结果不能保证有序。 

INDEXSKIP SCAN:适用索引前导列DISTINCT值较少，后续非前导列的可选择性好的情况。

补充：在确定索引的LEADINGCOLUMN时，绝对不是希望它的DISTINCT值较少，也就是选择性不好。当然是选择性好为最佳。不过，除了看表中的数据情况，还有一个就是看使用情况 ，也就是列的使用频率，以及使用方式（等，还是大于，小于，介于XX与YY之间？）综合来确定索引前导列。在生产环境中遇到过前导列选错了，性能差了近60倍的情况。

EG1:

create unique indexidx_emp_temp on emp(empno);
EXECdbms_stats.gather_table_stats(ownname=>'tester',tabname=>'EMP',estimate_percent=>100,cascade=>true,method_opt =>'for all columns size1',no_invalidate =>false);
alter system flushshared_pool;
alter system flushbuffer_cache;
select * from emp where empno = 7369;  
drop index idx_emp_temp ;


create index idx_emp_tempon emp(empno);
EXECdbms_stats.gather_table_stats(ownname=>'tester',tabname=>'EMP',estimate_percent=>100,cascade=>true,method_opt =>'for all columns size1',no_invalidate =>false);
alter system flushshared_pool;
alter system flushbuffer_cache;
select * from emp where empno = 7369;  
 

 

 

EG2:

tester@XE>select min(empno) from emp;

MIN(EMPNO)

----------

      7369

ExecutionPlan

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

Plan hashvalue: 51224572

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

| Id  | Operation                  | Name         |

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

|   0 | SELECT STATEMENT           |              |

|   1 | SORT AGGREGATE            |              |

|   2 |  INDEX FULL SCAN (MIN/MAX)| IDX_EMP_TEMP |

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

 

 

tester@XE>select min(empno),max(empno) from emp;

MIN(EMPNO)MAX(EMPNO)

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

      7369      7566

ExecutionPlan

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

Plan hashvalue: 2083865914

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

| Id  | Operation          | Name |

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

|   0 | SELECT STATEMENT   |     |

|   1 | SORT AGGREGATE    |      |

|   2 |  TABLE ACCESS FULL| EMP  |

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