Forum Stats

  • 3,770,120 Users
  • 2,253,069 Discussions
  • 7,875,327 Comments

Discussions

Table performance Issue(Huge table)

User_AS6XD
User_AS6XD Member Posts: 39 Red Ribbon
edited Oct 14, 2021 11:59AM in General Database Discussions

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;

Tagged:

Answers