1 Reply Latest reply: Jun 8, 2010 1:50 PM by DanCodi RSS

    Create new index on date column

    DanCodi
      I have an existing table with an index on two columns which are not the date column. The table has about 1 million rows and we are preparing to do some cleanup by copying records over to a temporary table using a date criteria.

      I want to add and index to the date column move records to the temp table using a date criteria then perform a truncate and move the records back. The question I have is do I add a separate index to the date column or modify the existing index and add a third column to it.

      The table definition is listed below:

      TABLE CLINICAL_CONTEXT
      (     
      "SESSIONID" NVARCHAR2(256) NOT NULL ENABLE,
      "UNIQUEID" NUMBER NOT NULL ENABLE,
      "CONTEXT" NVARCHAR2(2000) NOT NULL ENABLE,
      "INSERTED" DATE DEFAULT sysdate NOT NULL ENABLE,
      "ENCOUNTERID" NUMBER,
      SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS,
      SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS,
      SUPPLEMENTAL LOG DATA (UNIQUE INDEX) COLUMNS
      )
      PCTFREE 10
      PCTUSED 0
      INITRANS 1
      MAXTRANS 255
      NOCOMPRESS LOGGING
      STORAGE(INITIAL 40960 NEXT 40960 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
      TABLESPACE "HTCLNSM" ;


      CREATE INDEX "CC_SID_UID" ON "CLINICAL_CONTEXT" ("SESSIONID", "UNIQUEID")
      PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
      STORAGE(INITIAL 40960 NEXT 40960 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
      TABLESPACE "HTCLNISM" ;