Creating an Index Online - 線上建立索引
http://www.itpub.net/showthread.php?threadid=336222&pagenumber=
線上建立INDEX的文章 。
************************************************************************************
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.
--------------------------------------------------------------------------------
**************************************************************************************
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 entered
in the journal. This merge by which changed rows are incorporated into the new index is
done 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 server
process 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.5
This problem can occur on any platform.
Errors
ORA-8120
RFA-1304
Symptoms
You 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
Changes
A 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
Fix
Log into the database as the SYSDBA user and run the following script. from ORACLE_HOME/rdbms/admin
% @catcio.sql
References
----------------------------------
catcio.sql中的內容如下:
SET ECHO ON
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET 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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Creating an Index OnlineIndex
- 聊聊索引Index Rebuild和Rebuild Online(下)索引IndexRebuild
- 聊聊索引Index Rebuild和Rebuild Online(上)索引IndexRebuild
- 重建索引index rebuild online vs offline vs index coalesce vs index shrik space索引IndexRebuild
- zt_如何加速索引index建立索引Index
- create index online 與rebuild index onlineIndexRebuild
- create index online 與rebuild index onlineIndexRebuild
- online 建立索引失敗處理索引
- 建立index 使用Online導致的問題Index
- oracle10g_alter index rebuild_online_重構索引OracleIndexRebuild索引
- 加快建立索引(create / rebuild index) 的幾點索引RebuildIndex
- create index .. onlineIndex
- create index onlineIndex
- Index Online RebuildIndexRebuild
- Create Index ...ONLINEIndex
- "Alter index rebuild online parallel n"會修改索引的degree屬性IndexRebuildParallel索引
- rebuild index online和create index online及沒有online的區別RebuildIndex
- create index/create index online區別Index
- mysql下建立索引讓其index全掃描MySql索引Index
- index索引Index索引
- CREATE INDEX ......ONLINE分析Index
- 建約束(Constraint)時隱式建立索引(Index)及先建立索引後建立約束的區別AI索引Index
- 分割槽索引之本地(local index)索引和全域性索引(global index)索引Index
- create index online 和create index 不同及注意點Index
- Elasticsearch之索引模板index template與索引別名index aliasElasticsearch索引Index
- alter index rebuild 與 rebuild onlineIndexRebuild
- index rebuild online的問題IndexRebuild
- Mysql——index(索引)使用MySqlIndex索引
- oracle index索引原理OracleIndex索引
- mysql 索引( mysql index )MySql索引Index
- 點陣圖索引(Bitmap Index)——索引共用索引Index
- 關於rebuild index online 及drop index後重建問題RebuildIndex
- MySQL online create index實現原理MySqlIndex
- Index online operation的過程 ztIndex
- Elasticsearch 6 建立索引報錯 invalid_index_name_exception Invalid index name [testDemo], must be lowercaseElasticsearch索引IndexException
- 【INDEX】Postgresql索引介紹IndexSQL索引
- pandas(3):索引Index/MultiIndex索引Index
- 唯一性索引(Unique Index)與普通索引(Normal Index)差異(上)索引IndexORM