Database Subset Export via Segmented Table Transfer
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional methods for exporting a subset of data from a large and complex production database to a test system are resource-intensive and disruptive, as they often require executing numerous database queries on the production system, slowing it down and consuming significant computational resources.
Innovation Solution
The system divides database tables into two sets based on size or administrator-defined criteria, transferring the entire data of smaller tables via file copying and executing queries only on the largest tables, thereby reducing the number of queries and optimizing resource usage.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If database queries are executed on the production system to determine the subset of data, then the required data subset can be identified, but the production system performance deteriorates and computational resources are consumed
Solution Approach 1:
The patent segments the database tables into two distinct sets: a first set containing N largest tables and a second set containing remaining smaller tables. This segmentation allows different export strategies to be applied to each set, optimizing both resource usage and data subset identification accuracy
Solution Approach 2:
The patent extracts the problematic element (execution of database queries on production system) and replaces it with an alternative approach. Instead of querying the production database to identify subsets, the system copies entire files from the second set of tables, eliminating query execution on the production system while still achieving the goal of obtaining relevant data subsets
2Reliability
If a copy of the production database is made for testing and debugging, then testing can be performed without disrupting production, but significant time and computing resources are required
Solution Approach 1:
The patent divides the database export process into two segments: copying entire files for the second set of tables (smaller tables) and using targeted queries only for the first set of tables (N largest tables). This segmentation significantly reduces the overall time and resources required compared to copying the entire database or executing queries on all tables
Solution Approach 2:
The patent applies partial action by copying entire files for the second set of tables without filtering, accepting that some unnecessary data is transferred. This excessive action for small tables is offset by the efficient selective querying applied only to the critical first set of tables, resulting in net time and resource savings
3Measurement precision
If all tables of the database are processed to determine the subset of data, then complete data subset accuracy is achieved, but the number of queries and resource consumption increase significantly
Solution Approach 1:
The patent segments tables based on size criteria, creating a first set of N largest tables and a second set of smaller tables. This segmentation reduces device complexity by applying different processing strategies: selective querying for the first set and direct file copying for the second set, thereby reducing the total number of queries executed while maintaining data subset completeness
Solution Approach 2:
The patent applies local quality by treating different table sets differently based on their characteristics. The first set of largest tables receives selective querying treatment to identify relevant subsets, while the second set of smaller tables receives direct file copying treatment. This localized approach optimizes the balance between data completeness and query complexity
Data Source
AI summary
A system and a method are disclosed for exporting a subset of a database from a source system to a target system. The database system identifies a first set of tables and determines the subset of data for these tables for transfer by executing one or more database queries. For example, the first set of tables is selected based on their sizes. The database system identifies a second set of tables for transferring by performing a file copy operation. For example, the remaining set of tables other than the first set of tables is transferred by performing a file copy operation. The database system moves the tables of the first set to a new table space for separating the files storing the first set of tables from the files storing the second set of tables.