7 Replies Latest reply: Nov 4, 2012 9:52 AM by odie_63 RSS

    XML table left outer join COLLECTION ITERATOR PICKLER FETCH

    LindseyInIT
      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
          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
            LindseyInIT
            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
              LindseyInIT
              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
                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
                  LindseyInIT
                  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
                    LindseyInIT
                    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
                      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$")