Message-ID: <F001.00519041.20021212205837@fatcity.com>
Date: Thu, 12 Dec 2002 20:58:37 -0800
To: Multiple recipients of list ORACLE-L <ORACLE-L@fatcity.com>
Sender: root@fatcity.com
Reply-To: ORACLE-L@fatcity.com
Errors-To: ML-ERRORS@fatcity.com
From: "Reardon, Bruce (CALBBAY)" <Bruce.Reardon@comalco.riotinto.com.au>
Subject: RE: IOT Issues?
Organization: Fat City Network Services, San Diego, California
Precedence: bulk
Mime-Version: 1.0
Content-Type: text/plain;	charset="iso-8859-1"
Content-Transfer-Encoding: 8bit

Larry,

Have you seen paper 138 at Orapub.com (http://www.orapub.com/cgi/genesis.cgi?p1=sub&p2=abs138) titled "Index Organized Tables -- When should they be used? "
This has some benchmark figures.

Also, do you use Forms as a client - this can introduce some gotchas with IOTs (particularly if still on Forms 4.5)?

HTH,
Bruce Reardon

-----Original Message-----
Sent: Friday, 13 December 2002 1:19 PM

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@flash.net
214.954.1781
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Reardon, Bruce (CALBBAY)
  INET: Bruce.Reardon@comalco.riotinto.com.au

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