1 Reply Latest reply: Jul 26, 2011 1:25 AM by 655560 RSS

    Index not used for order by/subsequence

    761557
      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