RE: Index only on newest partition?
- From: "Connor McDonald" <mcdonald.connor@xxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 29 Jan 2008 21:28:15 +0900
_____
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:
