Hi Phil,
in nearly all cases, the query optimizer will try to apply the existing index structures. As the XQuery syntax is much more versatile than SQL, this won't always succeed. - However, your query can be easily written. Three alternative writings, that all should access the index, look as follows:
Query: //page[para[text() contains text "indiana"] and para[text() contains text "ninth"]]
Alternative 1: //page[para/text() contains text "indiana"] [para/text() contains text "ninth"]
Alternative 2: //page[para/text() contains text "indiana" and para/text() contains text "ninth"]
Alternative 3: //page[para/text() contains text "indiana" ftand "ninth"]
All three queries will yield different execution plans, and - to be strict - the third plan is not equivalent to the first two, as it expects the two query terms to occur in the very same text node. - Check out all of the alternatives to see which one works out best for you.
Christian
On Mon, Jan 18, 2010 at 7:25 PM, Phil Oliver phil3@olivercomputing.com wrote:
Another question regarding usage of full-text indices. My test database has the full text indexing enabled with no options currently for simplicity (e.g. wildcards, stemming, etc. disabled).
A simple query such as:
let $p:=//para[text() contains text "indiana"] return $p
executes rapidly using the FT index selection. However, if I try a more complex query (designed to find two words on the same page):
let $p:=//page[para[text() contains text "indiana"] and para[text() contains text "ninth"]] return $p
the query plan does *not* use the FT index and takes a long time to execute.
While this is a more complex query, it doesn't seem *really* complex, and would be a common use case. Can you give me some idea as to why the FT indices are not used in this instance, and if possible a suggestion on a rewritten query that would use them? Is there a way to give hints to the optimizer to always use them?