Oracle索引

最新推荐文章于 2023-07-07 09:43:11 发布
最新推荐文章于 2023-07-07 09:43:11 发布
阅读量98 收藏 1
点赞数
分类专栏： linux SQL oracle
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_38170766/article/details/105519344
版权
linux 同时被 3 个专栏收录
29 篇文章 0 订阅
订阅专栏
oracle
15 篇文章 1 订阅
订阅专栏
SQL
9 篇文章 0 订阅
订阅专栏

索引的概念

索引是oracle的一个对象，是否使用索引由oracle决定，索引中存储了特定列的排序数据，实现对表的快速访问。

使用索引可以很快速查找到建立索引时列的值所在的行，而不必对表实现全表扫描，适当使用索引可以减少磁盘的I/O量。

索引的特点：

  1. 对于具有只读特性或较少插入、更新或删除操作的大表通常可以提高查询速度。
  2. 可以对表的一列或多列建立索引。
  3. 建立索引的数量没有限制。
  4. 索引需要磁盘储存，需要oracle自动维护。
  5. 索引对用户透明，是否使用索引是oracle决定的。

Oracle实现数据访问的方法

  1. 全表扫描
  2. 通过ROWID
  3. 使用索引

  • 全表扫描

SQL> explain plan for select*from dual;

已解释。

SQL> select * from table(dbms_xplan.display);  

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

已选择8行。

  • 通过行ID(略）

  • 使用索引

通过索引找到数据行的ROWID，然后通过ROWID直接到表中查找数据，这种方式成为索引查找或者索引扫描。

因为一个ROWID对应一个数据行，因此这种方式采用的也是单块读。

在索引中，除了存储每一个索引值，还会存储相应的ROWID，索引扫描分为两步：

  1. 扫描索引得到相应的ROWID。
  2. 通过找到ROWID从表中读取对应的数据。

通过执行计划判断索引的使用

SQL> explain plan for select * from scott.emp where empno=10;

已解释。

SQL>  select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2949544139

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    38 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    38 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

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

   2 - access("EMPNO"=10)

已选择14行。

  索引扫描类型

根据索引类型、数据分布以及SQL等确定索引的扫描类型。

  1. 索引唯一扫描（INDEX UNIQUE SCAN）
  2. 索引范围扫描（INDEX RANGE SCAN）
  3. 索引全扫描（INDEX FULL SCAN）
  4. 索引快速扫描（INDEX FAST FULL SCAN）

  • 索引唯一扫描

索引唯一扫描通过指定UNIQUE键、主键，oracle通常返回一个数据行，因此采用的索引唯一扫描。

SQL> explain plan for select * from emp where empno=10;

已解释。

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2949544139

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    38 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    38 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

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

   2 - access("EMPNO"=10)

已选择14行。

  • 索引范围扫描

如果发生如下情况时候需要使用使用索引范围扫描：

在unique键上使用range操作符（> , < , <> , >= , <= , between）

在组合索引上，只使用部分列进行查询，导致查询出多行

对非唯一索引列上进行查询

使用索引范围扫描

SQL> explain plan for select * from emp where empno>10;     

已解释。

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 169057108

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |    14 |   532 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |    14 |   532 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | PK_EMP |    14 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

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

   2 - access("EMPNO">10)

已选择14行。

  • 扫描全索引

当使用索引全扫描时，查询出的数据必须全部从索引中得到

SQL> explain plan for select * from emp;

已解释。

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   532 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |   532 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

已选择8行。

  • 索引快速扫描

索引快速扫描，扫描索引快中的所有数据块，与全扫描相类似，但是索引快速扫描不进行数据排序，在这种方式下，可以使用多块读功能、也可以使用并行读功能，最大化吞吐量。

使用索引快速扫描

SQL> explain plan for select empno,ename from emp;

已解释。

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   140 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |   140 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

已选择8行。

限制索引使用的情况

很多情况及时创建了索引，也会导致索引不能使用。

  • 使用不等于运算符

使用度等于运算符 < , > , != 在where中使用这些不等于条件，将会导致索引失败

SQL> explain plan for select * from emp where empno<>10;

已解释。

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   532 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |    14 |   532 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

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

   1 - filter("EMPNO"<>10)

已选择13行。

  • 使用IS NULL 或者 IS NOT NULL

在where子句中使用IS NULL或者IS NOT NULL 同样会限制索引使用。

NULL判断影响索引使用（基于emp表SAL列创建B树索引）

SQL> select column_name from user_ind_columns where table_name='EMP';  

COLUMN_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EMPNO

SQL> explain plan for select * from emp;

已解释。

SQL> select column_name from user_ind_columns where table_name='EMP';  

COLUMN_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EMPNO

SQL> create index idx_sal_emp on emp(sal);

索引已创建。

SQL> select column_name from user_ind_columns where table_name='EMP';  

COLUMN_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SAL
EMPNO

不走索引走全表扫描

SQL> explain plan for select empno,ename,deptno from emp;

已解释。

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   182 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |   182 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

已选择8行。

建立索引

oracle建立索引使用CREATE INDEX来建立

对EMP表建立索引

SQL> create index emp_ename_idx on emp(ename);

索引已创建。

使用USER_INDEXES数据字典查询索引信息

从数据字典 USER_INDEXES可以详细地查看当前用户所拥有的索引信息，

SQL> select index_name,index_type,table_name,tablespace_name from user_indexes;

INDEX_NAME                                                                                 INDEX_TYPE
------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------------
TABLE_NAME                                                                                 TABLESPACE_NAME
------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------
PK_EMP                                                                                     NORMAL
EMP                                                                                        USERS

IDX_SAL_EMP                                                                                NORMAL
EMP                                                                                        USERS

EMP_ENAME_IDX                                                                              NORMAL
EMP                                                                                        USERS


INDEX_NAME                                                                                 INDEX_TYPE
------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------------
TABLE_NAME                                                                                 TABLESPACE_NAME
------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------
PK_DEPT                                                                                    NORMAL
DEPT                                                                                       USERS

创建索引表空间

SQL> create tablespace index_tbs
  2  datafile '/u01/app/oracle/oradata/tiqs/index_tbsl.dbf' size 100M autoextend on ;

表空间已创建。

创建多列索引

SQL> create index emp_name_sal_index on emp(ename,sal)
  2  tablespace index_tbs;

索引已创建。

查看多列索引


SQL> select index_name,table_name,tablespace_name from user_indexes where index_name like '%EMP%';

INDEX_NAME                                                                                 TABLE_NAME
------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------------------------------------------------------------------
PK_EMP                                                                                     EMP
USERS

IDX_SAL_EMP                                                                                EMP
USERS

EMP_ENAME_IDX                                                                              EMP
USERS


INDEX_NAME                                                                                 TABLE_NAME
------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------------------------------------------------------------------
EMP_NAME_SAL_INDEX                                                                         EMP
INDEX_TBS

查看索引

数据字典USER_IND_COLUMNS使得我们可以很方便查找一个索引所对应列的信息。

查询索引列相关的信息

SQL> select index_name,table_name,column_name from user_ind_columns;

INDEX_NAME                                                                                 TABLE_NAME
------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------
COLUMN_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EMP_ENAME_IDX                                                                              EMP
ENAME

EMP_NAME_SAL_INDEX                                                                         EMP
SAL

EMP_NAME_SAL_INDEX                                                                         EMP
ENAME


INDEX_NAME                                                                                 TABLE_NAME
------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------
COLUMN_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
IDX_SAL_EMP                                                                                EMP
SAL

PK_DEPT                                                                                    DEPT
DEPTNO

PK_EMP                                                                                     EMP
EMPNO


已选择6行。

重建索引

索引需要维护，不然如果建立了索引的表中有大量的删除和插入操作会使得索引很大，因为删除操作后，删除值所占用的索引空间不能够诶索引自动重新使用，而插入操作会不断使得索引变大

SQL> alter index emp_name_sal_index rebuild;

索引已更改。

SQL> alter index emp_name_sal_index rebuild tablespace tablespace_name;

索引已更改。

SQL> alter index emp_name_sal_index rebuild online;

索引已更改。

维护索引

维护索引就是修改索引的各种参数，在维护索引前首先需要指导当前索引的参数设置。

查询当前索引的参数设置

SQL> select index_name,pct_free,pct_increase,initial_extent,next_extent from  user_indexes;

INDEX_NAME                                                                                   PCT_FREE PCT_INCREASE INITIAL_EXTENT NEXT_EXTENT
------------------------------------------------------------------------------------------ ---------- ------------ -------------- -----------
PK_EMP                                                                                             10                       65536     1048576
IDX_SAL_EMP                                                                                        10                       65536     1048576
EMP_ENAME_IDX                                                                                      10                       65536     1048576
EMP_NAME_SAL_INDEX                                                                                 10                       65536     1048576
PK_DEPT                                                                                            10                       65536     1048576

通过rubuild修改索引参数

手动增加索引的磁盘空间

SQL> alter index EMP_ENAME_IDX allocate extent;

索引已更改。

合并索引碎片

SQL> alter index emp_ename_idx coalesce;

索引已更改。

删除索引

SQL> drop index emp_name_sal_index;

索引已删除。

SQL> select index_name,pct_free,pct_increase,initial_extent,next_extent from  user_indexes;

INDEX_NAME                                                                                   PCT_FREE PCT_INCREASE INITIAL_EXTENT NEXT_EXTENT
------------------------------------------------------------------------------------------ ---------- ------------ -------------- -----------
PK_EMP                                                                                             10                       65536     1048576
IDX_SAL_EMP                                                                                        10                       65536     1048576
EMP_ENAME_IDX                                                                                      10                       65536     1048576
PK_DEPT                                                                                            10                       65536     1048576

 

lalin的学习之路
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
oracle表重命名 索引,Oracle索引
weixin_35625397的博客
04-03 3190
Oracle索引Oracle索引(index)最大的作用是用来优化数据库查询的效率，提升数据库的查询性能。就好比书的目录一样，可以通过目录来直接定位所需内容存在的页数，大大提高检索效率。Oracle数据库中如果某列出现在查询的条件中，而该列的数据是无序的，查询时只能从第一行开始一行一行的匹配。创建索引就是对某些特定列中的数据进行排序或归类，生成独立的索引表。在某列上创建索引后，如果该列出现在查询条...
oracle 索引刷新,ORACLE索引失效，更新统计信息
weixin_39848970的博客
04-03 925
有时候建立索引的时候不走索引，排除了字段数据问题和sql写法问题，索引失效的问题之外，还得考虑是统计信息过旧，得重新收集。查看表的统计信息，看 user_index 的last_analyze(索引)，看user_tab_col_statistics 的last_analyze(字段)一：解锁统计信息为了稳定执行计划，一般统计信息都会被锁住的，在更新统计信息的时候得先解锁。①按用户schema解锁...
Oracle索引详解
热门推荐
D2cOneluo的博客
08-11 2万+
Oracle索引一、索引介绍1、什么是索引？2、使用索引的目的二、索引的分类及结构1、逻辑上：2、物理上：三、各种索引详解1、 B树索引（1）特点：（2）技巧：2、位图索引（1）特点：（2）技巧：3、 反向索引（1）特点：（2）技巧：4、HASH索引（1）特点：5、函数索引6、分区索引和全局索引7、域索引 Domain四、怎样建立索引1、普通索引2、唯一索引 Oracle 自动在表的主键上创建唯一索引3、位图索引4、组合索引5、基于函数索引6、反向键索引7.重置索引8.删除索引五、索引失效细节1.使用不等于
oracle索引
SherlockerSun的博客
06-20 1973
oracle索引使用
oracle 索引问题梳理
忙碌的菠萝
02-11 4042
索引基础知识 索引概念 索引是为了加快数据的查找而创建的可选数据库对象 索引是建立在表的一列或多列上且进行排序的一种结构 通过指针快速定位数据行的方法，减少CPU和磁盘的I/O 索引是由Oracle自动使用和维护的，数据更新会自动传播到所有相关的索引 索引存在双面性，表面存在过多的索引会影响到DML语句的性能 索引类型 唯一索引 保证在定义索引的列中没有重复值 Oracle自动在表的主键列上创建唯一索引 唯一索引对应的字段上可以插入null值 语法： create unique index idx
Oracle索引失效
最新发布
桃李不言下自成蹊
07-07 6万+
SQL开发中经常遇到索引失效导致查询速度缓慢的情况， 究竟哪些情况会导致索引失效呢， 以下以Oracle数据库为例列举几种可能导致索引失效的情况
oracle 索引优化
qq_40152902的博客
01-22 2341
oracle 索引选择 索引优化 查询优化
Oracle索引原理
鸡蛋达芬奇
10-23 2114
索引原理 oracle中的索引与mysql中的索引不一样，oracle中索引是存储了索引列的值以及rowid值。而mysql索引分为聚集索引，非聚集索引。其中聚集索引只能有一个。 oracle中索引的主要分为根，茎，叶子三部分。索引列值都是存放在叶子节点上，茎只是存放了叶子节点的相关信息。 oracle中的索引反应的是逻辑结构，不是物理结构。索引创建的时候，是先创建叶子节点，然后再创建茎，最后创建根
Oracle索引建立原则
developer
03-22 2944
数据库索引建立常用的规则如下： 1、表的主键、外键必须有索引； 2、数据量超过300的表应该有索引；3、经常与其他表进行连接的表，在连接字段上应该建立索引；4、经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；5、索引应该建在选择性高的字段上；6、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；7、复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替： 8、频繁进行数据操作的表，不要建立太多的索引；9、删除无用的索引，避免对执行计划造成负面影响； 索引的建立必
Oracle 索引
08-08
Oracle索引是数据库管理系统中用于加速数据检索的关键结构。它们的工作原理类似于书籍的目录，允许数据库系统快速定位和访问特定的数据行，而无需扫描整个表。Oracle提供了多种类型的索引，包括B树索引、位图索引、...
Oracle索引技术
09-26
Oracle 索引技术，正确使用Oracle数据库的索引不仅可以实现良好的性能，更重要的是能够创造出可伸缩的数据库应用程序。本书在介绍各种类型索引的过程中，始终围绕性能这一主线，透彻分析了为Oracle数据库创建和优化...
oracle 索引
04-02
Oracle索引是数据库管理系统中用于加速数据检索的关键结构。它们的工作原理类似于书籍的索引，允许用户快速定位所需的数据，而无需扫描整个表。在Oracle数据库中，索引的使用可以显著提高查询性能，尤其是在处理大量...
ORACLE 索引技术
10-25
ORACLE 索引技术
oracle索引介绍(图文详解)
09-10
Oracle索引是数据库管理系统中用于加速数据检索的关键组件。它们的工作原理类似于书籍的目录，允许数据库系统快速定位和访问所需的数据行，而无需扫描整个表。以下是对Oracle索引的详细解释： 首先，索引根据其逻辑...
Oracle 索引 详解
01-20
Oracle 索引详解 Oracle 索引是数据库性能优化的重要工具，它可以大大加快数据的检索速度，提高系统的性能。但是，索引也存在一些缺陷，例如创建索引和维护索引要耗费时间，索引需要占物理空间，等等。在本文中，...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值