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.
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