This discussion is archived
7 Replies Latest reply: Nov 4, 2012 7:52 AM by odie_63 RSS

XML table left outer join COLLECTION ITERATOR PICKLER FETCH

user485536 Newbie
Currently Being Moderated
Hi,
Oracle version 11.2.0.2
Example XML
<Approval id="a1">
<User name="u1"/>
<User name="u2"/>
</Approval>

Schema registered as O-R with elements Approval and User using SQLInline="false"

I am allways getting COLLECTION ITERATOR PICKLER FETCH
when trying to select all users using LEFT OUTER JOIN
XMLTABLE ( xmlnamespaces (DEFAULT 'http://local/wf/Approval'),
'/Approval'
PASSING workflow.VWorkflowMap
COLUMNS
VXMLApprovalid VARCHAR (100) PATH '@id'
, VXMLApprovalusers XMLTYPE PATH 'User'

) as VXMLApproval ON 1=1
LEFT OUTER JOIN XMLTABLE ( xmlnamespaces (DEFAULT 'http://local/wf/Approval'),
'/User'
PASSING VXMLApproval.VXMLApprovalusers
COLUMNS
VXMLApprovalusersName VARCHAR (100) PATH '@name'

) as VXMLApprovalUsers ON 1=1

Is there a way to rewrite this and to avoid COLLECTION ITERATOR PICKLER FETCH in query plan?
  • 1. Re: XML table left outer join COLLECTION ITERATOR PICKLER FETCH
    odie_63 Guru
    Currently Being Moderated
    Could you post the schema and the command you used to register it?

    Also give the full explain plan, thanks.

    Any particular reason to use out-of-line storage?
  • 2. Re: XML table left outer join COLLECTION ITERATOR PICKLER FETCH
    user485536 Newbie
    Currently Being Moderated
    Oracle server 10.2.0.5.

    This purchaseorder example schema from documentation and query bellow uses COLLECTION ITERATOR PICKLER FETCH when using left outer join

    SELECT poxml.Reference, poxmlli.ItemNumber

    from purchaseorder,
    XMLTABLE (
    '/'
    PASSING OBJECT_VALUE
    COLUMNS
    Reference VARCHAR (1000) PATH '/PurchaseOrder/Reference'
    ,LineItems XMLTYPE PATH 'LineItems/LineItem'
    ) poxml
    left outer join
    XMLTABLE (
    '/LineItem'
    PASSING poxml.LineItems
    COLUMNS
    ItemNumber VARCHAR (1000) PATH '@ItemNumber'

    ) poxmlli on 1=1

    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2513622627

    ---------------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ---------------------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 43G| 666T| 94M (1)|366:17:45 |
    | 1 | MERGE JOIN OUTER | | 43G| 666T| 94M (1)|366:17:45 |
    | 2 | NESTED LOOPS | | 2683K| 40G| 5438 (1)| 00:01:17 |
    | 3 | TABLE ACCESS FULL | PURCHASEORDER | 164 | 2592K| 2 (0)| 00:00:01 |
    | 4 | COLLECTION ITERATOR PICKLER FETCH | XQSEQUENCEFROMXMLTYPE | | | | |
    | 5 | BUFFER SORT | | 16360 | 8020K| 94M (1)|366:17:44 |
    | 6 | VIEW | | 16360 | 8020K| 35 (0)| 00:00:01 |
    | 7 | COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE | | | | |
    ---------------------------------------------------------------------------------------------------------------


    Statistics
    ----------------------------------------------------------
    16 recursive calls
    0 db block gets
    48 consistent gets
    0 physical reads
    0 redo size
    567 bytes sent via SQL*Net to client
    396 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    1 sorts (memory)
    0 sorts (disk)
    3 rows processed


    Without left join plan doesn't contains COLLECTION ITERATOR PICKLER FETCH

    SELECT poxml.Reference, poxmlli.ItemNumber

    from purchaseorder,
    XMLTABLE (
    '/'
    PASSING OBJECT_VALUE
    COLUMNS
    Reference VARCHAR (1000) PATH '/PurchaseOrder/Reference'
    ,LineItems XMLTYPE PATH 'LineItems/LineItem'
    ) poxml
    join
    XMLTABLE (
    '/LineItem'
    PASSING poxml.LineItems
    COLUMNS
    ItemNumber VARCHAR (1000) PATH '@ItemNumber'

    ) poxmlli on 1=1

    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1778379974

    ---------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ---------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 3828 | 9 (0)| 00:00:01 |
    | 1 | NESTED LOOPS | | 1 | 3828 | 9 (0)| 00:00:01 |
    |* 2 | INDEX FULL SCAN | SYS_IOT_TOP_279935 | 1 | 33 | 1 (0)| 00:00:01 |
    |* 3 | TABLE ACCESS BY INDEX ROWID| PURCHASEORDER | 8 | 30360 | 1 (0)| 00:00:01 |
    |* 4 | INDEX UNIQUE SCAN | SYS_C00429184 | 1 | | 1 (0)| 00:00:01 |
    ---------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

    2 - filter("SYS_NC_TYPEID$" IS NOT NULL)
    3 - filter("PURCHASEORDER"."SYS_NC00007$" IS NOT NULL)
    4 - access("SYS_ALIAS_0"."NESTED_TABLE_ID"="PURCHASEORDER"."SYS_NC0003400035$")


    Statistics
    ----------------------------------------------------------
    11 recursive calls
    0 db block gets
    381 consistent gets
    0 physical reads
    0 redo size
    567 bytes sent via SQL*Net to client
    396 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    3 rows processed

    Edited by: 968973 on Nov 1, 2012 7:06 PM
  • 3. Re: XML table left outer join COLLECTION ITERATOR PICKLER FETCH
    user485536 Newbie
    Currently Being Moderated
    This is example XML

    <PurchaseOrder
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:noNamespaceSchemaLocation="http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd">
    <Reference>SBELL-2002100912333601PDT</Reference>
    <Actions>
    <Action>
    <User>SVOLLMAN</User>
    </Action>
    </Actions>
    <Reject/>
    <Requestor>Sarah J. Bell</Requestor>
    <User>SBELL</User>
    <CostCenter>S30</CostCenter>
    <ShippingInstructions>
    <name>Sarah J. Bell</name>
    <address>400 Oracle Parkway
    Redwood Shores
    CA
    94065
    USA</address>
    <telephone>650 506 7400</telephone>
    </ShippingInstructions>
    <SpecialInstructions>Air Mail</SpecialInstructions>

    </PurchaseOrder>

    where part
    <LineItems>
    <LineItem ItemNumber="1">
    <Description>A Night to Remember</Description>
    <Part Id="715515009058" UnitPrice="39.95" Quantity="2"/>
    </LineItem>
    <LineItem ItemNumber="2">
    <Description>The Unbearable Lightness Of Being</Description>

    </LineItem>
    <LineItem ItemNumber="3">
    <Description>Sisters</Description>
    <Part Id="715515011020" UnitPrice="29.95" Quantity="4"/>
    </LineItem>
    </LineItems>
    was removed, but I still would like to get Reference from order.
  • 4. Re: XML table left outer join COLLECTION ITERATOR PICKLER FETCH
    odie_63 Guru
    Currently Being Moderated
    Can we focus on a single use case at a time please?

    So which version are using? You first mentioned 11.2.0.2, and now it's 10.2.0.5 ?

    The last query you posted (with the OUTER JOIN) is not correct, so I really doubt it can ever produce the plan you gave.

    I don't reproduce what you're describing anyway :
    (purchaseorder.xsd registered from : http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/apphxdb.htm#BABDAGBF)
    SQL> set autotrace on explain
    SQL> set lines 120
    SQL> SELECT poxml.Reference, poxmlli.ItemNumber
      2  FROM purchaseorder,
      3  XMLTABLE('/PurchaseOrder'
      4    PASSING OBJECT_VALUE
      5    COLUMNS
      6      Reference VARCHAR2(1000) PATH 'Reference'
      7    , LineItems XMLTYPE        PATH 'LineItems/LineItem'
      8  ) poxml
      9  left outer join
     10    XMLTABLE ('/LineItem'
     11      PASSING poxml.LineItems
     12      COLUMNS
     13        ItemNumber VARCHAR (1000) PATH '@ItemNumber'
     14    ) poxmlli on 1=1
     15  ;
    
    REFERENCE                                ITEMNUMBER
    ---------------------------------------- ----------------------------------------
    SBELL-2002100912333601PDT
    
    
    Plan d'exécution
    ----------------------------------------------------------
    Plan hash value: 2478135211
    
    --------------------------------------------------------------------------------------------------------
    | Id  | Operation             | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |                                |     1 |   574 |     7   (0)| 00:00:01 |
    |   1 |  NESTED LOOPS OUTER   |                                |     1 |   574 |     7   (0)| 00:00:01 |
    |   2 |   MERGE JOIN CARTESIAN|                                |     1 |    72 |     5   (0)| 00:00:01 |
    |   3 |    TABLE ACCESS FULL  | PURCHASEORDER                  |     1 |    72 |     3   (0)| 00:00:01 |
    |   4 |    BUFFER SORT        |                                |     1 |       |     2   (0)| 00:00:01 |
    |   5 |     FAST DUAL         |                                |     1 |       |     2   (0)| 00:00:01 |
    |   6 |   VIEW                |                                |     1 |   502 |     2   (0)| 00:00:01 |
    |*  7 |    TABLE ACCESS FULL  | SYS_NTUv1m6OGHTPeWzBmOnbZTZA== |     1 |    23 |     2   (0)| 00:00:01 |
    --------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       7 - filter("SYS_ALIAS_3"."NESTED_TABLE_ID"="PURCHASEORDER"."SYS_NC0003400035$")
    
    Note
    -----
       - dynamic sampling used for this statement (level=2)
    Show us exactly what you're doing by copy/pasting from SQL*Plus.

    Edited by: odie_63 on 1 nov. 2012 19:38
  • 5. Re: XML table left outer join COLLECTION ITERATOR PICKLER FETCH
    user485536 Newbie
    Currently Being Moderated
    Sorry for violating forum rules that I should create new thread for different oracle version and confusion. I just tested PurchaseOrder on 11.2.0.2 and it produces what you described. And it produces what I described on 10.2.0.5 without error that is raised on 11.2.0.2.

    I have to prepare my schema any way. Will take some time. Thanks.
  • 6. Re: XML table left outer join COLLECTION ITERATOR PICKLER FETCH
    user485536 Newbie
    Currently Being Moderated
    Hi,
    My schema:

    DECLARE
    doc CLOB:=
    to_clob ( '<?xml version="1.0" encoding="UTF-8"?>
    <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns="http://local/Approval"
    targetNamespace="http://local/Approval" elementFormDefault="qualified">

    <xs:complexType name="User_T">
    <xs:attribute name="name" type="xs:string" use="required"/>
    <xs:attribute name="fullName" type="xs:string"/>
    </xs:complexType>
    <xs:complexType name="ApprovalNode">
    <xs:choice maxOccurs="unbounded">
    <xs:element ref="User" maxOccurs="unbounded"/>
    <xs:element ref="Condition" minOccurs="0"/>
    </xs:choice>

    <xs:attribute name="id" type="xs:string" use="required"/>
    <xs:attribute name="label" type="xs:string"/>
    </xs:complexType>

    <xs:complexType name="Condition_T">
    <xs:attribute name="name" type="xs:string" use="required"/>
    </xs:complexType>

    <xs:element name="Condition" type="Condition_T"/>

    <xs:complexType name="ThreadNode">
    <xs:choice minOccurs="1" maxOccurs="unbounded">
    <xs:element ref="Approval"/>
    </xs:choice>

    <xs:attribute name="id" type="xs:string" use="required"/>
    <xs:attribute name="label" type="xs:string"/>
    </xs:complexType>

    <xs:complexType name="ForkNode">

    <xs:choice minOccurs="1" maxOccurs="unbounded">
    <xs:element ref="Thread" minOccurs="1" maxOccurs="unbounded"/>
    <xs:element ref="Approval" maxOccurs="unbounded"/>
    </xs:choice>
    <xs:attribute name="id" type="xs:string" use="required"/>
    <xs:attribute name="label" type="xs:string"/>

    </xs:complexType>

    <xs:element name="Approval" type="ApprovalNode"/>

    <xs:element name="Fork" type="ForkNode"/>
    <xs:element name="Thread" type="ThreadNode"/>

    <xs:element name="User" type="User_T"/>

    <xs:element name="ApprovalMap">
    <xs:complexType>
    <xs:choice minOccurs="1" maxOccurs="1">
    <xs:element ref="Fork" minOccurs="1" maxOccurs="1"/>
    <xs:element ref="Approval" maxOccurs="unbounded"/>
    </xs:choice>
    <xs:attribute name="hasDynamicWorkflowMap" type="xs:boolean" use="optional"/>
    <xs:attribute name="userResolver" type="xs:string" use="optional"/>
    <xs:attribute name="userResolverScript" type="xs:string" use="optional"/>
    </xs:complexType>
    </xs:element>
    </xs:schema>');
    BEGIN
    DBMS_XMLSCHEMA.registerSchema(
    SCHEMAURL => 'http://local/Approval',SCHEMADOC => doc, LOCAL => TRUE,
    GENTYPES => TRUE,
    GENBEAN =>FALSE,
    GENTABLES => TRUE);
    END;


    table

    CREATE TABLE tapproval (
    id NUMBER(10) PRIMARY KEY NOT NULL ,
    approval XMLType )
    XMLTYPE approval STORE AS OBJECT RELATIONAL XMLSCHEMA "http://local/Approval" ELEMENT "ApprovalMap" ;


    query

    SELECT /*+ CURSOR_SHARING_EXACT */
    condi.condiName RSN
    FROM
    tapproval tapproval
    , XMLTABLE ( xmlnamespaces (DEFAULT 'http://local/Approval'),
    '/ApprovalMap/Fork/Thread/Approval'
    PASSING tapproval.approval
    COLUMNS
    approvalid VARCHAR (100) PATH '@id'
    , approvalConditions XMLTYPE PATH 'Condition'
    ) as approval
    LEFT OUTER JOIN XMLTABLE ( xmlnamespaces (DEFAULT 'http://local/Approval'),
    'Condition'
    PASSING approval.approvalConditions
    COLUMNS
    condiName VARCHAR (1000) PATH '@name'
    ) as condi ON 1=1

    plan

    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production


    no rows selected


    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3138393791

    ----------------------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ----------------------------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 5470M| 21T| 19M (1)| 64:56:53 |
    | 1 | NESTED LOOPS OUTER | | 5470M| 21T| 19M (1)| 64:56:53 |
    | 2 | NESTED LOOPS | | 669K| 2418M| 2234 (1)| 00:00:27 |
    |* 3 | HASH JOIN | | 82 | 303K| 5 (20)| 00:00:01 |
    | 4 | TABLE ACCESS FULL | SYS_NTmypKThHYQISYtVyNj7jYNw== | 82 | 1640 | 2 (0)| 00:00:01 |
    | 5 | TABLE ACCESS FULL | TAPPROVAL | 82 | 301K| 2 (0)| 00:00:01 |
    | 6 | COLLECTION ITERATOR XMLSEQ FETCH | XQSEQUENCEFROMXMLTYPE | 8168 | 16336 | 27 (0)| 00:00:01 |
    | 7 | SORT AGGREGATE | | 1 | 6050 | | |
    | 8 | TABLE ACCESS BY INDEX ROWID | SYS_NTY46pJe8FS4GKrWttqth16A== | 1 | 6050 | 1 (0)| 00:00:01 |
    |* 9 | INDEX RANGE SCAN | SYS_C00112233 | 1 | | 2 (0)| 00:00:01 |
    | 10 | VIEW | | 8168 | 4004K| 29 (0)| 00:00:01 |
    | 11 | COLLECTION ITERATOR PICKLER FETCH| | 8168 | 16336 | 29 (0)| 00:00:01 |
    ----------------------------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

    3 - access("NESTED_TABLE_ID"="TAPPROVAL"."SYS_NC0001600017$")
    9 - access("NESTED_TABLE_ID"=:B1)


    Statistics
    ----------------------------------------------------------
    22 recursive calls
    0 db block gets
    1288 consistent gets
    0 physical reads
    0 redo size
    249 bytes sent via SQL*Net to client
    352 bytes received via SQL*Net from client
    1 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    0 rows processed


    query without LEFT JOIN
    SELECT /*+ CURSOR_SHARING_EXACT */
    condi.condiName RSN
    FROM
    tapproval tapproval
    , XMLTABLE ( xmlnamespaces (DEFAULT 'http://local/Approval'),
    '/ApprovalMap/Fork/Thread/Approval'
    PASSING tapproval.approval
    COLUMNS
    approvalid VARCHAR (100) PATH '@id'
    , approvalConditions XMLTYPE PATH 'Condition'
    ) as approval
    JOIN XMLTABLE ( xmlnamespaces (DEFAULT 'http://local/Approval'),
    'Condition'
    PASSING approval.approvalConditions
    COLUMNS
    condiName VARCHAR (1000) PATH '@name'
    ) as condi ON 1=1 ;

    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3795314584

    -------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    -------------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 82 | 165K| 7 (15)| 00:00:01 |
    |* 1 | HASH JOIN | | 82 | 165K| 7 (15)| 00:00:01 |
    |* 2 | HASH JOIN | | 82 | 4100 | 5 (20)| 00:00:01 |
    | 3 | NESTED LOOPS | | 82 | 2460 | 2 (0)| 00:00:01 |
    | 4 | TABLE ACCESS FULL| SYS_NTmypKThHYQISYtVyNj7jYNw== | 82 | 1640 | 2 (0)| 00:00:01 |
    |* 5 | INDEX UNIQUE SCAN| SYS_C00112252 | 1 | 10 | 0 (0)| 00:00:01 |
    | 6 | TABLE ACCESS FULL | SYS_NTY46pJe8FS4GKrWttqth16A== | 82 | 1640 | 2 (0)| 00:00:01 |
    | 7 | TABLE ACCESS FULL | SYS_NTQ5uMb24JTj6s7EBLxrAyAQ== | 82 | 161K| 2 (0)| 00:00:01 |
    -------------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

    1 - access("NESTED_TABLE_ID"="SYS_ALIAS_1"."SYS_NC0001000011$")
    2 - access("NESTED_TABLE_ID"="SYS_ALIAS_0"."SYS_NC0000800009$")
    5 - access("NESTED_TABLE_ID"="TAPPROVAL"."SYS_NC0001600017$")


    Statistics
    ----------------------------------------------------------
    9 recursive calls
    0 db block gets
    968 consistent gets
    0 physical reads
    0 redo size
    249 bytes sent via SQL*Net to client
    352 bytes received via SQL*Net from client
    1 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    0 rows processed


    query 2

    SELECT /*+ CURSOR_SHARING_EXACT */
    condi.condiName RSN
    FROM
    tapproval tapproval
    , XMLTABLE ( xmlnamespaces (DEFAULT 'http://local/Approval'),
    '/ApprovalMap/Fork/Approval'
    PASSING tapproval.approval
    COLUMNS
    approvalid VARCHAR (100) PATH '@id'
    , approvalConditions XMLTYPE PATH 'Condition'
    ) as approval
    LEFT OUTER JOIN XMLTABLE ( xmlnamespaces (DEFAULT 'http://local/Approval'),
    'Condition'
    PASSING approval.approvalConditions
    COLUMNS
    condiName VARCHAR (1000) PATH '@name'
    ) as condi ON 1=1 ;

    no rows selected


    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1726223554

    ------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ------------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 82 | 43624 | 166 (0)| 00:00:02 |
    | 1 | NESTED LOOPS OUTER | | 82 | 43624 | 166 (0)| 00:00:02 |
    | 2 | NESTED LOOPS | | 82 | 2460 | 2 (0)| 00:00:01 |
    | 3 | TABLE ACCESS FULL| SYS_NTN4i0H/IeSlmu+h6ssMIWsQ== | 82 | 1640 | 2 (0)| 00:00:01 |
    |* 4 | INDEX UNIQUE SCAN| SYS_C00112251 | 1 | 10 | 0 (0)| 00:00:01 |
    | 5 | VIEW | | 1 | 502 | 2 (0)| 00:00:01 |
    |* 6 | TABLE ACCESS FULL| SYS_NTCh2vTus4RSi9zbX1hL84Yg== | 1 | 2012 | 2 (0)| 00:00:01 |
    ------------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

    4 - access("NESTED_TABLE_ID"="TAPPROVAL"."SYS_NC0001800019$")
    6 - filter("SYS_ALIAS_1"."NESTED_TABLE_ID"="SYS_ALIAS_0"."SYS_NC0001000011$")


    Statistics
    ----------------------------------------------------------
    9 recursive calls
    0 db block gets
    959 consistent gets
    0 physical reads
    0 redo size
    249 bytes sent via SQL*Net to client
    352 bytes received via SQL*Net from client
    1 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    0 rows processed
  • 7. Re: XML table left outer join COLLECTION ITERATOR PICKLER FETCH
    odie_63 Guru
    Currently Being Moderated
    Thanks for the details.

    I can reproduce on 11.2.0.3 as well, but I don't have any explanation right now, maybe others will have some ideas?

    An obvious workaround would be a UNION ALL query :
    FROM tapproval t
       , XMLTable(
           XMLNamespaces(default 'http://local/Approval')
         , '/ApprovalMap/Fork/Thread/Approval'
           PASSING t.approval
           COLUMNS appid         VARCHAR2(100) PATH '@id'
                 , appConditions XMLTYPE       PATH 'Condition'
         ) app
       , XMLTable(
           XMLNamespaces(default 'http://local/Approval')
         , '/Condition'
           PASSING app.appConditions
           COLUMNS condName     VARCHAR2(1000) PATH  '@name'
         ) cond
    UNION ALL
    SELECT app.appid, null
    FROM tapproval t
       , XMLTable(
           XMLNamespaces(default 'http://local/Approval')
         , '/ApprovalMap/Fork/Thread/Approval[not(Condition)]'
           PASSING t.approval
           COLUMNS appid         VARCHAR2(100) PATH '@id'
         ) app
    ;
    Another option : store <tt>Approval</tt> elements out-of-line and query the target table directly.
    However we don't know which Approval comes from which level (root, fork, thread?) :
    <?xml version="1.0" encoding="UTF-8"?>
    <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns="http://local/Approval"
    targetNamespace="http://local/Approval" elementFormDefault="qualified"
    xmlns:xdb="http://xmlns.oracle.com/xdb">
    
        <xs:complexType name="User_T" xdb:SQLType="USER_T">
            <xs:attribute name="name" type="xs:string" use="required"/>
            <xs:attribute name="fullName" type="xs:string"/>
        </xs:complexType>
        <xs:complexType name="ApprovalNode" xdb:SQLType="APPROVAL_T">
              <xs:choice maxOccurs="unbounded">
                      <xs:element ref="User" maxOccurs="unbounded"/>
                      <xs:element ref="Condition" minOccurs="0"/>
              </xs:choice>
    
              <xs:attribute name="id" type="xs:string" use="required"/>
              <xs:attribute name="label" type="xs:string"/>
        </xs:complexType>
    
        <xs:complexType name="Condition_T" xdb:SQLType="CONDITION_T">
            <xs:attribute name="name" type="xs:string" use="required"/>
        </xs:complexType>
    
        <xs:element name="Condition" type="Condition_T" xdb:defaultTable=""/>
    
        <xs:complexType name="ThreadNode" xdb:SQLType="THREAD_T">
              <xs:choice minOccurs="1" maxOccurs="unbounded">
                      <xs:element ref="Approval" xdb:SQLInline="false" xdb:defaultTable="APPROVAL_TAB"/>
              </xs:choice>
    
              <xs:attribute name="id" type="xs:string" use="required"/>
              <xs:attribute name="label" type="xs:string"/>
        </xs:complexType>
    
        <xs:complexType name="ForkNode" xdb:SQLType="FORK_T">
    
              <xs:choice minOccurs="1" maxOccurs="unbounded">
                      <xs:element ref="Thread" minOccurs="1" maxOccurs="unbounded" xdb:SQLCollType="THREAD_COLL"/>
                      <xs:element ref="Approval" maxOccurs="unbounded"
                       xdb:SQLInline="false" xdb:defaultTable="APPROVAL_TAB"/>
              </xs:choice>
              <xs:attribute name="id" type="xs:string" use="required"/>
              <xs:attribute name="label" type="xs:string"/>
    
        </xs:complexType>
    
        <xs:element name="Approval" type="ApprovalNode" xdb:defaultTable=""/>
    
        <xs:element name="Fork" type="ForkNode" xdb:defaultTable=""/>
        <xs:element name="Thread" type="ThreadNode" xdb:defaultTable=""/>
    
        <xs:element name="User" type="User_T" xdb:defaultTable=""/>
    
        <xs:element name="ApprovalMap" xdb:defaultTable="">
            <xs:complexType xdb:SQLType="APPROVALMAP_T">
                      <xs:choice minOccurs="1" maxOccurs="1">
                                      <xs:element ref="Fork" minOccurs="1" maxOccurs="1"/>
                                      <xs:element ref="Approval" maxOccurs="unbounded" 
                                        xdb:SQLInline="false" xdb:defaultTable="APPROVAL_TAB"/>
                      </xs:choice>
                <xs:attribute name="hasDynamicWorkflowMap" type="xs:boolean" use="optional"/>
                <xs:attribute name="userResolver" type="xs:string" use="optional"/>
                <xs:attribute name="userResolverScript" type="xs:string" use="optional"/>
            </xs:complexType>
        </xs:element>
    </xs:schema>
    CREATE TABLE tapproval (
      id NUMBER(10)  PRIMARY KEY  NOT NULL,
      approval XMLType
    )
    XMLTYPE approval STORE AS OBJECT RELATIONAL 
    XMLSCHEMA "http://local/Approval" ELEMENT "ApprovalMap"
    ;
    insert into tapproval values(1,
    xmltype('<ApprovalMap xmlns="http://local/Approval">
     <Approval id="3" label="approval3"/>
     <Fork>
      <Thread id="1" label="thread1">
       <Approval id="1" label="approval1">
        <Condition name="c1"/>
        <Condition name="c2"/>
        <Condition name="c3"/>
       </Approval>
       <Approval id="2" label="approval2"/>
      </Thread>
     </Fork>
    </ApprovalMap>')
    );
    SQL> SELECT app.appid, cond.condName
      2  FROM approval_tab t
      3     , XMLTable(
      4         XMLNamespaces(default 'http://local/Approval')
      5       , '/Approval'
      6         PASSING t.object_value
      7         COLUMNS appid         VARCHAR2(15) PATH '@id'
      8               , appConditions XMLTYPE       PATH 'Condition'
      9       ) app
     10     LEFT OUTER JOIN XMLTable(
     11         XMLNamespaces(default 'http://local/Approval')
     12       , '/Condition'
     13         PASSING app.appConditions
     14         COLUMNS condName     VARCHAR2(15) PATH  '@name'
     15       ) cond ON (1=1)
     16  ;
    
    APPID           CONDNAME
    --------------- ---------------
    1               c1
    1               c2
    1               c3
    3
    2
    
    
    Plan d'exÚcution
    ----------------------------------------------------------
    Plan hash value: 2818703557
    
    -----------------------------------------------------------------------------------------------------------------
    | Id  | Operation                      | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT               |                                |     3 |    54 |    11   (0)| 00:00:01 |
    |   1 |  NESTED LOOPS OUTER            |                                |     3 |    54 |    11   (0)| 00:00:01 |
    |   2 |   NESTED LOOPS                 |                                |     3 |    27 |     5   (0)| 00:00:01 |
    |   3 |    FAST DUAL                   |                                |     1 |       |     2   (0)| 00:00:01 |
    |   4 |    TABLE ACCESS FULL           | APPROVAL_TAB                   |     3 |    27 |     3   (0)| 00:00:01 |
    |   5 |   VIEW                         |                                |     1 |     9 |     2   (0)| 00:00:01 |
    |*  6 |    FILTER                      |                                |       |       |            |          |
    |   7 |     TABLE ACCESS BY INDEX ROWID| SYS_NT8OVtZNrxQDONh5wyDBdRow== |     1 |    20 |     2   (0)| 00:00:01 |
    |*  8 |      INDEX RANGE SCAN          | SYS_C0011579                   |     1 |       |     1   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       6 - filter("T"."SYS_NC0001300014$" IS NOT NULL)
       8 - access("SYS_ALIAS_3"."NESTED_TABLE_ID"="T"."SYS_NC0001300014$")

Legend

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