xquery performance with multiple elements

556882

I have a problem with xquery that uses more elements, like this:
query "for $x in collection('praled.dbxml')/entry where (some $p in $x/form/orth|$x/form/variants/var/orth satisfies (starts-with($p,'test'))) return $x"

even though index is set for orth element, this query iterates all the documents.

Version used:
Oracle: Berkeley DB XML 2.4.16: (October 21, 2008)
Berkeley DB 4.6.21: (September 27, 2007)

Index: node-element-equality-string node-element-substring-string node-element-presence-none for node {}:orth

query plan

<XQuery>
<Return>
<ForTuple uri="" name="x">
<ContextTuple/>
<QueryPlanToAST>
<NodePredicateFilterQP uri="" name="x">
<StepQP axis="child" name="entry" nodeType="element">
<SequentialScanQP container="praled.dbxml" nodeType="document"/>
</StepQP>
<ValueFilterQP comparison="prefix">
<StepQP axis="child" name="orth" nodeType="element">
<UnionQP>
<StepQP axis="child" name="var" nodeType="element">
<StepQP axis="child" name="variants" nodeType="element">
<StepQP axis="child" name="form" nodeType="element">
<VariableQP name="x"/>
</StepQP>
</StepQP>
</StepQP>
<StepQP axis="child" name="form" nodeType="element">
<VariableQP name="x"/>
</StepQP>
</UnionQP>
</StepQP>
<Sequence>
<AnyAtomicTypeConstructor value="test" typeuri="http://www.w3.org/2001/XMLSchema" typename="string"/>
</Sequence>
</ValueFilterQP>
</NodePredicateFilterQP>
</QueryPlanToAST>
</ForTuple>
<QueryPlanToAST>
<VariableQP name="x"/>
</QueryPlanToAST>
</Return>
</XQuery>

if I compare only one of the elements, the query is quick
time query "for $x in collection('praled.dbxml')/entry where (some $p in $x/form/orth satisfies (starts-with($p,'test'))) return $x"
Time in seconds for command 'query': 0.021102
query plan
<XQuery>
<Return>
<ForTuple uri="" name="x">
<ContextTuple/>
<QueryPlanToAST>
<NodePredicateFilterQP uri="" name="#tmp3">
<StepQP axis="child" name="entry" nodeType="element">
<DocumentValueQP container="praled.dbxml" index="node-element-equality-string" operation="prefix" child="orth" value="test"/>
</StepQP>
<ValueFilterQP comparison="prefix">
<StepQP axis="child" name="orth" nodeType="element">
<StepQP axis="child" name="form" nodeType="element">
<VariableQP name="#tmp3"/>
</StepQP>
</StepQP>
<Sequence>
<AnyAtomicTypeConstructor value="test" typeuri="http://www.w3.org/2001/XMLSchema" typename="string"/>
</Sequence>
</ValueFilterQP>
</NodePredicateFilterQP>
</QueryPlanToAST>
</ForTuple>
<QueryPlanToAST>
<VariableQP name="x"/>
</QueryPlanToAST>
</Return>
</XQuery>


time query "for $x in collection('praled.dbxml')/entry where (some $p in $x/form/variants/var/orth satisfies (starts-with($p,'test'))) return $x"
Time in seconds for command 'query': 0.03974

<XQuery>
<Return>
<ForTuple uri="" name="x">
<ContextTuple/>
<QueryPlanToAST>
<NodePredicateFilterQP uri="" name="#tmp12">
<StepQP axis="child" name="entry" nodeType="element">
<DocumentValueQP container="praled.dbxml" index="node-element-equality-string" operation="prefix" child="orth" value="test"/>
</StepQP>
<ValueFilterQP comparison="prefix">
<StepQP axis="child" name="orth" nodeType="element">
<StepQP axis="child" name="var" nodeType="element">
<StepQP axis="child" name="variants" nodeType="element">
<StepQP axis="child" name="form" nodeType="element">
<VariableQP name="#tmp12"/>
</StepQP>
</StepQP>
</StepQP>
</StepQP>
<Sequence>
<AnyAtomicTypeConstructor value="test" typeuri="http://www.w3.org/2001/XMLSchema" typename="string"/>
</Sequence>
</ValueFilterQP>
</NodePredicateFilterQP>
</QueryPlanToAST>
</ForTuple>
<QueryPlanToAST>
<VariableQP name="x"/>
</QueryPlanToAST>
</Return>
</XQuery>


Do I miss some important setting or where should I look for the error?

556882

I can provide some more information or even the dbxml file if that would be helpful

Laurenfoutz-Oracle

Would rewriting your query as:

for $x in collection('praled.dbxml')/entry where (some $p in $x//orth satisfies (starts-with($p,'test'))) return $x

Produce the correct results for you?

An interesting fact about Berkeley DB XML, if a node is indexed then accessing it using //node is faster than using /ancestor/parent/node, since the first can be answered with just an index lookup, while the second requires the index lookup and checking the results against the the nodes ancestors to make sure they match.

Lauren Foutz

556882

Thank you, that helps. But in dbxml 4.5, even the full query is working perfectly. Why the regression?