Labelling a column changes access path

With table TB1(COL1, COL2, COL3, COL4) and index IX1(COL1, COL2, COL3), query:

SELECT COL1, COL2, VARCHAR(COL3) COL3, COL4

FROM TB1

WHERE COL1 = ?

AND COL2 = ?

ORDER BY COL1, COL2, COL3;

requires sort even though the order by is following the index sequence.

All it takes to "fix" it is change the label for VARCHAR(COL3) so that it is not the same as the column name, e.g.

SELECT COL1, COL2, VARCHAR(COL3) COL33, COL4....

The latter select won't require sort.

Could you please investigate why the contents of the list of the columns in a SELECT has impact on the access path in a situation like this?

Post comment

Guest

Reply
| Jun 6, 2019

Hi Janet,
In relation to this, may I ask what the sequence of accessed rows has to do with the data that is being retrieved afterwards?
Db2 goes into the index and searches where to start scanning the range or pages. How do you relate this to the fact that the returned data is somehow later processed as it is being returned to the application?
Regards,
Mark

[cid:image002.jpg@01D51C80.403DC800]Mark Smyth
IT Business Service Management - Technology Operations
7th Floor, Hume House, Pembroke Road, Dublin 4
mark.m.smyth@aib.ie
Technology Operations - Delivering Excellence Together

Image 1.png

Image 2.jpeg

Image 1.png
Open full size
Image 2.jpeg
Open full size
Image 1.png Image 2.jpeg

0 reply Hide replies

Admin

Janet Figone

Reply
| May 9, 2019

Thank you for submitting this Aha! Idea.
Db2 for z/OS development has reviewed this and determined this is working as designed. In the example - by "renaming" or labelling the column VARCHAR(COL3) AS COL3 - the ORDER BY COL1, COL2, COL3 now replaces COL3 with VARCHAR(COL3). This no longer matches the index on COL1, COL2, COL3 and thus a sort is required. By labelling the VARCHAR(COL3) expression as a name that does not match the column name in the ORDER BY, the ORDER BY will continue to match the index sequence allowing the sort to be avoided.
Sincerely,
Db2 for z/OS Team

0 reply Hide replies

By clicking the "Post Comment" or "Submit Idea" button, you are agreeing to the IBM Ideas Portal Terms of Use.
Do not place IBM confidential, company confidential, or personal information into any field.

Please enter your email address

RELATED IDEAS

Labelling a column changes access path