XML Scripting Language with DB Optimizer

1
XML Scripting Language with DB Optimizer

• Daniela Florescu
• Anguel Novoselsky
• Zhen Hua Liu
• Oracle Corporation
• PlanX 2006

2
Agenda

• Why XML Scripting Language ?
• XML Scripting Language Feature Overview
• Simple Examples
• Optimization with Database Optimizer
• Demo
• Q A

3
Why XML Scripting Language ?

• XQuery and XPath
• Declarative Query Language to query XML
• Powerful Functional Language
• Need Imperative logic enhancement
• Imperative and Declarative Duality
• Optimization Issue
• Automatic imperative to declarative conversion
• Cross tier optimization

4
API Emedding Approach

• Approach 1 Embed XQuery in conventional
  imperative languages Java, Perl, C/C via API
• JSR225(XQJ) API to access XQuery in Java
• Perl XMLXPath Package
• Issues
• Need an API for every imperative programming
  language
• Barrier to optimize crossing APIs
• Data Model Impedance mismatch
• User has to mentally switch between two language
  syntax/semantics

5
Language Extension Approach

• Approach 2 Extend conventional imperative
  languages to have XML query capability
• Issues
• Need to extend every conventional imperative
  programming language
• The extension may not be able to leverage XQuery
  syntax
• User has to mentally convert XQuery/Xpath syntax
  in new extension for the host programming
  language
• Data model impedance mismatch/conversion

6
XML Scripting Language Approach

• Natural Evolution from XQuery
• Leverage most of the XQuery expressions
• Share the SAME data model as XQuery
• Extended with imperative programming logic
• Has statements
• assignment statement, for statement, if
  statement, switch statement, statement block
• Update statement
• Local variables and return statement in function

7
Hello World! Example

• Hello World Example
• declare function main() as xsstring
• return "Hello World!"
• Output
• Hello World!

8
Summation using recursion

• Summation Example recursive style
• declare function sum(x) as xsinteger
• if (x lt 0) then do
• return 0
• else do
• return sum(x - 1) x
• declare function main() as xsinteger
• return sum(100)

9
Summation using iteration

• Summation Example iterative style
• declare function main() as xsinteger
• declare variable sum 0
• for i in (1 to 100) do
• set sum sum i
• return sum

10
XML Query capability

• Query fndoc() using XPath
• declare function main() as item()
• return (fndoc('inp.xml')/ab 3/c)

11
XML Query capability

• Input File Content - inp.xml
• ltagt
• ltbgt3lt/bgt
• ltbgt4lt/bgt
• ltbgt56lt/bgt
• lt!-- comment--gt
• ltcgthellolt/cgt
• lt/agt
• Ouput
• ltcgthellolt/cgt

12
XML update capability

• declare function main() as node()
•   declare variable t
  fndoc("t.xml")   delete t/root/leaf1
    replace t/root/leaf2 with
• (ltleaf3gt333lt/leaf3gt, ltleaf4gt444lt/leaf4gt)
• insert ltleaf5gt555lt/leaf5gt as last into
  t/root
• return t

13
XML update capability

• Input file "t.xml contentltrootgt
• ltleaf1gttextlt/leaf1gt
• ltleaf2gttextlt/leaf2gt
• lt/rootgt Output ltrootgt
• ltleaf3gt333lt/leaf3gt   ltleaf4gt444lt/leaf4gt  
  ltleaf5gt555lt/leaf5gt
• lt/rootgt

14
oraview()

• oraview() query XML view over relational data
• declare function main() as item()
• declare variable rslt ()
• for i in oraview("EMP")/ROW
• where i/SAL gt 3000
• order by i/ENAME
• do
• set rslt (rslt , i)
• return rslt

15
oraview()

• oraview() query XMLType table
• declare function main() as item()
• declare variable rslt ()
• for i in oraview("PURCHASEORDER")/PurchaseO
  rder where i/referenceNum
• do
• set rslt (rslt , i)
• return rslt

16
Write once , run on every tier

• XML scripting language is able to run on every
  tier (Mid-tier and DB-tier)
• Mid-tier push down optimization
• DB-Server optimization with Database Optimizer

17
Mid-tier Engine

• Standalone XML scripting language Java Engine
• Embeddable to Application Server/Web Server
• Evaluation/Optimization Strategies
• Only SQL fetching from table is pushed down
• Only Push Down XQuery to XQuery enabled DBMS
  server
• Capability to push down the whole scripting
  language execution to DBMS server

18
DBMS Server Side Engine

• XML scripting language supported in DBMS server
• Invocable from SQL
• Xqlang() function
• XMLTable(xqlang ) construct
• Integrate with database optimizer
• Native Compilation and Execution in database
  server
• Interoperable with database languages, such as
  PL/SQL

19
DBMS Server XQLang()

• XQLang() SQL function
• SQLgt select xqlang(
• 'declare function main() as xsstring
  return "Hello World!" '
• returning content) as rslt
• from dual
• RSLT
• -----------------
• Hello World!

20
DBMS Server Xqlang()

• XQLang() SQL function
• SQLgt select xqlang(
• 'declare function sum(x) as xsinteger
• if (x lt 0) then do return 0 else do
  return sum(x - 1) x
• declare function main() as xsinteger
  return sum(100) '
• returning content) as rslt
• from dual
• RSLT
• ---------------------------------
• 5050

21
DBMS Server XMLTable

• XMLTable(xqlang) construct
• SQL gt select from
• xmltable(xqlang
• 'declare function main() as item()
• declare variable rslt ()
• for i in oraview("EMP")/ROW
• where i/SAL gt 3000 do
• set rslt (rslt , i)
• return rslt ')
• COLUMN_VALUE
• --------------------------------------------------
  ------------------------------
• ltROWgtltEMPNOgt7788lt/EMPNOgtltENAMEgtSCOTTlt/ENAMEgtltJOBgtA
  NALYSTlt/JOBgtltMGRgt7566lt/MGRgtltHI
• REDATEgt1987-04-19lt/HIREDATEgtltSALgt3000lt/SALgtltDEPTNO
  gt20lt/DEPTNOgtlt/ROWgt
• ltROWgtltEMPNOgt7839lt/EMPNOgtltENAMEgtKINGlt/ENAMEgtltJOBgtPR
  ESIDENTlt/JOBgtltHIREDATEgt1981-11
• -17lt/HIREDATEgtltSALgt5000lt/SALgtltDEPTNOgt10lt/DEPTNOgtlt/
  ROWgt
• ltROWgtltEMPNOgt7902lt/EMPNOgtltENAMEgtFORDlt/ENAMEgtltJOBgtAN
  ALYSTlt/JOBgtltMGRgt7566lt/MGRgtltHIR

22
Optimization with DB Optimizer

• select from
• xmltable(xqlang
• 'declare function main() as item()
• declare variable rslt ()
• for i in oraview("EMP")/ROW
• where i/SAL gt 3000 do
• set rslt (rslt , i)
• return rslt ')
• Optimized into
• select xmlelement("row",
• xmlforest(empno, ename, job, mgr,
  hiredate, sal, deptno))
• from
• emp
• where sal gt 3000

23
DB Optimizer Plan

• explain plan for
• select from
• xmltable(xqlang
• 'declare function main() as item()
• declare variable rslt () for i in
  oraview("EMP")/ROW
• where i/SAL gt 3000 do
• set rslt (rslt , i) return
  rslt ')
• /

24
DB Optimizer Plan

• PLAN_TABLE_OUTPUT
• 0 SELECT STATEMENT
• 1 TABLE ACCESS BY INDEX ROWID EMP
• 2 INDEX RANGE SCAN SAL_IDX
• --------------------------------------------------
  ------------------------------
• Predicate Information (identified by operation
  id)
• --------------------------------------------------
  -
• 2 - access("SAL"gt3000)

25
Demo