oracle数据库关于索引建立及使用的详细介绍

索引的说明

索引是与表相关的一个可选结构,在逻辑上和物理上都独立于表的数据,索引能优化查询,不能优化DML操作,Oracle自动维护索引,频繁的DML操作反而会引起大量的索引维护。

如果SQL语句仅访问被索引的列,那么数据库只需从索引中读取数据,而不用读取表。

如果该语句同时还要访问除索引列之外的列,那么,数据库会使用rowid来查找表中的行。

通常,为检索表数据,数据库以交替方式先读取索引块,然后读取相应的表块。

索引的目的

主要是减少IO,这是本质,这样才能体现索引的效率。

1大表,返回的行数<5%

2经常使用where子句查询的列

3离散度高的列

4更新键值代价低

5逻辑AND、OR效率高

6查看索引在建在那表、列:

select * from user_indexes;

select * from user_ind_columns;

索引结构

oracle索引分为两大类结构:

B树索引结构<balance>

类似于字典查询,最后到leaf block ,存的是数据rowid和数据项

1.叶块之间使用双向链连接,为了可以范围查询。
2.删除表行时,索引叶块也会更新,但只是逻辑更改,并不做物理的删除叶块。
3.索引叶块不保存表行键值null的信息。

位图索引结构<bitmap>

在oracle中是根据rowid来定位记录的,因此,我们需要引入start rowid和end rowid,通过start rowid ,end rowid 和二进制位的偏移,我们就可以非常快速的计算出二进制位所代表的表记录rowid。位图索引的最终逻辑结构如下图:

我们称每一单元的<key ,startrowid,end rowid,bitmap>为一个位图片段。当我们修改某一行数据的时候,我们需要锁定该行列值所对应的位图片段,如果我们进行的是更新操作,同时还会锁定更新后新值所在的位图片段。例如我们将列值从01修改为03,就需要同时锁定01和03位图片段,此时如果有其他用户需要修改与01或者03关联的表记录上的索引字段,就会被阻塞,因此位图索引不适合并发环境,在并发环境下可能会造成大量事务的阻塞。
我们一般在表数据离散度比较低的时候使用位图索引,如t_doctor的empno值重复分布的比较多,则离散度比较低:create bitmap index job_bitmap on t_doctor(empno)

建立索引的方式

1.唯一索引:键值不重复

create unique index doctor_index on t_doctor(empno)

drop indexdoctor_index

2.一般索引:键值可重复

create index doctor_index on t_doctor(empno)

drop indexdoctor_index

3.复合索引:绑定了多个列

create index doctor_index ont_doctor(empno,job)

drop indexdoctor_index

4.反向索引:为避免平衡树索引热块,如t_doctor表中empno开头都是“7”,这样构建索引树的时候,很可能会把所有数据分配到一个块里,使用反向索引,避免此类问题,使索引树分布均匀

create index doctor_index ont_doctor(empno) reverse

drop indexdoctor_index

5.函数索引:查询时必须用到这个函数,才会使用到

create index func_index ont_doctor(lower(empno))

--select * from t_doctor where lower(empno) = 'lina'

drop indexfunc_index

6.压缩索引:不常用

create index doctor_index ont_doctor(empno) compress

drop indexdoctor_index

7.升序降序索引:

create index doctor_index ont_doctor(empno desc, job asc)

drop indexdoctor_index

索引碎片问题

由于对基表做DML操作,导致索引表块的自动更改操作,尤其是基表的delete操作会引起index表的index_entries的逻辑删除,注意只有当一个索引块中的全部index_entry都被删除了,才会把这个索引块删除,索引对基表的delete、insert操作都会产生索引碎片问题。

在Oracle文档里并没有清晰的给出索引碎片的量化标准,Oracle建议通过Segment Advisor(段顾问)解决表和索引的碎片问题,如果你想自行解决,可以通过查看index_stats视图,当以下三种情形之一发生时,说明积累的碎片应该整理了(仅供参考)。

查看执行计划:set autotrace traceonly explain;

分析索引列:

analyze index ind_1 validate structure;

select name,HEIGHT,PCT_USED,DEL_LF_ROWS/LF_ROWS from index_stats;

HEIGHT >=4 

 PCT_USED< 50% 

DEL_LF_ROWS/LF_ROWS>0.2

alter index ind_1 rebuild [online] [tablespace name];

到此这篇关于oracle数据库关于索引建立及使用的详细介绍的文章就介绍到这了,更多相关oracle数据库索引内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • oracle数据库索引失效

    今天一个同事突然问我索引为什么失效.说实在的,失效的原因有多种: 但是如果是同样的sql如果在之前能够使用到索引,那么现在使用不到索引,以下几种主要情况: 1. 随着表的增长,where条件出来的数据太多,大于15%,使得索引失效(会导致CBO计算走索引花费大于走全表) 2. 统计信息失效      需要重新搜集统计信息 3. 索引本身失效      需要重建索引 下面是一些不会使用到索引的原因 索引失效 1) 没有查询条件,或者查询条件没有建立索引 2) 在查询条件上没有使用引导列 3) 查询

  • Oracle数据库索引的维护

    正在看的ORACLE教程是:Oracle数据库索引的维护. 本文只讨论Oracle中最常见的索引,即是B-tree索引.本文中涉及的数据库版本是Oracle8i. 一. 查看系统表中的用户索引 在Oracle中,SYSTEM表是安装数据库时自动建立的,它包含数据库的全部数据字典,存储过程.包.函数和触发器的定义以及系统回滚段. 一般来说,应该尽量避免在SYSTEM表中存储非SYSTEM用户的对象.因为这样会带来数据库维护和管理的很多问题.一旦SYSTEM表损坏了,只能重新生成数据库.我们可以用下

  • Oracle数据库中建立索引的基本方法讲解

    怎样建立最佳索引? 1.明确地创建索引 create index index_name on table_name(field_name) tablespace tablespace_name pctfree 5 initrans 2 maxtrans 255 storage ( minextents 1 maxextents 16382 pctincrease 0 ); 2.创建基于函数的索引 常用与UPPER.LOWER.TO_CHAR(date)等函数分类上,例: create index

  • 在OracleE数据库的字段上建立索引的方法

    当where子句对某一列使用函数时,除非利用这个简单的技术强制索引,否则Oracle优化器不能在查询中使用索引. 通常情况下,如果在WHERE子句中不使用诸如UPPER.REPLACE 或SUBSTRD等函数,就不能对指定列建立特定的条件.但如果使用了这些函数,则会出现一个问题:这些函数会阻碍Oracle优化器对列使用索引,因而与采用索引的情况相比较,查询会花费更多的时间. 庆幸的是,如果在使用函数的这些列中包含了字符型数据,可以用这样一种方法修改查询语句,以达到强制性使用索引,更有效地运行查询

  • Oracle 数据库中创建合理的数据库索引

    笔者认为,在创建索引时要做到三个适当,即在适当的表上.适当的列上创建适当数量的索引.虽然这可以通过一句话来概括优化的索引的基本准则,但是要做到这一点的话,需要数据库管理员做出很大的努力.具体的来说,要做到这个三个适当有如下几个要求. 一. 根据表的大小来创建索引. 虽然给表创建索引,可以提高查询的效率.但是数据库管理员需要注意的是,索引也需要一定的开销的.为此并不是说给所有的表都创建索引,那么就可以提高数据库的性能.这个认识是错误的.恰恰相反,如果不管三七二十一,给所有的表都创建了索引,那么其反

  • oracle数据库关于索引建立及使用的详细介绍

    索引的说明 索引是与表相关的一个可选结构,在逻辑上和物理上都独立于表的数据,索引能优化查询,不能优化DML操作,Oracle自动维护索引,频繁的DML操作反而会引起大量的索引维护. 如果SQL语句仅访问被索引的列,那么数据库只需从索引中读取数据,而不用读取表. 如果该语句同时还要访问除索引列之外的列,那么,数据库会使用rowid来查找表中的行. 通常,为检索表数据,数据库以交替方式先读取索引块,然后读取相应的表块. 索引的目的 主要是减少IO,这是本质,这样才能体现索引的效率. 1大表,返回的行

  • 达梦数据库获取SQL实际执行计划方法详细介绍

    目录 一.set autotrace trace 二.v$cachepln中获取执行计划 三.ET系统函数 四.dbms_sqltune系统包 五.说明 环境说明: 操作系统:银河麒麟V10 数据库:DM8 相关关键字:DM数据库.SQL实际执行计划 一.set autotrace trace disql下执行set autotrace trace开启AUTOTRACE功能,执行SQL语句,并打印实际的执行计划. SQL> set autotrace trace SQL> select a.e

  • MySQL索引优化之分页探索详细介绍

    目录 ​​MySQL​​索引优化之分页探索 案例一 案例二 ​​MySQL​​索引优化之分页探索 表结构 CREATE TABLE `demo` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '姓名', `age` int(11) NOT NULL DEFAULT '0'

  • oracle数据库在客户端建立dblink语法

    oracle服务器没有建立目标数据库的TNS时,在客户端(有权限的情况下)建立dblink语法如下: 复制代码 代码如下: create database link dblink_name connect to user_name identified by user_psw using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xx.xxx.xx)(PORT = 1521)) ) (CONNEC

  • 随机获取oracle数据库中的任意一行数据(rownum)示例介绍

    最近看oracle资料的时候,了解rownum的概念,以前只知道对数据库表进行简单的增删改查: 看到了rownum的概念后,突然想到了好多业务场景应该都可以适用的,比如在进行随机发奖的时候, 我们就可以从先查一下奖品表中可以发的总奖品数,然后通过java的Random类在总奖品数内生成一个随机整数X,然后调用 select * from (select rownum no,id from Table where rownum<=X ) where no >=X 获取出该条奖品,这样获取出来的值

  • Oracle数据库快照的使用

    正在看的ORACLE教程是:Oracle数据库快照的使用.oracle数据库的快照是一个表,它包含有对一个本地或远程数据库上一个或多个表或视图的查询的结果.正因为快照是一个主表的查询子集,使用快照可以加快数据的查询速度;在保持不同数据库中的两个表的同步中,利用快照刷新,数据的更新性能也会有很大的改善. 下面以我在开发襄樊市电信局170话费催缴系统中使用快照加快查询速度的实现过程为例来说明快照的使用方法: 170话费催缴系统是一个向用户电话播放催缴话费提示音的系统.用户的欠费金额存放在rs6000

  • MySQL数据库之索引详解

    目录 一.MySQL索引简介 二.MySQL五种类型索引详解 (一)普通索引 (二)唯一性索引 (三)主键索引 (四)复合索引 (五)全文索引 三.MySQL索引使用原则 总结 今天继续给大家介绍MySQL相关知识,本文主要内容是MySQL索引相关内容. 一.MySQL索引简介 索引是MySQL数据库为了加快数据查询的速度,给表中的某一个或者是某几个列添加的一种"目录".MySQL的索引是一个特殊的文件,但是InnoDB类型引擎(关于MySQL的引擎我们会在今后的文章中进行讲解)的表的

  • Kettle连接Oracle数据库方法((Oracle19c&Oracle11g))

    目录 前言 Oracle驱动jar包: 连接Oracle ojdbc6.jar驱动可以链接11g和19c版本 迁移数据: 补充: 总结 前言 kettle通过域名或者IP连接Oracle,本地不需要安装Oracle,但是要把Oracle的驱动jar包复制到kettle的lib目录下. Oracle驱动jar包: (包括:ojdbc5.jar.ojdbc6.jar.ojdbc7.jar.ojdbc8.jar.ojdbc10.jar.ojdbc14.jar) 链接: https://pan.baid

  • Oracle数据库ORA-12560错误问题的解决办法

    官网中关于ORA-12560的解释: ORA-12560: TNS:protocol adapter error Cause: A generic protocol adapter error occurred. Action: Check addresses used for proper protocol specification. Before reporting this error, look at the error stack and check for lower level

  • 浅谈入门级oracle数据库数据导入导出步骤

    oracle数据库数据导入导出步骤(入门) 说明: 1.数据库数据导入导出方法有多种,可以通过exp/imp命令导入导出,也可以用第三方工具导出,如:PLSQL 2.如果熟悉命令,建议用exp/imp命令导入导出,避免第三方工具版本差异引起的问题,同时效率更高,但特别注意:采用命令时要注意所使用的用户及其权限等细节. 3.在目标数据库导入时需要创建与导出时相同的用户名(尽量一致),并赋予不低于导出时用户的权限:同时还需创建与原数据库相同的表空间名,若本地数据库已存在相同的表空间,则只能进行表空间

随机推荐