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) 
STORE in (uwdata)
  partition p1 values LESS THAN (to_date('2008-03-15','YYYY-MM-DD'))
  • 1
    I'm not sure I understand the question. You talk about an existing index but you don't tell us what column(s) the existing index is defined on. I'm not sure what you mean by "index type" in the context of your new index-- are you asking whether to create a b-tree index or a bitmap index, whether to create a local index or a global index, or are you asking something else. And then you state that you don't have enough space to build the index (which implies that you know what sort of index you want to build) which implies that you want to know how to make the index smaller. Commented Sep 24, 2012 at 4:08
  • @JustinCave: There is an existing Index on the date column(range partitioned). I would like to create a combined index on date and the unique id column. When i try to create a combined I hit a ORA:1652 as I run out of disk space. From what i read, I would not able to add a new column to an existing index.When i meant index type i meant local vs global. Will it actually solve the space problem? What would be the best choice of index when a user queries on both the unique id and date time. Should i create a local index on the partitions? Commented Sep 24, 2012 at 4:57
  • Please show us the (complete) CREATE TABLE and CREATE INDEX statements.
    – user1822
    Commented Sep 24, 2012 at 7:37
  • @a_horse_with_no_name, My create table statement is something similar to the one below. My question would be if the performance of a query which has both the (interva_date,person_id) be good if a hash sub partition is created for person_id to the range index that is created in this script. 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'))); Commented Sep 24, 2012 at 9:12

1 Answer 1


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.