create index online 和create index 不同及注意点

CREATE INDEX ONLINE 锁模式变化模拟

SESSION 139
SQL>  insert into test123
  2  select * from dba_objects;
 
50062 rows inserted
不提交
SESSION 148
SQL> create index test123_i on test123(owner) online;

回话148堵塞

SQL>  select * from v$lock where sid in ('139','148') order by sid;
 
ADDR             KADDR                   SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
0000000096669B90 0000000096669BB8        139 TM        53479          0          3          0         66          1
00000000966E6578 00000000966E65B0        139 TX       589843        343          6          0         66          0
0000000096669DD0 0000000096669DF8        148 TM        53481          0          4          0         40          0
0000000096669CB0 0000000096669CD8        148 TM        53479          0          2          4         42          0
00000000978E54F0 00000000978E5510        148 DL        53479          0          3          0         42          0
00000000978E53A0 00000000978E53C0        148 DL        53479          0          3          0         42          0

堵塞正是由于
0000000096669CB0 0000000096669CD8        148 TM        53479          0          2          4         42          0
可以看到已经以模式2也就是SS模式获得TM锁，但是期望以模式4获得TM锁也就是S模式，但是在OBJECTS 53479上被139会话以模式3也就是SX模式获得
因为SS和SX兼容所以可以获得，但是如果想获得S模式，S和SX是不兼容，所以堵塞
顺便提一下OBJECT_ID=53479 就是表TEST123，而对象53481是对象SYS_JOURNAL_53480，就是为了保证在建立索引的同时把可能的更改记录到所谓的日志表中
待索引建立完成后同步到日志中，这也是ONLINE建立索引所独有的。
此时我们COMMIT回话139

SQL>  select * from v$lock where sid in ('139','148') order by sid;
 
ADDR             KADDR                   SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
00000000978E53A0 00000000978E53C0        148 DL        53479          0          3          0        821          0
0000000096669DD0 0000000096669DF8        148 TM        53481          0          4          0        819          0
0000000096669CB0 0000000096669CD8        148 TM        53479          0          2          0         14          0
00000000978E54F0 00000000978E5510        148 DL        53479          0          3          0        821          0

一旦提交后期望的锁即可获得，而且获得后会降级为2也就是SS模式而不是S模式

然后我们又在会话139进行多次DML操作，看看CREATE INDEX ONLINE 是否堵塞 随后的DML

SQL>  select * from v$lock where sid in ('139','148') order by sid;
 
ADDR             KADDR                   SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
000000009666A250 000000009666A278        139 TM        53479          0          3          0         84          0
00000000966C0868 00000000966C08A0        139 TX       131088        311          6          0         80          0
0000000096669DD0 0000000096669DF8        148 TM        53481          0          4          0        562          0
00000000978E54F0 00000000978E5510        148 DL        53479          0          3          0        563          0
00000000978E53A0 00000000978E53C0        148 DL        53479          0          3          0        563          0
0000000096669CB0 0000000096669CD8        148 TM        53479          0          2          0        549          0

这里可以看到实际不会堵塞随后的DML操作，因为降级后只需要对TEST123获得SS模式即可，SS模式是SX模式是兼容的。
所以CREATE INDEX ONLINE会
1、如果在本表上有DML没有提交，那么CREATE INDEX ONLINE会等待其提交，因为初始的时候需要以S模式获得表上TM锁,S模式和SX模式不兼容
2、如果获得了表上S模式锁后，也就是进行创建过程中，实际对表的TM S锁已经降级为SS，这个时候就不会堵塞随后的DML了。这也是为什么
CREATE INDEX ONLINE优于CREATE INDEX的地方，他不会堵塞随后的DML，因为TM锁是SS模式而不是S模式。
   但是还是要注意第一点，所以为了保险还是关闭应用建立索引吧，特别是大表，CREATE INDEX ONLINE也不一定保险。

CREATE INDEX  锁模式变化模拟

其实CREATE INDEX 没什么好模拟的，
如果你还有DML操作在表上，那么一定有TX模式的TM锁，建立索引会报错如下
SQL> create index test123_i
  2  on test123(owner);
 
create index test123_i
on test123(owner)
 
ORA-00054: resource busy and acquire with NOWAIT specified

当然如果可以建立索引的话你会看到如下
回话148建立索引，查看其锁TM为模式4及模式S
SQL>  select * from v$lock where sid in ('139','148') order by sid;
 
ADDR             KADDR                   SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
00000000978E53A0 00000000978E53C0        148 DL        53479          0          3          0          6          0
00000000978E54F0 00000000978E5510        148 DL        53479          0          3          0          6          0
00000000966F2BA8 00000000966F2BE0        148 TX       458790        367          6          0          6          0
0000000096669CB0 0000000096669CD8        148 TM           18          0          3          0          6          0
0000000096669B90 0000000096669BB8        148 TM        53479          0          4          0          6          0

回话139进行操作
delete test123;
查看锁模式如下
SQL>  select * from v$lock where sid in ('139','148') order by sid;
 
ADDR             KADDR                   SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
0000000096669DD0 0000000096669DF8        139 TM        53479          0          0          3         15          0
00000000978E54F0 00000000978E5510        148 DL        53479          0          3          0         31          0
0000000096669B90 0000000096669BB8        148 TM        53479          0          4          0         31          1
00000000966F2BA8 00000000966F2BE0        148 TX       458790        367          6          0         31          0
0000000096668868 00000000966688C8        148 TS            4   18509883          6          0         17          0
00000000978E53A0 00000000978E53C0        148 DL        53479          0          3          0         31          0
0000000096669CB0 0000000096669CD8        148 TM           18          0          3          0         31          0

可以看到回话139想以模式3也就是SX模式获得TM锁，但是因为CREATE INDEX 的TM模式是4也就是S模式，是不共享的，所以不能获得，只能堵塞
等待create index 完成，所以CREATE INDEX一定不能再没有确定这个表没有DML操作的情况下使用，除非你确定没有DML操作在这个表上

兼容矩阵

held/get  null  ss   sx   s   ssx   x
null          1    1    1   1     1   1 
ss            1    1    1   1     1
sx            1    1    1
s             1    1         1
ssx          1    1
x             1

时间： 2024-10-11 13:36:35

create index online 和create index 不同及注意点的相关文章

index full scan 和 index fast full scan （IFS,FFS）的不同

转自ITPUB 首先来看一下IFS,FFS能用在哪里:在一句sql中,如果我们想搜索的列都包含在索引里面的话,那么index full scan 和 index fast full scan 都可以被采用代替full table scan.比如以下语句: SQL> CREATE TABLE TEST AS SELECT * FROM dba_objects WHERE 0=1; SQL> CREATE INDEX ind_test_id ON TEST(object_id); SQL>

MYSQL中的type:index 和 Extra:Using index

原创水平有限,如有错误请指出 考虑下面执行计划中的TYPE和Extra +----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table  | partitions | type  | possible_keys | key  | key_len | ref  |

INDEX FULL SCAN和INDEX FAST FULL SCAN的区别

原创 转载请注明出处 (原创)    关于INDEX FULL SCAN和INDEX FAST FULL SCAN的区别在于,前者在对索引进行扫描的时候会考虑大索引的结构,而且会按照索引的排序,    而后者则不会,INDEX FAST FULL SCAN不会去扫描根块和分支块,对索引像访问堆表一样访问,所以这两个扫描方式用在不同的场合    如果存在ORDER BY这样的排序,INDEX FULL SCAN是合适的,如果不需要排序,那INDEX FAST FULL SCAN效率是更高的.   

sql入门教程:sql 创建表create table 与数据库[create databse]

sql入门教程:sql 创建表create table 与数据库[create databse] 在创建数据库的声明 在CREATE DATABASE语句是用来建立一个数据库. 创建数据库的SQL语法 CREATE DATABASE database_name 创建数据库的例子现在,我们要建立一个数据库,所谓的" my_db " . 我们使用下面的CREATE DATABASE语句:CREATE DATABASE my_db   下面来看看sql 创建表create table 在创建

MySQL create table as与create table like对比

      在MySQL数据库中,关于表的克隆有多种方式,比如我们可以使用create table ..as .. ,也可以使用create table .. like ..方式.然而这2种不同的方式还是有些差异的,他的差异到底在哪里呢,本文通过演示对此展开描述. 1.mysql sakila表上的结构 --actor表状态 robin@localhost[sakila]> show table status like 'actor'\G ***************************

MySQL中表复制:create table like 与 create table as select_Mysql

复制代码 代码如下: CREATE TABLE A LIKE B 此种方式在将表B复制到A时候会将表B完整的字段结构和索引复制到表A中来. 复制代码 代码如下: CREATE TABLE A AS SELECT x,x,x,xx FROM B LIMIT 0 此种方式只会将表B的字段结构复制到表A中来,但不会复制表B中的索引到表A中来.这种方式比较灵活可以在复制原表表结构的同时指定要复制哪些字段,并且自身复制表也可以根据需要增加字段结构. 两种方式在复制表的时候均不会复制权限对表的设置.比如说原

sql入门教程:create index创建索引

create index创建索引, 我们先来看看语法吧. CREATE INDEX index_name ON table_name (column_name) 创建唯一索引 SQL语法指数创建一个独特的指数上就座.重复值是不允许使用:   CREATE UNIQUE INDEX index_name ON table_name (column_name)   我们来对person的man表创建sex 索引例子吧.   CREATE INDEX PIndex ON Persons (LastNa

index merge的数据结构和成本评估

1. 概述:index merge的数据结构 index merge的主要数据结构仍然是存放在SEL_TREE中: class SEL_TREE :public Sql_alloc { ... List<SEL_IMERGE> merges; ... }; 在merges这个list中存放了所有可能的index merge.本文将从几个案例,来看看SEL_TREE/SEL_IMERGE如何代表一个index merge访问方式.本文将不再重复介绍SEL_ARG/SEL_TREE的Range相关

CREATE TABLE – SQL Command

command CREATE TABLE – SQL CommandCreates a table having the specified fields. CREATE TABLE | DBF TableName1 [NAME LongTableName] [FREE] (FieldName1 FieldType [(nFieldWidth [, nPrecision])] [NULL | NOT NULL] [CHECK lExpression1 [ERROR cMessageText1]]