I have a table that has time series data of 2 billion records obtained from the clients. The data has two main columns one being the date and the other being an entity_id. The attributes date,entity_id would make a unique pair. The data is made into range partitions on the date across several tablespaces  . (But 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 combined index on both the columns as the queries would use both the columns. I am not sure of the choice of index type. Could you please advice me on the index type. I do not want to touch the existing index. If i build a new index on both the columns the index data file exceeds disk space availability. Kindly help me solve this issue.

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'))
)

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'))
)

I have a table that has time series data of 2 billion records obtained from the clients. The data has two main columns one being the date and the other being an entity_id. The attributes date,entity_id would make a unique pair. The data is made into range partitions on the date across several tablespaces . (But 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 combined index on both the columns as the queries would use both the columns. I am not sure of the choice of index type. Could you please advice me on the index type. I do not want to touch the existing index. If i build a new index on both the columns the index data file exceeds disk space availability. Kindly help me solve this issue.

I have a table that has time series data of 2 billion records obtained from the clients. The data has two main columns one being the date and the other being an entity_id. The attributes date,entity_id would make a unique pair. The data is made into range partitions on the date across several tablespaces . (But 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 combined index on both the columns as the queries would use both the columns. I am not sure of the choice of index type. Could you please advice me on the index type. I do not want to touch the existing index. If i build a new index on both the columns the index data file exceeds disk space availability. Kindly help me solve this issue.

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'))
)