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 >