Re: What is a good blocksize to use.

"Ben Brugman" <benbrugman_at_onbekend.nl> wrote in message news:3d984cbd.22460468_at_news.nl.uu.net...
>
> >
> >
> >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.

The "UROWID" data type is what is being talked about, and it is both a 'guess' and an approximate rowid (!).

When the secondary index is first created on the IOT, Oracle uses the location of the IOT's rows within the b*tree structure, which is of course determined by the primary key declared for the IOT, to calculate a urowid. At the moment it is calculated, the UROWID is one of the best guesses in town: it's perfectly accurate, and referencing the IOT via the secondary index would be no slower than referencing a regular table via an index.

Trouble is, IOTs are b*tree structures -which, as we all know, have a propensity to block split. And at a block split, the IOT rows start moving around into different leaf nodes. At which point, the secondary index is left pointing at where it *used* to be -ie, at the wrong place. Access to such a moved row in the IOT causes us to read the block where the secondary index tells us to look, discover that the row we want isn't there any more, and thus return to the IOT for a range scan, based on a 'guess' as to where the row has moved. It's actually not much of a guess: we end up identifying the primary key of the row involved, and doing a primary key lookup on the IOT. All of which gets bloody expensive.

Of course, a rebuild of the secondary index cures the problem at a stroke: except that index rebuilds are not exactly cheap, either.

9i, thankfully, has the ability to ask the secondary index to 'refresh' itself, without a rebuild: "alter index secondary_one update block references". This is a medium-cost way of keeping secondaries in reasonable shape. Cheaper than a complete rebuild, for sure. But not free. And it still means that secondary indexes on an IOT are a potential problem -well, high maintenance beasties, anyway.

Sounds like your table, were it an IOT, would be block splitting like crazy. And it also sounds like you have a requirement for many secondary indexes. In those circumstances, I wouldn't be recommending IOT treatment in principle, allowing for the fact that the costs might well be bearable in reality.

Regards
HJR
>
> ben
>
>
>
>
>
>
> >
> >
> >
>
> Ben Brugman
Received on Mon Sep 30 2002 - 15:10:37 CDT