This discussion is archived
6 Replies Latest reply: Oct 27, 2012 9:22 AM by odie_63 RSS

Xquery ,Clob and Ora:view being used..Kindly suggest

user8941550 Newbie
Currently Being Moderated
Our approach is like:

- We are doing all the processing /business logic in Xquery.
Then in Oracle we store our entire xquery code in the form of clob.


The code is like:

Create Function FunctionName(ExecDate In Date)
Return XMLTYPE AS

v_result XMLTYPE;

v_xquerycode1 clob := to_clob('declare.......all the xquery code');
v_xquerycode2 clob := to_clob('remaining code from v_xquerycode1.......');
v_xquerycode3 clob := to_clob('let $p1 := ora:view("T1")/ROW//Root:MessageStart
'let $p2 := ora:view("T2")/ROW//Root:MessageStart
Func(p1, p2, asofdate) ');
--Then we club the clobs as:
DBMS_LOB.APPEND(v_xquerycode1 ,v_xquerycode2);
DBMS_LOB.APPEND(v_xquerycode1 ,v_xquerycode3 );

-- Call the xquerycode as
select "COLUMN_VALUE"
INTO v_result
from XMLTable(v_xquerycode1
PASSING ExecDate AS "reportDate"
)Name;

return v_result ;

End FunctionName;

- In the DB we have stored xml files to be processed in tables T1 and T2.
T1 structure:
XMLID Number
XMLSource varchar2(20)
XMLContent XMLType
.............

T1 structure:
XMLID Number
XMLSource varchar2(20)
XMLContent XMLType
.............

XmlContent column is storing the XML Files.

Then when I query as :
select XMLType.getCLOBVal(FunctionName(to_date(Sysdate, 'yyyymmdd'))) from dual


It takes hours without executing
If I reduce the trades to 100 instead of 1000s then it gets executed within a minute.

Please suggest a better way here.
  • 1. Re: Xquery ,Clob and Ora:view being used..Kindly suggest
    odie_63 Guru
    Currently Being Moderated
    - We are doing all the processing /business logic in Xquery.
    Then in Oracle we store our entire xquery code in the form of clob.
    What's the database version?
    I guess probably 11.x since you can use a dynamic XQuery string...

    That would certainly not fit in my top-10 list of the best designs regarding XML in the db.

    Dynamic XQuery : security issues, no optimization possible...
    Please suggest a better way here.
    Why do you need dynamic XQuery?
    What's the underlying storage of the XMLType columns?

    Could you give a test case? Sample data + expected result?
  • 2. Re: Xquery ,Clob and Ora:view being used..Kindly suggest
    user8941550 Newbie
    Currently Being Moderated
    Hi Odie,

    Thanks for the reply. We are using Oracle 11g R2.

    So when you say:
    Dynamic XQuery : security issues, no optimization possible...

    Does that mean, even if I create any XMLIndexes on the Xpath, they won't be used?



    Could you give a test case..Sample data.. expected result..
    There are more than 50,000 xml files. One XML file is around 350 lines. We need to derive around 7-8 tags from every xml based on some conditions and perform calculations on them.

    Kindly suggest any ways to improve the process..

    Edited by: user8941550 on Oct 25, 2012 8:18 PM

    Edited by: user8941550 on Oct 25, 2012 8:19 PM
  • 3. Re: Xquery ,Clob and Ora:view being used..Kindly suggest
    odie_63 Guru
    Currently Being Moderated
    We are using Oracle 11g R2.
    As there are differences between versions in this release (new parser, new features etc.) it's important to know the exact version :
    SELECT * FROM v$version
    So when you say:
    Dynamic XQuery : security issues, no optimization possible...

    Does that mean, even if I create any XMLIndexes on the Xpath, they won't be used?
    Exactly.
    It's easy to check for yourself, even on a simple example.

    With a static XQuery string :
    Connected to:
    Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
    
    SQL> drop table tmp_xml;
    
    Table dropped.
    
    SQL> create table tmp_xml of xmltype;
    
    Table created.
    
    SQL> create index tmp_xml_ix on tmp_xml (object_value)
      2  indextype is xdb.xmlindex;
    
    Index created.
    
    SQL> insert into tmp_xml values(xmlparse(document '<root><item>ABC</item></root>'));
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> set autotrace traceonly explain
    SQL> set lines 120
    SQL> select x.*
      2  from tmp_xml t
      3     , xmltable('/root/item' passing t.object_value
      4         columns item varchar2(30) path '.'
      5       ) x
      6  ;
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 4160540934
    
    ---------------------------------------------------------------------------------------------------------------
    | Id  | Operation                    | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |                                |     1 |  3034 |     3   (0)| 00:00:01 |
    |   1 |  NESTED LOOPS                |                                |       |       |            |       |
    |   2 |   NESTED LOOPS               |                                |     1 |  3034 |     3   (0)| 00:00:01 |
    |   3 |    TABLE ACCESS FULL         | TMP_XML                        |     1 |    12 |     3   (0)| 00:00:01 |
    |*  4 |    INDEX RANGE SCAN          | SYS77583_TMP_XML_IX_PIKEY_IX   |     1 |       |     0   (0)| 00:00:01 |
    |*  5 |   TABLE ACCESS BY INDEX ROWID| SYS77583_TMP_XML_IX_PATH_TABLE |     1 |  3022 |     0   (0)| 00:00:01 |
    ---------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       4 - access("T".ROWID="SYS_P0"."RID" AND "SYS_P0"."PATHID"=HEXTORAW('2DF1') )
       5 - filter(SYS_XMLI_LOC_ISNODE("SYS_P0"."LOCATOR")=1)
    
    Note
    -----
       - dynamic sampling used for this statement (level=2)
    With a dynamic XQuery string :
    SQL> var xq varchar2(80)
    SQL> exec :xq := '/root/item'
    
    PL/SQL procedure successfully completed.
    
    SQL> select x.*
      2  from tmp_xml t
      3     , xmltable(:xq passing t.object_value
      4         columns item varchar2(30) path '.'
      5       ) x
      6  ;
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1822248632
    
    ------------------------------------------------------------------------------------------------------------
    | Id  | Operation                          | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                   |                       |  8168 |    15M|    32   (0)| 00:00:01 |
    |   1 |  NESTED LOOPS                      |                       |  8168 |    15M|    32   (0)| 00:00:01 |
    |   2 |   TABLE ACCESS FULL                | TMP_XML               |     1 |  2002 |     3   (0)| 00:00:01 |
    |   3 |   COLLECTION ITERATOR PICKLER FETCH| XQSEQUENCEFROMXMLTYPE |  8168 | 16336 |    29   (0)| 00:00:01 |
    ------------------------------------------------------------------------------------------------------------
    
    Note
    -----
       - dynamic sampling used for this statement (level=2)
       - Unoptimized XML construct detected (enable XMLOptimizationCheck for more information)
    There are more than 50,000 xml files. One XML file is around 350 lines. We need to derive around 7-8 tags from every xml based on some conditions and perform calculations on them.
    That's the point of a test case, it doesn't have to be the full picture but something simple enough that you can post here, yet sufficient to show us what kind of process you're doing.

    Kindly suggest any ways to improve the process..
    Difficult to help someone when we don't know what he's doing.
    As you didn't answer the previous questions (storage method? why dynamic XQ? XQuery logic?), I'm afraid I can't suggest anything useful.

    - Are you doing joins between XML rowsets returned by ora:view?
    - What kind of calculations are we talking about here?

    Still stabbing in the dark, but in this situation, I would probably look towards extracting everything I need as relational data and use SQL to perform the calculations.
  • 4. Re: Xquery ,Clob and Ora:view being used..Kindly suggest
    mdrake Expert
    Currently Being Moderated
    Our Architect's response to this is

    Use Execute Immediate to execute the SELECT statement..
  • 5. Re: Xquery ,Clob and Ora:view being used..Kindly suggest
    Marco Gralike Oracle ACE Director
    Currently Being Moderated
    mdrake wrote:
    Our Architect's response to this is

    Use Execute Immediate to execute the SELECT statement..
    The "EXECUTE IMMEDIATE" alternative has the same (current ? implementation) disadvantage, as using a "dynamic XQuery string" in this case, its very difficult for the CBO to performance optimize this: too much unknowns regarding the predicates and methods to be used
  • 6. Re: Xquery ,Clob and Ora:view being used..Kindly suggest
    odie_63 Guru
    Currently Being Moderated
    Marco Gralike wrote:
    The "EXECUTE IMMEDIATE" alternative has the same (current ? implementation) disadvantage, as using a "dynamic XQuery string" in this case
    That's not true.
    The statement will build an SQL cursor and it'll be treated by the CBO just like any other cursor.
    If the XQuery expression is known at parse time then the CBO will know what to do :
    SQL> declare
      2  
      3    v_xq_str   varchar2(80) := '/root/item';
      4    v_sql_str  varchar2(2000) := q'{
      5  SELECT /*FIND_ME*/ x.item
      6  FROM tmp_xml t
      7     , XMLTable(':xq_str' passing t.object_value
      8        columns item varchar2(30) path '.'
      9       ) x
     10    }';
     11  
     12    v_result   varchar2(30);
     13  
     14  begin
     15  
     16    execute immediate replace(v_sql_str, ':xq_str', v_xq_str) into v_result;
     17    dbms_output.put_line('result = '||v_result);
     18  
     19  end;
     20  /
     
    result = ABC
     
    PL/SQL procedure successfully completed
     
    SQL> select sql_text, sql_id from v$sql where sql_text like '%FIND_ME%';
     
    SQL_TEXT                                                                         SQL_ID
    -------------------------------------------------------------------------------- -------------
     SELECT /*FIND_ME*/ x.item FROM tmp_xml t    , XMLTable('/root/item' passing t.o 4j3vcczhb2r04
     select sql_text, sql_id from v$sql where sql_text like '%FIND_ME%'              b2ndymq7u2s7y
     declare    v_xq_str   varchar2(80) := '/root/item';   v_sql_str  varchar2(2000) b0ju35kdk7bwv
     
    SQL> select * from table(dbms_xplan.display_cursor('4j3vcczhb2r04'));
     
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    SQL_ID  4j3vcczhb2r04, child number 0
    -------------------------------------
     SELECT /*FIND_ME*/ x.item FROM tmp_xml t    , XMLTable('/root/item'
    passing t.object_value       columns item varchar2(30) path '.'      ) x
    Plan hash value: 3806182995
    --------------------------------------------------------------------------------
    | Id  | Operation                    | Name                           | Rows  |
    --------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |                                |       |
    |   1 |  NESTED LOOPS                |                                |       |
    |   2 |   NESTED LOOPS               |                                |     1 |
    |   3 |    TABLE ACCESS FULL         | TMP_XML                        |     1 |
    |*  4 |    INDEX RANGE SCAN          | SYS28105_TMP_XML_IX_PIKEY_IX   |     1 |
    |*  5 |   TABLE ACCESS BY INDEX ROWID| SYS28105_TMP_XML_IX_PATH_TABLE |     1 |
    --------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
       4 - access("T".ROWID="SYS_P0"."RID" AND "SYS_P0"."PATHID"=HEXTORAW('2DF1') )
       5 - filter(SYS_XMLI_LOC_ISNODE("SYS_P0"."LOCATOR")=1)
    Note
    -----
       - dynamic sampling used for this statement (level=2)
     
    28 rows selected
     
    Now this approach has the disadvantages of dynamic SQL in general :
    - no dependency tracking in stored programs
    - no compile-time validation, hence error-prone
    - SQL injection (in the worst case)
    - harder maintenance
    - could generate more parsing (if cursors aren't cached)

    and that takes us back to the original question, why dynamic XQuery?

Legend

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