索引的几种访问方法

最新推荐文章于 2024-10-16 14:00:00 发布

5icode.top

最新推荐文章于 2024-10-16 14:00:00 发布

阅读量1.1k

点赞数

CC 4.0 BY-SA版权

分类专栏： oracle 文章标签：索引

本文链接：https://blog.youkuaiyun.com/hzdxw/article/details/39716713

oracle 专栏收录该内容

30 篇文章

订阅专栏

本文通过SQL实例深入探讨了如何创建、管理和利用索引来优化数据库查询性能，包括创建普通索引、唯一索引、非唯一索引，以及如何在特定场景下选择合适的索引类型来提升查询效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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.

--搞了半天终于出来了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