Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: What is a good blocksize to use.

Re: What is a good blocksize to use.

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 30 Sep 2002 21:27:04 +0100
Message-ID: <anac26$jb0$1$8300dec7@news.demon.co.uk>

The secondary index entry consists of:

    {foreign key columns} {primary key} {block guess}

But I never ever managed to get Oracle to use the {block guess} to locate a row in an IOT - every test I did showed it traversing the {primary key} (whatever the manuals might say).

Has anyone ever seen a secondary index use a guess ? (at least in 8.1, I haven't tested 9.2 yet).

--
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 ...

>
>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.
Received on Mon Sep 30 2002 - 15:27:04 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US