Re: What is a good blocksize to use.

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.