Re: What is a good blocksize to use.

>
>
>In this case, the maintenance aspect is pretty irrelevant.
>Your comment about 'fairly random' inserts leading to 75%
>efficiency is correct. However, your data does not have a
>random distribution, which is why I made the comments
>I did.
>
>You have a very large number of client ids (type A, say)
>with 3 or 4 rows, 10% have several hundred rows (type B), and
>a few have row counts in the hundreds of thousands (type C).
>
>In your case, therefore, there is a significant risk that the arrival
>of a type B client will cause a leaf block split at the usual 50/50
>location. It is then less likely than average that future data
>(i.e for the few type A client sharing the lower block) will fill
>the lower data block, and the only data that could go into the
>higher block will be data for that one specific type B client.
>
>

Because most of the data is from the larger (B and C) clients, most of the splits will also occur within data of these clients.

But shurely if the growing process from one client occurs only at one end. So a B client is split all rows are added to the right of the client, yes than there will be more blocks from about 50 % fill rate so that the average of 75 % can not be realised.

(For SQL-server a reindex would alleviate this problem, in our surroundings one reindex every two years would solve this problem completely. Mayby something similar is possible in Oracle.)

Somebody else pointed out to me that another index on a IOT actually works with the key used in the IOT and not with an approximate row-id as I understood from the Oracle documentation. I haven't checked this yet.

ben

>
>
>

Ben Brugman Received on Mon Sep 30 2002 - 08:20:54 CDT