Re: What is a good blocksize to use.

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:an3l6q$iob$1$8300dec7_at_news.demon.co.uk...
>
> I think the "in the right circumstances" is the
> really big point - there are many features of
> Oracle which vary from good to poor to terrible
> depending on the whether you've managed to
> use them in the right place at the right time.
>
> Given the details that Ben Brugman has supplied
> so far for this particular case I __suspect__ that an index
> structure (any index structure, not just an IOT) on the
> client_id is going to degenerate quite badly because
> of the data split across clients. It is likely (but something
> to check) that a significant number of leaf blocks will take
> a 50/50 split and the lower block will remain half empty.
> But it's the nature of the data - combined with the design
> of the feature - that introduces the side-effect.

Maintenance on B-tree's is relatively low. Only a few percent of all diskaccesses are needed for maintenance. On growing datastructures, where the data is inserted 'fairly' random, on average each leave and each node (not the root) will be filled for 75 %. On a tree which has grown to 5000 blocks there have been 4999 splits. This is not to bad. Even for a high changing index the maintenance is only a few percent.

For B*tree's used by Oracle similar number are valid. Only when a lot of data get's removed/deleted, the B*tree's behavior becomes different because it does follow the rules of b-tree when a node (or leave) becomes less than half full.

Depending on the row size, one can estimate the number of splits and reorganisations. Say 40 rows fit in a block then a split will occure about every 30 inserts.

Because of the nature of tree's, a tree can not be stored consiqutive. Altough optimasation is still posible. (I do not know how Oracle stores the structure.) A tree can not predict where the next block comes, so it can ask for large segments, it can not keep the blocks within the tree in the 'correct'/'disk'/'same' order.

ben brugman

>
>
>
> --
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Next Seminar dates:
> (see http://www.jlcomp.demon.co.uk/seminar.html )
>
> ____USA__________November 7/9 (MI), 19/21 (TX)
> ____England______November 12/14
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
>
>
>
>
>
>
> Howard J. Rogers wrote in message
> <9j4l9.41244$g9.118420_at_newsfeeds.bigpond.com>...
> >Dunno. In the right circumstances, and especially with 9i, they can be
> >excellent.
> >
> >Their maintenance costs are high (being a b*tree structure). But if you
> >primarily access via primary key, I think they're a good solution.
> >
> >Regards
> >HJR
> >
>
>
>
Received on Sun Sep 29 2002 - 10:12:20 CDT