I may be way off base here, so any gurus please correct me with a gentle slap to the
back of the head...
Index and table access is not as simple as index entry..table row..index entry..table
row..etc. I just ran a quick test (which may not be represntative and is using the
primary key which can be understood as the row number in physical order of the data
blocks) and I found (using the sequence of wait events) that there was substantial
access to the index datafiles initially, followed by substantial access to the data
datafiles. Then another single access to index, multiple access to data, single
access to index, multiple access to data. It seems to me that this pattern is read
several index blocks, then access several data blocks, read several index blocks,
access several data blocks. This may be due to the sequential nature of the pk in the
data blocks. It seems that the most efficient algorithm is to read enough index blocks
to set up a list of data blocks to read, then go get them. Since you have the index
block pinned, don't waste any resource in releasing the pin to pin the data
blocks, then repin the index block.
The other issue is that indexes can be accessed using multiblock reads (index fast
full scan) and tables can be indexed using single block reads (table access by rowid).
Garry Gillies wrote:
>
> It's hot here. I wish I was at the beach and I feel like a rant.
>
> "oracle actually accesses indexes and tables serially"
>
> Is it just me or is this blindingly obvious?
> You cannot access the table data until you have completed accessing the
> index data
> because the index data contains the location of the table data.
>
> During an indexed query on a single table the index will be accessed, then
> the table,
> then the index,then the table, then the index,then the table then the
> index,then the table.
> If the index and the table are on the same disk then a lot of time will be
> taken up by
> head seek movement.
> If they are on the different disks then the "index" heads can locate their
> data and stay
> there - and the "data" heads can locate their data and stay there.
> Less head movement, less wasted time.
>
> That is the argument for what it is worth. Real life is of course vastly
> more complex than
> this and we are swimming in very muddy waters, which is why there is so
> much
> argument on the subject (raid salesmen - spit).
>
> Thanks for the vent
>
> Garry Gillies
begin:vcard
n:Fink;Daniel
tel;cell:303.808.3282
tel;work:303.272.3225
x-mozilla-html:TRUE
adr:;;;;;;
version:2.1
email;internet:[EMAIL PROTECTED]
title:DB Services Lead
x-mozilla-cpt:;-4832
fn:Daniel Fink
end:vcard
