Index Organized table - hanging on selects

Giganews Newsgroups
Subject: Index Organized table - hanging on selects
Posted by:  Dan (daniel.ostert…@visaer.com)
Date: Fri, 27 Apr 2012

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

Replies