索引的几种访问方法

本文通过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.


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> 


------------------------------------------------------------------------------------------------------------------------------------

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

5icode.top

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值