Linux AS2.1 , 3.0 系统
版本有 :
Oracle 8.1.6.0 , Oracle8.1.7.0 , Oracle92.0.1 ,
Oracle 9.2.0.4
主题: ONLINE 建立 INDEX 时Oracle做了什么 ?
以前建立大的索引大多数时候是在没有 transaction 状态下加入的,当然也没有使用online 选项 。偶尔小一些的表,在20秒内能加入的我们都没有使用online 选项 。
online 建立index的时候,是以什么机制来建立的? oracle会等待insert , update的行( 假设这些行不超过table 行的30% ) commit或rollback之后才建立吗? 如果是,那么如果一直都有这个表上的少量数据update 及insert , 那么建立这样的索引会很慢 。 如果不是, 那么当需要建立index的列的某些行由原来的值“ASIA_CHINA” 更新为“USA” 的时候,它在INDEX叶子结点上又是如何更新的 ; 还是说它的INDEX 根本还没有被建立, 而是存储在其他临时的地方 ?
我们在做测试的时候, 同样的表及数据量,其他环境也一样, online加入index会比非online 加入慢很多 。
在线建立INDEX的文章 。
http://download-west.oracle.com/ ... 56/indexes.htm#3062
************************************************************************************
Creating an Index Online
Previously, when creating an index on a table there has always been a DML S-lock on that table during the index build operation, which meant you could not perform. DML operations on the base table during the build.
Now, with the ever-increasing size of tables and necessity for continuous operations, you can create and rebuild indexes online--meaning you can update base tables at the same time you are building or rebuilding indexes on that table. Note, though, that there are still DML SS-locks, which means you cannot perform. other DDL operations during an online index build.
The following statements perform. online index build operations:
ALTER INDEX emp_name REBUILD ONLINE;
CREATE INDEX emp_name ON emp (mgr, emp1, emp2, emp3) ONLINE;
--------------------------------------------------------------------------------
Note:
While you can perform. DML operations during an online index build, Oracle recommends that you do not perform. major/large DML operations during this procedure. For example, if you wish to load rows that total up to 30% of the size of an existing table, you should perform. this load before the online index build.
--------------------------------------------------------------------------------
**************************************************************************************
版本有 :
Oracle 8.1.6.0 , Oracle8.1.7.0 , Oracle92.0.1 ,
Oracle 9.2.0.4
主题: ONLINE 建立 INDEX 时Oracle做了什么 ?
以前建立大的索引大多数时候是在没有 transaction 状态下加入的,当然也没有使用online 选项 。偶尔小一些的表,在20秒内能加入的我们都没有使用online 选项 。
online 建立index的时候,是以什么机制来建立的? oracle会等待insert , update的行( 假设这些行不超过table 行的30% ) commit或rollback之后才建立吗? 如果是,那么如果一直都有这个表上的少量数据update 及insert , 那么建立这样的索引会很慢 。 如果不是, 那么当需要建立index的列的某些行由原来的值“ASIA_CHINA” 更新为“USA” 的时候,它在INDEX叶子结点上又是如何更新的 ; 还是说它的INDEX 根本还没有被建立, 而是存储在其他临时的地方 ?
我们在做测试的时候, 同样的表及数据量,其他环境也一样, online加入index会比非online 加入慢很多 。
在线建立INDEX的文章 。
http://download-west.oracle.com/ ... 56/indexes.htm#3062
************************************************************************************
Creating an Index Online
Previously, when creating an index on a table there has always been a DML S-lock on that table during the index build operation, which meant you could not perform. DML operations on the base table during the build.
Now, with the ever-increasing size of tables and necessity for continuous operations, you can create and rebuild indexes online--meaning you can update base tables at the same time you are building or rebuilding indexes on that table. Note, though, that there are still DML SS-locks, which means you cannot perform. other DDL operations during an online index build.
The following statements perform. online index build operations:
ALTER INDEX emp_name REBUILD ONLINE;
CREATE INDEX emp_name ON emp (mgr, emp1, emp2, emp3) ONLINE;
--------------------------------------------------------------------------------
Note:
While you can perform. DML operations during an online index build, Oracle recommends that you do not perform. major/large DML operations during this procedure. For example, if you wish to load rows that total up to 30% of the size of an existing table, you should perform. this load before the online index build.
--------------------------------------------------------------------------------
**************************************************************************************
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/35489/viewspace-521325/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/35489/viewspace-521325/