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 [EMAIL PROTECTED] 214.954.1781 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
