Re: Index Organized table - hanging on selects

From: onedbguru <onedbguru_at_yahoo.com>
Date: Sat, 28 Apr 2012 11:27:17 -0700 (PDT)
Message-ID: <18751605.539.1335637637252.JavaMail.geo-discussion-forums_at_vbvx4>

On Friday, April 27, 2012 4:08:43 PM UTC-4, Dan wrote:
> 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

having slow performance on an IOT is indicative of not using the index properly. You must be looking for EXACT MATCH lookups on the entire key or at least the leading edge(s) and BEWARE of implicit data type conversions..

Example:

when you realize that:

to_date( SOME_DATE, 'dd-mon-yyyy')

is equivalent to:

to_date(
TO_CHAR(some_date),
'dd-mon-yyyy' )

and that that is really equivalent to:

to_date(
to_char(some_date,'NLS_DATE_FORMAT'),
'dd-mon-yyyy' )  

You may not be getting the results you were looking for either...

Also something like datefield = to_char(sysdate,'NLS_DATE_FORMAT') is evaluating a date to a character - can you say FTS? Received on Sat Apr 28 2012 - 13:27:17 CDT