Ghost Table Joins in Remote Database Systems
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional database management systems face challenges when handling large remote data sources, as pulling all remote data locally can overwhelm the system, and moving local data to the remote source for JOIN operations can be inefficient, especially if the remote data source struggles with disk-persisted table inserts.
Innovation Solution
The system creates a 'ghost' table in the remote data source with a storage handler to retrieve local data, allowing for a JOIN operation in volatile memory without disk operations, thereby optimizing data processing and handling.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If all remote data is pulled into the local file system to perform JOIN operations, then the JOIN operation can be completed, but the local system may be overwhelmed by large remote data
Solution Approach 1:
The patent introduces a ghost table as an intermediary structure in the remote file system that mediates between local data and remote data. Instead of pulling all remote data locally or moving local data to remote, the ghost table serves as a virtual representation that enables JOIN operations to be performed remotely with minimal data transfer, thus reducing local system load while maintaining JOIN operation completion.
2Productivity
If local data is moved to the remote data source to perform JOIN operations, then the JOIN can be executed, but it becomes inefficient if the remote data source cannot efficiently handle disk-persisted table inserts
Solution Approach 1:
The patent changes the parameter of data persistence by creating a ghost table that exists in volatile memory rather than being disk-persisted. This parameter change allows the remote data source to handle the JOIN operation efficiently by avoiding disk I/O operations for the ghost table, thus improving ease of manufacture while maintaining JOIN execution capability.
3Productivity
If a ghost table is created in the remote data source with a storage handler, then JOIN operations can be performed in volatile memory, but additional system components are required
Solution Approach 1:
The patent implements a storage handler that serves multiple functions: it manages the ghost table in volatile memory, handles data retrieval from the local file system, and facilitates JOIN operations. This multi-functional component achieves data processing efficiency improvements while minimizing the increase in system complexity by consolidating multiple responsibilities into a single universal handler.
Data Source
AI summary
A system includes reception of a client query including a JOIN between a local database table of a first computing system and a remote table of a second computing system, transmission of an instruction to the second computing system to define a new table in the second computing system, the new table associated with a storage handler for retrieving data of the local database table, generation of a remote query of the second computing system, the remote query including a second JOIN between the new table and the remote table, transmission of the remote query to the second computing system, and reception of results of the remote query from the second computing system.