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数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。
第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。
示例
--创建测试表create table t(id int,name varchar2(10),age int);--创建存储过程，为t表插入数据create or replace procedure p_insert_t asbeginfor i in 1..2000 loopinsert into t values(i,dbms_random.string('l',10),dbms_random.value(0,100));end loop;end;--执行存储过程execute p_insert_t;
解析脚本，并且查看执行计划
SQL>explain plan forselect * 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 calls0 db block gets170 consistent gets3 physical reads0 redo size52931 bytes sent via SQL*Net to client1771 bytes received via SQL*Net from client128 SQL*Net roundtrips to/from client3 sorts (memory)0 sorts (disk)1899 rows processedSQL> 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 calls0 db block gets31 consistent gets0 physical reads0 redo size620 bytes sent via SQL*Net to client385 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client3 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 calls0 db block gets160 consistent gets1 physical reads0 redo size55758 bytes sent via SQL*Net to client1848 bytes received via SQL*Net from client135 SQL*Net roundtrips to/from client0 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 calls0 db block gets143 consistent gets0 physical reads0 redo size55758 bytes sent via SQL*Net to client1848 bytes received via SQL*Net from client135 SQL*Net roundtrips to/from client0 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 asbeginfor i in 1..20000 loopinsert 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 asbeginfor i in 1..20000 loopinsert 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 statementSQL> 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 1SQL> delete from t where id >3;19997 rows deletedSQL> commit;Commit completeSQL> 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 1alter 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;
文章转载自QJ程序员，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
