I have a table that has time data of 2 billion records obtained from the clients. The data has two main columns,
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')) )