/*
索引
索引由三部分组成，根块(root),Branch(茎块)，Leaf(叶子块)。其中Leaf块主要存储了key column value(索引列具体值)，以及能具体
定位到数据块位置的rowid。

索引的创建过程：
1.要建索引先排序，将索引列的值顺序取出，及该行的rowid放入到内存中。
2.依次将内存中的顺序存放的值和rowid存放入索引块中。
3.当填满两个索引块后，oracle会产生一个块，用于管理同级的叶子块。这个块记录了叶子块的信息，并不记录索引列的键值，所以使用的空间比较少。
4.当管理叶子块的块被填满后，oracle又会产生一个上一级管理块，依次循环。同级两块需要管理。

索引结构的三大重要特点
1.索引的高度比较低
2.索引存储列值
3.索引本身是有序的
*/

----------------------------------------------索引高度较低验证-----------------------------------------
--索引的大小和高度是巨大差别的，可能大小差好多倍，但高度却一样。
--构造T1-T7表，记录从5到500W
CREATE TABLE T1 AS SELECT ROWNUM AS ID,ROWNUM+1 AS ID2 FROM DUAL CONNECT BY LEVEL<=5;
CREATE TABLE T2 AS SELECT ROWNUM AS ID,ROWNUM+1 AS ID2 FROM DUAL CONNECT BY LEVEL<=50;
CREATE TABLE T3 AS SELECT ROWNUM AS ID,ROWNUM+1 AS ID2 FROM DUAL CONNECT BY LEVEL<=500;
CREATE TABLE T4 AS SELECT ROWNUM AS ID,ROWNUM+1 AS ID2 FROM DUAL CONNECT BY LEVEL<=5000;
CREATE TABLE T5 AS SELECT ROWNUM AS ID,ROWNUM+1 AS ID2 FROM DUAL CONNECT BY LEVEL<=50000;
CREATE TABLE T6 AS SELECT ROWNUM AS ID,ROWNUM+1 AS ID2 FROM DUAL CONNECT BY LEVEL<=500000;
CREATE TABLE T7 AS SELECT ROWNUM AS ID,ROWNUM+1 AS ID2 FROM DUAL CONNECT BY LEVEL<=5000000;

--创建索引
CREATE INDEX IDX_ID_T1 ON T1(ID);
CREATE INDEX IDX_ID_T2 ON T2(ID);
CREATE INDEX IDX_ID_T3 ON T3(ID);
CREATE INDEX IDX_ID_T4 ON T4(ID);
CREATE INDEX IDX_ID_T5 ON T5(ID);
CREATE INDEX IDX_ID_T6 ON T6(ID);
CREATE INDEX IDX_ID_T7 ON T7(ID);

--查看索引大小
SELECT SEGMENT_NAME,BYTES/1024 FROM DBA_SEGMENTS WHERE SEGMENT_NAME IN 
(
 'IDX_ID_T1',
 'IDX_ID_T2',
 'IDX_ID_T3',
 'IDX_ID_T4',
 'IDX_ID_T5',
 'IDX_ID_T6',
 'IDX_ID_T7'
);
--查看索引高度

SELECT INDEX_NAME,
       BLEVEL,--索引高度,BLEVEL=0表示1层,BLEVEL=1表示2层
       LEAF_BLOCKS,--Number of leaf blocks in the index
       NUM_ROWS,
       DISTINCT_KEYS,
       CLUSTERING_FACTOR
  FROM USER_IND_STATISTICS
 WHERE TABLE_NAME IN ('T1', 'T2', 'T3', 'T4', 'T5', 'T6', 'T7');


INDEX_NAME       BLEVEL LEAF_BLOCKS   NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR
----------------------- ----------- ---------- ------------- -----------------
IDX_ID_T1             0           1          5             5                 1
IDX_ID_T2             0           1         50            50                 1
IDX_ID_T3             1           2        500           500                 1
IDX_ID_T4             1          11       5000          5000                 9
IDX_ID_T5             1         110      50000         50000               101
IDX_ID_T6             2        1113     500000        500000              1035
IDX_ID_T7             2       12023    5134040       4994100             22527

--相比T6表与T7表的查询效率，使用索引的查询效率一致，因为索引的高度低
--以下内容已多次执行消除 物理读和递归
admin@ORCL> select *  from T6 where id = 10;

已用时间:  00: 00: 00.07

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

-------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time
-------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |     9 |     4   (0)| 00:00:
|   1 |  TABLE ACCESS BY INDEX ROWID| T6        |     1 |     9 |     4   (0)| 00:00:
|*  2 |   INDEX RANGE SCAN          | IDX_ID_T6 |     1 |       |     3   (0)| 00:00:
-------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=10)


统计信息
----------------------
          0  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        462  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
          
admin@ORCL> select *  from T7 where id = 10;

已用时间:  00: 00: 00.01

执行计划
---------