Query optimization with zone map selectivity modeling

Patent metadata

Field	Value
Publication number	US-2016162599-A1
Application number	US-201414562325-A
Country	US
Kind code	A1
Filing date	Dec 5, 2014
Priority date	Dec 5, 2014
Publication date	Jun 9, 2016
Grant date	—

Abstract

According to one embodiment of the present invention, a system for processes a query for accessing data within one or more database objects stores an element of a database object among a plurality of different storage regions. Each storage region is associated with first and second range values indicating a value range for element values within that storage region. The system examines the first and second range values for the storage regions of each database object element and determines an effectiveness value representing a degree of overlap between the storage regions of that database object element. The system determines a selectivity model for the storage regions for each database object utilizing the effectiveness value, determines a query plan based on the selectivity model, and executes the query plan. Embodiments of the present invention further include a method and computer program product for processing a query in substantially the same manners.

First claim

1 . (canceled)

2 . (canceled)

3 . (canceled)

4 . (canceled)

5 . (canceled)

6 . (canceled)

7 . (canceled)

8 . (canceled)

9 . (canceled)

2

10 . A system for processing a query for accessing data within one or more database objects, wherein an element of a database object is stored among a plurality of different storage regions with each storage region being associated with first and second range values indicating a value range for element values within that storage region, the system comprising:
at least one processor configured to: determine an effectiveness value representing a degree of overlap between the storage regions of each database object element by examining the first and second range values for the storage regions of that database object element; determine a selectivity model for the storage regions for each database object element of the query utilizing the effectiveness value; determine a query plan for the query based on the selectivity model for one or more corresponding database object elements; and execute the query plan to access data from the database objects for the query.

11 . The system of claim 10 , wherein the effectiveness value for each database object element is in a value range between zero and one.

12 . The system of claim 10 , wherein determining the selectivity model further comprises:
combining the effectiveness value of a database object element with a total quantity of storage regions for that database object element to determine for the selectivity model an estimated amount of pages to be accessed for the query.

13 . The system of claim 10 , wherein each database object includes a database table, and the database object element includes a database table column.

14 . The system of claim 13 , wherein the query includes a restriction on plural database table columns and determining the selectivity model further comprises:
combining the selectivity models for the database table columns of the restriction with column correlation statistics to determine an amount of input/output operations for the plural column restriction.

15 . The system of claim 10 , wherein the query plan indicates at least one of a scan order and join order for the database objects based on the selectivity model of the corresponding database object elements.

16 . A computer program product for processing a query for accessing data within one or more database objects, wherein an element of a database object is stored among a plurality of different storage regions with each storage region being associated with first and second range values indicating a value range for element values within that storage region, the computer program product comprising:
a computer readable storage medium having computer readable program code embodied therewith for execution on a processing system, the computer readable program code comprising computer readable program code configured to: determine an effectiveness value representing a degree of overlap between the storage regions of each database object element by examining the first and second range values for the storage regions of that database object element; determine a selectivity model for the storage regions for each database object element of the query utilizing the effectiveness value; determine a query plan for the query based on the selectivity model for one or more corresponding database object elements; and execute the query plan to access data from the database objects for the query.

17 . The computer program product of claim 10 , wherein the effectiveness value for each database object element is in a value range between zero and one.

18 . The computer program product of claim 10 , wherein determining the selectivity model further comprises:
combining the effectiveness value of a database object element with a total quantity of storage regions for that database object element to determine for the selectivity model an estimated amount of pages to be accessed for the query.

19 . The computer program product of claim 10 , wherein each database object includes a database table, and the database object element includes a database table column.

20 . The computer program product of claim 19 , wherein the query includes a restriction on plural database table columns and determining the selectivity model further comprises:
combining the selectivity models for the database table columns of the restriction with column correlation statistics to determine an amount of input/output operations for the plural column restriction.

Assignees

• IBM

Inventors

• Dickie Garth A
• Wolf Jay B

Classifications

• G06F16/9017

  using directory or table look-up (use of a directory or look-up table in file systems G06F16/13) · CPC title

• G06F16/24545

  Selectivity estimation or determination · CPC title

• G06F16/24544 Primary

  Join order optimisation · CPC title

• G06F16/90335

  Query processing · CPC title

• G06F17/30979

  Physics · mapped topic