Referential Data Subsetting via Directed Graph Execution Plans

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Existing data subsetting techniques in relational databases often fail to create referentially-complete subsets, leading to issues like arbitrary subset size estimation, slow deletion processes, and inefficient logging, which can result in incomplete or oversized partial copies of databases.

Innovation Solution

The method involves estimating the expected size of a target subset database using subsetting rules, generating a subsetting execution plan, and processing it to create a referentially-complete target subset database, allowing users to influence the execution model and ensuring accurate storage space usage summaries.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Ease of manufacture

If arbitrary percentage of rows is selected for subsetting, then subset creation is simple, but referential completeness cannot be guaranteed

Engineering Contradiction:

Improvesubset creation simplicityVSAvoidreferential completeness

Core Design Contradiction:

Ease of manufactureVSReliability

Solution Approach 1:

The patent performs preliminary analysis of data relationships and cardinality constraints before creating the subset. It constructs a directed graph representing table relationships and uses this information to determine which rows to include, ensuring referential completeness is maintained from the outset rather than attempting to fix issues afterward.

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

The system provides feedback loops that allow users to review the estimated subset size and composition before final execution. The execution plan generation phase allows for validation and adjustment, ensuring that the subset will meet referential completeness requirements while achieving the desired size reduction.

Inventive Principle:

Principle #23Feedback

2Quantity of substance

If blind deletion technique is used to create subset, then subset size can be controlled, but processing speed becomes very slow

Engineering Contradiction:

Improvesubset size controlVSAvoidprocessing speed

Core Design Contradiction:

Quantity of substanceVSProductivity

Solution Approach 1:

The patent pre-calculates which rows should be retained in the subset by analyzing the directed graph of table relationships and applying subsetting rules. This execution plan is generated before actual data copying or deletion occurs, allowing the system to efficiently retrieve only the necessary rows rather than deleting unnecessary ones, thereby dramatically improving processing speed.

Inventive Principle:

Principle #10Preliminary action

3Reliability

If shadow production copy is created for subsetting, then data integrity is maintained, but storage space consumption doubles

Engineering Contradiction:

Improvedata integrityVSAvoidstorage space

Core Design Contradiction:

ReliabilityVSVolume of stationary object

Solution Approach 1:

The patent extracts only the specific rows and tables needed for the subset based on the execution plan, rather than creating a complete shadow copy of the entire production database. By using the directed graph to identify only the necessary data portions and their relationships, it creates a minimal subset that maintains referential completeness without duplicating unnecessary data, thus reducing storage space consumption.

Inventive Principle:

Principle #2Taking out (Extraction)

4Ease of operation

If JDBC copying is used for remote copy creation, then data can be moved to test system, but redo and undo logging is not minimized

Engineering Contradiction:

Improvedata movement capabilityVSAvoidlogging overhead

Core Design Contradiction:

Ease of operationVSLoss of energy

Solution Approach 1:

The patent generates an execution plan that identifies exactly which rows need to be copied before the actual data transfer occurs. This preliminary planning allows the system to minimize logging overhead by only performing copy operations on necessary rows and by batching operations efficiently, rather than logging every individual row copy operation as would occur with naive JDBC copying approaches.

Inventive Principle:

Principle #10Preliminary action

Data Source

PatentUS8935234B2Referentially-complete data subsetting using relational databases

Publication Date: 2015.01.13 ORACLE INT CORP

AI summary

A method, system, and computer program product for relational database management. The method constructs a referentially-complete target subset database from a source database by first estimating the expected size of the target subset database based on application of one or more subsetting rules. If the estimated size needs reduction, the user can modify the subsetting rules, and then modules are invoked to receive the modified subset rules. The method continues by generating a subsetting execution plan by applying the user-modified subset rules to the source database, and then modules process the generated execution plan, which processing results in storage of a referentially-complete target subset database. The user can influence the construction of the execution plan by suggesting an execution model to use during processing of the subsetting execution plan. Techniques estimate the expected size by constructing a directed graph wherein edges in the directed graph represent relationships between database tables.