Table performance Issue(Huge table)

User_AS6XD

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;

Mike Kutz

AFAIK - Triggers don't affect query performance. please edit your post and remove them.

Also, we can't help improve your query without seeing the query.

40M rows is considered SMALL by some.

User_AS6XD

Hi,

Triggers removed as suggested.

JohnWatson2

You should drop the unnecessary index ERDCRM.IDX_ACT_DAT

You should think about the table design. You have description1 and description2, both wide columns, as the second and third columns. As you also have PCTTHRESHOLD 50 it is more than likely that they will be in the index segment. Would you not prefer them to be in the overflow segment? Generally, I have found the INCLUDING clause to be much more useful than PCTTHRESHOLD. Order your columns such that the ones you usually project are first, and throw the others out into the overflow.

In general, you do seem to have a lot of indexes. How did you determine that you need them?

EdStevens

Just as a side observation, when you post code (or output) it is much MUCH easier to read when formatted as such. Please take a look at https://community.oracle.com/tech/apps-infra/kb/articles/12-format-text