Oracle Scratchpad

March 3, 2011

Index Rebuilds

Filed under: Index Rebuilds,Indexing,Infrastructure,IOT,Oracle,Performance — Jonathan Lewis @ 6:43 pm GMT 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.)


  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:
    “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 GMT 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 GMT Mar 10,2011 | Reply

  3. […] IOTs, FIFOs, Queues and rebuilds (Mar 2011) […]

    Pingback by IOTs | Oracle Scratchpad — February 11, 2021 @ 10:19 pm GMT Feb 11,2021 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

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

You are commenting using your 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: