Skip to main content
Kofax

Kofax Insight - Oracle ALTER INDEX … REBUILD ONLINE

Summary

This article will describe the effect of ALTER INDEX … REBUILD ONLINE statement based on the different editions of Oracle DBMS. 13437

Applies to: Insight 5.0, 5.1, 5.2, 5.3
Date: March 31, 2016

Insight Data Loads

As part of a normal Insight Data Load process, Insight will follow the basic pattern of:

  1. DISABLE Index
  2. INSERT Data
  3. ENABLE/REBUILD Index

ALTER INDEX … REBUILD ONLINE

In the context of an Oracle DBMS, the third step is accomplished with an ALTER INDEX … REBUILD ONLINE statement. However, it must be noted that this statement is currently only supported by the Enterprise Edition of Oracle.

Other editions of Oracle like Standard Edition and Express Edition do not support this statement (specifically the ONLINE part), so if you do a normal Insight Data Load with the Standard or Express Edition of Oracle your Indexes will be successfully disabled to insert data, but then they will not be re-enabled due to the mentioned Oracle limitation.

Oracle will generate an ORA-00439 error, and this will show up as a WARN entry in the InsightServer_WinApp.log file, as in this example:

2016-02-25 12:55:19,314 [6f61f706-5ea1-4e6c-abe9-eea1e4243cd1] INFO
AltoSoft.Insight.DataBaseManager.DatabaseConnectionSettings.BaseSqlConnectionSettings - Begin disable index I_EventTimeIDAverageCapture_01 from table IDAverageCaptureTime
2016-02-25 12:55:19,318 [6f61f706-5ea1-4e6c-abe9-eea1e4243cd1] INFO
AltoSoft.Insight.DataBaseManager.DatabaseConnectionSettings.BaseSqlConnectionSettings - End disable index I_EventTimeIDAverageCapture_01 from table IDAverageCaptureTime



2016-02-25 12:55:19,375 [6f61f706-5ea1-4e6c-abe9-eea1e4243cd1] INFO AltoSoft.Insight.DataBaseManager.DatabaseConnectionSettings.BaseSqlConnectionSettings - Begin enable index I_EventTimeIDAverageCapture_01 from table IDAverageCaptureTime
2016-02-25 12:55:19,378 [6f61f706-5ea1-4e6c-abe9-eea1e4243cd1] WARN AltoSoft.Insight.DatabaseManager.SQLQueryExecutor - Executing query:
alter index "I_EventTimeIDAverageCapture_01" rebuild online TimeOut 300
Connection: Data Source: User Id=KAFMO_DATA_01;Password=********; Data Source=XE2; Database name: Oracle Provider name: odp
Oracle.DataAccess.Client.OracleException (0x80004005): ORA-00439: feature not enabled: Online Index Build
    at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck, Int32 isRecoverable, OracleLogicalTransaction m_OracleLogicalTransaction)
    at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, Boolean bCheck, OracleLogicalTransaction m_OracleLogicalTransaction)
    at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()
    at AltoSoft.Insight.DatabaseManager.SQLQueryExecutor.<DirectExecuteNonQuery>b__a(IDbCommand dbCommand)
    at AltoSoft.Insight.DatabaseManager.SQLQueryExecutor.ExecuteEx(SQLExecutionContext context, CommandExecuteMethod commandExecuteMethod, Boolean read)

This leaves your indexes in an UNUSABLE state, and they must be manually rebuilt from within Oracle DBMS. As another option, additional SQL Query Step(s) can be added to the end of the Data Load's Execution Plan to rebuild each disabled index using the ALTER INDEX … REBUILD statement.

The Standard and Express Editions of Oracle only support the ALTER INDEX … REBUILD statement, which will cause the underlying table to be “offline” (locked) during the index rebuild process (which is why Insight only uses the ONLINE option).

Additional Notes

Please Note that future Insight 5.4 is planned to have a new “Rebuild Indexes Mode” feature to accommodate both Data Load performance and DB feature-set issues related to this. This feature will allow user to defeat the disabling (and therefore the subsequent enabling/rebuilding) of indexes during the Data Load process.
Insight 5.4 is not released at the time of this KB’s publication, so this particular new feature is still subject to change.

Keywords: Altosoft, Insight, Oracle, data, load, warn, error, index, rebuild, enable, disable