RE: oracle equivalent of teradata's sparse index
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <dd.yakkali@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 2 Aug 2010 20:47:51 -0400
Just supposing you have a column "created on time", I'm thinking that a
function based index on that column re-created at just past midnight each
night that decodes to null except for yesterday's time range would do the
trick.
NOT TESTED. As a gedanken experiment thought, it should only contain values
for yesterday at a cost of construction of roughly one full table scan.
Every query would presumably get all of yesterday each time and then filter
down afterward; the sparesness of course would be destroyed if you added not
null column values.thinking.it might be possible to add additional function
based columns that are also nulled if the result of the created_date
function is nulled, meaning any additional columns would also be function
based.
Totally untried. but it MIGHT work.
_____
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:
