RE: oracle equivalent of teradata's sparse index
- From: Jacques Kilchoer <Jacques.Kilchoer@xxxxxxxxx>
- To: "mwf@xxxxxxxx" <mwf@xxxxxxxx>, "dd.yakkali@xxxxxxxxx" <dd.yakkali@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 2 Aug 2010 18:03:50 -0700
Or, what Mark said. Once again I'm a day late and a dollar short.
De : oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] De la
part de Mark W. Farnham
Envoyé : lundi 2 août 2010 17:48
À : dd.yakkali@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Objet : RE: oracle equivalent of teradata's sparse index
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:
