I've did some tests on 816.
The tables were small, 3-4 fields including the PK
with a number of rows between 200 000 and 300 000.
The query time was the same except that the IOT read
50% less blocks.
- "Reardon, Bruce (CALBBAY)"
<Bruce.Reardon_at_comalco.riotinto.com.au> a écrit : >
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_at_flash.net
> 214.954.1781
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Reardon, Bruce (CALBBAY)
> INET: Bruce.Reardon_at_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_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).
>
Stéphane Paquette
DBA Oracle et DB2, consultant entrepôt de données
Oracle and DB2 DBA, datawarehouse consultant
stephane_paquette_at_yahoo.com
Lèche-vitrine ou lèche-écran ?
magasinage.yahoo.ca
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?Stephane=20Paquette?=
INET: stephane_paquette_at_yahoo.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).
Received on Fri Dec 13 2002 - 10:37:59 CST