Oracle 索引的简单使用

一、创建测试用表

SQL> create table test.tab01 as
  2  select rownum as id,
  3  dbms_random.string('x', 3) name_random_string,
  4  to_char(sysdate + rownum / 24 / 3600, 'yyyy-mm-dd hh24:mi:ss') as date_create_date,
  5  trunc(dbms_random.value(0, 10000000)) as id_random_id from dual connect by level <= 5000000;

表已创建。

SQL>

SQL> select count(*) from test.tab01;

  COUNT(*)
----------
   5000000

SQL>

二、查看表里面的数据

三、查看执行计划

SQL> alter session set statistics_level=all;

会话已更改。

SQL>

执行查询语句：

SQL> select * from test.tab01 where name_random_string='PAD';

查看执行计划：

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  1u88r598syszx, child number 0
-------------------------------------
select * from test.tab01 where name_random_string='PAD'

Plan hash value: 2044041692

----------------------------------------------------------------------------------------------
| Id  | Operation         | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |      1 |        |    117 |00:00:01.77 |   28201 |  28189 |
|*  1 |  TABLE ACCESS FULL| TAB01 |      1 |    312 |    117 |00:00:01.77 |   28201 |  28189 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("NAME_RANDOM_STRING"='PAD')

Note
-----
   - dynamic sampling used for this statement (level=2)


已选择22行。

SQL>

从执行计划可以看出：

1、访问表的方式是全表扫描，执行了1次，CBO估算返回312行，实际返回117行，总共累计花费时间为1.77S

2、过滤条件是："NAME_RANDOM_STRING"='PAD'

3、需要注意的是： dynamic sampling used for this statement (level=2) 表示该表的统计信息还没有收集

四、在过滤条件NAME_RANDOM_STRING字段上创建索引

SQL> create index random_string_idx on test.tab01(name_random_string) tablespace test;

索引已创建。

SQL> select index_name,index_type,tablespace_name,uniqueness from all_indexes where table_name ='TAB01';

INDEX_NAME                     INDEX_TYPE                  TABLESPACE_NAME                UNIQUENES
------------------------------ --------------------------- ------------------------------ ---------
RANDOM_STRING_IDX              NORMAL                      TEST                           NONUNIQUE

SQL>

五、再次来执行查询语句

SQL> select * from test.tab01 where name_random_string='PAC';

查看执行计划：

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  0dmk39djfc4kc, child number 0
-------------------------------------
select * from test.tab01 where name_random_string='PAC'

Plan hash value: 1432442977

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   |      1 |        |    106 |00:00:00.62 |     116 |    122 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TAB01             |      1 |    106 |    106 |00:00:00.62 |     116 |    122 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
|*  2 |   INDEX RANGE SCAN          | RANDOM_STRING_IDX |      1 |    106 |    106 |00:00:00.01 |      11 |      0 |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("NAME_RANDOM_STRING"='PAC')

Note
-----
   - dynamic sampling used for this statement (level=2)

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------


已选择23行。

SQL>

从执行计划可以看出：

1、访问的方式是：INDEX RANGE SCAN，执行了1次，COB估算返回106行，实际返回106行，INDEX RANGE SCAN花费0.01S

2、通过rowID回表花费0.62S，总共花费0.63S

3、需要注意的是： dynamic sampling used for this statement (level=2) 表示该表的统计信息还没有收集

六、同一条SQL查询语句，让其使用HINT强制走全表扫描，再来看执行计划

使用HINT固定走全表扫描：SELECT /*+FULL(tab01) */ * from test.tab01 where name_random_string='PAC';

查看执行计划：

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  1dysybncy3yrx, child number 0
-------------------------------------
SELECT /*+FULL(tab01) */ * from test.tab01 where
name_random_string='PAC'

Plan hash value: 2044041692

----------------------------------------------------------------------------------------------
| Id  | Operation         | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |      1 |        |    106 |00:00:01.85 |   28201 |  28189 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| TAB01 |      1 |    106 |    106 |00:00:01.85 |   28201 |  28189 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("NAME_RANDOM_STRING"='PAC')

Note
-----
   - dynamic sampling used for this statement (level=2)

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------


已选择23行。

SQL>

从执行计划可以看出：

1、表访问方式为：TABLE ACCESS FULL，执行了1次，COB估算返回106行，实际返回106行，累计花费时间1.85S。

可见，使用了索引后访问速度确实要快一些