INDEX Creation Advice Oracle

indexindex-tuningoracle-11g-r2

I have a table that has time data of 2 billion records obtained from the clients. The data has two main columns, date and entity_id.

The attributes date, and entity_id would make a unique pair.

Data is made into range partitions on the date field across several tablespaces. However, the script to create the index is a normal CREATE INDEX script which does not mention any tablespace or partition name.

I would like to create a combined index on both columns as the queries could use both columns, however the index data file exceeds disk space availability. I am not sure of the choice of index type.

I do not want to touch the existing index. 

create table interval_date 
(
  person_id   NUMBER(5) NOT NULL,
  last_name   VARCHAR2(30),
  dob         DATE
)
partition by range (dob) 
INTERVAL(numtoyminterval(1,'MONTH')) 
STORE in (uwdata)
(
  partition p1 values LESS THAN (to_date('2008-03-15','YYYY-MM-DD'))
)

Best Answer

Whether you use a local index or a global index will have (practically) no impact on the amount of space required for the index. If you are running out of space to create the index, you'll need to allocate additional disk space to the tablespace (or tablespace(s) if you create a local index where the index partitions are stored in different tablespaces). If you are building an index that involves the column you are partitioning on, you would almost always want to create a local index-- there is no benefit to creating a global index in this case.

Assuming that the dob column stores dates of birth and that the time component of that column is always set to midnight, you can probably reduce the space required for the index by making the dob column the leading column of the index and by using index compression. In your case, you probably want to specify COMPRESS 1 since you would only want to compress the dob column, not the person_id column assuming that person_id has many fewer repeated values.

Related Question