Oracle笔记之索引

最新推荐文章于 2024-11-02 12:29:24 发布

JAVA开发区

最新推荐文章于 2024-11-02 12:29:24 发布

阅读量552

点赞数 4

分类专栏： Oracle 文章标签： oracle 数据库

本文链接：https://blog.csdn.net/qq_45609369/article/details/121543024

版权

Oracle 专栏收录该内容

27 篇文章 16 订阅

订阅专栏

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;

JAVA开发区

关注

4
点赞
踩
2

收藏

觉得还不错? 一键收藏
打赏
1
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录