This discussion is archived
1 Reply Latest reply: Jul 25, 2011 11:25 PM by 655560 RSS

Index not used for order by/subsequence

761557 Newbie
Currently Being Moderated
Hello,

I can't seem to get DBXML to efficiently return an ordered subsequence (so that a web page can do pagination in order). I have 100,000 entries in my test container and the following indexes:
Index: unique-node-metadata-equality-string for node {http://www.sleepycat.com/2002/dbxml}:name
Index: edge-element-equality-dateTime for node {}:when
Index: edge-element-equality-double for node {}:whenNumber
queryPlan "
for $x in (collection('test.dbxml')/root)
    order by $x/whenNumber
    return subsequence($x, 1, 10)
"
Gives me a plan with a sequential scan in it:
<XQuery>
  <Return>
    <OrderByTuple direction="ascending" empty="least" stable="false" collation="http://www.w3.org/2005/xpath-functions/collation/codepoint">
      <ForTuple uri="" name="x">
        <ContextTuple/>
        <QueryPlanToAST>
          <StepQP axis="child" name="root" nodeType="element">
            <SequentialScanQP container="test.dbxml" nodeType="document"/>
          </StepQP>
        </QueryPlanToAST>
      </ForTuple>
      <TreatAs>
        <PromoteUntyped uri="http://www.w3.org/2001/XMLSchema" name="string">
          <Atomize>
            <QueryPlanToAST>
              <StepQP axis="child" name="whenNumber" nodeType="element">
                <VariableQP name="x"/>
              </StepQP>
            </QueryPlanToAST>
          </Atomize>
        </PromoteUntyped>
        <SequenceType type="item()?"/>
      </TreatAs>
    </OrderByTuple>
    <Function name="{http://www.w3.org/2005/xpath-functions}:subsequence">
      <QueryPlanToAST>
        <VariableQP name="x"/>
      </QueryPlanToAST>
      <NumericLiteral value="1.0E0" typeuri="http://www.w3.org/2001/XMLSchema" typename="double"/>
      <NumericLiteral value="1.0E1" typeuri="http://www.w3.org/2001/XMLSchema" typename="double"/>
    </Function>
  </Return>
</XQuery>
The index is used correctly if I do a conditional query as I woudl expect

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points