索引的几种访问方法
来源:互联网 发布:Linux持续ping命令 编辑:程序博客网 时间:2024/06/07 00:16
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>
------------------------------------------------------------------------------------------------------------------------------------
- 索引的几种访问方法
- oracle 索引访问的几种方法
- 索引的几种访问方法-续(SKIP SCAN)
- Magento刷新索引的几种方法
- 在OLTP中,索引访问的几种常规操作
- VB 访问ACCESS的几种方法
- 访问控件的几种方法
- dom访问的几种方法
- 前台访问后台的几种方法
- 访问数据库的几种方法
- 几种访问谷歌的方法
- 几种访问谷歌的方法
- opencv访问元素的几种方法
- JavaScript访问后台方法的几种实现方法
- Struts2的Action中方法的几种访问方式
- 【Oracle优化笔记】访问索引的方法
- ASP中页面限权访问的几种方法
- ASP中页面限权访问的几种方法
- 【网络流】 HDU4240 Route Redundancy 基础
- equals ==
- 【iOS开发-27】解决方案:An error was encountered while running(Domain=FBSOpenApplicationErrorDomain, Code=4)
- BLAS/LAPACK 函数/子程序命名规则
- 拉格朗日插值
- 索引的几种访问方法
- 杭电oj find your present (2)
- HDFS概述
- 在VC中调用WORD(显示,修改,存盘,运行宏)
- 1410010855-ny-Dinner
- 单例
- C++类对象大小的计算(一)常规类大小计算
- Apply Bug10010310 On Oracle RAC 10.2.0.5
- linux程序管理