Virtual Table Schemas with Logical Pointers for Federated Data
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Federated databases face inefficiencies in data retrieval from multiple sources, leading to time-consuming and resource-intensive processes, especially when altering data locations requires significant changes to database systems and queries.
Innovation Solution
Implementing virtual table schemas with logical pointers that can dynamically update to target tables at different locations, such as remote, replica, or cached tables, allowing for efficient data management without altering the database schema, and enabling query optimizers to recompile query plans accordingly.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If data is retrieved from multiple remote data sources in a federated database system, then data availability from diverse systems is improved, but query execution time and resource consumption increase
Solution Approach 1:
The system performs preliminary actions by caching table data from remote federated data sources in local storage before queries are executed. The virtual table schema pre-positions data in the cache, so when a query arrives, the data is already available locally rather than needing to be retrieved from remote sources at query time. This eliminates the time-consuming network round trips while maintaining data availability.
Solution Approach 2:
The patent introduces a virtual table schema as an intermediary layer between the application and the federated database system. This virtual schema includes logical pointers that can reference either remote tables or local cache tables, acting as a mediator that directs queries to the most appropriate data location. The intermediary abstracts the complexity of data location management from the query execution process.
2Productivity
If data is cached in local storage to reduce query execution time, then query performance is improved, but storage resource requirements increase
Solution Approach 1:
The system dynamically changes the state of the logical pointer in the virtual table schema to reflect whether data is currently cached or needs to be retrieved from remote sources. This parameter change allows the same virtual table structure to adapt to different data locations without requiring permanent storage allocations for all possible data, optimizing the balance between cache usage and remote access.
Solution Approach 2:
The virtual table schema employs dynamic logical pointers that can be updated to reference different data locations. When data is cached, the pointer dynamically updates to reference the local cache table; when cache space is needed or data is invalidated, the pointer can be updated to reference the remote source again. This dynamic behavior allows flexible resource management without committing storage resources permanently.
3Adaptability or versatility
If the database schema is altered to accommodate data location changes in federated systems, then data location flexibility is improved, but system complexity and modification effort increase
Solution Approach 1:
The patent segments the database schema into two distinct layers: the virtual table schema layer (which contains logical pointers to data locations) and the physical data storage layer (remote federated sources or local cache). This segmentation allows the virtual schema to be modified independently to reflect data location changes without affecting the underlying physical storage structures. The logical pointers can be updated to reference different data locations without requiring changes to the actual data sources or cache structures.
Data Source
AI summary
Techniques and solutions are described for implementing virtual table schemas. A virtual table schema includes a logical pointer that is used to target a table that includes data and is defined according to the virtual table schema. Values assigned to the logical pointer can be used to target tables at different locations, such as in a federated database system or in a cache of a database management system of a central computing system. When data associated with the virtual table is requested, or prior to receiving the request, the data can be stored in a table in the cache. The logical pointer can be updated to reference the cache. If the cache is full, the table can be removed from the cache, and the logical pointer can be updated to reference a table at the federated database system.