Pruning of columns in synopsis tables

• US 10,691,687 B2
• Filed: 12/18/2017
• Issued: 06/23/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/845,246
• Publication Number:
  US 20180107710A1
• Time in Patent Office: 918 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 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;

monitoring, by one or more processors, 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

dropping, by one or more processors, 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 method of claim 1, wherein monitoring the usage of the plurality of columns in the one or more data tables comprises:

comparing, by one or more processors, 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 method of claim 1, 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 period of time 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 period of time 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.

4. The method of claim 1, 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 period of time 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.

5. The method of claim 1, 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.

6. The method of claim 1, 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 period of time threshold, dropping, by one or more processors, the comprehensive data table.