Re: IOT Issues?

Larry Elkins wrote:
>
> Listers,
>
> Solaris 7, 8.1.7.4 64 bit, E10K.
>
> Have a test IOT of around 120 million rows being created as we speak --
> partitioned by month (3 months for the test), overflow by naming the column
> at which to break, compressing the concatenated key, using secondary BMI's.
> BMI's would be marked as unusable and rebuilt after loads if used in the
> real world.
>
> We've been reviewing Metalink for gotcha's (found a few, some fixed in our
> version, some minor), and have opened a tar since many known bugs aren't
> published, but just curious if anyone else has run into some big issues.
>
> I'm looking forward to running some comparison queries, and inserts, against
> the IOT and the existing partitioned heap table (with 400+ columns, don't
> ask why, but gives a hint as to why we are looking at IOT's and the use of
> the overflow ;-)). So ok, plans are to split that table into 20 some odd
> commonly used columns and the rest into a separate table in a 1 to 1
> relationship, greatly reducing the number of blocks we have to visit to
> satisfy the typical query. Or really looking at a re-design more complex
> than that -- the modelers (not the original ones!) have a few things in
> mind. Using an IOT and the overflow might help avoid this and a lot of code
> changes, and might be a good intermediate relief step, or maybe even long
> term. Secondly, we CTAS partitions out using an order by and exchange
> partition on a routine basis for the sake of clustering around a commonly
> used key, greatly reducing the number blocks to be visited (queries always
> include a month range which does the partition pruning, and a cust id, by
> which we order when doing the CTAS -- tremendous benefit performance wise
> since any cust id is concentrated in as few blocks as possible). Having that
> cust id as the leading column of the IOT key can give us the clustering
> without us having to do it manually as data is added to each partition over
> time. At least that's what we are hoping ;-)
>
> Ok, I swore I would be brief, but decided it would be worth bringing up some
> of the reasons above for conducting the test in case anyone has done similar
> things for the same reasons and has things they want to share. Anyway,
> Friday should be a fun day!
>
> Regards,
>
> Larry G. Elkins
> elkinsl_at_flash.net
> 214.954.1781

Larry,

   Generally speaking I am quite in favour of IOTs, but I have encountered two problems with them - which are not directly to your case but may nonetheless interest you and others.   The first one (seen in 8.1.6 and 8.1.7) was deadlocks caused by simultaneous SELECTs and the addition of a new partition (even though the queries were not querying the partition). Those deadlocks were occurring at the dictionary level - because even if an IOT is physically a single index, logically it's still a table and a PK index. Quite obviously, a SELECT puts a SHARE dictionary lock on both - and so does the ALTER. The snag is that one locks the table first and the index second, while the other does the opposite. Ouch. It may or may not be fixed by now.
 The second problem I have met was with a biggish table which looked like a test-case for IOTs - 5 or 6 columns, 4 of which were part of the PK. Strangely, I had worst performance with it as an IOT than as a heap table. The amount of data which was required was probably too big, and, bar the fast full scan, an index is no match to a regular table when you have to shovel huge amounts of data. I also have a feeling, which would need to be more seriously researched, that as indexes are, all other things being equal, more 'sticky' memory-wise than tables, when you go through enormous amounts of data as was my case after a while your SGA is clogged and you have a lot of latch problems.

  That said, these are special cases which still have not totally disgusted of IOTs (as opposed to clusters :-)).

Stephane Faroult

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).