Re: Index only on newest partition?
Date: Tue, 29 Jan 2008 20:32:50 +0100
thank you to all of you for input ;-)
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?
> 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
> but you can live with slower performance for history data.
> Bjorn D. Jensen
> I was experimenting with this recently (10.2), and the results were not
> 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
> 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)