xml in oracle - elte ikpeople.inf.elte.hu/kiss/10kor/oraclexmldownload-resource.pdf · 1.1 v2.1 1...

1.1

V2.1

www.skillbuilders.com

1

XML in Oracle

A practical introduction to the primary features of Oracle XDB.

Presented be Geoff Wiland.Based on article from Oracle by Tim Quinlan

1.2

V2.1


2

AgendaBrief historyIntro XML DBSQL/XML StandardStructured vs Unstructured XML StorageNamespacesSchema Typed vs Untyped DataSchemas & Validation

XQuery, XPATH, & FLOWRXML DB RepositoryConverting XML to Relational DataConverting Relational to XML DataXML Performance Tuning11g New Features

1.3

V2.1

When stored as LOBs, we had to use the DBMS_LOB package to process the XML data.


3

A Bit of History

Starting a few years ago, lots of XML entering databasesNo formal vendor tools or solutionHad to store, query, and update XML data as unstructured LOBsAlternatively, “shred” data into relational tables

Then put it back together

1.4

V2.1


4

Enter XML DB

1st delivered Oracle 9i Database Release 2 Built-in features for XML data

Storing as unstructured LOBs RetrievingManipulating XML data in the database

Oracle 10g Release 2 XML DB greatly extends features

1.5

V2.1

@xmldbset linesize 150set pagesize 32000column comp_name format a20column schema format a6select comp_name, status, schema from dba_registry where comp_name like '%XML%';

COMP_NAME STATUS Schema-------------------- ----------- ------Oracle XML Database VALID XDB


5

Do I Have XML DB?

XML DB included with XE, SE and EECheck if installed:SQL> select comp_name, status, schema

2 from dba_registry3 where comp_name like '%XML%'4 /

COMP_NAME STATUS SCHEMA-------------------- ----------- ------Oracle XML Database VALID XDB

SQL> select comp_name, status, schema2 from dba_registry3 where comp_name like '%XML%'4 /

COMP_NAME STATUS SCHEMA-------------------- ----------- ------Oracle XML Database VALID XDB

@xmldb@xmldb

1.6

V2.1


6

SQL/XML Standard

SQL/XML ANSI ISO standard See www.sqlx.org

SQL/XML defines a standardized mechanism for using SQL and XML togetherStore XML in RDBMSQuery with XQuery and XPathGenerate XML from SQL query / relational data10g R2 implements SQL/XML 2003

Plus some features…

1.7

V2.1

@test_xml

drop table test_xml;create table test_xml of XMLtypeXMLType store as CLOB;

desc test_xmlName Null? Type----------------------- -------- ----------------TABLE of SYS.XMLTYPE


7

XMLType

Use XMLType to create table, column ,viewBuilt-in methods to create, extract, index XML dataPL/SQL, Java and .NET supportStore as

Unstructured – single LOB columnStructured – set of objects

SQL> create table test_xml of XMLtype2 XMLType store as CLOB;

Table created.

SQL> create table test_xml of XMLtype2 XMLType store as CLOB;

Table created.@test_xml@test_xml

1.8

V2.1


8

Structured Storage…

Implemented as set of objectsDocument is – in effect – stored as a “virtual”document through the relational tablesMaintains Document Object Model (DOM) fidelity

(But not a byte-for-byte representation of doc)Creates an XMLType view over existing relational data

1.9

V2.1


9

…Structured Storage

Performance advantagesNo tags, so reduced memory & storageGranular retrievalBetter b-tree, function-based indexingIn-place, granular updates using XPath rewrite

DisadvantagesIncreased insert, select overhead for whole docNot a byte-for-byte copy of original docOrder of data in doc not maintained

1.10

V2.1


10

Unstructured Storage

CLOB storageAdvantages

Match byte-for-byte original documentCan perform better when read or update entire doc

DisadvantagesExtra overhead to update small piece of docConstraints cannot be implementedLess efficient memory management

1.11

V2.1


11

Namespaces…Namespace describes set of related attributes or elementsCan be used to ensure document constructs have unique namesXMLType methods and XML functions use namespace prefixesIf no target namespace

Prefix is in the noNameSpace namespaceSpecial namespaces

http://www.w3.org/2001/XMLSchemaoverall XMLSchema namespace

http://xmlns.oracle.com/xdbOracle-supplied XML DB namespace

1.12

V2.1


12

….Namespaces

Specify a namespace when defining an element

xmlns is a reserved word telling us this is a namespace definitionhttp.name.com is any URI identifying the namespacexy is a short prefix bound to the URIThe prefix is used to indicate that an XML construct belongs to a specific URI

<xy:elementName xmlns:xy="http.name.com" /><xy:elementName xmlns:xy="http.name.com" />

1.13

V2.1

Declare prefixes:• xs prefix binds to http://www.w3.org/2001/XMLSchema URI • xdb prefix binds to http://xmlns.oracle.com/xdb URI


13

Namespace Example

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb= "http://xmlns.oracle.com/xdb" version="1.0">

<xs:element name="INVOICESCHEMA" xdb:defaultTable="INVOICESCHEMA"><xs:complexType>

<xs:sequence><xs:element name="MailAddressTo">

<xs:complexType><xs:sequence>

<xs:element name="Person" type="xs:string"/><xs:element name="Street" type="xs:string"/>

</xs:sequence> <xs:attribute name="id" type="xs:string" use="required"/>

</xs:complexType> </xs:element>

</xs:sequence></xs:complexType>

</xs:element></xs:schema>

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb= "http://xmlns.oracle.com/xdb" version="1.0">

<xs:element name="INVOICESCHEMA" xdb:defaultTable="INVOICESCHEMA"><xs:complexType>



<xs:element name="Person" type="xs:string"/><xs:element name="Street" type="xs:string"/>




</xs:element></xs:schema> Declare prefixes xs and xdb

Create schema

1.14

V2.1

@invoiceXML_colcreate or replace directory xmldir as 'c:\oraclass'; drop table invoiceXML_col;create table invoiceXML_col (inv_id number primary key,inv_doc XMLType);

desc invoiceXML_colName Null? Type----------------------- -------- ----------------INV_ID NOT NULL NUMBERINV_DOC XMLTYPE


14

Loading XML Data…Support for SQL, PL/SQL, Java, C, SQL*LoaderExample: SQL Insert from OS fileCreate Oracle directory

Create table with XML columncreate or replace directory xmldir as 'c:\oraclass';create or replace directory xmldir as 'c:\oraclass';

create table invoiceXML_col (inv_id number primary key,inv_doc XMLType);

create table invoiceXML_col (inv_id number primary key,inv_doc XMLType);

@invoiceXML_col@invoiceXML_col

1.15

V2.1

@invoiceXML_tbldrop table invoiceXML_tbl;create table invoiceXML_tbl of XMLtype; desc invoiceXML_tblName Null? Type----------------------------- -------- --------------------TABLE of XMLTYPE

-- Two indexes are created for each table: 1 for the primary key and-- one for the LOB:select index_name, table_name from user_indexeswhere table_name in ('INVOICEXML_COL', 'INVOICEXML_TBL');

INDEX_NAME TABLE_NAME------------------------------ ------------------------------SYS_IL0000063107C00003$$ INVOICEXML_TBLSYS_C009739 INVOICEXML_TBLSYS_IL0000063099C00003$$ INVOICEXML_COLSYS_C009737 INVOICEXML_COL

4 rows selected.


15

…Loading XML Data…

Or create XMLType table

See indexes created (Two on each table)

select index_name, table_name from user_indexes where table_name in ('INVOICEXML_COL', 'INVOICEXML_TBL');

select index_name, table_name from user_indexes where table_name in ('INVOICEXML_COL', 'INVOICEXML_TBL');

create table invoiceXML_tbl of XMLtype;create table invoiceXML_tbl of XMLtype;

@invoiceXML_tbl@invoiceXML_tbl

1.16

V2.1

@insert1set long 32000truncate table invoicexml_col;truncate table invoicexml_tbl;

insert into invoicexml_col values (1, XMLType(bfilename('XMLDIR', 'invoicexml.txt'), nls_charset_id('AL32UTF8') ));

col inv_doc format a100select * from invoicexml_col;INV_ID INV_DOC

---------- ----------------------------------------1 <Invoice>

<MailAddressTo id="PA"><Person>Joe Smith</Person>

. . . . .</Invoice>

insert into invoicexml_tbl values (XMLType(bfilename('XMLDIR', 'invoicexml.txt'),nls_charset_id('AL32UTF8')));

-- Note the pseudocolumn object_value.select object_value from invoicexml_tbl; OBJECT_VALUE----------------------------------------------<Invoice>

<MailAddressTo id="PA"><Person>Joe Smith</Person>

. . . . .</Invoice>


16

…Loading XML DataInsert with SQL


select * from invoicexml_col;


select object_value from invoicexml_tbl; -- Note the pseudocolumn object_value.


select * from invoicexml_col;


select object_value from invoicexml_tbl; -- Note the pseudocolumn object_value.

@insert1@insert1

1.17

V2.1


17

Schema-Typed Data…

XMLType data implemented as schema-typed or untyped dataXMLSchema provides schema-typed

Supplies information about document structure and contents

Better documentation, validation, and controlsPotentially more-efficient query and update processing

1.18

V2.1


18

…Schema-Typed Data

XMLSchema enables structured storage dataDocument can be decomposed into a set of objects

XMLType methods schemaValidate() and isSchemaValid()

Provide validation of an XMLType document using the schema definition.

1.19

V2.1


19

Inserting Schema-Typed Docs

Create a schemaRegister the schema Create an XMLSchema-based table Insert data

1.20

V2.1


20

Create Schema<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"

xmlns:xdb="http://xmlns.oracle.com/xdb version=1.0"><xs:element name="INVOICEFORMTEST">


<xs:element name="MailAddressTo"><xs:complexType>

<xs:sequence> <xs:element name="Person" type="xs:string"/><xs:element name="Street" type="xs:string"/><xs:element name="City" type="xs:string"/><xs:element name="State" type="xs:string"/><xs:element name="Zipcode" type="xs:string"/>





<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb version=1.0">

<xs:element name="INVOICEFORMTEST"><xs:complexType>



<xs:element name="Person" type="xs:string"/><xs:element name="Street" type="xs:string"/><xs:element name="City" type="xs:string"/><xs:element name="State" type="xs:string"/><xs:element name="Zipcode" type="xs:string"/>





Create in invoiceformtest.xsd in directory XML_DIR

1.21

V2.1

The SCHEMAURL above is any URI. It does not have to exist anywhere.

@register_schemaBEGINDBMS_XMLSCHEMA.deleteSchema(SCHEMAURL => 'http://xmlns.oracle.com/xdb/invoiceformtest.xsd',DELETE_OPTION => dbms_xmlschema.DELETE_CASCADE_FORCE );

END;/

BEGINDBMS_XMLSCHEMA.registerSchema(SCHEMAURL => 'http://xmlns.oracle.com/xdb/invoiceformtest.xsd',SCHEMADOC => bfilename('XMLDIR','invoiceformtest.xsd'),GENTABLES => false, CSID => nls_charset_id('AL32UTF8'));

END;/

-- To view your registered schemas:SELECT schema_url FROM user_xml_schemas;SCHEMA_URL------------------------------------------------http://xmlns.oracle.com/xdb/invoiceformtest.xsd


21

Register Schema

BEGINDBMS_XMLSCHEMA.registerSchema(

SCHEMAURL => 'http://xmlns.oracle.com/xdb/invoiceformtest.xsd',SCHEMADOC => bfilename('XMLDIR','invoiceformtest.xsd'),GENTABLES => false, CSID => nls_charset_id('AL32UTF8'));

END;/

To view your registered schemas:SELECT schema_url FROM user_xml_schemas;

BEGINDBMS_XMLSCHEMA.registerSchema(

SCHEMAURL => 'http://xmlns.oracle.com/xdb/invoiceformtest.xsd',SCHEMADOC => bfilename('XMLDIR','invoiceformtest.xsd'),GENTABLES => false, CSID => nls_charset_id('AL32UTF8'));

END;/

To view your registered schemas:SELECT schema_url FROM user_xml_schemas;

@register_schema@register_schema

1.22

V2.1

@create_schema_tabledrop table invoiceformtestXML_tbl;create table invoiceformtestXML_tbl of xmltypexmlschema "http://xmlns.oracle.com/xdb/invoiceformtest.xsd"-- Note: these are in double quotes, not single quotes!Element "INVOICEFORMTEST";

desc invoiceformtestXML_tblName --------------------------------------------------------------------TABLE of SYS.XMLTYPE(XMLSchema "http://xmlns.oracle.com/xdb/invoiceformtest.xsd" Element "INVOICEFORMTEST") STORAGE Object-relational TYPE "INVOICEFORMTEST332_T"


22

Create Schema-Based XMLType Table

create table invoiceformtestXML_tbl of xmltype

xmlschema "http://xmlns.oracle.com/xdb/invoiceformtest.xsd"Element "INVOICEFORMTEST";

create table invoiceformtestXML_tbl of xmltype

xmlschema "http://xmlns.oracle.com/xdb/invoiceformtest.xsd"Element "INVOICEFORMTEST";

@create_schema_table@create_schema_table

1.23

V2.1

@insert2insert into invoiceformtestXML_tbl values (XMLType(bfilename('XMLDIR', 'invoiceformtest.txt'),nls_charset_id('AL32UTF8')));

-- Query dataset long 32000select object_value from invoiceformtestXML_tbl;

OBJECT_VALUE----------------------------------------<INVOICEFORMTEST><MailAddressTo id="1"><Person>Joe Smith</Person><Street>10 Apple Tree Lane</Street><City>New York</City><State>NY</State><Zipcode>12345</Zipcode>

</MailAddressTo></INVOICEFORMTEST>


23

Insert Data

insert into invoiceformtestXML_tbl values (XMLType(bfilename('XMLDIR', 'invoiceformtest.txt'),nls_charset_id('AL32UTF8')));


insert into invoiceformtestXML_tbl values (XMLType(bfilename('XMLDIR', 'invoiceformtest.txt'),nls_charset_id('AL32UTF8')));


@insert2@insert2

1.24

V2.1

Now let’s try to insert an XML document that is not in the format specified by the implemented schema. Such a file is invoiceformtestinvalid.txt: <INVOICEFORMTEST>

<MailAddressTo id="1"><Person>Joe Smith</Person><Street>10 Apple Tree Lane</Street><City>New York</City><State>NY</State><Zipcode>12345</Zipcode><Country>USA</Country> No Country tag in schema!


@insert3insert into invoiceformtestXML_tbl values (XMLType(bfilename('XMLDIR', 'invoiceformtestinvalid.txt'),nls_charset_id('AL32UTF8')));

Insert into invoiceformtestXML_tbl values*

ERROR at line 1:ORA-30937: No schema definition for 'Country' (namespace '##local') in parent '/INVOICEFORMTEST/MailAddressTo'

Note that Oracle validates the data. Schema validation is essentially an extension of Oracle’s constraint checking!


24

Validation at Insert<INVOICEFORMTEST>

<MailAddressTo id="1"><Person>Joe Smith</Person><Street>10 Apple Tree Lane</Street><City>New York</City><State>NY</State><Zipcode>12345</Zipcode><Country>USA</Country>


<INVOICEFORMTEST><MailAddressTo id="1">

<Person>Joe Smith</Person><Street>10 Apple Tree Lane</Street><City>New York</City><State>NY</State><Zipcode>12345</Zipcode><Country>USA</Country>


insert into invoiceformtestXML_tbl values (XMLType(bfilename('XMLDIR', 'invoiceformtestinvalid.txt'),nls_charset_id('AL32UTF8')));Insert into invoiceformtestXML_tbl values

*ERROR at line 1:ORA-30937: No schema definition for 'Country' (namespace '##local') in parent '/INVOICEFORMTEST/MailAddressTo'

insert into invoiceformtestXML_tbl values (XMLType(bfilename('XMLDIR', 'invoiceformtestinvalid.txt'),nls_charset_id('AL32UTF8')));Insert into invoiceformtestXML_tbl values


Doc does not adhere to XMLSchema; no Country tag in schema.

SQL INSERT fails

Schema validation is essentially an extension of Oracle’s constraint checking!

@insert3@insert3

1.25

V2.1

@create_schema_columndrop table invoiceformtestXML_col;create table invoiceformtestXML_col (inv_id number primary key,inv_doc XMLType)XMLTYPE COLUMN inv_doc xmlschema "http://xmlns.oracle.com/xdb/invoiceformtest.xsd"Element "INVOICEFORMTEST";set linesize 80

desc invoiceformtestXML_colName Null? Type-------- -------- --------INV_ID NOT NULL NUMBERINV_DOC SYS.XMLTYPE(XMLSchema

"http://xmlns.oracle.com/xdb/invoiceformtest.xsd" Element "INVOICEFORMTEST") STORAGE Object-relational TYPE "INVOICEFORMTEST332_T"

set linesize 150


25

Create Schema-Based XMLType Column

create table invoiceformtestXML_col (inv_id number primary key,inv_doc XMLType)XMLTYPE COLUMN inv_doc xmlschema "http://xmlns.oracle.com/xdb/invoiceformtest.xsd"Element "INVOICEFORMTEST";

create table invoiceformtestXML_col (inv_id number primary key,inv_doc XMLType)XMLTYPE COLUMN inv_doc xmlschema "http://xmlns.oracle.com/xdb/invoiceformtest.xsd"Element "INVOICEFORMTEST";

@create_schema_column@create_schema_column

1.26

V2.1

@insert4truncate table invoiceformtestXML_col;insert into invoiceformtestXML_col values (1, XMLType(bfilename('XMLDIR', 'invoiceformtest.txt'),nls_charset_id('AL32UTF8')));

-- Query dataset long 32000column inv_doc format a40

select * from invoiceformtestXML_col;

INV_ID INV_DOC---------- ----------------------------------------

1 <INVOICEFORMTEST><MailAddressTo id="1"><Person>Joe Smith</Person><Street>10 Apple Tree Lane</Street><City>New York</City><State>NY</State><Zipcode>12345</Zipcode>


clear columns


26

Insert Data

insert into invoiceformtestXML_col values (1, XMLType(bfilename('XMLDIR', 'invoiceformtest.txt'),nls_charset_id('AL32UTF8')));

-- Query dataset long 32000select * from invoiceformtestXML_tbl;

insert into invoiceformtestXML_col values (1, XMLType(bfilename('XMLDIR', 'invoiceformtest.txt'),nls_charset_id('AL32UTF8')));

-- Query dataset long 32000select * from invoiceformtestXML_tbl;

@insert4@insert4

1.27

V2.1

Now let’s try to insert an XML document that is not in the format specified by the implemented schema. Such a file is invoiceformtestinvalid.txt: <INVOICEFORMTEST>

<MailAddressTo id="1"><Person>Joe Smith</Person><Street>10 Apple Tree Lane</Street><City>New York</City><State>NY</State><Zipcode>12345</Zipcode><Country>USA</Country> No Country tag in schema!


@insert5insert into invoiceformtestXML_col values (2, XMLType(bfilename('XMLDIR', 'invoiceformtestinvalid.txt'),nls_charset_id('AL32UTF8')));

Insert into invoiceformtestXML_col values*

ERROR at line 1:ORA-30937: No schema definition for 'Country' (namespace '##local') in parent '/INVOICEFORMTEST/MailAddressTo'

Note again that Oracle validates the data. Schema validation is essentially an extension of Oracle’s constraint checking!


27

Validation at Insert<INVOICEFORMTEST>

<MailAddressTo id="1"><Person>Joe Smith</Person><Street>10 Apple Tree Lane</Street><City>New York</City><State>NY</State><Zipcode>12345</Zipcode><Country>USA</Country>


<INVOICEFORMTEST><MailAddressTo id="1">

<Person>Joe Smith</Person><Street>10 Apple Tree Lane</Street><City>New York</City><State>NY</State><Zipcode>12345</Zipcode><Country>USA</Country>


insert into invoiceformtestXML_col values (2, XMLType(bfilename('XMLDIR', 'invoiceformtestinvalid.txt'),nls_charset_id('AL32UTF8')));Insert into invoiceformtestXML_col values


insert into invoiceformtestXML_col values (2, XMLType(bfilename('XMLDIR', 'invoiceformtestinvalid.txt'),nls_charset_id('AL32UTF8')));Insert into invoiceformtestXML_col values


Doc does not adhere to XMLSchema; no Country tag in schema.

SQL INSERT fails

@insert5@insert5

1.28

V2.1


28

XQuery

XQuery standard developed by W3CQuery language to extract info from XML source

physical XML documents XML stored in relational database

XQuery is to XML as SQL is to relational databases

Just more complexXQuery uses XPath used to search

1.29

V2.1


29

XPath

Language for finding information in XML docPart of W3C standardSee www.w3c.org

Used to navigate XML documents

1.30

V2.1

@xpathselect object_value from invoicexml_tbl;

OBJECT_VALUE<Invoice>

<MailAddressTo id="PA"><Person>Joe Smith</Person><Street>10 Apple Tree Lane</Street><City>New York</City><State>NY</State><Zipcode>12345</Zipcode>

</MailAddressTo><MailAddressFrom id="PA">

<Person>Ed Jones</Person><Street>11 Cherry Lane</Street><City>Newark</City><State>NJ</State><Zipcode>67890</Zipcode>

</MailAddressFrom><Details id="2006Sept1to30PA">

<FromTo>Sept 1, 2006 to Sept 30, 2006</FromTo><Hours>70</Hours><Rate>30</Rate><Taxes>210</Taxes><TotalDue>2310</TotalDue><InvDate>Oct 1, 2006</InvDate><Contractor>Ed Jones</Contractor>

</Details></Invoice> </Invoice>


30

XPath Examplesselect object_value from invoicexml_tbl;




<Person>Ed Jones</Person><Street>11 Cherry Lane</Street><City>Newark</City>

(etcetera)

select object_value from invoicexml_tbl;




<Person>Ed Jones</Person><Street>11 Cherry Lane</Street><City>Newark</City>

(etcetera)

XML source doc for XPath examples

@xpath@xpath

1.31

V2.1

@extractselect extract(object_value, '/Invoice/MailAddressTo')from invoicexml_tbl;

EXTRACT(OBJECT_VALUE,'/INVOICE/MAILADDRESSTO')<MailAddressTo id="PA"><Person>Joe Smith</Person><Street>10Apple Tree Lane</Street><City>NewYork</City><State>NY</State><Zipcode>12345</Zipcode></MailAddressTo>

select extract(object_value, './/Person') from invoicexml_tbl;

EXTRACT(OBJECT_VALUE,'.//PERSON')<Person>Joe Smith</Person><Person>Ed Jones</Person>


31

extract

Use extract function to select Individual node and its leaf nodes Or a set of nodes and their leaf nodes

select extract(object_value, '/Invoice/MailAddressTo')from invoicexml_tbl;

EXTRACT(OBJECT_VALUE,'/INVOICE/MAILADDRESSTO')<MailAddressTo id="PA"><Person>Joe Smith</Person><Street>10Apple Tree Lane</Street><City>New York</City><State>NY</State><Zipcode>12345</Zipcode></MailAddressTo>

select extract(object_value, '/Invoice/MailAddressTo')from invoicexml_tbl;

EXTRACT(OBJECT_VALUE,'/INVOICE/MAILADDRESSTO')<MailAddressTo id="PA"><Person>Joe Smith</Person><Street>10Apple Tree Lane</Street><City>New York</City><State>NY</State><Zipcode>12345</Zipcode></MailAddressTo>

XPath expression





XPath expression

@extract@extract

1.32

V2.1

@extractValueselect extractValue(object_value, '/Invoice/MailAddressTo/Person')as Personfrom invoicexml_tbl;

PERSONJoe Smith

select extractValue(object_value, '/Invoice/MailAddressTo/@id')as Idfrom invoicexml_tbl;

IDPA

select extractValue(object_value, '/Invoice/MailAddressTo')as MailAddressTofrom invoicexml_tbl;from invoicexml_tbl

*ERROR at line 2:ORA-19025: EXTRACTVALUE returns value of only one node


32

extractValue

Select data value that exists in a leaf node with extractValueHigher level node cannot be extractedoutput not in XML-syntax format

select extractValue(object_value, '/Invoice/MailAddressTo/Person')as Person

from invoicexml_tbl;

PERSONJoe Smith

select extractValue(object_value, '/Invoice/MailAddressTo/Person')as Person

from invoicexml_tbl;

PERSONJoe Smith

@extractValue@extractValue

1.33

V2.1

@existsNodeselect count(*) from invoicexml_tblwhere existsNode(object_value, '/Invoice/MailAddressTo[Person="Joe Smith"]') = 1;

COUNT(*)1

select count(*) from invoicexml_tblwhere existsNode(object_value, '/Invoice/MailAddressTo[Person="Marv Albert"]') = 1;

COUNT(*)0

select count(*) from invoicexml_tblwhere existsNode(object_value, '/Invoice/MailAddressTo[@id="PA"]') = 1;

COUNT(*)

1


33

existsNode

Search for specific values at the node level and only the node level

Returns True (1) or False (0)select count(*) from invoicexml_tblwhere existsNode(object_value, '/Invoice/MailAddressTo[Person="Joe Smith"]') = 1;

COUNT(*)--------

1

select count(*) from invoicexml_tblwhere existsNode(object_value, '/Invoice/MailAddressTo[Person="Joe Smith"]') = 1;

COUNT(*)--------

1


COUNT(*)--------

0


COUNT(*)--------

0

@existsNode@existsNode

1.34

V2.1

@XMLSequence-- Let’s compare extractValue and extract with XMLSequence using the -- Person nodes.

-- 1. extractValue:set feedback 1select extractValue(object_value, './/Person') from invoicexml_tbl; Select extractValue(object_value, './/Person') from invoicexml_tbl

*ERROR at line 1:ORA-19025: EXTRACTVALUE returns value of only one node

-- 2. extract:select extract(object_value, './/Person') from invoicexml_tbl;

EXTRACT(OBJECT_VALUE,'.//PERSON')<Person>Joe Smith</Person><Person>Ed Jones</Person> 1 row selected.

-- Note that while extract does return all the Person nodes, it-- returns them as a single row. We can fortunately overcome this-- limitation by restructuring the query and using XMLSequence, -- as shown below:

-- 3. XMLSequence:select value(people) from invoicexml_tbl i,

table(XMLSequence(extract(i.object_value, './/Person'))) people;

VALUE(PEOPLE)<Person>Joe Smith</Person><Person>Ed Jones</Person>

2 rows selected.


34

XMLSequenceUse to look at multiple nodes or a fragment Creates a virtual table of XMLType objects

select value(people) from invoicexml_tbl i,



2 rows selected.

select value(people) from invoicexml_tbl i,



2 rows selected.

Returns multiple rows

XMLSequence returns an array, and the table function converts the array into a table

@XMLSequence@XMLSequence

1.35

V2.1

FLOWR acts like SQL for XML documents, but more complex.


35

FLOWR

Say “flower”FOR, LET, WHERE, ORDER BY, and RETURN FOR binds one or more variables in an iterative mannerLET binds variables, can also be used to perform joinsWHERE filters data ORDER BY sortsRETURN returns final result

1.36

V2.1


36

ora: Functions

XML DB provides 5 more XQuery functionsview, contains, matches, replace, and sqrt

Implemented in http://xmlns.oracle.com/xdb namespaceUse the prefix “ora:” (i.e. ora:view, etc.)ora:view particularly valuable

it can be used to transform relational data into XMLexamples will be shown later

1.37

V2.1

The ora:contains function returns a positive number if the text is found (my tests indicate that is usually returns a 1). If the text is not found, then 0 is returned. ora:contains is generally used with existsNode, extract, or extractValue. You need to include the namespace mapping parameter xmlns:ora="http://xmlns.oracle.com/xdb" to identify the ora namespace prefix.

@oracontainsSELECT count(*) FROM invoicexml_tblWHERE existsNode(object_value, '/Invoice/MailAddressTo/Person

[ora:contains(text(), "Smith") > 0]', 'xmlns:ora="http://xmlns.oracle.com/xdb"') = 1;

COUNT(*)1

SELECT count(*) FROM invoicexml_tblWHERE existsNode(object_value, '/Invoice/MailAddressTo/Person

[ora:contains(text(), "Einstein") > 0]', 'xmlns:ora="http://xmlns.oracle.com/xdb"') = 1;

COUNT(*)0


37

ora:contains Function

Structural search with a text predicate



COUNT(*)1



COUNT(*)0



COUNT(*)1



COUNT(*)0

@oracontains@oracontains

1.38

V2.1


38

Other ora: Functions

ora:matches Regular expression searchLike REGEXP_LIKE

ora:replace Regular expression replaceLike REGEXP_REPLACE

ora:sqrt Square root

1.39

V2.1


39

XML DB Repository

XML doc stored in XML DB Repository is called an XML DB Resource2 important functions

fn:doc XQuery function that can obtain a repository file containing XML dataReturns a single XML document stored in XML DB repository

fn:collectionReturns documents stored in same folder in the repository

1.40

V2.1

@resourcesexec DBMS_XDB.DELETERESOURCE('/public/orders.xml')exec DBMS_XDB.DELETERESOURCE('/public/partys.xml')exec DBMS_XDB.DELETERESOURCE('/public/ordersnamespace.xml')

DECLAREres BOOLEAN;ordersxmlstring VARCHAR2(500):= '<?xml version="1.0"?><orders><order orderno="15" partyno="1111" itemname="Widget" amt="5000"/><order orderno="25" partyno="1111" itemname="Do dad" amt="2000"/><order orderno="35" partyno="2222" itemname="All purpose item" amt="7000"/><order orderno="45" partyno="3333" itemname="The best thing" amt="15000"/>

</orders>';partysxmlstring VARCHAR2(500):='<?xml version="1.0"?><partys><party partyno="1111" partyname="ABC Corp" partycity="Toronto"/><party partyno="2222" partyname="Freds Inc" partycity="Chicago"/><party partyno="3333" partyname="Gofaster Corp" partycity="Montreal"/>

</partys>';ordersxmlnsstring VARCHAR2(500):='<?xml version="1.0"?><orders xmlns="http://order.com"><order orderno="15" partyno="1111" itemname="Widget" amt="5000"/><order orderno="25" partyno="1111" itemname="Do dad" amt="2000"/><order orderno="35" partyno="2222" itemname="All purpose item" amt="7000"/><order orderno="45" partyno="3333" itemname="The best thing" amt="15000"/>

</orders>';BEGINres := DBMS_XDB.createResource('/public/orders.xml', ordersxmlstring);res := DBMS_XDB.createResource('/public/partys.xml', partysxmlstring);res := DBMS_XDB.createResource('/public/ordersnamespace.xml', ordersxmlnsstring);

END;/

select any_path, res from resource_view where any_path like '%public%';


40

Repository Example…

Create the orders and partys resources

DECLAREres BOOLEAN;ordersxmlstring VARCHAR2(500):=

'<?xml version="1.0"?><orders>

<order orderno="15" partyno="1111" itemname="Widget" amt="500<order orderno="25" partyno="1111" itemname="Do dad" amt="2000<order orderno="35" partyno="2222" itemname="All purpose item" <order orderno="45" partyno="3333" itemname="The best thing" a

</orders>';

(etcetera)

DECLAREres BOOLEAN;ordersxmlstring VARCHAR2(500):=

'<?xml version="1.0"?><orders>

<order orderno="15" partyno="1111" itemname="Widget" amt="500<order orderno="25" partyno="1111" itemname="Do dad" amt="2000<order orderno="35" partyno="2222" itemname="All purpose item" <order orderno="45" partyno="3333" itemname="The best thing" a

</orders>';

(etcetera)

select any_path, res from resource_view where any_path like '%partys%';

select any_path, res from resource_view where any_path like '%partys%';

Retrieve resources created

@resources@resources

1.41

V2.1

@fndocSELECT XMLQuery('for $p in fn:doc("/public/partys.xml")

return $p'RETURNING CONTENT) as partys FROM DUAL;

PARTYS---------------------------------------------------------------------<partys><party partyno="1111" partyname="ABC Corp" partycity="Toronto"/><party partyno="2222" partyname="Freds Inc"partycity="Chicago"/> <party partyno="3333" partyname="Gofaster Corp"partycity="Montreal"/></partys>


41

…Repository ExampleDisplay a single partys.xml document using fn:doc

More on XMLQuery and FLOWR later

SELECT XMLQuery('for $p in fn:doc("/public/partys.xml") return $p'RETURNING CONTENT)as partys FROM DUAL;

PARTYS--------------------------------------------------------------<partys><party partyno="1111" partyname="ABC Corp" partycity="Toronto"/><party partyno="2222" partyname="Freds Inc"partycity="Chicago"/> <party partyno="3333" partyname="Gofaster Corp"partycity="Montreal"/></partys>

SELECT XMLQuery('for $p in fn:doc("/public/partys.xml") return $p'RETURNING CONTENT)as partys FROM DUAL;

PARTYS--------------------------------------------------------------<partys><party partyno="1111" partyname="ABC Corp" partycity="Toronto"/><party partyno="2222" partyname="Freds Inc"partycity="Chicago"/> <party partyno="3333" partyname="Gofaster Corp"partycity="Montreal"/></partys>

@fndoc@fndoc

1.42

V2.1

@fncollectionSELECT XMLQuery('for $p in fn:collection("/public")

return $p'RETURNING CONTENT) as collection_public FROM DUAL;

COLLECTION_PUBLIC----------------------------------------------------------------------<orders>

<order orderno="15" partyno="1111" itemname="Widget" amt="5000"/><order orderno="25" partyno="1111" itemname="Do dad" amt="2000"/><order orderno="35" partyno="2222" itemname="All purpose item" amt="7000"/><order orderno="45" partyno="3333" itemname="The best thing" amt="15000"/>

</orders>

<orders xmlns="http://order.com"><order orderno="15" partyno="1111" itemname="Widget" amt="5000"/><order orderno="25" partyno="1111" itemname="Do dad" amt="2000"/><order orderno="35" partyno="2222" itemname="All purpose item" amt="7000"/><order orderno="45" partyno="3333" itemname="The best thing" amt="15000"/>

</orders>

<partys><party partyno="1111" partyname="ABC Corp" partycity="Toronto"/><party partyno="2222" partyname="Freds Inc" partycity="Chicago"/><party partyno="3333" partyname="Gofaster Corp" partycity="Montreal"/>

</partys>

1 row selected.

Note: Output from XMLQuery is returned as one row, and is not pretty-printed. Output above was reformatted to make it easier to read.


42

fn:collection Return all docs stored in the same folder in the repository

Returned as one row, not pretty-printed

SELECT XMLQuery('for $p in fn:collection("/public") return $p'RETURNING CONTENT)as collection_public FROM

DUAL;

COLLECTION_PUBLIC--------------------------------------------------------------<orders>

<order orderno="15" partyno="1111" itemname="Widget" amt="5<order orderno="25" partyno="1111" itemname="Do dad" amt="2

(etcetera)

SELECT XMLQuery('for $p in fn:collection("/public") return $p'RETURNING CONTENT)as collection_public FROM

DUAL;

COLLECTION_PUBLIC--------------------------------------------------------------<orders>

<order orderno="15" partyno="1111" itemname="Widget" amt="5<order orderno="25" partyno="1111" itemname="Do dad" amt="2

(etcetera)

@fncollection@fncollection

1.43

V2.1


43

XMLQuery() and XMLTable()

Functions introduced in Oracle 10g Release 2Interface between SQL and XMLAllow query, construct, and transform relational data like it is XML

And XML like it is relational

XMLQuery() can be used to generate XML document from XML or Relational data

Can query the XML result with XQuery

XMLTable() to create relational views from XML dataCan the query the relational result with SQL

1.44

V2.1

Take a look at the partys.xml and orders.xml documents shown earlier. Each party is identified by a partyno. There can be multiple orders for each party.

This query above locates all parties with more than one order, and generates an XML document with a big-party element containing the partyno, the number of orders, and the average order amount.

@XMLQuery1SELECT XMLQuery('for $p in fn:doc("/public/partys.xml")/partys/party/@partynolet $o := fn:doc("/public/orders.xml")/orders/order[@partyno = $p]

where fn:count($o) > 1order by fn:avg($o/@amt) descendingreturn <big-party><partyno>{$p}</partyno>

<ordercount>{fn:count($o)}</ordercount><avgamt>{xs:integer(fn:avg($o/@amt))}</avgamt>

</big-party>'RETURNING CONTENT) as ORDERS FROM DUAL;

ORDERS-------------------------------------------------<big-party><partyno>1111</partyno><ordercount>2</ordercount><avgamt>3500</avgamt></big-party>


44

XMLQuery() and FLOWRQuery and join two XML documents:

partys.xml, orders.xml On partyno key

SELECT XMLQuery('for $p in fn:doc("/public/partys.xml")/partys/party/@partyno

let $o := fn:doc("/public/orders.xml")/orders/order[@partyno = $p]where fn:count($o) > 1order by fn:avg($o/@amt) descendingreturn <big-party><partyno>{$p}</partyno>



ORDERS<big-party><partyno>1111</partyno><ordercount>2</ordercount><avgamt>3500</avgamt></big-party>

SELECT XMLQuery('for $p in fn:doc("/public/partys.xml")/partys/party/@partyno

let $o := fn:doc("/public/orders.xml")/orders/order[@partyno = $p]where fn:count($o) > 1order by fn:avg($o/@amt) descendingreturn <big-party><partyno>{$p}</partyno>



ORDERS<big-party><partyno>1111</partyno><ordercount>2</ordercount><avgamt>3500</avgamt></big-party>

@XMLQuery1@XMLQuery1

1.45

V2.1

-- First. convert a relational table to XML output:-- Set up test tables.@XMLQuery2drop table department cascade constraints;create table department (dept_no number, dept_name varchar2(20));insert into department values (1, 'President');insert into department values (2, 'Scientist');

drop table employee cascade constraints;create table employee (emp_no number, firstname varchar2(20),lastname varchar2(20), dept_no number);

insert into employee values (1, 'Albert', 'Einstein', 2);insert into employee values (2, 'Neils', 'Bohr', 2);insert into employee values (3, 'Marie', 'Curie', 2);insert into employee values (4, 'Franklin', 'Roosevelt', 1);

-- Convert a relational table to XML output:SELECT XMLQuery(

'for $dep in ora:view("DEPARTMENT")return $dep' RETURNING CONTENT) AS ORAVIEW

FROM dual;

ORAVIEW--------------------------------------------------------------------<ROW><DEPT_NO>1</DEPT_NO><DEPT_NAME>President</DEPT_NAME></ROW><ROW><DEPT_NO>2</DEPT_NO><DEPT_NAME>Scientist</DEPT_NAME></ROW>

(etcetera)


45

XMLQuery() on Relational Data…

First, user ora:view to view relational tables as XML



FROM dual;ORAVIEW-----------------------------------------------------------------<ROW><DEPT_NO>1</DEPT_NO><DEPT_NAME>President</DEPT_NAME></ROW><ROW><DEPT_NO>2</DEPT_NO><DEPT_NAME>Scientist</DEPT_NAME></ROW>



FROM dual;ORAVIEW-----------------------------------------------------------------<ROW><DEPT_NO>1</DEPT_NO><DEPT_NAME>President</DEPT_NAME></ROW><ROW><DEPT_NO>2</DEPT_NO><DEPT_NAME>Scientist</DEPT_NAME></ROW>


1.46

V2.1

-- Second, user XQuery expressions to generate XML from Relational data:@XMLQuery3SELECT XMLQuery(

'<Departments>{for $dep in ora:view("DEPARTMENT")let $dept_no := $dep/ROW/DEPT_NO/text(),

$dept_name := $dep/ROW/DEPT_NAME/text()return

<Department id="{$dept_no}"><DepartmentName>{$dept_name}</DepartmentName><Employees>{for $emp in ora:view("EMPLOYEE")let $emp_no := $emp/ROW/EMP_NO/text(),

$emp_dept_no := $emp/ROW/DEPT_NO/text(),$lastname := $emp/ROW/LASTNAME/text(),$firstname := $emp/ROW/FIRSTNAME/text()

where $emp_dept_no = $dept_no return<Employee id="{$emp_no}">

<FirstName>{$firstname}</FirstName><LastName>{$lastname}</LastName>

</Employee>}

</Employees></Department>

}</Departments>' RETURNING CONTENT) AS XML_FROM_JOIN

FROM dual;

XML_FROM_JOIN<Departments><Department id="1"><DepartmentName>President</DepartmentName><Employees><Employee id="4"><FirstName>Franklin</FirstName><LastName>Roosevelt</LastName></Employee></Employees></Department><Department id="2"><DepartmentName>Scientist</DepartmentName><Employees><Employee id="1"><FirstName>Albert</FirstName><LastName>Einstein</LastName></Employee><Employee id="2"><FirstName>Neils</FirstName><LastName>Bohr</LastName></Employee><Employee id="3"><FirstName>Marie</FirstName><LastName>Curie</LastName></Employee></Employees></Department></Departments>


46

…XMLQuery() on Relational Data…Second, use XQuery expressions to generate XML from Relational Data

Join relational tablesSELECT XMLQuery(







</Employee>}



FROM dual;

SELECT XMLQuery('<Departments>{for $dep in ora:view("DEPARTMENT")let $dept_no := $dep/ROW/DEPT_NO/text(),






</Employee>}



FROM dual;


1.47

V2.1


47

XMLTable() Function…

Enables an XML value to be interpreted as a table or a setUsed to return table and columns from XQuery expressionsRather than returning a sequence as XQuery would normally doXMLTable() function used in a SQL From clause

1.48

V2.1

Previously we used ora:view to treat relational data as XML data. We then were able to use XQuery on the resulting XML data.

Here we use XMLTable to treat XML data as relational data. We then are able to use SQL on the resulting relational data.

See next page for full code example.


48

…XMLTable() FunctionUse XMLTable function to treat an XMLType column as a relational tableUse SQL to query the resulting relational table

SELECT inv_id, a.PersonName, a.StreetName, a.CityName, a.State, a.ZipcodeFROM invoicexml_col,

XMLTABLE('/Invoice'PASSING invoicexml_col.inv_docCOLUMNS

PersonName varchar2(10) PATH '/Invoice/MailAddressTo/Person',StreetName varchar2(20) PATH '/Invoice/MailAddressTo/Street',CityName varchar2(10) PATH '/Invoice/MailAddressTo/City',State varchar2(5) PATH '/Invoice/MailAddressTo/State',Zipcode varchar2(7) PATH '/Invoice/MailAddressTo/Zipcode'

) aWHERE a.CityName like 'New%';

INV_ID PERSONNAME STREETNAME CITYNAME STATE ZIPCODE------ ---------- ----------- --------- ----- --------

1 Joe Smith 10 Apple Tree Lane New York NY 12345

SELECT inv_id, a.PersonName, a.StreetName, a.CityName, a.State, a.ZipcodeFROM invoicexml_col,

XMLTABLE('/Invoice'PASSING invoicexml_col.inv_docCOLUMNS



INV_ID PERSONNAME STREETNAME CITYNAME STATE ZIPCODE------ ---------- ----------- --------- ----- --------


@XMLTable1@XMLTable1

1.49

V2.1

XMLTable() Function code:

@XMLTable1desc invoicexml_colName Null? Type----------------------- -------- ----------------INV_ID NOT NULL NUMBERINV_DOC XMLTYPE

column inv_doc format a60select * from invoicexml_col;

INV_ID INV_DOC---------- --------------------------------------------------

1 <Invoice><MailAddressTo id="PA"><Person>Joe Smith</Person><Street>10 Apple Tree Lane</Street><City>New York</City><State>NY</State><Zipcode>12345</Zipcode>

</MailAddressTo><MailAddressFrom id="PA"><Person>Ed Jones</Person><Street>11 Cherry Lane</Street><City>Newark</City><State>NJ</State><Zipcode>67890</Zipcode>

</MailAddressFrom><Details id="2006Sept1to30PA"><FromTo>Sept 1, 2006 to Sept 30, 2006</FromTo><Hours>70</Hours><Rate>30</Rate><Taxes>210</Taxes><TotalDue>2310</TotalDue><InvDate>Oct 1, 2006</InvDate><Contractor>Ed Jones</Contractor>

</Details></Invoice>

clear columns

set linesize 150SELECT inv_id, a.PersonName, a.StreetName, a.CityName, a.State, a.ZipcodeFROM invoicexml_col,

XMLTABLE('/Invoice' PASSING invoicexml_col.inv_docCOLUMNS



INV_ID PERSONNAME STREETNAME CITYNAME STATE ZIPCODE---------- ---------- -------------------- ---------- ----- -------


1.50

V2.1


50

XML Performance

Create indexes to help improve the performance

Improve performance of XPath functions like we’ve tuned SQL in the past

Use function-based indexes on structured and unstructured XMLType'sConsider binary indexes for functions like existsNode

Return flags of 0 or 1

1.51

V2.1

@tuning1

drop table department cascade constraints;create table department (dept_no number, dept_name varchar2(20));insert into department values (1, 'President');insert into department values (2, 'Scientist');insert into department values (3, 'Wannabepresident');

drop table employee cascade constraints;create table employee (emp_no number, firstname varchar2(20),lastname varchar2(20), dept_no number, big_char_column char(200));

insert into employee values (1, 'Albert', 'Einstein', 2, 'junk');insert into employee values (2, 'Neils', 'Bohr', 2, 'junk');insert into employee values (3, 'Marie', 'Curie', 2, 'junk');insert into employee values (4, 'Franklin', 'junk', 1,

'Einstein');

insert into EMPLOYEE select * from employee;////////////////

insert into employee values (5, 'Hillary', 'Clinton', 3, 'junk');insert into employee values (6, 'Barack', 'Obama', 3, 'junk');insert into employee values (7, 'John', 'McCain', 3, 'junk');insert into employee values (8, 'Rudy', 'Guiliani', 3, 'junk');

-- continued on next page…


51

Tuning Example…

set autotrace onSELECT XMLQuery(


$dept_name := $dep/ROW/DEPT_NAME/text()where $dep/ROW/DEPT_NO/text() = 3return

(etcetera)

Execution Plan----------------------------------------------------------Plan hash value: 3895618712

---------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 2 (0)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | 50 | | ||* 2 | TABLE ACCESS FULL| EMPLOYEE | 5270 | 257K| 3648 (2)| 00:00:44 || 3 | SORT AGGREGATE | | 1 | 25 | | ||* 4 | TABLE ACCESS FULL| DEPARTMENT | 1 | 25 | 3 (0)| 00:00:01 || 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |---------------------------------------------------------------------------------

set autotrace onSELECT XMLQuery(



(etcetera)



Index not used

@tuning1@tuning1

1.52

V2.1

exec dbms_stats.gather_table_stats(user, 'DEPARTMENT')exec dbms_stats.gather_table_stats(user, 'EMPLOYEE')select count(*) from DEPARTMENT;COUNT(*)

----------3

select count(*) from EMPLOYEE;COUNT(*)

----------524292

set linesize 150set autotrace onSELECT XMLQuery(





where $emp_dept_no = $dept_noreturn

<Employee id="{$emp_no}"><FirstName>{$firstname}</FirstName><LastName>{$lastname}</LastName>

</Employee>}



FROM dual;set autotrace off

XML_FROM_JOIN=============<Departments><Department id="3"><DepartmentName>Wannabepresident</DepartmentName><Employees><Employee id="5"><FirstName>Hillary</FirstName><LastName>Clinton</LastName></Employee><Employee id="6"><FirstName>Barack</FirstName><LastName>Obama</LastName></Employee><Employee id="7"><FirstName>John</FirstName><LastName>McCain</LastName></Employee><Employee id="8"><FirstName>Rudy</FirstName><LastName>Guiliani</LastName></Employee></Employees></Department></Departments>



Predicate Information (identified by operation id):---------------------------------------------------

2 - filter("DEPT_NO"=:B1)4 - filter("DEPT_NO"=3)

Note-----

- dynamic sampling used for this statement

Index not used

1.53

V2.1

Note that the index on the DEPARTMENT table is used. However, performance is not significantly improved since there are only 3 rows in the DEPARTMENT table.@tuning2-- Create index and compute statistics.drop index emp_idx1;drop index dept_idx1;create index emp_idx1 on employee (dept_no);create index dept_idx1 on department (dept_no);exec dbms_stats.gather_table_stats(user, 'DEPARTMENT')exec dbms_stats.gather_table_stats(user, 'EMPLOYEE')

-- Rerun query with filter on DEPARTMENT table.set autotrace onSELECT XMLQuery(







</Employee>}



FROM dual;set autotrace off

-- Code example continued on next page . . .


53

…Tuning Example…Create indexes to improve performanceRun query with filter on DEPARTMENT table

create index emp_idx1 on employee (dept_no);create index dept_idx1 on department (dept_no);exec dbms_stats.gather_table_stats(user, 'DEPARTMENT')exec dbms_stats.gather_table_stats(user, 'EMPLOYEE')

create index emp_idx1 on employee (dept_no);create index dept_idx1 on department (dept_no);exec dbms_stats.gather_table_stats(user, 'DEPARTMENT')exec dbms_stats.gather_table_stats(user, 'EMPLOYEE')


-------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 2 (0)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | 21 | | ||* 2 | TABLE ACCESS FULL | EMPLOYEE | 262K| 5374K| 3648 (2)| 00:00:44 || 3 | SORT AGGREGATE | | 1 | 15 | | || 4 | TABLE ACCESS BY INDEX ROWID| DEPARTMENT | 1 | 15 | 2 (0)| 00:00:01 ||* 5 | INDEX RANGE SCAN | DEPT_IDX1 | 1 | | 1 (0)| 00:00:01 || 6 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |-------------------------------------------------------------------------------------------


2 - filter("DEPT_NO"=:B1)5 ("DEPT NO" 3)


-------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 2 (0)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | 21 | | ||* 2 | TABLE ACCESS FULL | EMPLOYEE | 262K| 5374K| 3648 (2)| 00:00:44 || 3 | SORT AGGREGATE | | 1 | 15 | | || 4 | TABLE ACCESS BY INDEX ROWID| DEPARTMENT | 1 | 15 | 2 (0)| 00:00:01 ||* 5 | INDEX RANGE SCAN | DEPT_IDX1 | 1 | | 1 (0)| 00:00:01 || 6 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |-------------------------------------------------------------------------------------------


2 - filter("DEPT_NO"=:B1)5 - access("DEPT NO"=3)

Create indexes

Retest query, index used, but minimal improvement because only 3 rows in DEPARTMENT table

@tuning2@tuning2

1.54

V2.1

XML_FROM_JOIN<Departments><Department id="3"><DepartmentName>Wannabepresident</DepartmentName><Employees><Employee id="5"><FirstName>Hillary</FirstName><LastName>Clinton</LastName></Employee><Employee id="6"><FirstName>Barack</FirstName><LastName>Obama</LastName></Employee><Employee id="7"><FirstName>John</FirstName><LastName>McCain</LastName></Employee><Employee id="8"><FirstName>Rudy</FirstName><LastName>Guiliani</LastName></Employee></Employees></Department></Departments>


---------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 2 (0)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | 21 | | ||* 2 | TABLE ACCESS FUL | EMPLOYEE | 262K| 5374K| 3648 (2)| 00:00:44 || 3 | SORT AGGREGATE | | 1 | 15 | | || 4 | TBL ACCESS BY INDEX ROWID| DEPARTMENT | 1 | 15 | 2 (0)| 00:00:01 ||* 5 | INDEX RANGE SCAN | DEPT_IDX1 | 1 | | 1 (0)| 00:00:01 || 6 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |---------------------------------------------------------------------------------------


2 - filter("DEPT_NO"=:B1)5 - access("DEPT_NO"=3)


Retest query, index used, but minimal improvement because only 3 rows in DEPARTMENT table

1.55

V2.1

Note that the index on the EMPLOYEE table is used. Performance is significantly improved.@tuning3-- Rerun query with filter on EMPLOYEE table.set autotrace onSELECT XMLQuery(





where ($emp_dept_no = $dept_no) and($emp_dept_no = 3)return

<Employee id="{$emp_no}"><FirstName>{$firstname}</FirstName><LastName>{$lastname}</LastName>

</Employee>}



FROM dual/set autotrace off



55

…Tuning ExampleRerun query with filter on EMPLOYEE table:


-------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 2 (0)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | 21 | | || 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEE | 1 | 21 | 4 (0)| 00:00:01 ||* 3 | INDEX RANGE SCAN | EMP_IDX1 | 1 | | 3 (0)| 00:00:01 || 4 | SORT AGGREGATE | | 1 | 15 | | || 5 | TABLE ACCESS FULL | DEPARTMENT | 3 | 45 | 3 (0)| 00:00:01 || 6 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |-------------------------------------------------------------------------------------------


3 - access("DEPT_NO"=3)filter("DEPT_NO"=:B1)


-------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 2 (0)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | 21 | | || 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEE | 1 | 21 | 4 (0)| 00:00:01 ||* 3 | INDEX RANGE SCAN | EMP_IDX1 | 1 | | 3 (0)| 00:00:01 || 4 | SORT AGGREGATE | | 1 | 15 | | || 5 | TABLE ACCESS FULL | DEPARTMENT | 3 | 45 | 3 (0)| 00:00:01 || 6 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |-------------------------------------------------------------------------------------------



Retest query, index used, with significant improvement in performance

@tuning3@tuning3

1.56

V2.1

XML_FROM_JOIN------------------------------------------------------------<Departments><Department id="1"><DepartmentName>President</DepartmentName><Employees></Employees></Department><Department id="2"><DepartmentName>Scientist</DepartmentName><Employees></Employees></Department><Department id="3"><DepartmentName>Wannabepresident</DepartmentName><Employees><Employee id="5"><FirstName>Hillary</FirstName><LastName>Clinton</LastName></Employee><Employee id="6"><FirstName>Barack</FirstName><LastName>Obama</LastName></Employee><Employee id="7"><FirstName>John</FirstName><LastName>McCain</LastName></Employee><Employee id="8"><FirstName>Rudy</FirstName><LastName>Guiliani</LastName></Employee></Employees></Department></Departments>


---------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 2 (0)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | 21 | | || 2 | TBL ACCESS BY INDEX ROWID| EMPLOYEE | 1 | 21 | 4 (0)| 00:00:01 ||* 3 | INDEX RANGE SCAN | EMP_IDX1 | 1 | | 3 (0)| 00:00:01 || 4 | SORT AGGREGATE | | 1 | 15 | | || 5 | TABLE ACCESS FULL | DEPARTMENT | 3 | 45 | 3 (0)| 00:00:01 || 6 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |---------------------------------------------------------------------------------------



Note that the EMPLOYEE index is used, the results show all departments, but only employees in department 3! This is similar to an Outer Join on the DEPARTMENT table. And the costs are significantly reduced since a full table scan of the EMPLOYEE table is not required.

Our tuning of this XQuery expression using ora:view is very similar to our tuning of standard, non-XML SQL. XQuery will automatically utilize indexes as warranted.


Retest query, index used, with significant improvement in performance

1.57

V2.1

-- First run query with no index.desc invoicexml_tblselect * from invoicexml_tbl;drop index invoicexml_tbl_idx1;exec dbms_stats.gather_table_stats(user, 'invoicexml_tbl')set autotrace onselect extract(object_value, '/Invoice/MailAddressTo')from invoicexml_tblwhere extractValue(object_value, '/Invoice/MailAddressTo/Person')= 'Joe Smith';set autotrace off

EXTRACT(OBJECT_VALUE,'/INVOICE/MAILADDRESSTO')------------------------------------------------------------<MailAddressTo id="PA"><Person>Joe Smith</Person><Street>10Apple Tree Lane</Street><City>New York</City><State>NY</State><Zipcode>12345</Zipcode></MailAddressTo>

Execution Plan----------------------------------------------------------Plan hash value: 2364304528---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------| 0 |SELECT STMT | | 1 | 87 | 4 (0)| 00:00:01 ||* 1 |TBL ACC FULL|INVOICEXML_TBL| 1 | 87 | 4 (0)| 00:00:01 |---------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------

1 - filter(EXTRACTVALUE(SYS_MAKEXML("INVOICEXML_TBL"."XMLDATA"),'/Invoice/MailAddressTo/Person')='Joe Smith')



57

Optimizing structured XMLType dataIndexes can be used with both structured and unstructured dataStructured data access is generally more efficient than unstructured access. Optimize both with function-based indexes when using extractValue and similar functions

create index invoicexml_tbl_idx1 on invoicexml_tbl(extractValue(object_value, '/Invoice/MailAddressTo/Person'));

select extract(object_value, '/Invoice/MailAddressTo')from invoicexml_tblwhere extractValue(object_value, '/Invoice/MailAddressTo/Person')= 'Joe Smith';

Execution Plan----------------------------------------------------------Plan hash value: 4044500576----------------------------------------------------------------------------------------| Id | Operation | Name |Rows| Bytes|Cost (%CPU)| Time |----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 87 | 2 (0)| 00:00:01 || 1 | TBL ACCESS BY IDX ROWID| INVOICEXML_TBL | 1 | 87 | 2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | INVOICEXML_TBL_IDX1 | 1 | | 1 (0)| 00:00:01 |----------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------

2 - access(EXTRACTVALUE(SYS_MAKEXML("XMLDATA"),'/Invoice/MailAddressTo/Person')='JoeSmith')

create index invoicexml_tbl_idx1 on invoicexml_tbl(extractValue(object_value, '/Invoice/MailAddressTo/Person'));


Execution Plan----------------------------------------------------------Plan hash value: 4044500576----------------------------------------------------------------------------------------| Id | Operation | Name |Rows| Bytes|Cost (%CPU)| Time |----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 87 | 2 (0)| 00:00:01 || 1 | TBL ACCESS BY IDX ROWID| INVOICEXML_TBL | 1 | 87 | 2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | INVOICEXML_TBL_IDX1 | 1 | | 1 (0)| 00:00:01 |----------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------


@tuning4@tuning4

1.58

V2.1

-- Now create a function-based index and rerun query.

create index invoicexml_tbl_idx1 on invoicexml_tbl(extractValue(object_value, '/Invoice/MailAddressTo/Person'));exec dbms_stats.gather_table_stats(user, 'invoicexml_tbl')

set autotrace on


set autotrace off


Execution Plan----------------------------------------------------------Plan hash value: 4044500576----------------------------------------------------------------------------------------| Id | Operation | Name |Rows| Bytes|Cost (%CPU)| Time |----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 87 | 2 (0)| 00:00:01 || 1 | TBL ACCESS BY IDX ROWID| INVOICEXML_TBL | 1 | 87 | 2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | INVOICEXML_TBL_IDX1 | 1 | | 1 (0)| 00:00:01 |----------------------------------------------------------------------------------------



Indexes can be used effectively with structured and unstructured data.

Structured data access is generally more efficient than unstructured access.


1.59

V2.1

-- First run query with no index.create table invtest_unstruct of XMLtype XMLType store as CLOB;

Insert into invtest_unstruct values ( XMLType(bfilename('XMLDIR', 'invoicexml.txt'), nls_charset_id('WE8MSWIN1252') )); exec dbms_stats.gather_table_stats(user, 'INVTEST_UNSTRUCT')

set autotrace onselect extract(object_value, '/Invoice/MailAddressTo')from invtest_unstructwhere extractValue(object_value, '/Invoice/MailAddressTo/Person')='Joe Smith';set autotrace off



59

Optimizing UNstructured XMLType dataAgain, optimize with function-based indexes

create index invtest_unstruct_idx1 on invtest_unstruct(extractValue(object_value, '/Invoice/MailAddressTo/Person'));

select extract(object_value, '/Invoice/MailAddressTo')from invtest_unstructwhere extractValue(object_value, '/Invoice/MailAddressTo/Person')='Joe Smith';

EXTRACT(OBJECT_VALUE,'/INVOICE/MAILADDRESSTO')------------------------------------------------------------<MailAddressTo id="PA"><Person>Joe Smith</Person><Street>10Apple Tree Lane</Street><City>New York</City><State>NY</State><Zipcode>12345</Zipcode></MailAddressTo> Execution Plan----------------------------------------------------------Plan hash value: 2915445422-----------------------------------------------------------------------------------------| Id | Operation | Name |Rows|Bytes| Cost(%CPU)| Time |-----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 2002| 2 (0)| 00:00:01 || 1 | TBL ACCESS BY IDX ROWID| INVTEST_UNSTRUCT | 1 | 2002| 2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | INVTEST_UNSTRUCT_IDX1| 1 | | 1 (0)| 00:00:01 |-----------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------


create index invtest_unstruct_idx1 on invtest_unstruct(extractValue(object_value, '/Invoice/MailAddressTo/Person'));

select extract(object_value, '/Invoice/MailAddressTo')from invtest_unstructwhere extractValue(object_value, '/Invoice/MailAddressTo/Person')='Joe Smith';

EXTRACT(OBJECT_VALUE,'/INVOICE/MAILADDRESSTO')------------------------------------------------------------<MailAddressTo id="PA"><Person>Joe Smith</Person><Street>10Apple Tree Lane</Street><City>New York</City><State>NY</State><Zipcode>12345</Zipcode></MailAddressTo> Execution Plan----------------------------------------------------------Plan hash value: 2915445422-----------------------------------------------------------------------------------------| Id | Operation | Name |Rows|Bytes| Cost(%CPU)| Time |-----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 2002| 2 (0)| 00:00:01 || 1 | TBL ACCESS BY IDX ROWID| INVTEST_UNSTRUCT | 1 | 2002| 2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | INVTEST_UNSTRUCT_IDX1| 1 | | 1 (0)| 00:00:01 |-----------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------


@tuning5@tuning5

1.60

V2.1



-----------------------------------------------------------------------------------------| Id | Operation | Name |Rows|Bytes| Cost(%CPU)| Time |-----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 2002| 2 (0)| 00:00:01 || 1 | TBL ACCESS BY IDX ROWID| INVTEST_UNSTRUCT | 1 | 2002| 2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | INVTEST_UNSTRUCT_IDX1| 1 | | 1 (0)| 00:00:01 |-----------------------------------------------------------------------------------------



-- Now create a function-based index and rerun query.

create index invtest_unstruct_idx1 on invtest_unstruct(extractValue(object_value, '/Invoice/MailAddressTo/Person'));exec dbms_stats.gather_table_stats(user, 'INVTEST_UNSTRUCT')

-- Now see is index is used.set autotrace onselect extract(object_value, '/Invoice/MailAddressTo')from invtest_unstructwhere extractValue(object_value, '/Invoice/MailAddressTo/Person')='Joe Smith';set autotrace off



-----------------------------------------------------------------------------------------| Id | Operation | Name |Rows|Bytes| Cost(%CPU)| Time |-----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 2002| 2 (0)| 00:00:01 || 1 | TBL ACCESS BY IDX ROWID| INVTEST_UNSTRUCT | 1 | 2002| 2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | INVTEST_UNSTRUCT_IDX1| 1 | | 1 (0)| 00:00:01 |-----------------------------------------------------------------------------------------



1.61

V2.1

In-place XML schema evolution allows us to make changes to an XML schema without requiring that existing data to be copied, deleted, and reinserted.

Oracle binary XML is a compact representation of an XML document. This significantly reduces the amount of disk space required to store unstructured (schema-less) XML documents.

For a full description of the Oracle11g enhancements to XML, see the Oracle white paper titled New Features in Oracle XML DB for Oracle Database 11g Release 1 located at http://www.oracle.com/technology/products/database/oracle11g/pdf/xml-db-11g-whitepaper.pdf


61

11g Features

Features for increased flexibility and better performance

In-place XML schema evolutionPartitioning supportMore efficient schema-optimized storageSimplification of text based searches using Oracle TextEnhanced XQuery supportElimination of the 64k limit on size of text nodeOptimization of non-schema based XML operations (new binary XML format)

1.62

V2.1


62

SummaryOracle9i

Introduced XML DB repository, XMLType datatype, and XMLSchemaSupport for both LOB and structured storage optionsXPath expressions operate on individual elements of a document

10g enhancementsW3C XML XQuery languageXMLQuery() and XMLTable() functionsInterchangeable use of relational and XML data

SQL queries can operate on XML dataXML queries can access relational data

11g EnhancementsIn-place XML schema evolutionPartitioning support… plus more

1.63

V2.1


63

To Learn More…

SkillBuildersOnsite, Offsite Training, Online Classes (instructor-led!)

Just a few examples:Introduction to XMLUsing XML in OracleOracle Performance TuningSQL / Application TuningRAC11g Coming Very Soon!

To registerVisit www.skillbuilders.comOr call 1-888-803-5607

Java, J2EESOA, Web ServicesSoftware Security

1.64

V2.1

Copyright 2007-2008 Kyle Hailey. Distributed by www skillbuilders com

64

SkillBuilders Consulting

Oracle Performance TuningOracle AdministrationShort-term assignments (4 hour minimum)

Have a performance problem?Onsite or RemoteCall 1-888-803-5607

1.65

V2.1

Copyright 2007-2008 Kyle Hailey. Distributed by www skillbuilders com

65

Oracle Software VAR

Call us for your Oracle software requirementsDatabase and OptionsBusiness IntelligenceContent Management

Expert and Honest adviceCall 1-888-803-5607

1.66

V2.1

66

Thanks for Listening

[email protected]

xml in oracle - elte ikpeople.inf.elte.hu/kiss/10kor/oraclexmldownload-resource.pdf · 1.1 v2.1 1...

Documents