But the problem is that YESTERDAY is not a constant but a varying value. It's not deterministic, so the function based index would not fit in this case. ================================ Dion Cho http://dioncho.wordpress.com ================================ 2010/8/3 Mark W. Farnham <mwf@xxxxxxxx> > 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 >