Re: Index only on newest partition?
- From: "Bjørn D. Jensen" <bjorn.d.jensen@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 29 Jan 2008 20:32:50 +0100
thank you to all of you for input ;-)
Best Regards
Bjorn D. Jensen
2008/1/29, Connor McDonald <mcdonald.connor@xxxxxxxxx>:
>
>
>
> ------------------------------
> *From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
> oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Bjørn D. Jensen
> *Sent:* Tuesday, 29 January 2008 4:43 AM
> *To:* oracle-l@xxxxxxxxxxxxx
> *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
>
Other related posts:
