SQL> select index_name,TABLE_NAME from user_indexes order by TABLE_NAME;
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
PK_DEPT
DEPT
IDX_DEPT_CP_LOC
DEPT_CP
PK_EMP EMP
IDX_EMP_CP_MGR
EMP_CP
IDX_EMP_CP_EMPNO EMP_CP
IDX_EMP_MGR
EMP_EXECPLAN
IDX_DEPTNO_TEMP
EMP_TEMP
IDX_MGR_TEMP
EMP_TEMP
SYS_IL0000011316C00036$$ PLAN_TABLE
SQL> set autotrace traceonly exp;
SQL> select * from emp_temp where empno=7369;
Execution Plan
----------------------------------------------------------
Plan hash value: 2473744504
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP_TEMP | 1 | 37 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"=7369)
SQL> select * from emp_temp where empno=7369;
Execution Plan
----------------------------------------------------------
Plan hash value: 2473744504
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP_TEMP | 1 | 37 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"=7369)
SQL> create unique index idx_empno_temp on emp_temp(empno);
Index created.
SQL> select * from emp_temp where empno=7369;
Execution Plan
----------------------------------------------------------
Plan hash value: 367080262
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP_TEMP | 1 | 37 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | IDX_EMPNO_TEMP | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7369)
SQL> drop index idx_empno_temp;
Index dropped.
SQL> select * from emp_temp where empno=7369;
Execution Plan
----------------------------------------------------------
Plan hash value: 2473744504
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP_TEMP | 1 | 37 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"=7369)
SQL> create index idx_empno_temp on emp_temp(empno);
Index created.
SQL> select * from emp_temp where empno=7369;
Execution Plan
----------------------------------------------------------
Plan hash value: 3152835344
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP_TEMP | 1 | 37 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_EMPNO_TEMP | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7369)
SQL>
SQL> select * from emp_temp where empno>7369;
Execution Plan
----------------------------------------------------------
Plan hash value: 3152835344
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
13 |481 |
2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP_TEMP
| 13 |
481 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN
| IDX_EMPNO_TEMP | 13 | |
1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO">7369)
SQL> drop index idx_empno_temp;
Index dropped.
SQL> create unique index idx_empno_temp on emp_temp(empno);
Index created.
SQL> select * from emp_temp where empno>7369;
Execution Plan
----------------------------------------------------------
Plan hash value: 3152835344
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
13 |481 |
2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP_TEMP
| 13 |
481 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_EMPNO_TEMP | 13 |
| 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO">7369)
SQL>
--综上所诉,INDEX RANGE SCAN 是在 创建非unique index使不使用等值“=” 或者 创建unique index 使用范围运算符> < 等的情况下.
SQL> select TABLE_NAME,INDEX_NAME,UNIQUENESSfrom user_indexes where table_NAME='EMP_TEMP';
TABLE_NAME INDEX_NAME UNIQUENES
------------------------------ ------------------------------ ---------
EMP_TEMP IDX_EMPNO_TEMP NONUNIQUE
SQL> drop index idx_empno_temp;
Index dropped.
SQL> create UNIQUE index idx_empno_temp on emp_temp(empno);
Index created.
SQL> select TABLE_NAME,INDEX_NAME,UNIQUENESS from user_indexes where table_NAME='EMP_TEMP';
TABLE_NAME INDEX_NAME UNIQUENES
------------------------------ ------------------------------ ---------
EMP_TEMP IDX_EMPNO_TEMP UNIQUE
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> select empno from emp_temp;
EMPNO
----------
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2473744504
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 |
56 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP_TEMP | 14 |
56 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
676 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
SQL> ALTER TABLE MODIFY(EMPNO NOT NULL);
ALTER TABLE MODIFY(EMPNO NOT NULL)
*
ERROR at line 1:
ORA-00903: invalid table name
SQL> ALTER TABLE EMP_TEMP
MODIFY(EMPNO NOT NULL);
Table altered.
SQL> select empno from emp_temp;
EMPNO
----------
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 494924801
-----------------------------------------------------------------------------------
| Id | Operation | Name
| Rows | Bytes | Cost (%CPU)| Time
|
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 56 |1 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN | IDX_EMPNO_TEMP | 14 | 56 |1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
196 recursive calls
0 db block gets
31 consistent gets
0 physical reads
0 redo size
676 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
14 rows processed
SQL>
--搞了半天终于出来了ALTER TABLE EMP_TEMP MODIFY(EMPNO NOT NULL);
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> select /*+ index_ffs(emp_temp IDX_EMPNO_TEMP )*/empno from emp_temp;
EMPNO
----------
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2368457255
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 56 | 2(0)| 00:00:01 |
| 1 | INDEX FAST FULL SCAN| IDX_EMPNO_TEMP | 14 | 56 | 2(0)| 00:00:01 |
---------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
676 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
SQL>
--------------------------------------------------------------------------------------------------------------------------------
SQL> create table skip_idx_emp(gender varchar2(1),emp_id number);
Table created.
SQL> ALTER TABLE skip_idx_emp modify(emp_id not null);
Table altered.
SQL> create index idx_skip_idx_emp on skip_idx_emp(gender,emp_id);
Index created.
SQL> begin
2 for i in 1 .. 5000 loop
3 insert into skip_idx_emp values('F',i);
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> begin
2 for i in 5001 ..10000 loop
3 insert into skip_idx_emp values('M',i);
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> select * from skip_idx_emp where emp_id=1000;
G EMP_ID
- ----------
F 1000
Execution Plan
----------------------------------------------------------
Plan hash value: 1119378318
----------------------------------------------------------------------------------
| Id | Operation | Name| Rows | Bytes | Cost (%CPU)| Time
|
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| SKIP_IDX_EMP | 1 | 15 | 6 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMP_ID"=1000)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
49 consistent gets
0 physical reads
0 redo size
579 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> set autotrace traceonly;
SQL> select * from skip_idx_emp where emp_id=1000;
Execution Plan
----------------------------------------------------------
Plan hash value: 1119378318
----------------------------------------------------------------------------------
| Id | Operation | Name| Rows | Bytes | Cost (%CPU)| Time
|
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| SKIP_IDX_EMP | 1 | 15 | 6 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMP_ID"=1000)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
24 consistent gets
0 physical reads
0 redo size
579 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> select /*+ index_ss(skip_idx_emp IDX_SKIP_IDX_EMP)*/* from skip_idx_emp where emp_id=1000;
Execution Plan
----------------------------------------------------------
Plan hash value: 3164335757
-------------------------------------------------------------------------------------
| Id | Operation | Name
| Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
15 |11 (0)| 00:00:01 |
|* 1 | INDEX SKIP SCAN | IDX_SKIP_IDX_EMP | 1 |
15 |11 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMP_ID"=1000)
filter("EMP_ID"=1000)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
33 consistent gets
0 physical reads
124 redo size
579 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
--加了hint才实现,没加的时候 并没出现,与书籍 不同,除非先统计信息,如:
SQL> exec dbms_stats.gather_table_stats(ownname =>'SCOTT',TABNAME =>'SKIP_IDX_EMP',estimate_percent =>100,cascade =>true,method_opt
=>'for all columns size 1');
PL/SQL procedure successfully completed.
SQL> select * from skip_idx_emp;
10000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1119378318
----------------------------------------------------------------------------------
| Id | Operation | Name
| Rows | Bytes | Cost (%CPU)| Time
|
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 50000 | 6 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| SKIP_IDX_EMP | 10000 | 50000 | 6 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
690 consistent gets
0 physical reads
0 redo size
234300 bytes sent via SQL*Net to client
7795 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed
SQL> select * from skip_idx_emp where emp_id=1000;
Execution Plan
----------------------------------------------------------
Plan hash value: 3164335757
-------------------------------------------------------------------------------------
| Id | Operation
| Name
| Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
|
1 | 5 |
3 (0)| 00:00:01 |
|* 1 | INDEX SKIP SCAN
| IDX_SKIP_IDX_EMP |
1 | 5 |
3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMP_ID"=1000)
filter("EMP_ID"=1000)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
24 consistent gets
0 physical reads
0 redo size
579 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
------------------------------------------------------------------------------------------------------------------------------------