1 2 Previous Next 15 Replies Latest reply: Nov 18, 2012 1:04 PM by Marco Gralike RSS

    Where clause with XMLExists and join on another table

    user8941550
      Hi,

      We have table like:

      drop table xml_tbl;

      create table xml_tbl (
      xml_msg_id integer,
      xml_msg_text xmltype
      );

      insert into xml_tbl values
      (1, '<main><id>1</id></main>') ;

      insert into xml_tbl values --(xml_msg_id,xml_msg_text)
      (1, '<main><id>2</id></main>') ;


      Another table like:

      create Table Table1
      ( id1 int);

      Insert into Table1 values(2);
      Insert into Table1 values(3);

      We need to have a view on top of the table xml_tbl where /main/id should have only those values which are in id1 column of table Table1.

      Something like

      CREATE OR REPLACE VIEW V_xml_tbl
      (
      xml_msg_text
      )
      AS
      SELECT T.xml_msg_text
      FROM xml_tbl T
      WHERE XMLEXISTS (
      'declare namespace Namesp1 ="Abc:Set";
      let $Results as xs:boolean := fn:exists($p/main/id in (Select id1 from Table1)) --Now here I know I can't do Select id1 from

      Table1*
      return if ($Results ) then true() else ()'
      PASSING T.xml_msg_text AS "p");


      Actually in the real scenario Table1 will have many IDs and xml_tbl has many XML files..
      So I am stuck on how to do it. Please help.

      Thanks..

      Edited by: user8941550 on Nov 20, 2012 7:19 PM
        • 1. Re: Where clause with XMLExists and join on another table
          odie_63
          One of these two :
          SQL> select t.xml_msg_text
            2  from xml_tbl t
            3  where exists (
            4    select null
            5    from table1 t1
            6    where t1.id1 = xmlcast(
            7                     xmlquery('/main/id' passing t.xml_msg_text returning content)
            8                     as integer
            9                   )
           10  );
           
          XML_MSG_TEXT
          --------------------------------------------------------------------------------
          <main>
            <id>2</id>
          </main>
           
           
          SQL> select t.xml_msg_text
            2  from xml_tbl t
            3     , xmltable('/main' passing t.xml_msg_text
            4         columns id integer path 'id'
            5       ) x
            6  where exists (
            7    select null
            8    from table1 t1
            9    where t1.id1 = x.id
           10  );
           
          XML_MSG_TEXT
          --------------------------------------------------------------------------------
          <main>
            <id>2</id>
          </main>
           
          And a third one, using XMLExists :
          SQL> select t.xml_msg_text
            2  from xml_tbl t
            3  where xmlexists (
            4    'fn:collection("oradb:/DEV/TABLE1")/ROW[ID1=$d/main/id]'
            5    passing t.xml_msg_text as "d"
            6  );
          
          XML_MSG_TEXT
          ------------------------------------------------------------------------------------------------------------------------
          <main>
            <id>2</id>
          </main>
          
          
          
          Execution Plan
          ----------------------------------------------------------
          Plan hash value: 3633580934
          
          -------------------------------------------------------------------------------
          | Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
          -------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT    |         |     2 |   116 |     8   (0)| 00:00:01 |
          |*  1 |  FILTER             |         |       |       |            |          |
          |   2 |   TABLE ACCESS FULL | XML_TBL |     2 |   116 |     3   (0)| 00:00:01 |
          |   3 |   NESTED LOOPS      |         |     1 |     5 |     5   (0)| 00:00:01 |
          |   4 |    TABLE ACCESS FULL| TABLE1  |     2 |     6 |     3   (0)| 00:00:01 |
          |*  5 |    XPATH EVALUATION |         |       |       |            |          |
          -------------------------------------------------------------------------------
          
          Predicate Information (identified by operation id):
          ---------------------------------------------------
          
             1 - filter( EXISTS (SELECT 0 FROM "DEV"."TABLE1"
                        "SYS_ORAVW_2",XPATHTABLE('/main/id' PASSING :B1 COLUMNS "C_00$" XMLTYPE
                        PATH '.', "C_01$" XQEXVAL CHAR PATH '.')  "P" WHERE
                        TO_BINARY_DOUBLE("ID1")=TO_BINARY_DOUBLE("P"."C_01$")))
             5 - filter(TO_BINARY_DOUBLE("ID1")=TO_BINARY_DOUBLE("P"."C_01$"))
          The plan is similar to that of the second query above (XMLTable/EXISTS).

          Still using XMLExists, a plan similar to the first query (EXISTS/XMLCast/XMLQuery) can be achieved by casting id to an integer datatype :
          SQL> select t.xml_msg_text
            2  from xml_tbl t
            3  where xmlexists (
            4    'fn:collection("oradb:/DEV/TABLE1")/ROW[ID1=xs:int($d/main/id)]'
            5    passing t.xml_msg_text as "d"
            6  );
          
          XML_MSG_TEXT
          ------------------------------------------------------------------------------------------------------------------------
          <main>
            <id>2</id>
          </main>
          
          
          
          Execution Plan
          ----------------------------------------------------------
          Plan hash value: 1149640166
          
          ------------------------------------------------------------------------------
          | Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
          ------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT   |         |     1 |    61 |     7  (15)| 00:00:01 |
          |*  1 |  HASH JOIN SEMI    |         |     1 |    61 |     7  (15)| 00:00:01 |
          |   2 |   TABLE ACCESS FULL| XML_TBL |     2 |   116 |     3   (0)| 00:00:01 |
          |   3 |   TABLE ACCESS FULL| TABLE1  |     2 |     6 |     3   (0)| 00:00:01 |
          ------------------------------------------------------------------------------
          
          Predicate Information (identified by operation id):
          ---------------------------------------------------
          
             1 - access("ID1"=SYS_XQ_ATOMCNVCHK(TO_NUMBER(SYS_XQ_UPKXML2SQL(SYS_XQ
                        EXVAL(SYS_XQEXTRACT(SYS_MAKEXML(0,"T"."SYS_NC00003$"),'/main/id'),1,50,3
                        3792,8192),50,1,0)),2,37))
          
          Note
          -----
             - Unoptimized XML construct detected (enable XMLOptimizationCheck for more information)
          Check each one on your real scenario to see which show best performance.
          (I would tend to say the ones involving streaming evaluation)

          Edited by: odie_63 on 5 nov. 2012 12:24

          Edited by: odie_63 on 5 nov. 2012 12:38
          • 2. Re: Where clause with XMLExists and join on another table
            user8941550
            Thanks Odie.I appreciate your help. :-)
            • 3. Re: Where clause with XMLExists and join on another table
              odie_63
              BTW, I'm interested in some feedback regarding the solution you'll implement, thanks.
              • 4. Re: Where clause with XMLExists and join on another table
                user8941550
                I will check all the scenarios and share the performance soon.Thanks again.
                • 5. Re: Where clause with XMLExists and join on another table
                  user8941550
                  Hi Odie,

                  I am able to use namespaces for the first queries and note the performance time.

                  Can you please suggest how to use the namespace with third query.
                  i.e.

                  select t.xml_msg_text
                  from xml_tbl t
                  where xmlexists (
                  'fn:collection("oradb:/DEV/TABLE1")/ROW[ID1=$d/main/id]'
                  passing t.xml_msg_text as "d"
                  );


                  In real scenario id in the XMLType column is like -> Namesp1:id. So I am trying to place declare namespace condition but getting errors..
                  'declare namespace Namesp1 ="Abc:Set";


                  Thanks...
                  • 6. Re: Where clause with XMLExists and join on another table
                    odie_63
                    So I am trying to place declare namespace condition but getting errors..
                    What errors?

                    It might be due to your client tool interpreting the semicolon as the end of the statement.
                    Documented workaround : add an XQuery comment at the end of the declaration.
                    SQL> truncate table xml_tbl;
                     
                    Table truncated
                     
                    SQL> insert into xml_tbl values(1, '<main xmlns:ns1="Abc:SET"><ns1:id>1</ns1:id></main>');
                     
                    1 row inserted
                     
                    SQL> insert into xml_tbl values(1, '<main xmlns:ns1="Abc:SET"><ns1:id>2</ns1:id></main>');
                     
                    1 row inserted
                     
                    SQL> select t.xml_msg_text
                      2  from xml_tbl t
                      3  where xmlexists (
                      4    'declare namespace ns1 = "Abc:SET"; (: :)
                      5     fn:collection("oradb:/DEV/TABLE1")/ROW[ID1=$d/main/ns1:id]'
                      6    passing t.xml_msg_text as "d"
                      7  );
                     
                    XML_MSG_TEXT
                    --------------------------------------------------------------------------------
                    <main xmlns:ns1="Abc:SET">
                      <ns1:id>2</ns1:id>
                    </main>
                     
                    Or put it all on the same line :
                    SQL> select t.xml_msg_text
                      2  from xml_tbl t
                      3  where xmlexists (
                      4    'declare namespace ns1 = "Abc:SET"; fn:collection("oradb:/DEV/TABLE1")/ROW[ID1=$d/main/ns1:id]'
                      5    passing t.xml_msg_text as "d"
                      6  );
                     
                    XML_MSG_TEXT
                    --------------------------------------------------------------------------------
                    <main xmlns:ns1="Abc:SET">
                      <ns1:id>2</ns1:id>
                    </main>
                     
                    BTW, "DEV" is my schema name here :
                    "oradb:/DEV/TABLE1"
                    Don't forget to replace it with your own.

                    Edited by: odie_63 on 5 nov. 2012 14:09
                    • 7. Re: Where clause with XMLExists and join on another table
                      user8941550
                      Thanks Odie..3rd one is the best solution.
                      Whereeas others I tried were taking around a minute, this one works in 2 secs..Plan show Table Access by Index RowID as well. These are the Xpath indexes, I created on the XMLType column.
                      :-)
                      • 8. Re: Where clause with XMLExists and join on another table
                        user8941550
                        All thanks to Odie, my question is answered very well.Thanks. :-)
                        • 9. Re: Where clause with XMLExists and join on another table
                          user8941550
                          Hi,

                          I am having to reopen this thread as there is a small change to our table with XML Type Data.

                          We created around 100 partitions in advance and around 100 as backdated ones on the table with XMLType data.

                          Now my query does not use the XPath Indexes. I tried with Index hint as well. Even that doesn't work.

                          Does a lot of partitions on table cause the indexes not to be used.

                          Please suggest.

                          Thanks..
                          • 10. Re: Where clause with XMLExists and join on another table
                            user8941550
                            I have created the index in the following way:


                            BEGIN
                            DBMS_XMLINDEX.REGISTERPARAMETER(
                            'ParameterName,
                            'PATH TABLE PathTableName
                            PATHS (INCLUDE (
                            /main/id
                            ))
                            ');
                            end;

                            create index IndexName on "XML_TBL " (XML_TEXT) indextype is xdb.xmlindex
                            parameters ('PARAM 'ParameterName) local; --I have mentioned this as local as my main table is partitioned on some column as well..
                            /

                            But before creating the partitions, this index was being used.
                            It's not being used now. Does it suggest something.

                            Thanks..
                            • 12. Re: Where clause with XMLExists and join on another table
                              odie_63
                              Please post the DDL of your new partitioned table.

                              Is there only one /main/id per XML document?
                              • 13. Re: Where clause with XMLExists and join on another table
                                user8941550
                                Hi Odie,

                                Thanks for the response. I am getting a good performance now as I changed some parameter in the XPATH.

                                Can you please suggest how can I remove the schema name- "DEV" from the following code that I am using finally. I need to deploy the code in UAT and need it to be without the schema name.

                                select t.xml_msg_text
                                from xml_tbl t
                                where xmlexists (
                                'fn:collection("oradb:/DEV/TABLE1")/ROW[ID1=$d/main/id]'
                                passing t.xml_msg_text as "d"
                                );


                                Thanks..
                                • 14. Re: Where clause with XMLExists and join on another table
                                  user8941550
                                  Hi,

                                  I used PUBLIC, instead of the schema name. It's working. :-)
                                  I hope it doesn't have any side effect on the UAT.

                                  Thanks..
                                  1 2 Previous Next