DB2 z/OS V7 - Avoiding SORT on ORDER BY

Ray Price

DB2 z/OS V7 - Avoiding SORT on ORDER BY
Hi,

I have a performance critical bit of SQL, and I'm trying to tune it to
prevent results set materialisation. This is because the number of rows
returned can be large, and the application only needs to retrieve a small
number of rows. I have two tables to JOIN, which are something like this:

Table1
COL1
COL2
Index on COL1 and COL2

Table2
COLA
COLB
COLC
COLD
Index on COLA, COLB, COLC, COLD

There are other columns, but these are the important ones.

The SQL is like this:

SELECT (various)
FROM Table1
INNER JOIN
Table2
ON COL2 = COLA

WHERE COL1 = literal


ORDER BY COLA, COLB, COLC, COLD.
OPTIMIZE FOR 1 ROWS

The EXPLAIN shows the indexes are being used as expected, MATCHCOLS 1 in
both cases, no prefetch. So the data is being accessed such that no SORT is
needed. But DB2 is doing a SORT. I can't see why. I tried OPTHINT, but even
though the HINT is used, a SORT is added. I just can't see why this is. If I
use ORDER BY COLA, there is no SORT, and the data is actually in the order I
need. But I don't want to rely on this if it could change.

Can anyone tell me why the SORT is being done, please? And any way to
prevent it?


Thanks.

Have a nice day.



Regards,

Ray Price
Dresdner Kleinwort Wasserstein
London.



--------------------------------------------------------------------------------
The information contained herein is confidential and is intended solely for the
addressee. Access by any other party is unauthorised without the express
written permission of the sender. If you are not the intended recipient, please
contact the sender either via the company switchboard on +44 (0)20 7623 8000, or
via e-mail return. If you have received this e-mail in error or wish to read our
e-mail disclaimer statement and monitoring policy, please refer to
http://www.drkw.com/disc/email/ or contact the sender. 3167
--------------------------------------------------------------------------------


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Phil Grainger

Re: DB2 z/OS V7 - Avoiding SORT on ORDER BY
(in response to Ray Price)
Hi Ray,

I imagine that DB2 is doing a sort because it is only considering the
first column of your ABCD index and doesn't actually notice that your
ORDER BY matches the other 3 columns exactly.

Shame really - as you say the data IS in the sequence you want, DB2 just
doesn't realise it

The saving grace is that the sort will be pretty cheap (i.e. there is no
sorting to be done), but the cost still won't be zero.

You could try adding predicates for columns B, C and D such that all
rows qualify in an attempt to get MATCHCOLS 4, but then there will be an
additional cost to checking three redundant predicates for every row -
that might be more than the non-sort you have now

As things stand, I think you are stuck with what you have until DB2
learns to use it's indexes better. There are changes in this area for
v8, but I am not sure this is one of them


Phil Grainger
CA
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]



_____

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Price, Ray
Sent: 24 November 2005 09:49
To: [login to unmask email]
Subject: [DB2-L] DB2 z/OS V7 - Avoiding SORT on ORDER BY



Hi,

I have a performance critical bit of SQL, and I'm trying to tune it to
prevent results set materialisation. This is because the number of rows
returned can be large, and the application only needs to retrieve a
small number of rows. I have two tables to JOIN, which are something
like this:

Table1

COL1

COL2

Index on COL1 and COL2

Table2

COLA

COLB

COLC

COLD

Index on COLA, COLB, COLC, COLD

There are other columns, but these are the important ones.

The SQL is like this:

SELECT (various)

FROM Table1

INNER JOIN

Table2

ON COL2 = COLA

WHERE COL1 = literal


ORDER BY COLA, COLB, COLC, COLD.

OPTIMIZE FOR 1 ROWS

The EXPLAIN shows the indexes are being used as expected, MATCHCOLS 1 in
both cases, no prefetch. So the data is being accessed such that no SORT
is needed. But DB2 is doing a SORT. I can't see why. I tried OPTHINT,
but even though the HINT is used, a SORT is added. I just can't see why
this is. If I use ORDER BY COLA, there is no SORT, and the data is
actually in the order I need. But I don't want to rely on this if it
could change.

Can anyone tell me why the SORT is being done, please? And any way to
prevent it?


Thanks.

Have a nice day.

Regards,

Ray Price

Dresdner Kleinwort Wasserstein

London.



------------------------------------------------------------------------
--------
The information contained herein is confidential and is intended solely
for the
addressee. Access by any other party is unauthorised without the express
written permission of the sender. If you are not the intended recipient,
please
contact the sender either via the company switchboard on +44 (0)20 7623
8000, or
via e-mail return. If you have received this e-mail in error or wish to
read our
e-mail disclaimer statement and monitoring policy, please refer to
http://www.drkw.com/disc/email/ or contact the sender. 3167
------------------------------------------------------------------------
--------
------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Agus Kwee

Re: DB2 z/OS V7 - Avoiding SORT on ORDER BY
(in response to Phil Grainger)
Hi Ray,

Is there any differences in the following information in the PLAN_TABLE rows
when you were using ORDER BY COLA and ORDER BY COLA,COLB,COLC,COLB:
1. Which table is the first/composite table in the join (METHOD = 0)?
2. Which table is the second/new table, and what value does it have in
the METHOD column ( 1 or 2 or 4 ) ?

Regards,
Agus Kwee
Themis Training
http://www.themisinc.com


----- Original Message -----
From: "Price, Ray" <[login to unmask email]>
Date: Thursday, November 24, 2005 4:49 am
Subject: [DB2-L] DB2 z/OS V7 - Avoiding SORT on ORDER BY

> Hi,
>
> I have a performance critical bit of SQL, and I'm trying to tune it to
> prevent results set materialisation. This is because the number of
> rowsreturned can be large, and the application only needs to
> retrieve a small
> number of rows. I have two tables to JOIN, which are something like
> this:
> Table1
> COL1
> COL2
> Index on COL1 and COL2
>
> Table2
> COLA
> COLB
> COLC
> COLD
> Index on COLA, COLB, COLC, COLD
>
> There are other columns, but these are the important ones.
>
> The SQL is like this:
>
> SELECT (various)
> FROM Table1
> INNER JOIN
> Table2
> ON COL2 = COLA
>
> WHERE COL1 = literal
>
>
> ORDER BY COLA, COLB, COLC, COLD.
> OPTIMIZE FOR 1 ROWS
>
> The EXPLAIN shows the indexes are being used as expected, MATCHCOLS
> 1 in
> both cases, no prefetch. So the data is being accessed such that no
> SORT is
> needed. But DB2 is doing a SORT. I can't see why. I tried OPTHINT,
> but even
> though the HINT is used, a SORT is added. I just can't see why this
> is. If I
> use ORDER BY COLA, there is no SORT, and the data is actually in
> the order I
> need. But I don't want to rely on this if it could change.
>
> Can anyone tell me why the SORT is being done, please? And any way to
> prevent it?
>
>
> Thanks.
>
> Have a nice day.
>
>
>
> Regards,
>
> Ray Price
> Dresdner Kleinwort Wasserstein
> London.
>
>
>
> --------------------------------------------------------------------
> ------------
> The information contained herein is confidential and is intended
> solely for the
> addressee. Access by any other party is unauthorised without the
> expresswritten permission of the sender. If you are not the
> intended recipient, please
> contact the sender either via the company switchboard on +44 (0)20
> 7623 8000, or
> via e-mail return. If you have received this e-mail in error or
> wish to read our
> e-mail disclaimer statement and monitoring policy, please refer to
> http://www.drkw.com/disc/email/ or contact the sender. 3167
> --------------------------------------------------------------------
> ------------
>
>
> --------------------------------------------------------------------
> -------------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives
> and home page at http://www.idugdb2-l.org/archives/db2-l.html. From
> that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
> http://www.idugdb2-l.org. The IDUG List Admins can be reached at
> [login to unmask email] Find out the latest on IDUG
> conferences at http://conferences.idug.org/index.cfm
>

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Ray Price

Re: DB2 z/OS V7 - Avoiding SORT on ORDER BY
(in response to Agus Kwee)
Hi Agus,

Thanks for your reply. I compared PLAN_TABLE entries for the two versions of
ORDER BY, and they are identical, except for the extra line for the SORT
when ordering by 4 columns. The JOIN method for the second table (table2) is
1 in both cases.



Regards,

Ray Price
Dresdner Kleinwort Wasserstein
London


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Agus Kwee
Sent: 24 November 2005 11:34
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 z/OS V7 - Avoiding SORT on ORDER BY

Hi Ray,

Is there any differences in the following information in the PLAN_TABLE rows

when you were using ORDER BY COLA and ORDER BY COLA,COLB,COLC,COLB:
1. Which table is the first/composite table in the join (METHOD = 0)?
2. Which table is the second/new table, and what value does it have in
the METHOD column ( 1 or 2 or 4 ) ?

Regards,
Agus Kwee
Themis Training
http://www.themisinc.com


----- Original Message -----
From: "Price, Ray" <[login to unmask email]>
Date: Thursday, November 24, 2005 4:49 am
Subject: [DB2-L] DB2 z/OS V7 - Avoiding SORT on ORDER BY

> Hi,
>
> I have a performance critical bit of SQL, and I'm trying to tune it to
> prevent results set materialisation. This is because the number of
> rowsreturned can be large, and the application only needs to
> retrieve a small
> number of rows. I have two tables to JOIN, which are something like
> this:
> Table1
> COL1
> COL2
> Index on COL1 and COL2
>
> Table2
> COLA
> COLB
> COLC
> COLD
> Index on COLA, COLB, COLC, COLD
>
> There are other columns, but these are the important ones.
>
> The SQL is like this:
>
> SELECT (various)
> FROM Table1
> INNER JOIN
> Table2
> ON COL2 = COLA
>
> WHERE COL1 = literal
>
>
> ORDER BY COLA, COLB, COLC, COLD.
> OPTIMIZE FOR 1 ROWS
>
> The EXPLAIN shows the indexes are being used as expected, MATCHCOLS
> 1 in
> both cases, no prefetch. So the data is being accessed such that no
> SORT is
> needed. But DB2 is doing a SORT. I can't see why. I tried OPTHINT,
> but even
> though the HINT is used, a SORT is added. I just can't see why this
> is. If I
> use ORDER BY COLA, there is no SORT, and the data is actually in
> the order I
> need. But I don't want to rely on this if it could change.
>
> Can anyone tell me why the SORT is being done, please? And any way to
> prevent it?
>
>
> Thanks.
>
> Have a nice day.
>
>
>
> Regards,
>
> Ray Price
> Dresdner Kleinwort Wasserstein
> London.
>
>
>
> --------------------------------------------------------------------
> ------------
> The information contained herein is confidential and is intended
> solely for the
> addressee. Access by any other party is unauthorised without the
> expresswritten permission of the sender. If you are not the
> intended recipient, please
> contact the sender either via the company switchboard on +44 (0)20
> 7623 8000, or
> via e-mail return. If you have received this e-mail in error or
> wish to read our
> e-mail disclaimer statement and monitoring policy, please refer to
> http://www.drkw.com/disc/email/ or contact the sender. 3167
> --------------------------------------------------------------------
> ------------
>
>
> --------------------------------------------------------------------
> -------------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives
> and home page at http://www.idugdb2-l.org/archives/db2-l.html. From
> that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
> http://www.idugdb2-l.org. The IDUG List Admins can be reached at
> [login to unmask email] Find out the latest on IDUG
> conferences at http://conferences.idug.org/index.cfm
>

----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org.
The IDUG List Admins can be reached at [login to unmask email] Find
out the latest on IDUG conferences at http://conferences.idug.org/index.cfm


--------------------------------------------------------------------------------
The information contained herein is confidential and is intended solely for the
addressee. Access by any other party is unauthorised without the express
written permission of the sender. If you are not the intended recipient, please
contact the sender either via the company switchboard on +44 (0)20 7623 8000, or
via e-mail return. If you have received this e-mail in error or wish to read our
e-mail disclaimer statement and monitoring policy, please refer to
http://www.drkw.com/disc/email/ or contact the sender. 3166
--------------------------------------------------------------------------------

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Walter Jani&#223;en

Re: DB2 z/OS V7 - Avoiding SORT on ORDER BY
(in response to James Campbell)
I agree with James, that DB2 isn't smart enough to avoid the sort, because
table1 is the outer. If you are able to switch the join order, I think the
sort will disappear. Well, you then get a non-matching index scan for
table2, but a 2 matching index-access for table1. I don't know, how big
each one is and whether you can afford this.

To give this a try, you have to fake the statistics.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Agus Kwee

Re: DB2 z/OS V7 - Avoiding SORT on ORDER BY
(in response to Walter Janißen)
Hi Ray,

Like what the other responders mentioned, there are a lot of reasons why you are
getting the extra row in plan_table with method column value 3 indicating a sort
on the join result table.

I was trying to emulate your join using the IBM plan tables:
select * from projact pa join empprojact ep
on pa.projno = ep.projno
where pa.projno = ?
order by ep.projno, ep.actno, ep.emstdate,ep.empno

1.without OPTIMIZE FOR 1 ROW the plan table has an extra row for the sort
because the PREFETCH = 'L' cancelled the order of the 4 column values in the
index.
2. with OPTIMIZE FOR 1 ROW:
-there is no extra row for the sort
- the emprojact table becomes the first table in the plan table, because
the 4 columns used in the order by are the exact columns of the clustering index.
Is the index on cola,colb,colc,cold a clustering index?
The join METHOD were always: 1

Regards,
Agus Kwee
Themis Training
http://www.themisinc.com


----- Original Message -----
From: "Price, Ray" <[login to unmask email]>
Date: Thursday, November 24, 2005 7:11 am
Subject: Re: [DB2-L] DB2 z/OS V7 - Avoiding SORT on ORDER BY

> Hi Agus,
>
> Thanks for your reply. I compared PLAN_TABLE entries for the two
> versions of
> ORDER BY, and they are identical, except for the extra line for
> the SORT
> when ordering by 4 columns. The JOIN method for the second table
> (table2) is
> 1 in both cases.
>
>
>
> Regards,
>
> Ray Price
> Dresdner Kleinwort Wasserstein
> London
>
>

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

M. Khalid Khan

Re: DB2 z/OS V7 - Avoiding SORT on ORDER BY
(in response to Agus Kwee)
DB2 z/OS V7 - Avoiding SORT on ORDER BYHi Ray
Since no columns from Table1 in are required to be in SELECT list (COL1 is known and COL2 is same as COLA), you can try this:

SELECT ...
FROM TABLE2
WHERE COLA IN ( SELECT COL2 FROM TABLE1 WHERE COL1 = literal )
ORDER BY COLA, COLB, COLC, COLD

HTH
Khalid


----- Original Message -----
From: Price, Ray
Newsgroups: bit.listserv.db2-l
To: [login to unmask email]
Sent: Thursday, November 24, 2005 3:49 AM
Subject: [DB2-L] DB2 z/OS V7 - Avoiding SORT on ORDER BY


Hi,

I have a performance critical bit of SQL, and I'm trying to tune it to prevent results set materialisation. This is because the number of rows returned can be large, and the application only needs to retrieve a small number of rows. I have two tables to JOIN, which are something like this:

Table1

COL1

COL2

Index on COL1 and COL2

Table2

COLA

COLB

COLC

COLD

Index on COLA, COLB, COLC, COLD

There are other columns, but these are the important ones.

The SQL is like this:

SELECT (various)

FROM Table1

INNER JOIN

Table2

ON COL2 = COLA

WHERE COL1 = literal



ORDER BY COLA, COLB, COLC, COLD.

OPTIMIZE FOR 1 ROWS

The EXPLAIN shows the indexes are being used as expected, MATCHCOLS 1 in both cases, no prefetch. So the data is being accessed such that no SORT is needed. But DB2 is doing a SORT. I can't see why. I tried OPTHINT, but even though the HINT is used, a SORT is added. I just can't see why this is. If I use ORDER BY COLA, there is no SORT, and the data is actually in the order I need. But I don't want to rely on this if it could change.

Can anyone tell me why the SORT is being done, please? And any way to prevent it?



Thanks.

Have a nice day.


Regards,

Ray Price

Dresdner Kleinwort Wasserstein

London.




--------------------------------------------------------------------------------
The information contained herein is confidential and is intended solely for the
addressee. Access by any other party is unauthorised without the express
written permission of the sender. If you are not the intended recipient, please
contact the sender either via the company switchboard on +44 (0)20 7623 8000, or
via e-mail return. If you have received this e-mail in error or wish to read our
e-mail disclaimer statement and monitoring policy, please refer to
http://www.drkw.com/disc/email/ or contact the sender. 3167
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

James Campbell

Re: DB2 z/OS V7 - Avoiding SORT on ORDER BY
(in response to Ray Price)
I think the sort is because DB2 cannot carry forward the idea that
data is sorted in COL2 sequence. If it did it would realise that it
must also be sorted in COLA sequence.

There are various comments to the effect that ORDER BY will only
avoid a sort if the columns are in the outer table.

If sort avoidance is so critical you might need to "roll your own"
SELECT COL2
FROM Table1
WHERE COL1 = literal

for each col2:
SELECT (various)
FROM Table2
WHERE COLA = :HV-COL2
ORDER BY COLA, COLB, COLC, COLD.
OPTIMIZE FOR 1 ROWS

James Campbell


On 24 Nov 2005 at 10:16, Grainger, Phil wrote:

>
> Hi Ray,
>
> I imagine that DB2 is doing a sort because it is only considering the first column of your ABCD
> index and doesn't actually notice that your ORDER BY matches the other 3 columns exactly.
>
> Shame really - as you say the data IS in the sequence you want, DB2 just doesn't realise it
>
> The saving grace is that the sort will be pretty cheap (i.e. there is no sorting to be done), but the
> cost still won't be zero.
>
> You could try adding predicates for columns B, C and D such that allrows qualify in an attempt
> to get MATCHCOLS 4, but then there will be an additional cost to checking three redundant
> predicates for every row - that might be more than the non-sort you have now
>
> As things stand, I think you are stuck with what you have until DB2 learns to use it's indexes
> better. There are changes in this area for v8, but I am not sure this is one of them
>
> Phil Grainger
> CA
> Product Manager, DB2
> Tel: +44 (0)161 928 9334
> Fax: +44 (0)161 941 3775
> Mobile: +44 (0)7970 125 752
> [login to unmask email]
>
>
>
> From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Price, Ray
> Sent: 24 November 2005 09:49
> To: [login to unmask email]
> Subject: [DB2-L] DB2 z/OS V7 - Avoiding SORT on ORDER BY
>
> Hi,
> I have a performance critical bit of SQL, and I'm trying to tune it to prevent results set
> materialisation. This is because the number of rows returned can be large, and the application
> only needs to retrieve a small number of rows. I have two tables to JOIN, which are something
> like this:
> Table1
> COL1
> COL2
> Index on COL1 and COL2
> Table2
> COLA
> COLB
> COLC
> COLD
> Index on COLA, COLB, COLC, COLD
> There are other columns, but these are the important ones.
> The SQL is like this:
> SELECT (various)
> FROM Table1
> INNER JOIN
> Table2
> ON COL2 = COLA
> WHERE COL1 = literal
>
> ORDER BY COLA, COLB, COLC, COLD.
> OPTIMIZE FOR 1 ROWS
> The EXPLAIN shows the indexes are being used as expected, MATCHCOLS 1 in both cases, no
> prefetch. So the data is being accessed such that no SORT is needed. But DB2 is doing a
> SORT. I can't see why. I tried OPTHINT, but even though the HINT is used, a SORT is added. I
> just can't see why this is. If I use ORDER BY COLA, there is no SORT, and the data is actually in
> the order I need. But I don't want to rely on this if it could change.
> Can anyone tell me why the SORT is being done, please? And any way to prevent it?
>
> Thanks.
> Have a nice day.
> Regards,
> Ray Price
> Dresdner Kleinwort Wasserstein
> London.
>
>
> --------------------------------------------------------------------------------
> The information contained herein is confidential and is intended solely for the
> addressee. Access by any other party is unauthorised without the express
> written permission of the sender. If you are not the intended recipient, please
> contact the sender either via the company switchboard on +44 (0)20 7623 8000, or
> via e-mail return. If you have received this e-mail in error or wish to read our
> e-mail disclaimer statement and monitoring policy, please refer to
> http://www.drkw.com/disc/email/ or contact the sender. 3167
> --------------------------------------------------------------------------------
> --------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list.
> To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-
> l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
> http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-
> [login to unmask email] Find out the latest on IDUG conferences at
> http://conferences.idug.org/index.cfm
> --------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list.
> To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-
> l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
> http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-
> [login to unmask email] Find out the latest on IDUG conferences at
> http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Philip Sevetson

Re: DB2 z/OS V7 - Avoiding SORT on ORDER BY
(in response to M. Khalid Khan)
Ray,

Try adding "Table2.COLA = Table2.COLA" to the query to encourage DB2 to use
Table2 as the outer table for this query. It looks like you're getting
Table1 as the outer query; but since you then join on Table1.COL2 =
Table2.COLA, and COL2 is not in order in the index, the result set is not
ordered by COLA, COLB, etc.

You could ALSO try curing this with an index on Table1.COL2 specifically;
then the fetches from Table2 would be in order by COLA, COLB, COLC, COLD.
This would pretty certainly _radically_ improve your access time. (If
you've got a reasonably short list of columns needed from Table 1, you could
consider making a "fat" index of COL2 followed by all the other columns
you'll need.)

Note to all; I wouldn't have thought of this before yesterday, but I just
this week took a mind-blowingly good course on index design from Larry
Kintisch of Able Information Systems, Inc. [unpaid plug] which showed me
this among other techniques.

--Phil S.


On 11/24/05, Price, Ray <[login to unmask email]> wrote:
>
> Hi,
>
> I have a performance critical bit of SQL, and I'm trying to tune it to
> prevent results set materialisation. This is because the number of rows
> returned can be large, and the application only needs to retrieve a small
> number of rows. I have two tables to JOIN, which are something like this:
>
> Table1
>
> COL1
>
> COL2
>
> Index on COL1 and COL2
>
> Table2
>
> COLA
>
> COLB
>
> COLC
>
> COLD
>
> Index on COLA, COLB, COLC, COLD
>
> There are other columns, but these are the important ones.
>
> The SQL is like this:
>
> SELECT (various)
>
> FROM Table1
>
> INNER JOIN
>
> Table2
>
> ON COL2 = COLA
>
> WHERE COL1 = literal
>
> ORDER BY COLA, COLB, COLC, COLD.
>
> OPTIMIZE FOR 1 ROWS
>
> The EXPLAIN shows the indexes are being used as expected, MATCHCOLS 1 in
> both cases, no prefetch. So the data is being accessed such that no SORT
> is needed. But DB2 is doing a SORT. I can't see why. I tried OPTHINT, but
> even though the HINT is used, a SORT is added. I just can't see why this
> is. If I use ORDER BY COLA, there is no SORT, and the data is actually in
> the order I need. But I don't want to rely on this if it could change.
>
> Can anyone tell me why the SORT is being done, please? And any way to
> prevent it?
>
> Thanks.
>
> Have a nice day.
>
> Regards,
>
> Ray Price
>
> Dresdner Kleinwort Wasserstein
>
> London.
>
>
>
>
> --------------------------------------------------------------------------------
> The information contained herein is confidential and is intended solely
> for the
> addressee. Access by any other party is unauthorised without the express
> written permission of the sender. If you are not the intended recipient,
> please
> contact the sender either via the company switchboard on +44 (0)20 7623
> 8000, or
> via e-mail return. If you have received this e-mail in error or wish to
> read our
> e-mail disclaimer statement and monitoring policy, please refer to
> http://www.drkw.com/disc/email/ or contact the sender. 3167
>
> --------------------------------------------------------------------------------
> ---------------------------------------------------------------------------------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
> page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
> select "Join or Leave the list". The IDUG DB2-L FAQ is at
> http://www.idugdb2-l.org. The IDUG List Admins can be reached at
> [login to unmask email] Find out the latest on IDUG conferences
> at http://conferences.idug.org/index.cfm




--
--Phil Sevetson
IBM Certified Solutions Expert -- DB2 V7.1 Database Administration for
OS/390
IBM Certified Database Administrator -- DB2 Universal Database V8.1 for zOS
Wakefern Food Corporation
Edison, NJ, USA
ANY OPINIONS OR RECOMMENDATIONS EXPRESSED IN THIS EMAIL
ARE MY OWN, NOT MY EMPLOYER'S OR IBM'S.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm