Join the world’s largest interactive community dedicated to Oracle technologies.
5 stars based on
Important design choices for your application include what XMLType storage model to use and which indexing approaches to use. Because it is an abstract data type, your applications and database queries gain in flexibility: Different applications use XML data in different ways.
Sometimes it is constructed from relational data sources, so it is relatively structured. Sometimes it is used for extraction, transformation, and loading ETL operations, in which case it is also quite structured.
Sometimes it is used for oracle sys xmltype best binary options australia storage binary options documents unstructured or semi-structured such as books and articles. Retrieval approaches can also be different for different kinds of data. Data-centric use cases often involve a fixed set of queries, whereas document-centric use cases often involve arbitrary ad-hoc queries. Because there is a broad spectrum of XML usage, there is no one-size-fits-all storage model that offers optimal performance and flexibility for every use case.
You can tailor performance and functionality to best fit the kind of XML data you have and the ways you use it. This chapter helps you choose the best storage option for a given use case. Binary XML oracle sys xmltype best binary options australia storage binary options — This is also referred to as post-parse persistence.
It is a post-parse, binary format designed specifically for XML data. The biggest advantage of Binary XML storage is flexibility: You can use it with an XML schema that allows for high data variability or that evolves considerably or unexpectedly. This storage model also provides efficient partial updating and streamable query evaluation.
Object-relational storage — This is also referred to as structured storage and object-based persistence. It provides the best performance for highly structured data with a known and more or less fixed set of queries. Query performance matches that of relational data, and updates can be performed in place.
Use binary XML storage instead. B-tree indexes on the secondary tables created automatically for XMLIndex both structured and unstructured components on binary XML storage.
Different use cases call for different combinations of XMLType storage model and indexes. There is a spectrum of use cases, ranging from most data-centric to most document-centric. This is illustrated in Figurewhich shows oracle sys xmltype best binary options australia storage binary options most data-centric cases at the left and the most document-centric cases at the right. Data-centric data is highly structured, with relatively static and predictable structure, and your applications take advantage of this structure.
The data oracle sys xmltype best binary options australia storage binary options to an XML schema. The data is generally without structure or is of variable structure. This includes the case of documents that have both structured and unstructured parts. Document structure can vary over time evolutionand the content can be mixed semi-structuredwith many elements containing both text nodes and child elements. Many XML elements can be absent or can appear in different orders. Documents might or might not conform to an XML schema.
The data is relatively structured, but your applications do not take advantage of that structure: If your use case is a common one, so that it is not covered here, then refer to the rest of this chapter for information about special cases.
That case is not covered here, as it involves relational storage and the generated XML data is not necessarily persisted. In this use case there is no requirement to update or query fragments of XML data that is stored in the database.
And you cannot perform XMLType operations on the data. If the XML data contains islands of structured, predictable data, and your queries are known, then use XMLIndex with a structured component to index the structured islands even if the data surrounding these islands is unstructured. A structured index component reflects the queries you use.
An RSS news aggregator is an example of such a use case. If there are no such structured islands or your queries are unknown ahead of time ad hoc then use XMLIndex with an unstructured component. If you use queries that involve full-text search then use an XML search index, together with XQuery pragma ora: In this use case, data is extracted from outside sources, transformed to fit operational needs typically relationaland then loaded into the database: In particular, transformation distinguishes this use case.
ETL use cases often integrate data from multiple applications that are maintained or hosted by multiple parties using different software and hardware systems. The data that is extracted is often the responsibility of parties other than those who transform it or use it after transformation.
Updating can involve replacement of an entire XML document or changes to only fragments of a document partial updating. Object-relational storage of XMLType data is generally appropriate for this use case. In this use case, either your XML data is of variable form or large portions of it are not well defined.
There might not be an associated XML schema, or the XML schema might allow for high data variability or evolve considerably or in unexpected ways. Use structured-component XMLIndex indexing when query paths are known, and use path-subsetted unstructured-component XMLIndex indexing when paths are not known beforehand ad hoc queries. If the XMLIndex index is created in one-to-one correspondence to these views, Oracle Database automatically translates queries over the views to queries over the relational tables of the structured XMLIndex component, providing relational performance.
Object-relational storage is appropriate in special cases. Object-relational storage is not appropriate unless all of the following are true:. Your applications are data-centric. You do not expect your XML schema to evolve frequently in ways that do not allow in-place schema evolution. You do not necessarily insert and select whole XML documents at a time. Partial updates and selections are common.
Table provides more detail about this. The guidelines it presents for choosing an XMLType storage model are not independent: But also store a copy of the original documents in a CLOB relational column. You rarely need to select or update only a portion of your XML data.
Instead, you typically insert and select whole XML documents at a time. If you think that your data could benefit from XML schema validation, then consider also whether you can generate an XML schema for it using a schema-generation tool. You expect your XML schema to evolve frequently or in unexpected ways, and you cannot take advantage of in-place XML schema evolution.
In-place evolution is generally permitted only if the changes do not invalidate existing documents and they do not involve changing the storage model. Your XML schema does not make use of constructs such as elements any and choicewhich do not provide a detailed specification of the data format.
You can modify your XML schema to remove constructs such as any and choice that prevent a rigorous definition of the structure of your XML data.
For XMLType data stored object-relationally, create B-tree and bitmap indexes just as you would for relational data.
This is appropriate for queries that are ad hoc arbitrary. For oracle sys xmltype best binary options australia storage binary options that contains predictable, fixed parts that you query frequently, use XMLIndex with structured components for those parts.
An example of this use case is a specification that is generally free-form but that has fixed fields for the author, date, and title. To handle islands of structure oracle sys xmltype best binary options australia storage binary options generally unstructured content, create an XMLIndex index that has both structured and unstructured components.
A use case where you might use both components would be to support queries that extract an XML fragment from a document whenever some structured data is present. The structured component of the index would be used for a query WHERE clause condition that checks for the structured data. The unstructured component would be used for the fragment extraction.
These guidelines are independent: Table summarizes the advantages and disadvantages of each XMLType storage model. Queries that cannot use an index use streaming XPath evaluation, which can also be fast. You can create B-tree oracle sys xmltype best binary options australia storage binary options on the underlying object-relational columns.
XMLType tables and columns can be stored in the following ways: You have these options for this use case: This use case has three subcases: Staged XML Data for ETL In this use case, data is extracted from outside sources, transformed to fit operational needs typically relationaland then loaded into the database: For business-intelligence queries, you will generally do all of the following: Use an XMLIndex index with a structured component.
Write your application queries against these relational views. Object-relational storage is not appropriate unless all of the following are true: Your data is not especially sparse does not include many elements that are empty or missing.
You do not need document fidelity DOM fidelity is sufficient. You need the property of document fidelity, preserving all original whitespace. It is your responsibility to keep the two versions synchronized, if you update the data. Use binary XML storage. You do not have an XML schema for your data. Your XML data is very sparse. XML schema generators often include such constructs in the generated schemas. Remove such constructs, then use object-relational storage. You cannot remove such constructs.
Your data contains predictable islands of structured data. Use XMLIndexwith a structured component for each of the structured islands.