Pruning of columns in synopsis tables

• US 10,649,991 B2
• Filed: 04/26/2016
• Issued: 05/12/2020
• 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.
• Primary Examiner(s): Mobin, Hasanul
• Application Number:
  US15/138,637
• Publication Number:
  US 20170308572A1
• Time in Patent Office: 1,477 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 computer program product for data management, comprising:

a computer readable storage medium and program instructions stored on the computer readable storage medium, the program instructions comprising;

program instructions to organize a plurality of columns in one or more data tables, wherein respective columns of the plurality of columns are utilized upon receiving a query that includes a predicate to search a main data table;

program instructions to monitor a usage of the plurality of columns in the one or more data tables using an in-memory structure, wherein the in-memory structure stores, for each column in the plurality of columns, (i) a predicate type and (ii) a count of queries that have used the respective column to search the main data table; and

program instructions to drop one or more columns of the plurality of columns in the one or more data tables, based on a period of time threshold which defines an effectiveness for optimizing data queries, wherein dropping the one or more columns comprises dropping a subset of a plurality of content associated with at least one type of data structure within the one or more data tables.

2. The computer program product of claim 1, wherein program instructions to monitor the usage of the plurality of columns in the one or more data tables, comprise:

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

3. The computer program product of claim 1, further comprising:

program instructions to compare 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 period of time threshold, program instructions to drop 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;

program instructions to compare 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 period of time threshold, program instructions to drop 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.

4. The computer program product of claim 1, further comprising:

program instructions to compare data table performance between a third data table of the one or more data tables containing a first data structure type to a corresponding third data table not containing the first data structure type;

responsive to determining the third data table at least meets the period of time threshold, program instructions to drop the subset of the plurality of content associated with the first data structure type within the one or more data tables;

program instructions to compare data table performance between a fourth data table of the one or more data tables with a 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, program instructions to drop 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.

5. The computer program product of claim 1, further comprising:

responsive to determining a predefined threshold is met, program instructions to regenerate 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.

6. The computer program product of claim 1, further comprising:

program instructions to compare 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 period of time threshold, program instructions to drop the comprehensive data table.

7. A computer system for data management, comprising:

one or more computer processors;

one or more computer readable storage media; and

program instructions stored on the one or more computer readable storage media for execution by at least one of the one or more processors, the program instructions comprising;

program instructions to organize a plurality of columns in one or more data tables, wherein respective columns of the plurality of columns are utilized upon receiving a query that includes a predicate to search a main data table;

program instructions to monitor a usage of the plurality of columns in the one or more data tables using an in-memory structure, wherein the in-memory structure stores, for each column in the plurality of columns, (i) a predicate type and (ii) a count of queries that have used the respective column to search the main data table; and

program instructions to drop one or more columns of the plurality of columns in the one or more data tables, based on a period of time threshold which defines an effectiveness for optimizing data queries, wherein dropping the one or more columns comprises dropping a subset of a plurality of content associated with at least one type of data structure within the one or more data tables.

8. The computer system of claim 7, wherein program instructions to monitor the usage of the plurality of columns in the one or more data tables, comprise:

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

9. The computer system of claim 7, further comprising:

program instructions to compare 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 period of time threshold, program instructions to drop a subset of a 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;

program instructions to compare 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 period of time threshold, program instructions to drop 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.

10. The computer system of claim 7, further comprising:

program instructions to compare data table performance between a third data table of the one or more data tables containing a first data structure to a corresponding third data table not containing the first data structure;

responsive to determining the third data table at least meets the period of time threshold, program instructions to drop the subset of the plurality of content associated with the first data structure type within the one or more data tables;

program instructions to compare data table performance between a fourth data table of the one or more data tables with a 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, program instructions to drop 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.

11. The computer system of claim 7, further comprising:

responsive to determining a predefined threshold is met, program instructions to regenerate 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.

12. The computer system of claim 7, further comprising:

program instructions to compare 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 period of time threshold, program instructions to drop the comprehensive data table.