Re: index unused

From: Ana C. Dent <anacedent_at_hotmail.com>
Date: Sun, 11 Jan 2004 10:48:10 -0800
Message-ID: <LTgMb.68525$BQ5.64799@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()').getStringVal()
>>  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()').getStringVal()
> 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.

An index can get the results faster than a FTS if & when only a specific subset of rows is being returned AND the WHERE clause can actually use whole column(s) in an index.

P.S.
the use of indexes is NOT restricted to XML. Go read the Oracle Concepts Manual at
http://tahiti.oracle.com Received on Sun Jan 11 2004 - 12:48:10 CST