Oracle Scratchpad

March 3, 2011

Index Rebuilds

Filed under: Index Rebuilds,Indexing,Infrastructure,Performance — Jonathan Lewis @ 6:43 pm BST Mar 3,2011

A couple of days ago I found several referrals coming in from a question about indexing on the Russian Oracle Forum. Reading the thread I found a pointer to a comment I’d written for the Oracle-L list server a couple of years ago about Advanced Queueing and why you might find that it was necessary to rebuild the IOTs (index organized tables) that support AQ.

The queue tables are, of course, a perfect example of what I call the “FIFO” index so it’s not a surprise that they might need special consideration. Rather than rewrite the whole note I’ll just link to it from here. (One of the notes in the rest of the Oracle-L thread also points to MOS document 271855.1 which describes the whys and hows of rebuilding AQ tables.)

2 Comments »

  1. Had this on our databases, exactly on “FIFO” tables, i.e. “queue like” tables: insert, process, delete.
    Coalesced helped.
    Does it sound strange for a well priced database like Oracle? Well, whatever the price you can not beat physics. A bit of underlying theory might help, for example: http://en.wikipedia.org/wiki/B-tree#Access_concurrency
    “The cost associated with this improvement is that empty pages cannot be removed from the btree during normal operations.” That’s called engineering. Oracle does exactly that. It optimizes some areas but sacrifices others and … introduces bugs.

    Comment by Laimis N — March 7, 2011 @ 7:13 am BST Mar 7,2011 | Reply

  2. [...] wrote a short note last week that linked to a thread on the Russian Oracle forum about indexing, and if you’ve followed [...]

    Pingback by Index Rebuilds « Oracle Scratchpad — March 10, 2011 @ 6:16 pm BST Mar 10,2011 | Reply


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,161 other followers