Implementation of data access metrics for automated physical database design

Patent metadata

Field	Value
Publication number	US-11550762-B2
Application number	US-202117316901-A
Country	US
Kind code	B2
Filing date	May 11, 2021
Priority date	Feb 24, 2021
Publication date	Jan 10, 2023
Grant date	Jan 10, 2023

Abstract

The present disclosure involves systems, software, and computer implemented methods for improved design and implementation of data access metrics for automated physical database design. An example method includes identifying a database workload for which index advisor access counters are to be tracked. Each SQL statement in the database workload is executed. For each SQL statement, attribute sets are determined for which a selection predicate filters a result for an SQL statement. An output cardinality of each selection predicate is determined. A logarithmic counter for an attribute set corresponding to the selection predicate is determined based on the output cardinality of the selection predicate. The determined logarithmic counter is incremented. Respective values for logarithmic counters of the determined attributes are provided to an index advisor. The index advisor determines attribute sets for which to propose an index based on the logarithmic counters of the respective attribute sets.

First claim

What is claimed is:

1. A computer-implemented method comprising:
identifying a database workload for which index advisor access counters are to be tracked, wherein the database workload includes at least one SQL (Structured Query Language) statement;
executing each SQL statement in the database workload, wherein executing a respective SQL statement comprises:
determining attribute sets of at least one free attribute for which a selection predicate filters a result for the SQL statement;
determining an output cardinality of each selection predicate;
determining, for each selection predicate, whether the output cardinality of the selection predicate is greater than a threshold; and
in response to determining that the output cardinality of a respective selection predicate is not greater than the threshold:
creating logarithmic counters for the selection predicate if logarithmic counters have not yet been created for the selection predicate;
determining, based on the output cardinality of the selection predicate, a logarithmic counter for an attribute set corresponding to the selection predicate; and
incrementing the logarithmic counter; and

providing respective values for the logarithmic counters of the determined attributes to an index advisor, as the index advisor access counters.

2. The computer-implemented method of claim 1 , wherein the logarithmic counter is selected from among a collection of logarithmic counters for the attribute set.

3. The computer-implemented method of claim 2 , wherein determining the logarithmic counter comprises determining a collection index of a particular logarithmic counter in the collection of logarithmic counters for the attribute set.

4. The computer-implemented method of claim 2 , wherein determining the collection index comprises determining the logarithm of the output cardinality of the selection predicate.

5. The computer-implemented method of claim 4 , wherein determining the logarithm of the output cardinality of the selection predicate comprises:
determining a current value of a configurable logarithm base parameter; and
using the current value of the configurable logarithm base parameter when determining the logarithm of the output cardinality of the selection predicate.

6. The computer-implemented method of claim 1 , wherein the threshold is based on a maximum query result size.

7. The computer-implemented method of claim 1 , wherein the logarithmic counters for an attribute set are created in response to determining that an executed query includes a selection predicate for the attribute set.

8. The computer-implemented method of claim 1 , wherein the index advisor determines attribute sets for which to propose an index based on the logarithmic counters of the respective attribute sets.

9. A system comprising:
one or more computers; and
a computer-readable medium coupled to the one or more computers having instructions stored thereon which, when executed by the one or more computers, cause the one or more computers to perform operations comprising:
identifying a database workload for which index advisor access counters are to be tracked, wherein the database workload includes at least one SQL (Structured Query Language) statement;
executing each SQL statement in the database workload, wherein executing a respective SQL statement comprises:
determining attribute sets of at least one free attribute for which a selection predicate filters a result for the SQL statement;
determining an output cardinality of each selection predicate;
determining, for each selection predicate, whether the output cardinality of the selection predicate is greater than a threshold; and
in response to determining that the output cardinality of a respective selection predicate is not greater than the threshold:
creating logarithmic counters for the selection predicate if logarithmic counters have not yet been created for the selection predicate;
determining, based on the output cardinality of the selection predicate, a logarithmic counter for an attribute set corresponding to the selection predicate; and
incrementing the logarithmic counter; and

providing respective values for the logarithmic counters of the determined attributes to an index advisor, as the index advisor access counters.

10. The system of claim 9 , wherein the logarithmic counter is selected from among a collection of logarithmic counters for the attribute set.

11. The system of claim 10 , wherein determining the logarithmic counter comprises determining a collection index of a particular logarithmic counter in the collection of logarithmic counters for the attribute set.

12. The system of claim 10 , wherein determining the collection index comprises determining the logarithm of the output cardinality of the selection predicate.

13. The system of claim 12 , wherein determining the logarithm of the output cardinality of the selection predicate comprises:
determining a current value of a configurable logarithm base parameter; and
using the current value of the configurable logarithm base parameter when determining the logarithm of the output cardinality of the selection predicate.

14. The system of claim 9 , wherein the threshold is based on a maximum query result size.

15. A computer program product encoded on a non-transitory storage medium, the product comprising non-transitory, computer readable instructions for causing one or more processors to perform operations comprising:
identifying a database workload for which index advisor access counters are to be tracked, wherein the database workload includes at least one SQL (Structured Query Language) statement;
executing each SQL statement in the database workload, wherein executing a respective SQL statement comprises:
determining attribute sets of at least one free attribute for which a selection predicate filters a result for the SQL statement;
determining an output cardinality of each selection predicate;
determining, for each selection predicate, whether the output cardinality of the selection predicate is greater than a threshold; and
in response to determining that the output cardinality of a respective selection predicate is not greater than the threshold:
creating logarithmic counters for the selection predicate if logarithmic counters have not yet been created for the selection predicate;
determining, based on the output cardinality of the selection predicate, a logarithmic counter for an attribute set corresponding to the selection predicate; and
incrementing the logarithmic counter; and

providing respective values for the logarithmic counters of the determined attributes to an index advisor, as the index advisor access counters.

16. The computer program product of claim 15 , wherein the logarithmic counter is selected from among a collection of logarithmic counters for the attribute set.

17. The computer program product of claim 16 , wherein determining the logarithmic counter comprises determining a collection index of a particular logarithmic counter in the collection of logarithmic counters for the attribute set.

18. The computer program product of claim 16 , wherein determining the collection index comprises determining the logarithm of the output cardinality of the selection predicate.

19. The computer program product of claim 18 , wherein determining the logarithm of the output cardinality of the selection predicate comprises:
determining a current value of a configurable logarithm base parameter; and
using the current value of the

Assignees

• Sap Se

Inventors

• Brendle Michael
• May Norman
• Schulze Robert
• Boehm Alexander
• Moerkotte Guido
• Grossniklaus Michael

Classifications

• G06F16/213

  with details for schema evolution support · CPC title

• G06F16/2272

  Management thereof · CPC title

• G06F16/21 Primary

  Design, administration or maintenance of databases · CPC title

• H03M7/6064

  Selection of Compressor · CPC title

• G06F16/2455

  Query execution · CPC title