# US 2017/0255674 A1 · Query plan optimization for large payload columns

> Agents: this data is available as JSON from the patentgrep API and MCP server — see https://patentgrep.com/llms.txt

- Number: US 2017/0255674 A1
- Document ID: US20170255674A1
- Status: Published application
- Kind: A1
- Application number: 15/062,324
- Filed: 2016-03-07
- Published: 2017-09-07
- Earliest claimed: 2016-03-07
- Assignees: International Business Machines Corporation
- Applicants: International Business Machines Corporation
- Inventors: Gopi K. Attaluri; Vijayshankar Raman; David C. Sharpe; Richard S. Sidle; Liping Zhang
- Web page: https://patentgrep.com/patent/US20170255674A1

## CPC

- G06F 16/24544: {Join order optimisation}
- G06F 16/24542: {Plan optimisation}
- G06F 16/24545: {Selectivity estimation or determination}
- G06F 16/2456: {Join operations}
- G06F 16/248: Presentation of query results
- G06F 16/284: {Relational databases}
- G06F: ELECTRIC DIGITAL DATA PROCESSING (computer systems based on specific computational models G06N)

## Abstract

A computer-implemented method includes receiving a query plan. The method includes identifying a plurality of qualified relevant rows and one or more encoding candidate payload columns. The method includes analyzing the relevant rows in the encoding candidate payload columns to yield a count of distinct contents and a payload column width. The method includes estimating a cost and determining whether the cost is larger than an amount of available memory for on-the-fly encoding all of the plurality of encoding candidate payload columns. The method is responsive to the estimated cost being less than the amount of available memory, by on-the-fly encoding the encoding candidates and responsive to the estimated cost being greater than the amount of available memory by on-the-fly encoding fewer than all of the encoding candidates so as not to exceed the available memory, and leaving alone one or more remaining encoding candidate payload columns unencoded.

## Claims

### Claim 1

A computer-implemented method, the method comprising:
receiving a query plan, said query comprising a first phase query plan and a second phase query plan;
identifying a plurality of qualified relevant rows, based on the first phase query plan, each of said plurality of qualified relevant rows comprising one or more payload columns;
identifying one or more encoding candidate payload columns, based on determining those of said plurality of qualified relevant rows that are not encoded;
analyzing said relevant rows of said candidate payload column to yield a count of distinct contents and a payload column width;
estimating a cost, said cost comprising an estimated size for an encoding dictionary sufficient to perform on-the-fly encoding of said plurality of qualified relevant rows of said payload columns, said estimated size being based on said count of distinct contents, said payload column width, and a hash table bucket size for said encoding dictionary;
determining whether said cost is larger than an amount of available memory for on-the-fly encoding all of said plurality of encoding candidate payload columns; and
responsive to said cost being less than said amount of available memory, on-the-fly encoding said encoding candidate payload column;
responsive to said cost being greater than said amount of available memory, on-the-fly encoding fewer than all of said encoding candidates so as not to exceed said available memory, and leaving alone one or more remaining encoding candidate payload columns unencoded.

### Claim 2 (depends on 1)

The computer-implemented method of claim 1, wherein said distinct content is a tuple count.

### Claim 3 (depends on 1)

The computer-implemented method of claim 1, wherein said distinct content is a uniqueness ratio.

### Claim 4 (depends on 1)

The computer-implemented method of claim 1, further comprising processing said second phase query plan, said second phase query plan having been one-the-fly encoded.

### Claim 5 (depends on 1)

The computer-implemented method of claim 1, wherein said query plan is an operation for a join.

### Claim 6

A computer program product comprising:
one or more computer readable storage media and program instructions stored on said one or more computer readable storage media, said program instructions comprising instructions to:
receive a query plan, said query comprising a first phase query plan and a second phase query plan;
identify a plurality of qualified relevant rows, based on the first phase query plan, each of said plurality of qualified relevant rows comprising one or more payload columns;
identify one or more encoding candidate payload columns, based on determining those of said plurality of qualified relevant rows that are not encoded;
analyze said relevant rows of said candidate payload column to yield a count of distinct contents and a payload column width;
estimate a cost, said cost comprising an estimated size for an encoding dictionary sufficient to perform on-the-fly encoding of said plurality of qualified relevant rows of said payload columns, said estimated size being based on said count of distinct contents, said payload column width, and a hash table bucket size for said encoding dictionary;
determine whether said cost is larger than an amount of available memory for on-the-fly encoding all of said plurality of encoding candidate payload columns; and
responsive to said cost being less than said amount of available memory, on-the-fly encode said encoding candidate payload column;
responsive to said cost being greater than said amount of available memory, on-the-fly encode fewer than all of said encoding candidates so as not to exceed said available memory, and leaving alone one or more remaining encoding candidate payload columns unencoded.

### Claim 7 (depends on 6)

The computer program product of claim 6, wherein said distinct content is a tuple count.

### Claim 8 (depends on 6)

The computer program product of claim 6, wherein said distinct content is a uniqueness ratio.

### Claim 9 (depends on 6)

The computer program product of claim 6, further comprising instructions to process said second phase query plan, said second phase query plan having been one-the-fly encoded.

### Claim 10 (depends on 6)

The computer program product of claim 6, wherein said query plan is an operation for a join.

### Claim 11

A computer system comprising:
one or more computer processors;
one or more computer readable storage media;
computer program instructions; and
said computer program instructions being stored on said computer readable storage media for execution by at least one of said one or more processors, said computer program instructions comprising instructions to:
receive a query plan, said query comprising a first phase query plan and a second phase query plan;
identify a plurality of qualified relevant rows, based on the first phase query plan, each of said plurality of qualified relevant rows comprising one or more payload columns;
identify one or more encoding candidate payload columns, based on determining those of said plurality of qualified relevant rows that are not encoded;
analyze said relevant rows of said candidate payload column to yield a count of distinct contents and a payload column width;
estimate a cost, said cost comprising an estimated size for an encoding dictionary sufficient to perform on-the-fly encoding of said plurality of qualified relevant rows of said payload columns, said estimated size being based on said count of distinct contents, said payload column width, and a hash table bucket size for said encoding dictionary;
determine whether said cost is larger than an amount of available memory for on-the-fly encoding all of said plurality of encoding candidate payload columns; and
responsive to said cost being less than said amount of available memory, on-the-fly encode said encoding candidate payload column;
responsive to said cost being greater than said amount of available memory, on-the-fly encode fewer than all of said encoding candidates so as not to exceed said available memory, and leaving alone one or more remaining encoding candidate payload columns unencoded.

### Claim 12 (depends on 11)

The computer system of claim 11, wherein said distinct content is a tuple count.

### Claim 13 (depends on 11)

The computer system of claim 11, wherein said distinct content is a uniqueness ratio.

### Claim 14 (depends on 11)

The computer system of claim 11, further comprising instructions to process said second phase query plan, said second phase query plan having been one-the-fly encoded.

### Claim 15 (depends on 11)

The computer system of claim 11, wherein said query plan is an operation for a join.
