RE: oracle equivalent of teradata's sparse index
- From: "Goulet, Richard" <Richard.Goulet@xxxxxxxxxxx>
- To: <dd.yakkali@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 3 Aug 2010 13:40:51 -0400
OK, biggest problem I see here is that date fields contain time as well
as date, namely DD-MON-YYYY HH24:MI:SS and all you want is date. That
being the case a function based index as:
create index <pick_a_name>
on <table_name> (trunc(<date_column>));
Would do the job since the date field is truncated all the time, so A
query of the type:
select * from <table_name> where trunc(d<date_field>) =
trunc(sysdate-1);
Would suffice.
OH, BTW 5 Billion rows of indexed data is quite douable. I've done
100billion rows, no problems.
Dick Goulet
Senior Oracle DBA/NA Team Lead
PAREXEL International
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of dd yakkali
Sent: Monday, August 02, 2010 7:36 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: oracle equivalent of teradata's sparse index
hello dbas,
Some one asked me that teradata can create index for only a subset of
rows of a table. We have a huge table (5 billion) which is for few
months of data but the data for most part is accessed for yesterday. So
the question is: is there any way to create index only for yesterday.
Google did not come up wth any hits.
thanks
Deen
Other related posts:
