Oracle索引
索引: 提高查询速度的一种手段 -->目录
1、唯一性较好字段适合建立索引
2、大数据量才有效果
3、主键|唯一: 唯一索引
索引的语法
create index 索引名 on表名 (字段列表...)
drop index 索引名
create index idx_emp on emp(sal,ename);
drop index idx_emp;
select * from emp order by sal,ename
索引的优点和缺点
- 第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
- 第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
- 第三，对于那些定义为blob数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。
- 第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。
1. 示例
--创建测试表
create table t(id int,name varchar2(10),age int);
--创建存储过程，为t表插入数据
create or replace procedure p_insert_t as
begin
for i in 1..2000 loop
insert into t values(i,dbms_random.string('l',10),dbms_random.value(0,100));
end loop;
end;
--执行存储过程
execute p_insert_t;
解析脚本，并且查看执行计划
SQL>explain plan for
select * from t where id =345; --要解析的SQL脚本
SQL>select * from table(DBMS_XPLAN.DISPLAY); --查看执行计划
--创建索引后
create index idx_t_id on t(id);
select * from t where id=345;--查看执行计划和数据统计结果
explain plan for select * from t where id =345;
select * from table(dbms_xplan.display);
查看执行计划还可以在plsql下运行
set autotrace traceonly;
set timing on;
select * from t where id=345;
2. 不同的选择率(基数)会影响索引的使用
SQL> select * from t where id <1900;
已选择1899行。
已用时间: 00: 00: 00.06
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1899 | 62667 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1899 | 62667 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"<1900)
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
136 recursive calls
0 db block gets
170 consistent gets
3 physical reads
0 redo size
52931 bytes sent via SQL*Net to client
1771 bytes received via SQL*Net from client
128 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1899 rows processed
SQL> select * from t where id <5;
已用时间: 00: 00: 00.02
执行计划
----------------------------------------------------------
Plan hash value: 514881935
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 132 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 4 | 132 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T_ID | 4 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"<5)
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
94 recursive calls
0 db block gets
31 consistent gets
0 physical reads
0 redo size
620 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
4 rows processed
3. 使用绑定变量会使用语句会使用软解析成为可能,但是有可能影响正确使用执行计划
SQL> select * from t where id<2001; --不使用绑定变量
已选择2000行。
已用时间: 00: 00: 00.07
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2000 | 66000 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 2000 | 66000 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"<2001)
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
9 recursive calls
0 db block gets
160 consistent gets
1 physical reads
0 redo size
55758 bytes sent via SQL*Net to client
1848 bytes received via SQL*Net from client
135 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2000 rows processed
--使用绑定变量
SQL> var myid number;
SQL> execute :myid:=2001;
1* select * from t where id<:myid
已选择2000行。
已用时间: 00: 00: 00.05
执行计划
----------------------------------------------------------
Plan hash value: 514881935
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------
|0 | SELECT STATEMENT | | 100 | 3300 | 2 (0)| 00:00:01 |
|1 | TABLE ACCESS BY INDEX ROWID| T | 100 | 3300 |2 (0)| 00:00:01 |
|*2 | INDEX RANGE SCAN | IDX_T_ID | 18 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"<TO_NUMBER(:MYID))
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
143 consistent gets
0 physical reads
0 redo size
55758 bytes sent via SQL*Net to client
1848 bytes received via SQL*Net from client
135 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2000 rows processed
4. 查询索引的信息
SQL> select * from user_indexes where table_name='T';
SQL> select * from user_ind_columns where table_name='T';
5. 基于函数的索引
create table t(id int,name varchar2(4),age int);
create or replace procedure p_insert_t as
begin
for i in 1..20000 loop
insert into t values(i,dbms_random.string('a',4),dbms_random.value(0,100));--大小字母混合
end loop;
end;
insert into t values(23458,'AbcD',89);
insert into t values(23453,'abcD',89);
create index idx_t_name on t(name);
SQL> set autotrace traceonly exp;
SQL> select * from t where upper(name)='ABCD';
已用时间: 00: 00: 00.02
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 60 | 14 (8)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 2 | 60 | 14 (8)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(UPPER("NAME")='ABCD')
Note
-----
- dynamic sampling used for this statement
可以发现没有使用索引
drop index idx_t_name;--删除重新创建基于函数的索引
SQL> create index idx_t_name on t(upper(name));
1* select * from t where upper(name)='ABCD'
已用时间: 00: 00: 00.00
执行计划
----------------------------------------------------------
Plan hash value: 1816869952
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 60 | 12 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 2 | 60 | 12 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T_NAME | 80 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(UPPER("NAME")='ABCD')
Note
-----
- dynamic sampling used for this statement
6. 索引的重建(比删除后再建立高效)
当我们创建索引时，oracle会为索引创建索引树，表和索引树通过rowid(伪列)来定位数据。当表里的数据发生更新时，oracle会自动维护索引树。但是在索引树中没有更新操作，只有删除和插入操作。
例如在某表id列上创建索引，某表id列上有值“101”，当我将“101”更新为“110”时，oracle同时会来更新索引树，但是oracle先将索引树中的“101”标示为删除（实际并未删除，只是标示一下），然后再将“110”写到索引树中。
如果表更新比较频繁，那么在索引中删除标示会越来越多，这时索引的查询效率必然降低，所以我们应该定期重建索引。来消除索引中这些删除标记。
一般不会选择先删除索引，然后再重新创建索引，而是rebuild索引。在rebuild期间，用户还可以使用原来的索引，并且rebuild新的索引时也会利用原来的索引信息，这样重建索引会块一些。
SQL> analyze index idx_t_id validate structure;
create table t(id int,name varchar2(4),age int);
create or replace procedure p_insert_t as
begin
for i in 1..20000 loop
insert into t values(i,dbms_random.string('a',4),dbms_random.value(0,100));
end loop;
end;
exec p_insert_t ;
create index idx_t_id on t(id);
SQL> select * from t where id = 345;
已用时间: 00: 00: 00.02
执行计划
----------------------------------------------------------
Plan hash value: 514881935
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 30 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T_ID | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=345)
Note
-----
- dynamic sampling used for this statement
SQL> select index_name,table_name,blevel,leaf_blocks,degree from user_indexes where table_name='T';
INDEX_NAME TABLE_NAME BLEVEL LEAF_BLOCKS DEGREE
------------------------------ ------------------------------ ---------- ----------- ----------------------------------------
IDX_T_ID T 1 44 1
SQL> delete from t where id >3;
19997 rows deleted
SQL> commit;
Commit complete
SQL> select index_name,table_name,blevel,leaf_blocks,degree from user_indexes where table_name='T';
INDEX_NAME TABLE_NAME BLEVEL LEAF_BLOCKS DEGREE
------------------------------ ------------------------------ ---------- ----------- ----------------------------------------
IDX_T_ID T 1 44 1
alter index idx_t_id rebuild;
SQL> select index_name,table_name,blevel,leaf_blocks,degree from user_indexes where table_name='T';
或者在删除大量数据后为了释放空间使用了
SQL> alter table t move;命令
但是此时的索引无效了
1* select * from t where id = 345
已用时间: 00: 00: 00.00
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 14 (8)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1 | 10 | 14 (8)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=345)
SQL> alter index idx_t_id rebuild online; --重建后又变为有效了
1* select * from t where id = 345
已用时间: 00: 00: 00.00
执行计划
----------------------------------------------------------
Plan hash value: 514881935
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 10 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T_ID | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=345)
7. 更改索引的名称
SQL> alter index idx_t_id rename to my_index_t_id;
3698
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
