1. 首页
  2. IT资讯

Creating an Index Online – 在线建立索引

http://www.itpub.net/showthread.php?threadid=336222&pagenumber=

在线建立INDEX的文章 。 http://download-west.oracle.com/doc…ndexes.htm#3062

************************************************************************************

Creating an Index OnlinePreviously, 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.

——————————————————————————–

**************************************************************************************

When the ONLINE keyword is specified as a part of an ALTER INDEX or CREATE INDEX command, a temporary index-organized journal table is created to record changes made to the base table. This journal table is created in the same tablespace as the index being altered or created.While the server process is rebuilding the index, other users can continue to access the old index structure. Any updates to the old index during the rebuild operation are recorded in the journal table.

所谓的journal table 就是:ORA-08120: Need to create SYS.IND_ONLINE table in order to (re)build index Cause: Alter index Build/Rebuild online require existing of SYS.IND_ONLINE table. Action: User/DBA needs to create sys.ind_online before alter the index /rdbms/admin/catcio.sql contains script. to create ind_online.

Online Index Rebuild (continued)

When the server process has completed the rebuild operation, it merges the changes enteredin the journal. This merge by which changed rows are incorporated into the new index isdone while the table is still online.This is accomplished by scanning the journal table and operating on a per row basis.Operations are committed every 20 rows. Locked rows are skipped. The Oracle serverprocess may make multiple passes over the journal table to process previously locked rows.

——————

主题: Ora-8120 On Online Index Rebuild After 9.2.0.5 Upgrade 类型: PROBLEM 状态: MODERATED 内容类型: TEXT/X-HTML 创建日期: 31-MAR-2004 上次修订日期: 12-MAY-2004

The information in this article applies to: Oracle Server – Enterprise Edition – Version: 9.2.0.5This problem can occur on any platform.

ErrorsORA-8120RFA-1304

SymptomsYou have applied the 9205 patchset and when you try to rebuild an index online, you receive the following error:

ORA-08120: Need to create SYS.IND_ONLINE$ table in order to (re)build index ChangesA new table, SYS.IND_ONLINE$, was created in the 9205 patchset that is referenced to relieve the performance related issues that are caused when rebuilding an index online. When rebuilding an index online, this table is updated, however, this table does not exist in the dictionary for 9205. Please note that this is NEW to 9205 FixLog into the database as the SYSDBA user and run the following script. from ORACLE_HOME/rdbms/admin

% @catcio.sql

References

———————————-

catcio.sql中的内容如下:SET ECHO ONSET FEEDBACK 1SET NUMWIDTH 10SET LINESIZE 80SET TRIMSPOOL ONSET TAB OFFSET PAGESIZE 100

create table ind_online$( obj# number not null,type# number not null, /* what kind of index is this? *//* normal : 1 *//* bitmap : 2 *//* cluster : 3 *//* iot – top : 4 *//* iot – nested : 5 *//* secondary : 6 *//* ansi : 7 *//* lob : 8 *//* cooperative index method : 9 */flags number not null/* index is being online built : 0x100 *//* index is being online rebuilt : 0x200 */)/

实际上是在Oracle建库时,没有调用catcio.sql,也可算是个BUG吧.自已手动创建一下此表就行了.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/35489/viewspace-594266/,如需转载,请注明出处,否则将追究法律责任。

主题测试文章,只做测试使用。发布者:布吉卡,转转请注明出处:http://www.cxybcw.com/194900.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code