This discussion is archived
1 Reply Latest reply: Jun 8, 2010 11:50 AM by DanCodi RSS

Create new index on date column

DanCodi Newbie
Currently Being Moderated
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" ;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points