Skip to Main Content

Berkeley DB Family

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

dateTime index query returning empty set - Berkeley DB XML 6.1.4

User_F8QRJApr 13 2022

I have schema conforming node documents with the following structure:

<ty:Detections xmlns:ty="http://tethys.sdsu.edu/schema/1.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <Id>SOCAL38M_fin_demo</Id> 
 </lots_of_other_stuff_removed>
 <Effort>
  <Start>2010-04-10T00:00:00Z</Start>
 </Effort>
</ty:Detections>

stored in Berkeley DB XML 6.1.4 running on Windows 10. I have set up indices for elements that are queried frequently and have a dateTime edge index on the Start element:

Index: edge-element-equality-dateTime for node {http://tethys.sdsu.edu/schema/1.0}:Start

Is there a specific way that XQueries should be written to take advantage of the index? The following query looks like it is using the index, but returns an empty result set.

declare default element namespace "http://tethys.sdsu.edu/schema/1.0";
for $det in collection("Detections")/Detections[Effort/Start >= xs:dateTime("2000-01-01T00:00:00Z")]
     return
        $det/Effort/Start

with query plan

<XQuery>
  <Return>
    <ForTuple name="{}det" position="">
      <ContextTuple/>
      <QueryPlanToAST>
        <LevelFilterQP>
          <StepQP axis="parent-of-child" uri="http://tethys.sdsu.edu/schema/1.0" localname="Detections" nodeType="element">
            <StepQP axis="parent-of-child" uri="http://tethys.sdsu.edu/schema/1.0" localname="Effort" nodeType="element">
              <ValueQP container="Detections" index="edge-element-equality-dateTime" operation="gte" parent="Effort:http://tethys.sdsu.edu/schema/1.0" child="Start:http://tethys.sdsu.edu/schema/1.0" value="2000-01-01T00:00:00Z"/>
            </StepQP>
          </StepQP>
        </LevelFilterQP>
      </QueryPlanToAST>
    </ForTuple>
    <QueryPlanToAST>
      <NodePredicateFilterQP name="{}#tmp21">
        <PresenceQP container="Detections" index="edge-element-equality-dateTime" operation="prefix" parent="Effort:http://tethys.sdsu.edu/schema/1.0" child="Start:http://tethys.sdsu.edu/schema/1.0"/>
        <ChildJoinQP>
          <VariableQP name="{}det"/>
          <StepQP axis="parent-of-child" uri="http://tethys.sdsu.edu/schema/1.0" localname="Effort" nodeType="element">
            <VariableQP name="{}#tmp21"/>
          </StepQP>
        </ChildJoinQP>
      </NodePredicateFilterQP>
    </QueryPlanToAST>
  </Return>
</XQuery>

I assume that this is a pilot error, are there any suggestions on how to resolve this? Leaving off the xs:dateTime results in a similar query plan.
Thank you - Marie

Comments
Post Details
Added on Apr 13 2022
0 comments
196 views