Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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'))
)
share|improve this question
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. –  Justin Cave Sep 24 '12 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? –  karthikbharadwaj Sep 24 '12 at 4:57
    
Please show us the (complete) CREATE TABLE and CREATE INDEX statements. –  a_horse_with_no_name Sep 24 '12 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'))); –  karthikbharadwaj Sep 24 '12 at 9:12
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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