Table total records: 39435879
Problem Statement: How to improve performance while querying this huge table? Kindly share suggestions.
Table Script:
DROP TABLE ERDCRM.ACTIVITY CASCADE CONSTRAINTS;
CREATE TABLE ERDCRM.ACTIVITY
(
ID NVARCHAR2(64) NOT NULL,
NAME NVARCHAR2(80) NOT NULL,
DESCRIPTION1 NVARCHAR2(2000),
DESCRIPTION2 NVARCHAR2(2000),
LEGACY_ID NUMBER(10) NOT NULL,
VERSION NUMBER(10) NOT NULL,
ACTIVITY_DATE DATE NOT NULL,
CREATED_FOR NVARCHAR2(100) NOT NULL,
TYPE_CODE NVARCHAR2(40) NOT NULL,
IS_URGENT CHAR(1 BYTE) NOT NULL,
MAIN_CONTACT_ID NVARCHAR2(64),
CREATED_BY NVARCHAR2(255) NOT NULL,
REQUESTED_BY NVARCHAR2(255),
SOURCE_SYSTEM NVARCHAR2(5),
VISIBLE_IN_RADAR CHAR(1 BYTE) DEFAULT 'Y' NOT NULL,
CREATION_DATE TIMESTAMP(6) DEFAULT sys_extract_utc(systimestamp) NOT NULL,
OVERRIDE_PRIVACY CHAR(1 BYTE),
SUBJECT_AUTOGENERATED CHAR(1 BYTE),
LAST_UPDATE_DATE TIMESTAMP(6) DEFAULT SYS_EXTRACT_UTC(SYSTIMESTAMP),
DURATION_SECS NUMBER,
IS_DURATION_DEFAULT CHAR(1 BYTE),
IS_MKT_MATERIAL_ATTACHED CHAR(1 BYTE),
ACTIVITY_START_DATETIME TIMESTAMP(6),
ACTIVITY_END_DATETIME TIMESTAMP(6),
IS_BROKER_REVIEW_RELEVANT CHAR(1 BYTE),
IS_AT_CLIENT_OFFICE CHAR(1 BYTE),
MEETING_ID VARCHAR2(20 BYTE),
QUESTION_BANK VARCHAR2(4000 BYTE),
PRIMARY KEY
(ACTIVITY_DATE, ID)
ENABLE VALIDATE
)
ORGANIZATION INDEX
PCTTHRESHOLD 50
TABLESPACE ERDCRM
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
OVERFLOW TABLESPACE ERDCRM
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
);
CREATE INDEX ERDCRM.IDX_ACTIVITY_MEETING_ID ON ERDCRM.ACTIVITY
(MEETING_ID)
NOLOGGING
TABLESPACE ERDCRM
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
PARALLEL ( DEGREE 4 INSTANCES 1 );
CREATE INDEX ERDCRM.IDX_ACT_CREATED_FOR ON ERDCRM.ACTIVITY
(CREATED_FOR, MAIN_CONTACT_ID)
LOGGING
TABLESPACE ERDCRM
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
PARALLEL ( DEGREE 4 INSTANCES 1 );
CREATE INDEX ERDCRM.IDX_ACT_DAT ON ERDCRM.ACTIVITY
(ACTIVITY_DATE)
LOGGING
TABLESPACE ERDCRM
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
);
ALTER INDEX ERDCRM.IDX_ACT_DAT
MONITORING USAGE;
CREATE UNIQUE INDEX ERDCRM.IDX_ACT_DATE_CODE_ID ON ERDCRM.ACTIVITY
(ACTIVITY_DATE, TYPE_CODE, ID)
LOGGING
TABLESPACE ERDCRM
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
);
ALTER INDEX ERDCRM.IDX_ACT_DATE_CODE_ID
MONITORING USAGE;
CREATE UNIQUE INDEX ERDCRM.IDX_ACT_ID_DATE_CODE ON ERDCRM.ACTIVITY
(ID, ACTIVITY_DATE, TYPE_CODE, CREATED_FOR, MAIN_CONTACT_ID)
LOGGING
TABLESPACE ERDCRM
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
);
ALTER INDEX ERDCRM.IDX_ACT_ID_DATE_CODE
MONITORING USAGE;
CREATE INDEX ERDCRM.IDX_ACT_LUD ON ERDCRM.ACTIVITY
("LAST_UPDATE_DATE" DESC)
NOLOGGING
TABLESPACE ERDCRM
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
PARALLEL ( DEGREE 4 INSTANCES 1 );
CREATE INDEX ERDCRM.IDX_ACT_NAME ON ERDCRM.ACTIVITY
(NAME)
LOGGING
TABLESPACE ERDCRM
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
);
ALTER INDEX ERDCRM.IDX_ACT_NAME
MONITORING USAGE;
-- There is no statement for index ERDCRM.SYS_IOT_TOP_112642.
-- The object is created when the parent object is created.
CREATE UNIQUE INDEX ERDCRM.UN_ACT_ID ON ERDCRM.ACTIVITY
(ID)
LOGGING
TABLESPACE ERDCRM
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
);
ALTER INDEX ERDCRM.UN_ACT_ID
MONITORING USAGE;
CREATE UNIQUE INDEX ERDCRM.UN_ACT_LEGACY_ID ON ERDCRM.ACTIVITY
(LEGACY_ID)
LOGGING
TABLESPACE ERDCRM
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
);
ALTER INDEX ERDCRM.UN_ACT_LEGACY_ID
MONITORING USAGE;
ALTER TABLE ERDCRM.ACTIVITY ADD (
CONSTRAINT UN_ACT_ID
UNIQUE (ID)
USING INDEX ERDCRM.UN_ACT_ID
ENABLE VALIDATE
, CONSTRAINT UN_ACT_LEGACY_ID
UNIQUE (LEGACY_ID)
USING INDEX ERDCRM.UN_ACT_LEGACY_ID
ENABLE VALIDATE);
ALTER TABLE ERDCRM.ACTIVITY ADD (
CONSTRAINT ACTIVITY_MAIN_CON_FK
FOREIGN KEY (MAIN_CONTACT_ID)
REFERENCES ERDCRM.CONTACT (CONTACT_ID)
ENABLE VALIDATE
, CONSTRAINT ACTIVITY_OWNER_FK
FOREIGN KEY (CREATED_FOR)
REFERENCES ERDCRM.UBS_EMPLOYEE (GPN)
ENABLE VALIDATE
, CONSTRAINT ACTIVITY_TYPE_CODE_FK
FOREIGN KEY (TYPE_CODE)
REFERENCES ERDCRM.ACTIVITY_TYPE (CODE)
ENABLE VALIDATE);
GRANT UPDATE ON ERDCRM.ACTIVITY TO CRM;
GRANT SELECT ON ERDCRM.ACTIVITY TO CRMADMIN;
GRANT SELECT ON ERDCRM.ACTIVITY TO CRM_READONLY_ROLE;
GRANT DELETE, INSERT, SELECT, UPDATE ON ERDCRM.ACTIVITY TO CRUD_ERDCRM_ROLE;
GRANT DELETE, INSERT, SELECT, UPDATE ON ERDCRM.ACTIVITY TO ERDCRM_APP;
GRANT SELECT ON ERDCRM.ACTIVITY TO READONLY_ERDCRM_ROLE;