This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Nov 18, 2012 11:04 AM by Marco Gralike RSS

Where clause with XMLExists and join on another table

user8941550 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks Odie.I appreciate your help. :-)
  • 3. Re: Where clause with XMLExists and join on another table
    odie_63 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    All thanks to Odie, my question is answered very well.Thanks. :-)
  • 9. Re: Where clause with XMLExists and join on another table
    user8941550 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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..
  • 11. Re: Where clause with XMLExists and join on another table
    user8941550 Newbie
    Currently Being Moderated
    No suggestions..:-(
  • 12. Re: Where clause with XMLExists and join on another table
    odie_63 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

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