訪問索引的方法
1.B樹索引的結構
索引分支塊
包含指向索引分支塊/葉子塊的指針和索引鍵值列
（這裡指針指的是指向索引分支塊/葉子塊的塊地址RDBA。每個索引分支塊有兩種類型的指針，一種lmc（left most child）,一種索引行記錄所記錄的指針。lmc那就是指向最左邊的那個分支塊/葉子塊）
lmc指向的分支塊/葉子塊的最大值一定<lmc在的那個分支塊的鍵值列的最小值
簡單來說就是lmc那個地址>它下面最左邊的分支塊/葉子塊的地址
索引行記錄所記錄的指針指向的分支塊/葉子塊的索引鍵值列的最小值一定>=該行記錄索引鍵值列的值
例如索引行記錄AD指向的葉子塊都以AD開頭，所以AD<=AD開頭的葉子塊的值，相當於AD指向的葉子塊都是AD開頭
索引葉子塊
索引鍵值和ROWID
2.常見訪問B樹索引的方法
————————————————————————————————————————————————————————
（1）索引唯一性掃描（Index Unique Scan）
針對Unique Index
where條件是等值
所以至多返回一條記錄
————————————————————————————————————————————————————————
（2）索引範圍掃描（Index Range Scan）
針對Unique Index時，where條件是between，> ,<等
針對nonUnique Index時，可以=，>,<等
可能返回多條記錄
—————————————————————————————————————————————————————————
（3）索引全掃描（Index Full Scan）
從左至右順序掃描全部葉子塊，單塊讀，不能並行
因為索引有序，索引結果是按照鍵值列的順序排序。
使用前提：索引鍵值列只要有一個是not null
SQL> set autotrace on
SQL> select empno ,ename from EMP; --只有針對empno欄位的索引，這時候無法用到索引，走表全部掃描
EMPNO ENAME
---------- ----------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
EMPNO ENAME
---------- ----------
7900 JAMES
7902 FORD
7934 MILLER
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 140 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 14 | 140 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
837 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
收集統計信息
SQL> exec dbms_stats.gather_table_stats(OWNNAME=>'SCOTT',tabname=>'EMP',estimate_percent=>100,cascade=>true,method_opt=>'for all columns size 1')
Index Full Scan實驗
實驗一：查詢有唯一索引的列
SQL> select empno from emp;
EMPNO
----------
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
EMPNO
----------
7900
7902
7934
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 179099197
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 56 | 1 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN | PK_EMP | 14 | 56 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
686 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
empno列上有唯一性索引PK_EMP，所以走的是Index Full Scan
實驗二：在允許為空的列上建索引
SQL> create index idx1_emp on emp(ename,deptno);
Index created.
SQL> exec dbms_stats.gather_table_stats(OWNNAME=>'SCOTT',tabname=>'EMP',estimate_percent=>100,cascade=>true,method_opt=>'for all columns size 1')
PL/SQL procedure successfully completed.
SQL> select ename,deptno from emp;
ENAME DEPTNO
---------- ----------
SMITH 20
ALLEN 30
WARD 30
JONES 20
MARTIN 30
BLAKE 30
CLARK 10
SCOTT 20
KING 10
TURNER 30
ADAMS 20
ENAME DEPTNO
---------- ----------
JAMES 30
FORD 20
MILLER 10
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 126 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 14 | 126 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
825 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
實驗結果：ename,deptno的鍵值都是允許為空，在上面列建索引，走TABLE ACCESS FULL
實驗三：修改一個列不允許為空
SQL> alter table emp modify(ename not null); --把ename改為not null，走的是index full scan
Table altered.
SQL> exec dbms_stats.gather_table_stats(OWNNAME=>'SCOTT',tabname=>'EMP',estimate_percent=>100,cascade=>true,method_opt=>'for all columns size 1')
PL/SQL procedure successfully completed.
SQL> select ename,deptno from emp;
ENAME DEPTNO
---------- ----------
ADAMS 20
ALLEN 30
BLAKE 30
CLARK 10
FORD 20
JAMES 30
JONES 20
KING 10
MARTIN 30
MILLER 10
SCOTT 20
ENAME DEPTNO
---------- ----------
SMITH 20
TURNER 30
WARD 30
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1165009684
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 126 | 1 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN | IDX1_EMP | 14 | 126 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
825 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
實驗結果：走Index Full Scan
實驗四：在有很多null欄位的列上建索引
SQL> create index idx2_emp on emp(comm);
Index created.
SQL> exec dbms_stats.gather_table_stats(OWNNAME=>'SCOTT',tabname=>'EMP',estimate_percent=>100,cascade=>true,method_opt=>'for all columns size 1')
PL/SQL procedure successfully completed.
SQL> select comm from emp;
COMM
----------
300
500
1400
0
COMM
----------
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 28 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 14 | 28 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
645 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
實驗結果：走TABLE ACCESS FULL ，結果也體現出包含了那些null的結果
總結：在允許為null的列上建立索引，還是會走TABLE ACCESS FULL。因為欄位允許為null，而null並不會存儲索引的。只有走全錶掃描，才能體現值得完整性。
Index Full Scan掃描結果是有序的，順序按照索引鍵值列的順序
Index Full Scan必須滿足條件：目標索引至少有一個索引鍵值列的屬性為not null.
——————————————————————————————————————————————————————————
（4）索引快速全掃描（Index Fast Full Scan）
（1）僅適用于CBO
（2）使用多塊讀，可以並行
（3）結果不一定有序
其他和index full scan一樣咯
SQL> select /*+index_ffs(emp IDX1_EMP) */ename,deptno from emp;
ENAME DEPTNO
---------- ----------
ADAMS 20
ALLEN 30
BLAKE 30
CLARK 10
FORD 20
JAMES 30
JONES 20
KING 10
MARTIN 30
MILLER 10
SCOTT 20
ENAME DEPTNO
---------- ----------
SMITH 20
TURNER 30
WARD 30
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3993716296
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 126 | 2 (0)| 00:00:01 |
| 1 | INDEX FAST FULL SCAN| IDX1_EMP | 14 | 126 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
825 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
————————————————————————————————————————————————————————
（5）索引跳躍掃描（Index Skip Scan）
簡單點來講就是你在ename,deptno兩個欄位建了索引，但是我們查詢的時候只用了deptno(沒有使用該索引的前導列)
但是僅僅使用于那些前導列的distinct值很少，後續非前導列選擇性又非常好的情景。
SQL> select ename from emp where deptno>20;
ENAME
----------
ALLEN
BLAKE
JAMES
MARTIN
TURNER
WARD
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3860167482
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 63 | 1 (0)| 00:00:01 |
|* 1 | INDEX SKIP SCAN | IDX1_EMP | 7 | 63 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEPTNO">20)
filter("DEPTNO">20)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
628 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed
