This discussion is archived
8 Replies Latest reply: Jan 17, 2013 1:50 AM by odie_63 RSS

XMLquery- Outlines not used in entire code but only in selected xml query

user8941550 Newbie
Currently Being Moderated
Hi,

We are using -> Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit
We have a part of our s/w system working as:
Drop table xml_tbl

create table xml_tbl (
xml_msg_id integer,
xml_msg_text xmltype
);

insert into xml_tbl values(1, '<main xmlns:ns1="Abc:SET"><ns1:id>1</ns1:id></main>');
insert into xml_tbl values(1, '<main xmlns:ns1="Abc:SET"><ns1:id>1</ns1:id></main>');
insert into xml_tbl values(1, '<main xmlns:ns1="Abc:SET"><ns1:id>2</ns1:id></main>');
commit;

--Code

DECLARE
   v_output   CLOB;

   FUNCTION fn_xmlquery (p_date IN DATE)
      RETURN XMLTYPE
   AS
      v_xmlout   XMLTYPE;
      v_query    CLOB
         := TO_CLOB (
               'declare namespace ns1 = "Abc:SET"; fn:count($File_Data/main/ns1:id)');
   BEGIN
      SELECT XMLQUERY (v_query
                       PASSING t_tbl.xml AS "File_Data" RETURNING CONTENT)
        INTO v_xmlout
        FROM (SELECT XMLAGG (xml_msg_text) AS xml
                FROM xml_tbl t
               WHERE XMLEXISTS (
                        'declare namespace ns1 = "Abc:SET";  /main/ns1:id = ("1") '
                        PASSING xml_msg_text)) t_tbl;

      RETURN (v_xmlout);
   END fn_xmlquery;
BEGIN
   v_output :=
      XMLType.getClobVal (fn_xmlquery (TO_DATE ('20121231', 'YYYYMMDD')));
   DBMS_OUTPUT.put_line (v_output);
END;

--Now if I try the outline on xml_tbl query as:

drop outline o_name;

CREATE OUTLINE o_name ON
SELECT XMLAGG (xml_msg_text) AS xml
                FROM xml_tbl t
               WHERE XMLEXISTS (
                        'declare namespace ns1 = "Abc:SET";  /main/ns1:id = ("1") '
                        PASSING xml_msg_text);


select * from dba_outlines;
                                        If we just execute this query again, outline O_Name is used as can be seen in :
Select * from dba_outlines;
It's used in both the schemas in DB as we want.

But if we execute the entire code above, which we need to, for doing all xquery processing,
OUTLINE O_Name is not used. Not even in the same schema.
You can try the above in your machine to replicate.

Kindly suggest how can we make the Outlines work in this particular scenario. As things are not working in our system at all due to wrong query plan by the query above on which we are creating the Outline.
Thanks..

Edited by: user8941550 on Jan 9, 2013 11:16 PM
  • 1. Re: XMLquery- Outlines not used in entire code but only in selected xml query
    odie_63 Guru
    Currently Being Moderated
    Hi,

    The stored outline will be used only on the exact same query, not the case here.
    Maybe we should first focus on why you need to use Outlines?

    And what is your justification for using a dynamic XQuery expression? Do you want to get worst performance at all cost?

    Isn't the whole thing equivalent to this :
    SQL> SELECT count(*)
      2  FROM xml_tbl
      3  WHERE XMLEXISTS (
      4         'declare namespace ns1 = "Abc:SET"; /main[ns1:id="1"]'
      5         PASSING xml_msg_text
      6        ) ;
     
      COUNT(*)
    ----------
             2
     
    ?

    Edited by: odie_63 on 10 janv. 2013 11:06
  • 2. Re: XMLquery- Outlines not used in entire code but only in selected xml query
    user8941550 Newbie
    Currently Being Moderated
    Hi odie,

    Thanks for the reply.
    I managed to use the baseline for this scenario.
    Although I am still concerned about the more scenarios which I come across in future.

    Yes, whole thing is equivalent to this i.e. the query you wrote. :-)
    But in our system, it's not just fn:count(), but hundreds of lines of processing in xquery.

    The reason for this dynamic XQuery expression is that we wanted most of our code to be database independent and working in XQuery.
    And then execute it throguh Oracle.
    So can you please suggest any better way which we can use so that we can avoid the dynamic xquery thing.
  • 3. Re: XMLquery- Outlines not used in entire code but only in selected xml query
    odie_63 Guru
    Currently Being Moderated
    Maybe XQuery Modules will be supported in a future release of the database.
    So can you please suggest any better way which we can use so that we can avoid the dynamic xquery thing.
    Make the whole SQL dynamic instead, not just the XQuery, and execute it via EXECUTE IMMEDIATE or DBMS_SQL.
    That way, the CBO will know the XQ expression at parse time and be able to optimize it.

    I'm not a big fan of that approach but for the lack of something better.
  • 4. Re: XMLquery- Outlines not used in entire code but only in selected xml query
    user8941550 Newbie
    Currently Being Moderated
    Thanks for the answer Odie.

    I am able to do it dynamically and using EXECUTE IMMEDIATE in this particular example:
    DECLARE
       v_output   CLOB;
     
       FUNCTION fn_xmlquery (p_date IN DATE)
          RETURN XMLTYPE
       AS
          v_xmlout   XMLTYPE;
          v_sql_str  clob;
          v_query    CLOB
             := TO_CLOB (
                   'declare namespace ns1 = "Abc:SET"; fn:count($File_Data/main/ns1:id)');           
       BEGIN
       
         v_sql_str := q'{
     SELECT XMLQUERY (':v_query'
                           PASSING t_tbl.xml AS "File_Data"  RETURNING CONTENT)
            FROM (SELECT XMLAGG (xml_msg_text) AS xml
                    FROM xml_tbl 
                   WHERE XMLEXISTS (
                            'declare namespace ns1 = "Abc:SET";  /main/ns1:id = ("1") '
                            PASSING xml_msg_text)) t_tbl}';
                             Execute Immediate replace(v_sql_str, ':v_query', v_query)  into v_xmlout;
     RETURN (v_xmlout);
       END fn_xmlquery;
    BEGIN
       v_output :=
          XMLType.getClobVal (fn_xmlquery (TO_DATE ('20100101', 'YYYYMMDD')));
       DBMS_OUTPUT.put_line (v_output);
    END;
    But if I do the same in my actual code I get the error:
    ORA-19109: RETURNING keyword expected


    Actual code is like as following but with different names as:
     v_sql_str:=  q'{
    SELECT XMLQUERY (
              ':v_query'
              PASSING tbl_1.xml AS "tb_xml",
                      p_date AS "P_Date",
                      p_name AS "P_Name"
              RETURNING CONTENT)
      FROM (SELECT XMLAGG (xml_msg_text) AS xml
              FROM xml_tbl
             WHERE XMLEXISTS (
                      'declare namespace Namesp1 ="Abc:Set"; declare Namespace Namesp2 ="XYZ:Set"; declare namespace Namesp3 ="DEF:Set"; /Namesp1:Tag1/Namesp2:Tag2/Namesp3:Tag3[@id="ID"][Namesp2:Tag5[@Scope="SCOPE"] = ("1000", "2000") ]'
                      PASSING xml_msg_text)) tbl_1
     }';
                      Execute Immediate replace(v_sql_str, ':v_query', v_query)  into v_xmlout ;  -- Here I created another function to replace in Huge CLOB which is working fine as I used in the code pasted above (i.e. Working Example Code)
        RETURN (v_xmlout);
    Please suggest how to resolve this error as I tried Returning Clause in different places but doesn't seem to be working....

    -------------------------------------

    So that leads me to my original problem. What privilege or parameter could I be missing in DB2
    EXPLAIN PLAN
       FOR
          SELECT Column_Name
            FROM TNAME
           WHERE XMLEXISTS (
                    'declare namespace Namesp1 ="Abc:Set"; declare Namespace Namesp2 ="XYZ:Set"; declare namespace Namesp3 ="DEF:Set"; /Namesp1:Tag1/Namesp2:Tag2/Namesp3:Tag3[@id="ID"][Namesp2:Tag5[@Scope="SCOPE"] = ("1000", "2000") ]'
                    PASSING Column_Name);
    Above code works for Schema1 and Schema2 in DB1. -->>I get the result in both schemas here. i.e. statement uses XMLType Indexes on TNAME and executes in both schemas.

    ALSO IN (Another DATABASE) DB2- SCHEMA1, I can do explain plan for as above..and it executes.
    BUT IN DB2- SCHEMA2, I can't do EXPLAIN PLAN for above code..I get ORA-00942: table or view does not exist ..IT'S NOT RELATED to PLAN_TABLE related permissions as I can do EXPLAIN PLAN FOR SELECT * FROM TNAME.

    I have tried dba_tab_privs, SESSION_PRIVS but can't find anything.

    Thanks...

    Edited by: user8941550 on Jan 14, 2013 3:30 AM
  • 5. Re: XMLquery- Outlines not used in entire code but only in selected xml query
    odie_63 Guru
    Currently Being Moderated
    The two additional parameters P_DATE and P_NAME have to be bound to the query otherwise they just appear as unknown identifiers, hence the parser complaining about finding something else than the RETURNING keyword.

    I also suggest you don't use braces { } as the alternate quote delimiter, since those are often used in XQuery (node constructors).
    Choose a character that doesn't appear for sure in the expression. Personally, I often use a hash (#) or a tilde (~).
    v_sql_str:=  q'#
    SELECT XMLQUERY (
            ':v_query'
            PASSING tbl_1.xml AS "tb_xml"
                  , :1 AS "P_Date"
                  , :2 AS "P_Name"
            RETURNING CONTENT
           )
    FROM (
      SELECT XMLAGG (xml_msg_text) AS xml
      FROM xml_tbl
      WHERE XMLEXISTS (
             'declare namespace Namesp1 ="Abc:Set"; (: :)
              declare Namespace Namesp2 ="XYZ:Set"; (: :) 
              declare namespace Namesp3 ="DEF:Set"; (: :)
              /Namesp1:Tag1/Namesp2:Tag2/Namesp3:Tag3[@id="ID"][Namesp2:Tag5[@Scope="SCOPE"] = ("1000", "2000") ]'
             PASSING xml_msg_text
            )
    ) tbl_1
    #';
                      
    EXECUTE IMMEDIATE replace(v_sql_str, ':v_query', v_query)  
                 INTO v_xmlout
                 USING p_date, p_name ;
  • 6. Re: XMLquery- Outlines not used in entire code but only in selected xml query
    user8941550 Newbie
    Currently Being Moderated
    Hi Odie,

    Thanks for the response.
    Can you also comment on second part of the question.
    ANy particular privilege or parameter which could be missing when I try to do Explain Plan for.
    As I can do:
    Explain Plan for Select * from TNAME;
    BUT can't do : (I get ORA-00942: table or view does not exist )
    EXPLAIN PLAN
       FOR
          SELECT Column_Name
            FROM TNAME
           WHERE XMLEXISTS (
                    'declare namespace Namesp1 ="Abc:Set"; declare Namespace Namesp2 ="XYZ:Set"; declare namespace Namesp3 ="DEF:Set"; /Namesp1:Tag1/Namesp2:Tag2/Namesp3:Tag3[@id="ID"][Namesp2:Tag5[@Scope="SCOPE"] = ("1000", "2000") ]'
                    PASSING Column_Name);
    There is an XMLTYpe Index on table TNAME.
    Schema1 owns TNAME. Schem2 has a synonym and Grant ALL priviliege on TNAME.
    Problem is in Schema2. In Schema1 it works fine.

    Edited by: user8941550 on Jan 14, 2013 2:21 AM
  • 7. Re: XMLquery- Outlines not used in entire code but only in selected xml query
    user8941550 Newbie
    Currently Being Moderated
    Hi Odie,

    As suggested by you, I am able to execute the following successfully in test scenario:
    DECLARE
       v_output   CLOB;
    
       FUNCTION fn_xmlquery (p_date IN DATE, p_name VARCHAR2)
          RETURN XMLTYPE
       AS
          v_xmlout    XMLTYPE;
          v_sql_str   CLOB;
          v_query     CLOB
             := TO_CLOB (
                   'declare namespace ns1 = "Abc:SET"; fn:count($File_Data/main/ns1:id)');
       BEGIN
          v_sql_str :=
             q'#
    SELECT XMLQUERY (
            ':v_query'
            PASSING t_tbl.xml AS "File_Data"  
             , :1 AS "P_Date"
                  , :2 AS "P_Name"
    
            RETURNING CONTENT)
            FROM (SELECT XMLAGG (xml_msg_text) AS xml
            
                    FROM xml_tbl 
                   WHERE XMLEXISTS (
    
           'declare namespace ns1 = "Abc:SET";  /main/ns1:id = ("1") '
                            PASSING xml_msg_text   )
    ) t_tbl
    #';
    
          EXECUTE IMMEDIATE REPLACE (v_sql_str, ':v_query', v_query)
             INTO v_xmlout
             USING p_date, p_name;
    
          RETURN (v_xmlout);
       END fn_xmlquery;
    BEGIN
       v_output :=
          XMLType.getClobVal (
             fn_xmlquery (TO_DATE ('20100101', 'YYYYMMDD'), 'ABA'));
       DBMS_OUTPUT.put_line (v_output);
    END;
    But I still get the same error:
    ORA-19109: RETURNING keyword expected.

    It's not related to the two parameters being passed i.e. "P_Date" and "P_Name" as when I remove them as params, still I get the same error.
    I find the only thing that has changed is the XQuery code in v_query here which has lots of namespaces defined. Could that be related to it.
    Thanks..
  • 8. Re: XMLquery- Outlines not used in entire code but only in selected xml query
    odie_63 Guru
    Currently Being Moderated
    It's not related to the two parameters being passed i.e. "P_Date" and "P_Name"
    Well, I'd rather say it was only a part of the problem.
    I find the only thing that has changed is the XQuery code in v_query here which has lots of namespaces defined. Could that be related to it.
    How could I know?
    You don't want to post something we can use to reproduce the issue so...

    Could you reproduce on a smaller test case, with dummy data, so that you can post it?

Legend

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