RE: Index only on newest partition?

From: Connor McDonald <mcdonald.connor_at_gmail.com>
Date: Tue, 29 Jan 2008 21:28:15 +0900
Message-ID: <013101c86272$6ed58560$0b00a8c0@dell600m>

 


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
Received on Tue Jan 29 2008 - 06:28:15 CST

Original text of this message