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:
- DISABLE Index
- INSERT Data
- 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 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).
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