ch06 oracle索引

--------------------------------------oracle索引------------------------
1．what
索引:在数据库的表或者视图上创建的对象,使用树状结构存储.

2．why
提高对含有大量记录的表的查询效率
通过快速定位数据的方法，减少磁盘I/O操作；

3．索引的类型(6种)
---B-Tree索引（不指定任何选项的创建方法所创建）----------------------------------------
也叫平衡树索引。
是按升序对被索引的列进行排序，同时存储一个行ID，用来指出该行的其他数据的物理存储地，Oracle以一种树型结构来存储这些数。
查询时，首先搜索该多级树结构形式组织的索引，然后按ID访问需要的表数据。
1）如果表数据行很多，只返回一个表中的少数行（Oracle建议是5%）。
2）具有高基数的列。且这些列经常被用在where条件中。
此时Oracle建议创建一个B树索引。
格式：Create index idx_ne_cell on ne_cell(ne_id) tablespace unicom_dbs;
eg:组合索引
SQL>create index index_sal_comm on emp(sal,comm);
eg:唯一的索引:
SQL>create unique index aa on custmer(customerID);

判断是否需要重建索引
B树索引设计为总是平衡的，Oracle随着insert操作而连续的分裂索引块，但是，随着insert和delete操作，该索引会产生许多级，增加索引从根遍历的时间，降低了效率，此时需要重建索引。
Oracle建议：
1）当级别深度到4或超过4时。（增加了从根遍历的时间）
索引被分析后，执行
SQL>select index_name,blevel from dba_indexes where blevel >=4;--检查
2）被删除数占总数的20%以上时。（增加了访问某项所访问的index块数）
- 执行
SQL> analyze index index_sal_comm validate structure;--填充表index_stats数据。
- 执行
SQL> select (del_lf_rows_len/lf_rows_len)*100 "Wasted Space" from index_stats where name='index_sal_comm';

Column Description
LF_ROWS NUMBER   Number of leaf rows (values in the index)
LF_BLKS NUMBER   Number of leaf blocks in the B-Tree
LF_ROWS_LEN NUMBER   Sum of the lengths of all the leaf rows
LF_BLK_LEN NUMBER   Usable space in a leaf block
BR_ROWS NUMBER   Number of branch rows in the B-Tree
BR_BLKS NUMBER   Number of branch blocks in the B-Tree
BR_ROWS_LEN NUMBER   Sum of the lengths of all the branch blocks in the B-Tree
BR_BLK_LEN NUMBER   Usable space in a branch block
DEL_LF_ROWS NUMBER   Number of deleted leaf rows in the index
DEL_LF_ROWS_LEN NUMBER   Total length of all deleted rows in the index
DISTINCT_KEYS NUMBER   Number of distinct keys in the index (may include rows that have been deleted)
MOST_REPEATED_KEY NUMBER   How many times the most repeated key is repeated (may include rows that have been deleted)
BTREE_SPACE NUMBER   Total space currently allocated in the B-Tree
USED_SPACE NUMBER   Total space that is currently being used in the B-Tree
PCT_USED NUMBER   Percent of space allocated in the B-Tree that is being used
ROWS_PER_KEY NUMBER   Average number of rows per distinct key (this figure is calculated without consideration of deleted rows)
BLKS_GETS_PER_ACCESS NUMBER   Expected number of consistent mode block reads per row, assuming that a randomly chosen row is accessed using the index. Used to calculate the number of consistent reads that will occur during an index scan.
PRE_ROWS NUMBER   Number of prefix rows (values in the index)
PRE_ROWS_LEN NUMBER   Sum of lengths of all prefix rows
OPT_CMPR_COUNT NUMBER   Optimal key compression length
OPT_CMPR_PCTSAVE NUMBER   Corresponding space savings after an ANALYZE

 

 

---压缩B树索引----------------------------------------
如果B树索引非常大，此时就建议使用压缩B树索引了。
压缩B树索引：在索引列存在重复值时，只记录一个值和不同的row id，减少了重复的列值的存储（同理，基数非常大或列值都唯一的，压缩就没多少必要了）。最大限度的减少了B树索引占用的磁盘空间。同时索引扫描也会访问更少的数据库块，减少I/O。

压缩语句：
SQL> create index index_job on emp(ename)  compress;

索引已创建。
或：
SQL> alter index index_job rebuild compress;

索引已更改。

 

 

---Bitmap（位图）索引----------------------------------------
创建表行的一个二进制映像，比如是存储1，不是存储0。所以占用更少的存储空间。
1）位图索引检索、匹配的行会更快。
2）对于底基数列，工作的更好。（高基数，B树好）
3）被索引表要DML操作少、长度大、有极少不同值的表列上极有用。（DML操作会引起位图级的加锁发生，而且要求重建所有可能值的整个位图；即使发生很多行被操作，但只按一次DML进行更新）
4）适合在决策支持系统或数据仓库使用。

SQL> create bitmap index index_jobs on emp(job);

索引已创建。

 

 

 

---基于函数的索引----------------------------------------
函数索引适用于B树索引和bitmap索引。但必须把init.ora的query_rewrite_enabled参数设置成true才可使用。（默认是false）
在SQL语句的WHERE字句中有任一函数或操作被运用于一个索引列时，执行计划将使用全表扫描而不会使用非函数索引。
如：select job from emp
where upper(job) = ‘REGINALD’;
或：select sal,sal*comm from emp where (sal*comm) > 10000;
都不会使用job或sal,comm列上的索引。

如下创建函数索引后优化程序才会考虑使用索引：
SQL>create index index_job on emp (upper(job));
SQL>create index index_sal_comm on emp (sal*comm);

 

 

---Reverse Key Index（RKI，反向键索引）----------------------
属于B树索引。
RKI：首先反向每个列键值的字节（如123，就被反为321，新数值在范围上，比原来那些列值会分布的更均匀），然后在反向后的数据上进行索引。
非常适用于含有序数的列。（因传统的B树，此时往往会产生很多级，而超过4级性能就会很低）

注意：RKI只能用于等于、不等于判断。其他如>、<、between等会导致无法使用该索引，可能会导致全表扫描。

连续的数据不利于查询
原列值：

10479

10480

10482

反转后的列值：（完全不一样）

97401

08401

28401

SQL>create index idx_employee_iem_id on employee(emp_id)
Reverse
Tablespace idx_dbs;
或
SQL>alter index idx_employee_iem_id rebuild reverse;

 

 

---Index Organized Table（IOT，索引组织表）-------------------

1．what
一种有序的表，必须要有主键,不对应segment,表里所有的数据都存放在主键所在的索引的叶子节点里，因此IOT表是虚拟表。
即在索引叶子节点里德索引条目里，不仅存放了被索引的列的值，还存放了其他列的值.

2．why
---由于索引项和数据存储在一起，所以无论是基于主键的等值查询还是范围查询都能大大节省磁盘访问时间。
---为了能够更快地访问那些频繁访问的列，可以使用溢出存储选项将那些访问不频繁的列放在B树叶结点数据块之外的溢出堆空间中。这样一来便可以得到更小的B树，以及包含更多行的叶结点
---和堆组织表和索引不同，主键不需要被存储两次。
---ROWID伪列是基于主键值的逻辑rowid，而不是物理rowid，即使表被重新组织过，造成了基表行的迁移，二级索引仍然可用，不需要重建。

IOT和普通表对于应用程序来说，例如sql查询语句，是没有区别的。也就是说oracle中对表的组织形式对应用来说是透明的。
3．IOT与一般表的区别：
 
+-----------------------普通表----------------+--------------------- IOT ---------------------+
|ROWID唯一确定一行录                          |主键唯一确定一行记录                           |
+---------------------------------------------+-----------------------------------------------+
|可以不指定主键                               |必须指定主键                                   |
+---------------------------------------------+-----------------------------------------------+
|ROWID伪列中保存物理rowid，可以构建二级索引   |ROWID伪列中保存逻辑rowid，可以构建二级索引     |
+---------------------------------------------+-----------------------------------------------+
|基于rowid进行访问                            |基于逻辑rowid进行访问                          |
+---------------------------------------------+-----------------------------------------------+
|顺序扫描才能得到所有行                       |只须扫描索引即可返回所有行                     |
+---------------------------------------------+-----------------------------------------------+ 
|可以和其它表一起聚集存储                     |不能存储为聚集表                               |
+---------------------------------------------+-----------------------------------------------+
|可以包含long和lob类型的列                    |可以包含lob类型但不可以包括long类型的列        |
+---------------------------------------------+-----------------------------------------------+

4．二级索引（也可理解为聚集索引），在IOT表的基础上,在其他列上再次创建索引.
使用二级索引查找数据时,存在以下两个阶段.
物理猜测:根据二级索引里记录的物理ROWID，去扫描其指向的IOT表所依附的索引segment里的索引节点。
逻辑猜测:由于IOT表的数据存放在索引的叶子节点里,而由于在索引里数据都是有序存放的，数据会在叶子节点中间插入，因此就存在向叶子节点的拆分问题。一旦叶子节点被拆分，数据所在的索引块就会变化，那么二级索引所指向的位置也就错误了。通过物理ROWID去访问IOT表就会找不到数据。这个时候,oracle会进行逻辑猜测，这时就不使用ROWID了，而是使用主键列的值去扫描IOT表

5．how
例：
--创建表空间
create tablespace indx
datafile 'd:/indx.dbf' size 10M

--创建IOT表
create table iot_test(
id number,
c1 varchar2(20),
c2 varchar2(20),
c3 varchar2(30),
c4 varchar2(20),
constraint pk_iot_test primary key(id) --主键约束
)
organization index --指定为索引组织表
tablespace indx --设置表空间
pctthreshold 10 --（percent threshold）留在索引块里的数据空间占总数据块大小的百分比,从0到50%
including c2 --表示从c2列开始，后面所有的列(c2,c3,c4)都放入overflow
overflow tablespace users --overflow是一种segment

备注:
优先考虑pctthreshold,也就是说如果id列的值占数据块大小的百分比已经超过了指定的pctthreshold,则尽管指定的是从c2列开始后面的列都放入overflow,但是由于违反了pctthreshold,则c1以后的列也全都放入overflow.

假设在iot_test表中,id列和c1列总共大概需要500字节,索引块大小为8KB，我们希望id列和c1列留在索引块里，则留在索引块里的数据量占整个索引块大小的6%(500/8*1024).

5．测试性能
---测试数据
create or replace procedure test_iot
(
    p_line in number
) is
    v_line number;
begin
    v_line:=p_line;
    if(v_line<1) then
        dbms_output.put_line('请输入>=1的整数');
    end if;
    for v in 1..p_line loop
    insert into iot_test values(v,'test','test','test','test');
    end loop;
end test_iot;

call test_iot(100000);

--比较的表
create table iot_test1(
id number,
c1 varchar2(20),
c2 varchar2(20),
c3 varchar2(30),
c4 varchar2(20),
constraint pk_iot_test1 primary key(id) --主键约束
)

create or replace procedure test1
(
    p_line in number
) is
    v_line number;
begin
    v_line:=p_line;
    if(v_line<1) then
        dbms_output.put_line('请输入>=1的整数');
    end if;
    for v in 1..p_line loop
    insert into iot_test1 values(v,'test','test','test','test');
    end loop;
end test1;

call test1(100000);

--速度比较
select id,c1 from iot_test1 where rownum<1000;
耗时0.425
select id,c1 from iot_test where rownum<1000;
耗时0.365

 

 

4．索引的分区:
create table customer_range
(
       customerID number(10)
)
partition by range(customerID)
(
       partition p1 values less than(100),  --<100
       partition p2 values less than(200),  --100<=customerId<200
       partition p3 values less than(maxvalue) --null 不在所有范围
)
本地分区:索引的分区与基表的分区一样，基表的分区变了索引的分区也变了
create index index_1 on customer(customerID) local;
全局分区:索引的分区与基表的分区不一样，基表的分区不影响索引的分区
create index index_1 on customer(customerID)

 

5．Oracle全表扫描
　
　　Oracle全表扫描(Full Table Scans, FTS)

　　为实现Oracle全表扫描，Oracle读取表中所有的行，并检查每一行是否满足语句的WHERE限制条件。Oracle顺序地读取分配给表的每个数据块，直到读到表的最高水线处(high water mark, HWM，标识表的最后一个数据块)。一个多块读操作可以使一次I/O能读取多块数据块(db_block_multiblock_read_count参数设定)，而不是只读取一个数据块，这极大的减少了I/O总次数，提高了系统的吞吐量，所以利用多块读的方法可以十分高效地实现Oracle全表扫描，而且只有在Oracle全表扫描的情况下才能使用多块读操作.

　　由FTS模式读入的数据被放到高速缓存的Least Recently Used (LRU)列表的尾部，这样可以使其快速交换出内存，从而不使内存重要的数据被交换出内存。

　　使用FTS的前提条件：在较大的表上不建议使用Oracle全表扫描，除非取出数据的比较多，超过总量的5% -- 10%，或你想使用并行查询功能时。

　　使用Oracle全表扫描的例子：

　　explain plan for select * from dual; Query Plan SELECT STATEMENT [CHOOSE] Cost= TABLE ACCESS FULL DUAL

　　行的ROWID指出了该行所在的数据文件、数据块以及行在该块中的位置，所以通过ROWID来存取数据可以快速定位到目标数据上，是Oracle存取单行数据的最快方法。

　　为了通过ROWID存取表，Oracle 首先要获取被选择行的ROWID，或者从语句的WHERE子句中得到，或者通过表的一个或多个索引的索引扫描得到。Oracle然后以得到的ROWID为依据定位每个被选择的行。

　　这种存取方法不会用到多块读操作，一次I/O只能读取一个数据块。我们会经常在执行计划中看到该存取方法，如通过索引查询数据。

　　使用ROWID存取的方法：

　　explain plan for select * from dept where rowid = 'AAAAyGAADAAAAATAAF'; Query Plan SELECT STATEMENT [CHOOSE] Cost=1 TABLE ACCESS BY ROWID DEPT [ANALYZED]