Re: oracle equivalent of teradata's sparse index

From: Dion Cho <ukja.dion_at_gmail.com>
Date: Tue, 3 Aug 2010 09:52:50 +0900
Message-ID: <AANLkTi=pis92cu-uz_EjWB8nVvbTjbH6PDP+yFs1sM83_at_mail.gmail.com>

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_at_rsiz.com>

> 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_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *dd yakkali
> *Sent:* Monday, August 02, 2010 7:36 PM
> *To:* oracle-l_at_freelists.org
> *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
>

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Aug 02 2010 - 19:52:50 CDT