Pre-computed Indices for SQL Queries on Tree Structured Data
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database systems face inefficiencies when processing SQL queries on tree-structured data, such as XML documents, as they often ignore the structural information, leading to suboptimal use of computing resources and poor query performance.
Innovation Solution
The method involves forming top-down trees and generating pre-computed indices for tree-structured documents, which are then combined to dynamically create tables, allowing for efficient resolution of structured query language queries and supporting multiple query languages like SQL, XQuery, and XPath.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If conventional text search tools are used on XML documents, then the documents can be searched, but the structural information provided by XML is ignored leading to suboptimal query performance
Solution Approach 1:
The XML document is segmented into multiple hierarchical levels (root element, child elements, grandchild elements, etc.), with each level represented as a separate table in the relational database. This segmentation preserves the hierarchical structure while enabling efficient SQL queries on specific levels without processing the entire document structure.
Solution Approach 2:
A relational database structure acts as an intermediary between the hierarchical XML data and SQL queries. The XML hierarchy is transformed into normalized relational tables with foreign key relationships, allowing SQL to operate on the structured data while preserving the original hierarchical relationships through the schema design.
2Productivity
If SQL queries are executed directly on tree-structured data, then standard query processing can be used, but the tree structure is not respected leading to inefficient data reordering
Solution Approach 1:
The XML hierarchical structure is pre-transformed into a normalized relational database schema during data loading. This preliminary action organizes the data into tables with appropriate foreign key relationships, so that subsequent SQL queries can execute efficiently without requiring runtime reordering or restructuring of the hierarchical data.
Solution Approach 2:
The data structure is changed from a hierarchical tree format to a normalized relational format with multiple tables connected by foreign keys. This parameter change in data representation allows SQL's set-based operations to work efficiently on hierarchical data without the computational overhead of maintaining or traversing tree structures during query execution.
3Adaptability or versatility
If the entire XML document structure is materialized to support SQL queries, then complete structural information is available, but significant computational resources and time are consumed
Solution Approach 1:
Instead of materializing the entire XML hierarchy in a single structure, the data is segmented across multiple normalized tables (parent elements table, child elements table, grandchild elements table, etc.). Each table contains only the data relevant to its hierarchical level, reducing redundancy and improving query performance for level-specific operations.
Solution Approach 2:
The system performs partial materialization by creating only the necessary relational tables for the specific query needs. The normalization process creates tables with foreign key relationships that preserve hierarchical information without requiring complete materialization of all possible query paths, reducing the computational overhead while maintaining versatility.
Data Source
AI summary
A method of operating a tree structured database includes receiving a document, forming a top-down tree characterizing the document, generating pre-computed indices characterizing the document, combining subsets of the pre-computed indices to dynamically create a table of information characterizing the document, and resolving a structured query language query against the table to form collected data.