Re: oracle equivalent of teradata's sparse index
- From: Dion Cho <ukja.dion@xxxxxxxxx>
- To: mwf@xxxxxxxx
- Date: Tue, 3 Aug 2010 09:52:50 +0900
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
>
Other related posts:
