8 Replies Latest reply: Jan 17, 2013 3:50 AM by odie_63 RSS

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

    user8941550
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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?