Re: Clusters and insert performance

Your sizing is a nuisance for clustering. at 100 rows for 25 bytes each, you get
2,500 rows per cluster value.

For 2K blocks you chain once, and
waste more than half a block, on 4K
blocks you waste 25% of a block.

On the other hand, if you use a 8K blocks you get good usage, and 3 cluster keys
per block.

Your index space saving would
seem to be quite large - the cluster
index would be the same size as
the index on the detail1 table, so
the rather larger index (100 times
the size) for detail 2 won't be needed
you seem to have got a benefit
there.

Since the ratio is high (1:100) anything which does a tablescan on detail2
doesn't suffer much of an overhead - however a tablescan on table 1 would be a bit
heavy.

Since you insert all the values for detail1 and detail2 for a given key, the overhead will be small. (It tends to be large when detail2 rows have to be put into the correct place in pre-existing cluster blocks)
The drawback is that you cannot do a
direct load in SQL*Load, and the cluster index has to exist whenever you do any
inserting, so any re-orgs would be slower.

Periodic deletion of all detail1 and detail2 for a given key value - you can't partition and cluster in 8.0.5, so there are no quick 'drop partition' options to get rid of large volumes of past data. This may be the
crunch factor that means you are better off partitioning the data.

NB In 8.1 - the optimiser is aware of
partition-wise' joins if all the tables are partitioned the same way. It is not
that smart in 8.0.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Ole Christian Meldahl wrote in message <37E232B6.F13B73F9_at_statoil.com>...
>Hi
>
>I'm on 8.0.5 (I guess...., the jury is still out....) and have some
>question about clusters.
>
>We have a master - detail1 - detail2 setup of tables, and every hour we
>need to insert a huge(?) set of data into the detail1 - detail2 tables.
>
>Detail2 will always be accessed with detail 1.
>Detail 1 is never interesting with at least on detail 2 row.
>The ration between size of detail 1 and detail 2 is about 1:100, both
>for rows and bytes.(Both tables are narrow, about 25 bytes)
>I have the whole set of data for detail 2 for a given detail 1
>available togheter before insert.
>Updates will not happen to detail 1 nor detail 2.
>Deletion happens periodically of detail 1 rows with all corresponding
>detail 2.
>
>Clustering detail 1 and detail 2 will obviously(?) give a performance
>gain for queries, but all documentation advises me to be careful about
>insert performance. Will the given sequential nature of my data reduce
>the impact?
>Can I insert/load the data in some creative way exploiting the
>sequential nature, and get some performance gain?
>
>How about partitioning, given different disk, the inserts should really
>speed up, cluster or no cluster? I think partitioning on master is a
>viable option, which should give insert and query performance gain.
>
>Please excuse my non-specific questions, but it's all I have to go on
>so far. Any ideas, views, pointers or hints are welcome. Do not hesitate
>to make the necessarry assumptions to give a useful answer.
>
>Thanks!
>
>ole c
>
>
>
Received on Fri Sep 17 1999 - 08:34:36 CDT