创建索引的两种方式比对

本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 创建索引的两种方式比对

有一个应用,需要创建索引,创建索引一般有两种方法,一种是


CREATE INDEX ...;

一种是


CREATE INDEX ... ONLINE;

字面意思上看,一个是在线,一个是非在线,有什么不同?

1.语句执行时间的不同 
创建测试表, 
创建索引的两种方式比对

(1) 使用非在线创建索引,用时00.06秒, 

创建索引的两种方式比对

(2) 使用在线方式创建索引,用时00.32秒, 
创建索引的两种方式比对

表只有一条数据,ONLINE是非ONLINE用时的5倍以上了。

2.阻塞对象的不同 
(1) 非在线方式创建索引期间,执行任何DML语句,会hang住,直至索引创建完,


insert into tbl_index select * from tbl_index where rownum=1;无响应直至索引创建完成

出现hang是现象,原理则是锁等待。 
创建索引的两种方式比对
从V$LOCKED_OBJECT视图可以了解锁等待信息,使用DBA_OBJECTS视图可以知道,OBJECT_ID是18和168111代表的对象是什么, 
创建索引的两种方式比对

(2) 在线方式创建索引期间,允许任何DML语句的执行,不会阻塞。但实际从V$LOCKED_OBJECT看,是有一些锁等待信息的, 
创建索引的两种方式比对
167111知道是TBL_INDEX表,我们看下168114(此处截图问题请忽略,默认168141就是168114),他代表的对象是SYS_JOURNAL_168113,查看168113代表的对象则是我们创建TBL_INDEX表的索引IDX_TBL_INDEX_01, 
创建索引的两种方式比对

我们看下SYS_JOURNAL_168113,他是一张表, 
创建索引的两种方式比对

表有四个字段, 
创建索引的两种方式比对

记录为空(此处截图问题请忽略,默认168112是168114), 
创建索引的两种方式比对

表大小为0,


SELECT SUM(bytes)/1024/1024 FROM dba_segments WHERE segment_name='SYS_JOURNAL_168113';

3.执行逻辑的不同 
我们对这两种方法执行10046,看下Oracle执行了什么, 
(1) 非在线方式的trace主要内容, 
首先,我们看见了以SHARE NOWAIT模式LOCK了TBL_INDEX整张表, 
创建索引的两种方式比对

向obj$、seg$、icol$、ind$这些数据字典中维护索引相关信息, 
创建索引的两种方式比对

创建索引的两种方式比对 创建索引的两种方式比对 创建索引的两种方式比对 创建索引的两种方式比对 创建索引的两种方式比对 创建索引的两种方式比对

完成非唯一索引的创建, 
创建索引的两种方式比对

(2) 在线方式的trace主要内容, 
首先,以ROW SHARE模式LOCK表TBL_INDEX,这是和非在线方式一点不同, 
创建索引的两种方式比对

另外的不同,就是会创建一张叫”SYS_JOURNAL_92450”的表,索引创建用的是这张“临时表”,因此不会直接影响原表的DML语句, 
创建索引的两种方式比对

这张表用完了,会被drop purge,因此回收站找不着痕迹, 
创建索引的两种方式比对

删除一些con$、seg$数据字典的记录, 
创建索引的两种方式比对

我们从这两种创建索引生成的trace文件大小也可以得出一些结论,online方式创建索引的trace文件大小是非online方式创建索引的trace文件大小的10倍,说明online方式创建索引要执行更多的工作,尽管不会影响原表的DML语句,因此用时要久一些, 
创建索引的两种方式比对

总结: 
(1) online和非online方式创建索引,效果相同。 
(2) online方式创建索引,由于使用了一张临时表,以ROW SHARE锁表,不会阻塞原表DML的语句,非online方式创建索引,则会以SHARE NOWAIT锁表,阻塞原表DML语句。 
(3) 由于online方式创建索引,Oracle执行工作复杂,因此比非online方式创建索引用时要久。 
(4) 一句话“不能什么便宜均占着”,要么选择可以快速创建索引的非online方式但创建期间会锁表阻塞DML语句,要么选择不会阻塞原表DML语句的online方式创建索引但用时较久。从实际来看,我理解,若小表选择任何一种均可,大表,尤其是生产系统,找不着非高峰时间,选择online更合理一些,若不关注是否影响DML操作,则两种方式均可以了。

如果您觉得此篇文章对您有帮助,欢迎关注微信公众号:bisal的个人杂货铺,您的支持是对我最大的鼓励!共同学习,共同进步:)

创建索引的两种方式比对
本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 创建索引的两种方式比对


 上一篇
三种批量删除PLSQL写法效率的比对 三种批量删除PLSQL写法效率的比对
我们有一个重要的旧系统,最近夜维出现了一些问题,夜间执行5小时未完成,为了不影响业务,只能早上高峰期之前,DBA手工kill夜维进程。 这一个夜维程序采用了PLSQL写的存储过程,通过数据库job定时启动执行。存储过程我很少使用,借着这次机
下一篇 
ORA-01653,01654错误和dba_free_space视图的理解 ORA-01653,01654错误和dba_free_space视图的理解
上周,兄弟部门提出了一个问题, 描述如下, 开发库,对表X他们查询,或者DELETE的时候,经常出这个问题,  好像还与查询或者DELETE的数据量有关,是不是由于没建索引的原因,我查百度也没解决  select sum(bytes