Re: index unused

"Ana C. Dent" <anacedent_at_hotmail.com> wrote in message news:LTgMb.68525$BQ5.64799_at_fed1read03...
> Kai Zimmer wrote:
> > Hi Ana,
> >
> > thanks for your fast answer. But i still have questions...
> >
> > "Ana C. Dent" <anacedent_at_hotmail.com> wrote in message
news:<6mdMb.68495$BQ5.13185_at_fed1read03>...
> >
> >
>
>>p.xmldoc.extract('/TEI.2/teiHeader/fileDesc/titleStmt/title/text()').getSt
ringVal()
> >> like 'Lexiko%';
> >>
> >>requires a FTS
> >
> >
> > but why exactly?
> > e.g. the same query without the "where-clause" still uses no index but
> > instead the FTS:
> >
> > select

p.xmldoc.extract('/TEI.2/teiHeader/fileDesc/titleStmt/title/text()').getStri ngVal()
> > from xmldemotable p;
> >
> > so, how does indexing xml-files work then?
>
>
> An index is NOT a magic "go faster bullet".
>
> With no WHERE clause you are asking Oracle
> to return every row; which means a FTS is
> the ONLY way to satisfy your query. The use
> of an index would make the query run SLOWER,
> because it would 1st have to read the index
> and then go read tha data for EVERY row! It
> doubles the number of I/O operations in this
> case.
>

Hi Ana,

A little point I would make is in relation to your "doubles the number of I/O operations" comment. If we read an entire table via an index range scan, the I/O operations is likely to be a lot lot worse than just double.

Assuming nice round figures, if we have an average of 10 rows per block and a multiblock read capability of 10 blocks on average, then the use of an index works out to be 100+ times more I/O operations.

That's because we have to visit each and every table block via a logical I/O on average 10 times in order to read each of these rows. The likelihood of these I/Os being physical I/Os is very much dependent on the size of the table and importantly the cardinality of the index. A FTS only has to read each block the once.

However, as the FTS can take advantage of a multiblock reads, assuming we can read on average 10 blocks per logical I/O, then the FTS become 100 times better off. Whether or not Oracle can actually read 10 blocks per call is of course dependent on a number of factors, not least how the table is currently cached, but you get my point.

Then of course there's the cost of navigating down the index structure and reading all the index leaf nodes. Hence the 100+ times worse off.

Add to that the fact that the FTS can also be performed in parallel and the poor index option doesn't look so appealing.

Cheers

Richard Received on Mon Jan 12 2004 - 06:17:35 CST