Storing and Querying Ordered XML Using Relational Database

Storing and Querying Ordered XML Using Relational Database System Swapna Dhayagude

Agenda n Ordered XML Data Model n Order Encoding Methods n Shredding Ordered XML into Relations n Translating XML queries to SQL n Performance Evaluation

Ordered XML Data Model n XML document as a tree structure - Relation as the ‘root’ - Nodes represent elements - Leaf nodes hold data values n n Document Type Descriptor - schema information about the XML document Order - a salient feature of an XML document

Significance of order in XML Order – n Important from the point of view of reconstruction of XML documents - n To ensure a lossless mapping from XML to RDB Performance issues - Choice of order dramatically affects performance - Enhances Efficient Translation of XML into SQL Order based functionality of XPath and XQuery XPath – a simple ‘path based’ query language XQuery – a complex query language based on XPath

Three dimensions of XML order n Evaluation of Order based axes XPath expressions requiring document order 1. 2. n n preceding following Inter Element Order result set enforces document order among result set elements Intra Element Order For reconstruction, document order is important

Agenda n n n Ordered XML Data Model Order Encoding Methods Shredding Ordered XML into Relations Translating XML queries to SQL Performance Evaluation

How is order encoded ? n n Order is preserved using a simple numbering scheme Each node is represented using a node_id Node-id is stored as a data value within the relation Numbering schemes capture enough information to reconstruct XML documents

Order Based Functionality of XPath n n n XPath follows a step-by-step sequential evaluation, Each step is applied to a single node (context node) Result of each step is a set of nodes {node 1, node 2, . . , node n} XPath syntax Path : : = /Step 1/Step 2/…/Step. N Where each Xpath Step is defined as follows: Step : : = Axis : : Node-test Predicate* Axis selects a direction of navigation e. g. child : : title Would select all children that are ‘titles’

Order Based Functionality of XPath Axes – specify the direction of navigation in an XML document n Up n n n Down n child descendant Left n n n parent ancestor preceding Preceding-sibling Right n n following Following-sibling

Order Based Functionality of XQuery n BEFORE operator - Return nodes from the first sequence that are before some node in the second sequence n AFTER operator - Return nodes from the first sequence that are after some node in the second sequence XQuery supports range predicates - allows selection of a range of elements from a sequence e. g. /play/act[2 TO 4] Will return act #2 , act #3, and act #4 in document order. n

Global Order Encoding Methods n play(1) title(2) text#(3) act(4) title(5) text#(6) act(8) scene(7) Global Order Encoding n Absolute positioning of nodes n Best performance on queries n Query evaluation requires simple comparison between node positions n Worst performance on updates, especially deletes

Global Order Encoding (contd) Initially, sparse numbering is used for Global Order Encoding n n Sparse numbering brings down the cost of renumbering (on inserts/updates) n n Sparse numbering results in better performance on updates Makes intra-element and inter-element ordering easy (since global document order is easily available) n Drawback - performs poorly on inserts (Local Order offers better performance for inserts/updates)

Global Order Renumbering Scenario n play(1) title(2) act(8) n text#(3) act(4) scene(7) New Element title(5) Inserting a new element in an existing document causes many nodes to be renumbered scene(7) In the adjoining figure, the highlighted nodes need to be renumbered (maximum in the global ordering scheme)

Local Order Encoding Methods play(1) Local Order Encoding n title(1) act(2) act(3) 1. 2. text(1) title(1) text(1) scene(2) 3. Relative positioning of nodes Best performance on updates Worst performance on queries

Local Order Encoding (continued…. ) How does local Order encoding reconstruct absolute path ? n n the relative position of a node is combined with the relative order of the parent this combined effect yields a vector that uniquely identifies the absolute position within the document (relative position of node) + (relative position of ancestor) = (absolute position of node in the document)

Local Order Renumbering Scenario n play(1) title(1) act(2) n text#(1) act(2) scene(1) New Element title(1) scene(2) As opposed to Global Order Encoding, Local Order requires a minimum number of nodes to be renumbered This is a major advantage, since it dramatically reduces the cost of inserts

Local Order Encoding (continued…. ) n Incurs low overhead on updates n Only “following-sibling “ may require renumbering n Drawbacks – Lack of global order information results in complex evaluations of following and preceding axes

Dewey Order Encoding Methods Play 1 title(1. 1) text(1. 1. 1) act(1. 2) title(1. 1. 2) text(1. 1. 2. 1) n act(1. 3) scene(1. 2. 2) Dewey Order Encoding 1. Strikes a balance between Global and Local 2. Reasonable performance on updates and queries

Dewey Order Encoding n n Each path uniquely identifies absolute position of a node in a document Query processing is similar to that of Global order Only “following-sibling “ may require renumbering Drawbacks – Extra space required to store paths from root to the node

Dewey Order Renumbering Scenario Renumbering required is more than that for Local Encoding, however much less than that for Global Encoding play title act text# act scene New element title scene

Agenda n n n Ordered XML Data Model Order Encoding Methods Shredding Ordered XML into Relations Translating XML queries to SQL Performance Evaluation

Shredding XML into Relations n n Schema-less Case Unknown schema of input XML documents Edge Approach Each document is stored as a single table Schema-aware Case Schema of input XML documents is available Inlining – Single occurrence of child – store within parent relation Multiple occurrences – store as a new relation table

Inlining is an effective way of storing and querying XML provided the availability of Document Schema Inlining adapts to Global, Local and Dewey Orders. Every relation requires an additional column to encode document order storing order information of ‘inlined’ elements is unnecessary (Element position is determined from the position of parent and from the document schema)

Storing Order Information – Schema less case The Edge Approach n Each relation is stored as a table n Each tuple within the table represents a node n Edge (id, parent_id, name, value) id synonymous to a primary key parent_id synonymous to the foreign key, provides link to the node’s parent name stores tag name of element value stores text value

Storing Order Information – Schema less case Edge approach adapts differently to Global, Local and Dewey n Global Order Edge (id, parent_id, end_desc_id, path_id, value) end_desc_id – id of the last descendant of a node n Local Order Edge (id, parent_id, s. Index, path_id, value) s. Index – sibling index of a node n Dewey Order Edge (dewey, path_id, value) dewey – represents both order and ancestor information

Agenda n n n Ordered XML Data Model Order Encoding Methods Shredding Ordered XML into Relations Translating XML queries to SQL Performance Evaluation

Query Translation for Global Order Edge (id, parent_id, end_desc_id, path_id, value) Translation of following/preceding Select nodes from Edge table where id value (context node) > end_descendant_id of context node Select nodes from Edge table where id value (context node) < end_descendant_id of context node Translation of following-sibling/ preceding-sibling Select (nodes in Edge table with id value > id of context node) AND (nodes with parent_id = parent_id of context node) Select (nodes in Edge table with id value < id of context node) AND (nodes with parent_id = parent_id of context node) Note : above expressions are NOT actual SQL statements

Query Translation for Local Order Edge (id, parent_id, s. Index, path_id, value) Translation of following-sibling/ preceding-sibling (Similar to Global and Dewey Order) Translation of following/preceding 1. 2. 3. (Complex Task !!!) Compute all ancestors of context node – {anc} Compute ancestors of following-sibling - {anc_sib} Compute descendants of {anc_sib} Challenges: n Without knowledge of XML schema, retrieving ancestors/descendants is a complex task n Involves recursion

Query Translation for Dewey Order Edge (dewey, path_id, value) dewey column - stored as variable length byte string - replaces parent_id, and end_desc_id in Global Edge Table - Encodes parent and descendant information within the dewey path - Eliminates need to store parent_id and child_id Drawback: Storage overhead due to large number of bytes allocated to each component.

Query Translation in Inlining Essentially uses the same algorithm as that for Edge approach but with 2 extensions n XML data can be spread across several tables therefore evaluating axes requires access to multiple tables as opposed to accessing just one Edge table n Secondly translation algorithm does not use recursion (since the schema contains sufficient information about depth and postion of nodes) Drawback: Data is partitioned across many tables, too many tables to handle

Agenda n n n Ordered XML Data Model Order Encoding Methods Shredding Ordered XML into Relations Translating XML queries to SQL Performance Evaluation

Storage Requirements Table 1: Indicates the storage requirements of Global, Local and Dewey Encoding Methods Edge Inlining Order Scheme Table Size Index Size Global 52. 1 MB 57. 9 MB 44. 1 MB 28. 9 MB Local 52. 1 MB 87. 9 MB 47. 7 MB 36. 8 MB Dewey 48. 9 MB 38. 7 MB 44. 5 MB 15. 8 MB

Performance All experiments are based on the Shakespeare’s Plays dataset. Table 2: Test Queries Query Definition Q 1 /play Q 2 /play/act//speech Q 3 /play/act/scene/speech Q 4 /play/act/scene/speech[2] Q 5 /play/act/scene/*[2] Q 6 /play/act/scene/speech[1 TO 3] Q 7 /play/act[2]/following: : speech Q 8 /play/act/scene/speech/speaker/following-sibling: : line[2] Q 9 //act/scene/speech BEFORE /play/act[2]

Select and Reconstruct Modes XPath Queries essentially run in 2 different modes n n Select Mode : Result set contains only the ID’s of the nodes satisfying the XPath expression Reconstruct Mode: Entire XML fragments are extracted from the database in document order

Ordered Selection Edge Results X axis: Queries Y axis: Time (seconds)

Inlining Results

Reconstruction n Note: In reconstruct mode, XML documents need to be extracted from DB in document order Optimizers inability to pick the best plan rendered poor results On the other hand, using ‘tuned’ SQL queries yielded better results Queries Q 3, Q 4, Q 5, Q 9 had a disastrous performance (way beyond the scope of indicated scale)

Performance Results based on experiments n Global order is the most efficient order encoding method n n Followed by Dewey Order – second best performance Local Order uses sorting very often which degrades overall performance Typically Inlining performs better than Edge In general the XML document parsing overhead was more than XPath processing

Performance Conclusions based on results n n n RDBMS efficiently supports ordered XML Global order is the best for query workloads Dewey Order is slightly less efficient than Global Order Best for a mix of queries and updates Schema Information makes Local Order a viable alternative Incomprehensiveness of Relational Optimizers to the hierarchical XML structure

Acknowledgements… Prof. Elke Rundensteiner Thank You …