IT分销/经销数据库
db2 建立大索引 db2中建立索引是不是不会锁住dml语句
我测试在一张 700万的表上建立索引1.在建立索引的中间我 对这张表 插入数据，delete数据，update数据，照样能操作2.我对表增加一个列 锁住了
（oracle中create index会锁住dml和ddl 语句，online后会锁住ddl语句)
而db2中没有online的语法，是不是因为create index 本身就不会锁dml语法？？
请专家帮我 解释一下这个原理 ，谢谢了
（oracle中create index会锁住dml和ddl 语句，online后会锁住ddl语句)
而db2中没有online的语法，是不是因为create index 本身就不会锁dml语法？？
请专家帮我 解释一下这个原理 ，谢谢了
2同行回答
浏览318
一下是摘自infocenter的对create index的说明：
Concurrent read/write access to the table is permitted while an index is being created. However, the default index creation behavior differs for indexes on nonpartitioned tables, nonpartitioned indexes, and partitioned indexes:
* For indexes on nonpartitioned tables, once the index has been built, changes that were made to the table during index creation time are forward-fitted to the new index. Write access to the table is then briefly blocked while index creation completes, after which the new index becomes available.
* For nonpartitioned indexes, once the index has been built, changes that were made to the table during index creation time are forward-fitted to the new index. Write access to the table is then briefly blocked while index creation completes, after which the new index becomes available.
* For partitioned indexes, once the index partition has been built, changes that were made to the partition during creation time of that index partition are forward-fitted to the new index partition. Write access to the data partition is then blocked while index creation completes on the remaining data partitions. After the index partition for the last data partition is built and the transaction is committed, all data partitions are available for read and write.
To circumvent this default behavior, use the LOCK TABLE statement to explicitly lock the table before issuing a CREATE INDEX statement. (The table can be locked in either SHARE or EXCLUSIVE mode, depending on whether read access is to be allowed.)收起
Concurrent read/write access to the table is permitted while an index is being created. However, the default index creation behavior differs for indexes on nonpartitioned tables, nonpartitioned indexes, and partitioned indexes:
* For indexes on nonpartitioned tables, once the index has been built, changes that were made to the table during index creation time are forward-fitted to the new index. Write access to the table is then briefly blocked while index creation completes, after which the new index becomes available.
* For nonpartitioned indexes, once the index has been built, changes that were made to the table during index creation time are forward-fitted to the new index. Write access to the table is then briefly blocked while index creation completes, after which the new index becomes available.
* For partitioned indexes, once the index partition has been built, changes that were made to the partition during creation time of that index partition are forward-fitted to the new index partition. Write access to the data partition is then blocked while index creation completes on the remaining data partitions. After the index partition for the last data partition is built and the transaction is committed, all data partitions are available for read and write.
To circumvent this default behavior, use the LOCK TABLE statement to explicitly lock the table before issuing a CREATE INDEX statement. (The table can be locked in either SHARE or EXCLUSIVE mode, depending on whether read access is to be allowed.)收起
浏览328