Oracle Scratchpad

December 17, 2009

Partition Indexing

Filed under: CBO,Indexing,Infrastructure,Partitioning — Jonathan Lewis @ 9:01 pm GMT Dec 17,2009

From time to time I get asked if it’s possible to index a partitioned table so that recent partitions have different (local) indexes from older partitions. The answer is “not really, but there’s a couple of dirty tricks which aren’t very nice and aren’t very stable“. (You can always play around – dangerously – with unusable indexes or function-based indexes).

With Oracle 11.2 there’s a new optimizer feature called “table expansion” which I’m guessing has been created to address this issue. Christian Antognini introduces it in this posting – which is actually starts by talking about zero-sized segments and unusable indexes.

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,430 other followers