The recommended partitioning strategy is interval partitioning based on the request_timestamp column, with a single partition per day.
CREATE TABLE RSB_SERVICE_ACTIVITY ( ACTIVITY_NUM NUMBER(19) NOT NULL, ACTIVITY_STATE VARCHAR2(8), APPLICATION_NAME VARCHAR2(8), BUSINESS_OPERATION_NAME VARCHAR2(256), BUSINESS_SERVICE_NAME VARCHAR2(256), BUSINESS_SERVICE_URI VARCHAR2(256), ERROR_CODE VARCHAR2(64), ERROR_DETAIL VARCHAR2(2048), ERROR_REASON VARCHAR2(1024), MESSAGE_ECID VARCHAR2(64) NOT NULL, MESSAGE_FAMILY VARCHAR2(25), PROXY_OPERATION_NAME VARCHAR2(64), PROXY_SERVICE_NAME VARCHAR2(256), PROXY_SERVICE_URI VARCHAR2(256), REQUEST_TIMESTAMP TIMESTAMP(6), REQUEST_XML CLOB, RESPONSE_TIMESTAMP TIMESTAMP(6), RESPONSE_XML CLOB )
Indexes were added to assist in obtaining optimal performance of the active, real-time console. Six indexes were added, naming of indexes should be updated from what is listed below. Recommended indexes are as follows. All indexes are LOCAL partitioned indexes.
CREATE INDEX RSB_SERVICE_ACTIVITY _I1 ON RSB_SERVICE_ACTIVITY (REQUEST_TIMESTAMP, APPLICATION_NAME, MESSAGE_FAMILY, PROXY_SERVICE_NAME) LOGGING TABLESPACE RETAIL_INDEX LOCAL INITRANS 12; CREATE INDEX RSB_SERVICE_ACT_TEST_I3 ON RSB_SERVICE_ACTIVITY (REQUEST_TIMESTAMP, APPLICATION_NAME, ACTIVITY_STATE) TABLESPACE RETAIL_INDEX INITRANS 12 LOGGING LOCAL; CREATE INDEX RSB_SERVICE_ACTIVITY_I4 ON RSB_SERVICE_ACTIVITY (REQUEST_TIMESTAMP, PROXY_SERVICE_NAME, ACTIVITY_STATE) TABLESPACE RETAIL_INDEX INITRANS 12 LOGGING LOCAL; CREATE INDEX RSB_SERVICE_ACTIVITY_I5 ON RSB_SERVICE_ACTIVITY (REQUEST_TIMESTAMP, ACTIVITY_STATE) TABLESPACE RETAIL_INDEX INITRANS 12 LOGGING LOCAL; CREATE INDEX RSB_SERVICE_ACTIVITY_I7 ON RSB_SERVICE_ACTIVITY (RESPONSE_TIMESTAMP, PROXY_SERVICE_NAME) TABLESPACE RETAIL_INDEX INITRANS 12 LOCAL LOGGING; CREATE INDEX RSB_SERVICE_ACTIVITY_I8 ON RSB_SERVICE_ACTIVITY (REQUEST_TIMESTAMP, RESPONSE_TIMESTAMP, APPLICATION_NAME) TABLESPACE RETAIL_INDEX INITRANS 12 LOCAL LOGGING;
ALTER TABLE RSB_SERVICE_ACTIVITY ADD CONSTRAINT PK_RSB_SERVICE_ACT_TEST PRIMARY KEY (ACTIVITY_NUM) USING INDEX TABLESPACE RETAIL_INDEX INITRANS 12;
Purging of this table should be done at the partition level. Dropping or truncating a partition would perform far better than a delete statement. Global indexes must be maintained/updated as well; there is only 1 global index at this time and that is on the primary key of the table. Updating of global indexes could be expensive with the potential size of this table. It will be a good idea to limit the number of global indexes used on this table.