Index Organized table - hanging on selects

From: Dan <daniel.ostertag_at_visaer.com>
Date: Fri, 27 Apr 2012 13:08:43 -0700 (PDT)
Message-ID: <83665304-70e2-41fd-b810-9de8915cac8e_at_e15g2000vbt.googlegroups.com>



We are running Oracle 11g and seem to be encountering an occasional problem in which index organized tables (IOT) are hanging for a long time upon a select. Locks are the causing it, and it eventually returns data, but even if doing a "select * from iot_table" it may take 5 minutes to return rows in TOAD, when this normally is instantaneous.

We have a large query in which one of the tables joined to is an IOT. Every once in a while (maybe 5% of the time) a query will slow up to the point that a 1 hour query could take 2 days. It doesn't happen all the time. Yesterday on a particularly troublesome query I recreated the IOT as a heap table with an index and the query ran as expected.

Has anyone encountered issues like this? IOT's are suppsed to be faster while reading? Is there a bug or a problem perhaps with how I'm building the table?

Thanks,
Dan Received on Fri Apr 27 2012 - 15:08:43 CDT

Original text of this message