Oracle XMLDB：优化XML查询的最佳实践

5星 · 超过95%的资源需积分: 10 200 浏览量更新于2024-07-24 收藏 483KB PDF 举报

"Oracle XMLDB: Best Practices to Get Optimal Performance out of XML Queries" 这篇Oracle白皮书主要关注如何优化在Oracle数据库中处理XML查询的性能。Oracle XMLDB是Oracle数据库的一个组件，它提供了对XML数据的全面支持，包括存储、查询和管理。以下是白皮书中涉及的关键知识点和最佳实践： 1. SQL/XML & XQuery（页码7） - SQL/XML是SQL标准的一部分，允许在SQL查询中处理XML数据。XQuery则是一种更强大的查询语言，专门用于处理XML文档。文档建议将Oracle特有的XPath1.0语法迁移到基于标准SQL/XML和XQuery的语法，以提高兼容性和性能。 2. 从Oracle专有（XPath1.0基础）语法迁移到标准SQL/XML XQuery基础语法（页码10） - XPath是查询XML文档中节点的语言，而XPath1.0是Oracle早期支持的版本。升级到标准SQL/XML和XQuery可以利用更丰富的功能和优化，例如模块化、函数库和更好的数据类型支持。 3. 获取最佳的XQuery性能（页码13） - 白皮书强调了几个关键策略来提高XQuery执行的效率： - XQuery Guideline 1：使用`XMLExists()`和`XMLQuery()`函数搜索和转换存储在XMLDB中的XML数据。这两个函数提供了一种高效的方式来查找XML文档中的特定信息或执行转换。 - XQuery Guideline 2：使用`XMLExists()`配合XML DML操作符来搜索要修改的XML文档。这允许在更新操作前先进行条件判断，从而减少不必要的处理。 - XQuery Guideline 3：使用`XMLTable`构造查询XML数据，同时提供关系型访问模式。`XMLTable`将XML数据转换为表格形式，使得可以使用标准SQL语法进行查询，简化了复杂XML数据的处理。 4. 存储独立的最佳实践（页码14） - 文档还强调了与存储相关的优化策略，如合理选择存储选项、使用索引和缓存策略，以适应不同工作负载的需求。这些实践旨在确保XML数据的快速检索和处理，无论数据量大小。这份白皮书为在Oracle环境中优化XML查询性能提供了深入的指导，涵盖了从语法选择到查询设计和存储策略的各个方面。遵循这些最佳实践，开发者和DBA能够有效地管理和查询XML数据，提升系统整体性能。

Oracle White Paper—Title of White Paper Here

Migrating from Oracle Proprietary (XPath 1.0 based) syntax to

Standard SQL/XML XQuery based syntax

Starting 11gR2, Oracle has deprecated many older proprietary mainly XPath 1.0 based operators

in favor of standards based XQuery syntax, as listed in Table 1 below. If you don’t have any code

with the functions or operators being deprecated, you may jump to the next section.

TABLE 1. MIGRATING FROM OLD TO XQUERY SYNTAX

OLD ORACLE PROPRIETARY SYNTAX NEW XQUERY SQL/XML BASED

SYNTAX

extract() XMLQuery()

extractValue XMLCast(XMLQuery())

existsNode() XMLExists()

Table (XMLSequence) XMLTable

ora:instanceof instanceof

ora:instanceof-only @xsi:type

getNamespace fn:namespace-uri

getRootElement fn:local-name

getStringVal, getBlobVal, getClobVal XMLSerialize

Xmltype() XMLParse() for varchar, clob,

blob input

DBMS_XMLQUERY XMLQuery()

DBMS_XMLGEN SQL/XML Operators

Oracle XML DML Operators XQUery Update Facility

There are some important semantic differences between the deprecated mainly XPath 1.0 based

sytnax and the XQuery based syntax. These are listed in Appendix A to make the migration

easier for the users. Please also check “XQuery Guideline 6” in this document to see how to

apply XQuery to PL/SQL XMLType variable instead of calling extract() and existsNode()

methods of xmltype.

Oracle White Paper—Title of White Paper Here

The table below shows examples of Oracle Proprietary XML DML operators and their

equivalent Xquery Update syntax:

Note: Oracle Proprietary XMLDML does not have “rename” and “insert as first into”

operations.

Update warehouses set warehouse_spec =

appendChildXML(warehouse_spec,

‘Warehouse/Building’,

XMLType('<Owner>Grandco</Owner>'));

Update warehouses set warehouse_spec =

XMLQuery(‘copy $tmp := . modify (for $i in

$tmp/Warehouse/Building return insert

node <Owner>Grandco</Owner> as last

into $i) return $tmp’ passing

warehouse_spec returning content);

Update warehouses set warehouse_spec =

deleteXML(value(po), ‘/Warehouse/Building’);

Update warehouses set warehouse_spec =

XMLQuery(‘copy $tmp := . modify delete

node $tmp/Warehouse/Building return

$tmp’ passing warehouse_spec returning

content);

[Single Node Case]

Update warehouses set warehouse_spec =

insertXML(warehouse_spec,

‘/Warehouse/Building/Owner[2]',

XMLType('<Owner>ThirdOwner</Owner>');

Update warehouses set warehouse_spec =

XMLQuery(‘copy $tmp := . modify insert

node <Owner>ThirdOwner</Owner> into

$tmp/Warehouse/Building/Owner[2] return

$tmp’ passing warehouse_spec returning

content);

[Single Node Case]

Update warehouses set warehouse_spec =

insertXMLBefore(warehouse_spec,

‘/Warehouse/Building/Owner[2]',

XMLType('<Owner>FirstOwner</Owner>');

Update warehouses set warehouse_spec =

XMLQuery(‘copy $tmp := . modify insert

node <Owner>FirstOwner</Owner> before

$tmp/Warehouse/Building/Owner[2] return

$tmp’ passing warehouse_spec returning

content);

[Single Node Case]

Update warehouses set warehouse_spec =

insertXMLAfter(warehouse_spec,

‘/Warehouse/Building/Owner[2]',

XMLType('<Owner>ThirdOwner</Owner>');

Update warehouses set warehouse_spec =

XMLQuery(‘copy $tmp := . modify insert

node <Owner>ThirdOwner</Owner> after

$tmp/Warehouse/Building/Owner[2] return

$tmp’ passing warehouse_spec returning

content);

Update warehouses set warehouse_spec =

updateXML(warehouse_spec,

'/Warehouse/Docks/text()', 4);

Update warehouses set warehouse_spec =

XMLQuery(‘copy $tmp := . modify (for $i in

$tmp/Warehouse/Docks/text() return

replace value of node $i with 4) return $tmp’

passing warehouse_spec returning

content);

Update warehouses set warehouse_spec =

insertChildXML(warehouse_spec,

'/Warehouse/Building', 'Owner',

XMLType('<Owner>LesserCo</Owner>'));

Update warehouses set warehouse_spec =

XMLQuery(‘copy $tmp := . modify (for $i in

$tmp/Warehouse/Building return insert

node <Owner>LesserCo</Owner> into $i)

Oracle White Paper—Title of White Paper Here

return $tmp’ passing warehouse_spec

returning content);

Update warehouses set warehouse_spec =

insertChildXMLBefore(warehouse_spec,

'/Warehouse/Building', 'Owner',

XMLType('<Owner>LesserCo</Owner>'));

Update warehouses set warehouse_spec =

XMLQuery(‘copy $tmp := . modify (for $i in

$tmp/Warehouse/Building return insert

node <Owner>LesserCo</Owner> before

$i) return $tmp’ passing warehouse_spec

returning content);

Update warehouses set warehouse_spec =

insertChildXMLAfter(warehouse_spec,

'/Warehouse/Building', 'Owner',

XMLType('<Owner>LesserCo</Owner>'));

Update warehouses set warehouse_spec =

XMLQuery(‘copy $tmp := . modify (for $i in

$tmp/Warehouse/Building return insert

node <Owner>LesserCo</Owner> after $i)

return $tmp’ passing warehouse_spec

returning content);

[Collection Case]

Update warehouses set warehouse_spec =

insertXML(warehouse_spec,

‘/Warehouse/Building/Owner',

XMLType('<Owner>AnotherOwner</Owner>');

Update warehouses set warehouse_spec =

XMLQuery(‘copy $tmp := . modify (for $i in

$tmp/Warehouse/Building/Owner return

insert node

<Owner>AnotherOwner</Owner> into $i)

return $tmp’ passing warehouse_spec

returning content);

[NULL Case]

Update warehouses set warehouse_spec =

updateXML(warehouse_spec,

'/Warehouse/Docks', null);

Update warehouses set warehouse_spec =

XMLQuery(‘copy $tmp := . modify delete

node $tmp/Warehouse/Docks return $tmp’

passing warehouse_spec returning

content);

[Empty Node Case]

Update warehouses set warehouse_spec =

updateXML(warehouse_spec,

'/Warehouse/Docks', '' );

Update warehouses set warehouse_spec =

XMLQuery(‘copy $tmp := $p1 modify (for $j

in $tmp/Warehouse/Docks return replace

node $j with $p2) return $i' passing passing

warehouse_spec “p1”, '' as "p2" returning

content) ;

[Multiple Path Case]

Update warehouses set warehouse_spec =

updateXML(warehouse_spec,

'/Warehouse/Docks/text()',

extractValue(warehouse_spec,

'/Warehouse/Docks/text()')+4,

'/Warehouse/Docks/text()',

extractValue(warehouse_spec,

'/Warehouse/Docks/text()')+4);

Update warehouses set warehouse_spec =

XMLQuery(‘copy $tmp := . modify ((for $i in

$tmp/Warehouse/Docks/text() return

replace value of node $i with $i+4), (for $i in

$tmp/Warehouse/Docks/text() return

replace value of node $i with $i+4)) return

$tmp’ passing warehouse_spec returning

content);

剩余69页未读，继续阅读

joyce_lee

粉丝: 4
资源: 5

Oracle XMLDB：优化XML查询的最佳实践

Oracle xml简单操作

Apache Solr [Apache Con 2006]

SQL Server Query Store

mysql optimize_MYSQL OPTIMIZE命令

mysql OPTIMIZE 的使用

from pickletools import optimize

OPTIMIZE TABLE 执行时间

OPTIMIZE TABLE 怎么使用

optimize table语法示例

OPTIMIZE TABLE 怎么用

最新资源