Re: Index only on newest partition?

thank you to all of you for input ;-)
Best Regards
Bjorn D. Jensen

2008/1/29, Connor McDonald <mcdonald.connor_at_gmail.com>:
>
>
>
> ------------------------------
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Bjørn D. Jensen
> *Sent:* Tuesday, 29 January 2008 4:43 AM
> *To:* oracle-l_at_freelists.org
> *Subject:* Index only on newest partition?
>
>
> Hi!
> If you have an huge table that is partitioned;
> is it possible to only have an index on the newest partition?
>
> think about the situation where users typical are intereted in the newest
> part,
> but you can live with slower performance for history data.
>
> Thanks
> Bjorn D. Jensen
>
> I was experimenting with this recently (10.2), and the results were not
> spectacular.
>
> Option 1 - create local indexes, set all the index partitions to unusable except
> the current one. SQL queries had to be specially crafted (typically as
> "union all" between active and inactive partitions).
>
> Option 2 - create function based indexes which went to "null" for all
> non-active data. SQL queries (still) had to be specially crafted to use the
> fbi's..
>
> All in all it was disappointing (from a optimal perspective of a standard
> SQL being split by Oracle to prove old partitions without the index and
> current partitions with the index)
>
> hth
> Connor
>

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