D RSB_SERVICE_ACTIVITY Table

Create Table

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

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;

Constraints

ALTER TABLE RSB_SERVICE_ACTIVITY ADD CONSTRAINT PK_RSB_SERVICE_ACT_TEST PRIMARY KEY
(ACTIVITY_NUM)
USING INDEX
  TABLESPACE RETAIL_INDEX
  INITRANS   12;

Purging / Maintenance

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.