Database Index Path String for Tree Table Search
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Searching all records within a particular table and its child tables in a database is computationally expensive due to the lack of parent table information in the index, requiring costly comparisons between table membership and long lists of table names.
Innovation Solution
Expanding the index to include a path string for each record, which includes the table name and all parent table names, allowing for efficient string comparison to determine record location within the tree structure.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Device complexity
If the index contains only table membership information without parent table paths, then the index structure remains simple, but searching records in a table and its child tables becomes computationally expensive
Solution Approach 1:
The patent pre-computes and stores the full path from the root table to each table in the index structure. This preliminary action allows search operations to quickly determine whether a record belongs to a target table or its descendants by simple path prefix matching, eliminating the need for expensive runtime computations to traverse the table hierarchy.
Solution Approach 2:
The patent introduces path strings as an intermediary data structure that mediates between the table hierarchy and search operations. Instead of directly comparing table names against a list of all descendant tables, the path string serves as a compact representation that enables efficient prefix-based filtering to determine table membership.
2Productivity
If the index is expanded to include path strings for each record, then search efficiency improves through direct path comparison, but the index size and storage requirements increase
Solution Approach 1:
The patent changes the representation parameter of table membership from a list of table names to a hierarchical path string. This parameter change enables more efficient storage and comparison operations, as path prefixes can be matched without comparing entire lists of descendant tables, reducing the effective storage footprint despite adding path information.
3Reliability
If all records in a table and its child tables are searched using traditional methods, then complete search coverage is achieved, but computational resources like processor cycles and memory bandwidth are excessively consumed
Solution Approach 1:
The patent extracts only the necessary path information from the full table hierarchy and stores it in the index. This extraction allows the search operation to quickly filter out records that cannot possibly match the search criteria by comparing path prefixes, thereby eliminating unnecessary computational work while maintaining complete search coverage for all relevant records.
Data Source
AI summary
Embodiments are provided that include receiving a search query that represents a particular table of a plurality of tables contained in a database and that includes an additional criterion. Records are contained within the tables, which are organized in a tree structure. An index of the database contains a plurality of entries, each corresponding to a respective record in the plurality of tables. A particular entry in the index includes a path string that is indicative of a location, within the tree structure, of a table that contains a record that corresponds to the particular entry. The embodiments also include determining whether the entries in the index satisfy the search query by: (i) determining that the path string of the particular entry begins with a further path string of the particular table, and (ii) responsively determining whether a particular record that corresponds to the particular entry satisfies the additional criterion.