RE: IOT Issues?

Thanks for the comments. And your comment about clusters -- from the standpoint of getting the great clustering factor and minimizing I/O, I experimented with self clustering a single table. I used data from a single partition and it was around 25 million rows. A straight insert into a heap (parallel direct) completed in around 5 minutes. I killed the insert into the clustered table at about the one hour mark ;-). I could do an insert ordered by into a heap in around 20 minutes. But that would mean we would have to throw that data out there and partition exchange back in (like we currently do). Was hoping the IOT could automatically address the clustering factor aspect, but an ora-600 got in our way during the test.

> 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: Larry Elkins
  INET: elkinsl_at_flash.net

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