PRUNING OF COLUMNS IN SYNOPSIS TABLES

• US 20180107710A1
• Filed: 12/18/2017
• Published: 04/19/2018
• Est. Priority Date: 04/26/2016
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Finlay, Ian R., Garcia-Arellano, Christian M., Storm, Adam J., Zuzarte, Calisto P.
• Granted Patent: US 10,691,687 B2
• Time in Patent Office: Days
• CPC Class Codes: G06F 16/2453 Query optimisation

Abstract

Embodiments of the present invention provide systems and methods for data management. Synopsis tables have been found to be more effective for maintaining a high level of system performance while answering analytical queries. Synopsis tables, which contain MAX, MIN, and Bloom filter columns, may be modified by dropping ineffective data content within these columns and regenerating dropped data when beneficial. By automatically modifying data, database queries may be optimized.

Claims

1. A method for data management, comprising the steps of:

organizing, by one or more processors, a plurality of data content in one or more data tables;

monitoring, by one or more processors, a usage of the plurality of data content in the one or more data tables using an in-memory structure; and

modifying, by one or more processors, the plurality of data content in the one or more data tables, based on a selectivity threshold which defines an effectiveness for optimizing data queries.

2. The method of claim 1, wherein monitoring the usage of the plurality of data content in the one or more data tables comprises at least one of:

determining, by one or more processors, predicate types applied within the one or more data tables;

determining, by one or more processors, a percentage of the plurality of data content skipped over blocks of the one or more data tables; and

comparing, by one or more processors, a number of updates, deletes, and inserts on each region of a main data table, to the number of updates, deletes, and inserts of an associated row of the one or more data tables.

3. The method of claim 1, wherein modifying the one or more data tables, comprises dropping a subset of the plurality of content associated with at least one type of data structure within the one or more data tables.

4. The method of claim 3, further comprising:

comparing, by one or more processors, data table performance between a first data table of the one or more data tables containing a predicate type, and a corresponding first data table not containing the predicate type;

responsive to determining the first data table at least meets the selectivity threshold, dropping, by one or more processors, a subset of the plurality of content associated with a first type of data structure, a second type of data structure, and a third type of data structure within the one or more data tables;

comparing, by one or more processors, data table performance between a second data table of the one or more data tables with the predicate type to a corresponding second data table with more instances of the predicate type than the second data table; and

responsive to determining data table performance of the second data table at least meets the selectivity threshold, dropping, by one or more processors, the subset of the plurality of content associated with the first type of data structure, the second type of data structure, and the third type of data structure within the one or more data tables.

5. The method of claim 3, further comprising:

comparing, by one or more processors, data table performance between a third data table of the one or more data tables containing a first type of data structure to a corresponding third data table not containing the first type of data structure;

responsive to determining the third data table at least meets the selectivity threshold, dropping, by one or more processors, the subset of the plurality of content associated with the first type of data structure within the one or more data tables;

comparing, by one or more processors, data table performance between a fourth data table of the one or more data tables with the first type of data structure, a second type of data structure and a third type of data structure, and a corresponding fourth data table with the first type of data structure and the second type of data structure; and

responsive to determining the fourth data table at least meets the selectivity threshold, dropping, by one or more processors, the subset of the plurality of content associated with the third type of data structure in the fourth data table within the one or more data tables.

6. The method of claim 3, further comprising:

responsive to determining a predefined threshold is met, regenerating, by one or more processors, one of the dropped subset of the plurality of content associated with at least one type of data structure within the one or more data tables.

7. The method of claim 3, further comprising:

comparing, by one or more processors, data table performance between a comprehensive data table of the one or more data tables and a corresponding comprehensive data table; and

responsive to determining the comprehensive data table is below the selectivity threshold, dropping, by one or more processors, the comprehensive data table.