Simple but annoying SQL statement

Raymond Bell

Simple but annoying SQL statement
Colleagues,

I've got a simple but annoying SQL statement I'm trying to make run quickly
but I'm stumbling on the no-doubt obvious fix. I have a table, T1, with
4.5m rows. I have a table, T2, with 180 rows. The definitions of T1 and T2
are the same (create table T2 like T1, indexes created to match) and
runstats are current for both. Both tables are in the same tablespace - not
ideal, but the smaller one is just for the life of the task I've got.

T2's rows have come from T1. T2 is a backup of these rows, as the originals
in T1 are to be deleted. I want to delete the rows from T1 where there's a
matching row in T2. Something like this, in fact:

DELECT
FROM owner.T1 A
WHERE EXISTS (
SELECT 1
FROM owner.T2 B
WHERE B.keycol1=A.keycol1
AND B.keycol2=A.keycol2 )

Simple, huh? Explaining this shows T1 is scanned first (4.5m rows,
remember) then matching rows from T2 (only 180 rows) are found using
matchcols=2. I want it the other way around i.e. scan T2 then use
matchcols=2 to T1.

What can I do to get DB2 to access T2 first?

Cheers,


Raymond



Gregory Palgrave

Re: Simple but annoying SQL statement
(in response to Raymond Bell)
Given there's only 180 rows in T2, why not just generate the delete
statements?

e.g.

SELECT 'DELETE FROM T1 WHERE keycol1='||x'7D'||CAT_M||x'7D'
||' AND keycol2='||x'7D'||PROD_M||x'7D5E'
FROM T2

Generates statements like:

DELETE FROM T1 WHERE keycol1='Fred' AND keycol2='John';

The x'5E' just puts a ';' on the end (use x'3B' for little DB2...), the
x'7D' puts a single quote (use x'27' for little DB2).

Capture the output and run it through DB2. Easy.

You may need to modify this SQL if the keycols are numeric etc...

Regards,

Greg Palgrave
Technical Services
Unisys West
eMail : [login to unmask email]


---------------------------------------- Message History
----------------------------------------


From: "Bell, Raymond W" <[login to unmask email]>@RYCI.COM> on
13/12/2000 09:54



Please respond to DB2 Data Base Discussion List <[login to unmask email]>

Sent by: DB2 Data Base Discussion List <[login to unmask email]>


To: [login to unmask email]
cc:

Subject: Simple but annoying SQL statement


Colleagues,

I've got a simple but annoying SQL statement I'm trying to make run quickly
but I'm stumbling on the no-doubt obvious fix. I have a table, T1, with
4.5m rows. I have a table, T2, with 180 rows. The definitions of T1 and
T2
are the same (create table T2 like T1, indexes created to match) and
runstats are current for both. Both tables are in the same tablespace -
not
ideal, but the smaller one is just for the life of the task I've got.

T2's rows have come from T1. T2 is a backup of these rows, as the
originals
in T1 are to be deleted. I want to delete the rows from T1 where there's a
matching row in T2. Something like this, in fact:

DELECT
FROM owner.T1 A
WHERE EXISTS (
SELECT 1
FROM owner.T2 B
WHERE B.keycol1=A.keycol1
AND B.keycol2=A.keycol2 )

Simple, huh? Explaining this shows T1 is scanned first (4.5m rows,
remember) then matching rows from T2 (only 180 rows) are found using
matchcols=2. I want it the other way around i.e. scan T2 then use
matchcols=2 to T1.

What can I do to get DB2 to access T2 first?

Cheers,


Raymond








_______________________________________________________________________________
Unencrypted electronic mail is not secure and may not be authentic.
If you have any doubts as to the contents please telephone to confirm.

This electronic transmission is intended only for those to whom it is
addressed. It may contain information that is confidential, privileged
or exempt from disclosure by law. Any claim to privilege is not waived
or lost by reason of mistaken transmission of this information.
If you are not the intended recipient you must not distribute or copy this
transmission and should please notify the sender. Your costs for doing
this will be reimbursed by the sender.
_______________________________________________________________________________



Steve Tennant

Re: Simple but annoying SQL statement
(in response to Gregory Palgrave)
****************************************************************
IMPORTANT

The information transmitted is for the use of the intended
recipient only and may contain confidential and/or legally
privileged material. Any review, re-transmission, disclosure,
dissemination or other use of, or taking of any action in
reliance upon, this information by persons or entities other
than the intended recipient is prohibited and may result in
severe penalties. If you have received this e-mail in error
please notify the Privacy Hotline of the Australian Taxation
Office, telephone 13 2869 and delete all copies of this
transmission together with any attachments.

****************************************************************
Isn't it just a case of removing the correlation? Delete where id in
(select id from t2).

Steve T

-----Original Message-----
From: Bell, Raymond W [mailto:[login to unmask email]
Sent: Wednesday, 13 December 2000 12:54
To: [login to unmask email]
Subject: Simple but annoying SQL statement


Colleagues,

I've got a simple but annoying SQL statement I'm trying to make run quickly
but I'm stumbling on the no-doubt obvious fix. I have a table, T1, with
4.5m rows. I have a table, T2, with 180 rows. The definitions of T1 and T2
are the same (create table T2 like T1, indexes created to match) and
runstats are current for both. Both tables are in the same tablespace - not
ideal, but the smaller one is just for the life of the task I've got.

T2's rows have come from T1. T2 is a backup of these rows, as the originals
in T1 are to be deleted. I want to delete the rows from T1 where there's a
matching row in T2. Something like this, in fact:

DELECT
FROM owner.T1 A
WHERE EXISTS (
SELECT 1
FROM owner.T2 B
WHERE B.keycol1=A.keycol1
AND B.keycol2=A.keycol2 )

Simple, huh? Explaining this shows T1 is scanned first (4.5m rows,
remember) then matching rows from T2 (only 180 rows) are found using
matchcols=2. I want it the other way around i.e. scan T2 then use
matchcols=2 to T1.

What can I do to get DB2 to access T2 first?

Cheers,


Raymond








Massimo Ceraso

Re: Simple but annoying SQL statement
(in response to Steve Tennant)
Hi Raymond.

The solution is in the middle.

Your SQL
> DELECT
> FROM owner.T1 A
> WHERE EXISTS (
> SELECT 1
> FROM owner.T2 B
> WHERE B.keycol1=A.keycol1
> AND B.keycol2=A.keycol2 )

New SQl

> DELECT
> FROM owner.T1 A
> WHERE EXISTS (
> SELECT 1
> FROM owner.T2 B
> WHERE B.keycol1=A.keycol1
> AND B.keycol2=A.keycol2 )
--- AND A.Keycol1 in
--- ( Select B.Keycol1 from owner.T2 b)
--- AND A.Keycol2 in
--- ( Select B.Keycol2 from owner.T2 b)

BYE
------------------------
From: "Bell, Raymond W" <[login to unmask email]>
Subject: Simple but annoying SQL statement
Date: Wed, 13 Dec 2000 12:54:15 +1100
To: [login to unmask email]
Newsgroups: bit.listserv.db2-l


> Colleagues,
>
> I've got a simple but annoying SQL statement I'm trying to make run quickly
> but I'm stumbling on the no-doubt obvious fix. I have a table, T1, with
> 4.5m rows. I have a table, T2, with 180 rows. The definitions of T1 and T2
> are the same (create table T2 like T1, indexes created to match) and
> runstats are current for both. Both tables are in the same tablespace - not
> ideal, but the smaller one is just for the life of the task I've got.
>
> T2's rows have come from T1. T2 is a backup of these rows, as the originals
> in T1 are to be deleted. I want to delete the rows from T1 where there's a
> matching row in T2. Something like this, in fact:
>
> DELECT
> FROM owner.T1 A
> WHERE EXISTS (
> SELECT 1
> FROM owner.T2 B
> WHERE B.keycol1=A.keycol1
> AND B.keycol2=A.keycol2 )
>
> Simple, huh? Explaining this shows T1 is scanned first (4.5m rows,
> remember) then matching rows from T2 (only 180 rows) are found using
> matchcols=2. I want it the other way around i.e. scan T2 then use
> matchcols=2 to T1.
>
> What can I do to get DB2 to access T2 first?
>
> Cheers,
>
>
> Raymond
>
>
> DB2-L webpage at
http://www.ryci.com/db2-l. The owners of the list can

---------------End of Original Message-----------------

--------------------------------------------------------
Name: Massimo Ceraso
E-mail: [login to unmask email]
Phone: +39-081-7564798
Date: 12/13/2000
Time: 08:10:21
"You never have a second chance to make a first impression"
--------------------------------------------------------



Matthias Pohlig

Re: Simple but annoying SQL statement
(in response to Massimo Ceraso)
Hi Raymond,

Of course, it seems ugly that DB2 doesn't find a good way
to access T1 in a "reasonable manner".
(unless Steve's idea already did the trick).

Here's another "dirty hack" that might work if you are in a hurry ...
I know it looks queer, but ...

Step 1:

SELECT 'DELETE FROM owner.T1 WHERE keycol1=' !! keycol1 !!
' AND keycol2=' !! keycol2 !!
' ; '
FROM T2;

The result of this should be 180 Delete statements with a
good chance for a fast delete...

DELETE FROM owner.T1 where keycol1 = 12345 and keycol2 = 99999;
DELETE FROM owner.T1 where keycol1 = 23456 and keycol2 = 88888;
...(etc.) ..

Step 2: EXECUTE the result - file

If your keycols are alphameric, you'd need to fiddle a bit with the
apostrophes of course...

Just a funny idea from early-morning europe :-)



On Wed, 13 Dec 2000 17:00:15 +1100, Tennant, Steve
<[login to unmask email]> wrote:

>****************************************************************
> IMPORTANT
>
>
>Isn't it just a case of removing the correlation? Delete where id in
>(select id from t2).
>
>Steve T
>
>-----Original Message-----
>From: Bell, Raymond W [mailto:[login to unmask email]
>Sent: Wednesday, 13 December 2000 12:54
>To: [login to unmask email]
>Subject: Simple but annoying SQL statement
>
>
>Colleagues,
>
>I've got a simple but annoying SQL statement I'm trying to make run quickly
>but I'm stumbling on the no-doubt obvious fix. I have a table, T1, with
>4.5m rows. I have a table, T2, with 180 rows. The definitions of T1 and
T2
>are the same (create table T2 like T1, indexes created to match) and
>runstats are current for both. Both tables are in the same tablespace -
not
>ideal, but the smaller one is just for the life of the task I've got.
>
>T2's rows have come from T1. T2 is a backup of these rows, as the
originals
>in T1 are to be deleted. I want to delete the rows from T1 where there's a
>matching row in T2. Something like this, in fact:
>
> DELECT
> FROM owner.T1 A
> WHERE EXISTS (
> SELECT 1
> FROM owner.T2 B
> WHERE B.keycol1=A.keycol1
> AND B.keycol2=A.keycol2 )
>
>Simple, huh? Explaining this shows T1 is scanned first (4.5m rows,
>remember) then matching rows from T2 (only 180 rows) are found using
>matchcols=2. I want it the other way around i.e. scan T2 then use
>matchcols=2 to T1.
>
>What can I do to get DB2 to access T2 first?
>
>Cheers,
>
>
>Raymond
>
>
>
the
>
>
>
>
>





Rajeev P. Dhanawade

Re: Simple but annoying SQL statement
(in response to Matthias Pohlig)
Raymond,

I have a question. When you select the rows to insert into T2, is the
access path so bad that you would not want to use the same criteria to
delete the rows?

Rajeev P Dhanawade
Phone : 302-577-4659 Ext. 221 ++ Fax : 302-577-4454 ++
-------------
Original Text
From: "Bell, Raymond W" <[login to unmask email]>, on 12/12/2000
8:54 PM:
Colleagues,

I've got a simple but annoying SQL statement I'm trying to make run quickly
but I'm stumbling on the no-doubt obvious fix. I have a table, T1, with
4.5m rows. I have a table, T2, with 180 rows. The definitions of T1 and
T2
are the same (create table T2 like T1, indexes created to match) and
runstats are current for both. Both tables are in the same tablespace -
not
ideal, but the smaller one is just for the life of the task I've got.

T2's rows have come from T1. T2 is a backup of these rows, as the
originals
in T1 are to be deleted. I want to delete the rows from T1 where there's a
matching row in T2. Something like this, in fact:

DELECT
FROM owner.T1 A
WHERE EXISTS (
SELECT 1
FROM owner.T2 B
WHERE B.keycol1=A.keycol1
AND B.keycol2=A.keycol2 )

Simple, huh? Explaining this shows T1 is scanned first (4.5m rows,
remember) then matching rows from T2 (only 180 rows) are found using
matchcols=2. I want it the other way around i.e. scan T2 then use
matchcols=2 to T1.

What can I do to get DB2 to access T2 first?

Cheers,


Raymond








Lockwood Lyon

Re: Simple but annoying SQL statement
(in response to Rajeev P. Dhanawade)
Raymond,

I'm puzzled by this, especially your statement that "T1 is scanned first". Please confirm a few basics first.

Is this DB2 V? for OS/390?
Are you running this SQL statement as static or dynamic SQL (program? SPUFI?)
Has the tablespace containing the tables been REORG-ed?
Has RunStats been run recently? Before/after a Reorg?
If static SQL, was it Re-BIND-ed after Runstats?

Last, are you using EXPLAIN output directly from the PLAN_TABLE, or a third-party product to display your access path?

Last last, assuming that you've done a Reorg, run full Runstats, and submitted the query through SPUFI with an EXPLAIN PLAN, please publish to the ListServ the PLAN_TABLE rows. This might clear things up a bit for us.

¯ LL

Original Text
From: "Bell, Raymond W" < [login to unmask email] >, on 12/12/2000
8:54 PM:
Colleagues,

I've got a simple but annoying SQL statement I'm trying to make run quickly
but I'm stumbling on the no-doubt obvious fix. I have a table, T1, with
4.5m rows. I have a table, T2, with 180 rows. The definitions of T1 and
T2
are the same (create table T2 like T1, indexes created to match) and
runstats are current for both. Both tables are in the same tablespace -
not
ideal, but the smaller one is just for the life of the task I've got.

T2's rows have come from T1. T2 is a backup of these rows, as the
originals
in T1 are to be deleted. I want to delete the rows from T1 where there's a
matching row in T2. Something like this, in fact:

DELECT
FROM owner.T1 A
WHERE EXISTS (
SELECT 1
FROM owner.T2 B
WHERE B.keycol1=A.keycol1
AND B.keycol2=A.keycol2 )

Simple, huh? Explaining this shows T1 is scanned first (4.5m rows,
remember) then matching rows from T2 (only 180 rows) are found using
matchcols=2. I want it the other way around i.e. scan T2 then use
matchcols=2 to T1.

What can I do to get DB2 to access T2 first?

Cheers,


Raymond


visit
the DB2-L webpage at http://www.ryci.com/db2-l . The owners of the list can
be reached at [login to unmask email] .


the DB2-L webpage at http://www.ryci.com/db2-l . The owners of the list can be reached at [login to unmask email] .



Lockwood Lyon -- Meijer Technical Support
(616) 735-7553 (office)
(616) 791-5131 (fax)

Copyright (c) 2000 by Lockwood Lyon. All rights reserved. These opinions are mine and not necessarily those of my employer, Meijer, Inc.


Raymond Bell

Re: Simple but annoying SQL statement
(in response to Lockwood Lyon)
Greg/Steve/Massimo/Matthias/Rajeev/Lockwood/Jeff/Bill/anyone_else_I've_misse
d,

Thanks for the excellent ideas on this 'illegitimate' SQL statement. The
prize for finding the solution goes to... (drum roll, please)... Massimo and
Jeff. Sorry guys, you'll have to share the T-shirt between you. The trick,
as Jeff explained, was to create an 'IN' list for my two keys so DB2 could
use this to access T1 efficiently. Still goes to T1 first though -
according to (the way I'm reading) the plan_table anyway, which to me
doesn't make sense - but with accesstype=N (in-list processing) using the
unique index via matchcols=2. And I still needed the correlation to filter
out the 'duplicates' from using each part of the key separately.

Steve, I can't remove the correlation because there's two columns in the key
and each on their own isn't unique. I'd get more deletions than I want.

Greg and Matthias, yes your suggestions would have worked. I thought about
it, but then got the access path I wanted so didn't persue it. I'm sure it
would have worked though (maybe you want to go 1/4's with Massimo and Jeff
on that T-shirt).

Rajeev, the rows in T2 were actually provided via insert statements from
someone else from somewhere else. So I didn't have an access path for that.
I suspect the poor person who provided them did a long-ish scan to get them.

Bill, your join works well for selection, but I ultimately wanted to delete
the rows so I can't use a join to find them. Can't 'Delete From T1 A, T2 B'
you see.

And Lockwood, to answer your questions: DB2 for OS/390 V5; dynamic via QMF;
no reorg run; runstats very current; plan_table output viewed via QMF; no
rebind after runstats (dynamic SQL); no, I won't post the plan_table rows
'cause it's been solved.

So, the statement the did the dirty was:

SELECT * -- DELETE

FROM owner.T1 A
WHERE EXISTS (

SELECT 1

FROM owner.T2 B

WHERE B.keycol1=A.keycol1
AND B.keycol2=A.keycol2
)

AND A.keycol1 IN (SELECT B.keycol1 FROM owner.T2 B)
AND A.keycol2 IN (SELECT B.keycol2 FROM owner.T2 B)

Phew! Thanks everyone for your comments and valuable help.

Cheers,


Raymond


> -----Original Message-----
> From: Massimo Ceraso [SMTP:[login to unmask email]
> Sent: Wednesday, 13 December 2000 6:10 pm
> To: [login to unmask email]
> Subject: Re: Simple but annoying SQL statement
>
> Hi Raymond.
>
> The solution is in the middle.
>
> Your SQL
> > DELECT
> > FROM owner.T1 A
> > WHERE EXISTS (
> > SELECT 1
> > FROM owner.T2 B
> > WHERE B.keycol1=A.keycol1
> > AND B.keycol2=A.keycol2 )
>
> New SQl
>
> > DELECT
> > FROM owner.T1 A
> > WHERE EXISTS (
> > SELECT 1
> > FROM owner.T2 B
> > WHERE B.keycol1=A.keycol1
> > AND B.keycol2=A.keycol2 )
> --- AND A.Keycol1 in
> --- ( Select B.Keycol1 from owner.T2 b)
> --- AND A.Keycol2 in
> --- ( Select B.Keycol2 from owner.T2 b)
>
> BYE
>



Ulrich KUHN

Re: Simple but annoying SQL statement
(in response to Raymond Bell)
Just to add a last comment to this discussion - should anybody ever need to
solve this type of problem in little db2 (Unix etc), there you CAN use
something similar to Steve's suggestion since DB2 UNO allows expressions on
either side of the IN predicate - i.e.

SELECT * -- DELETE
FROM owner.T1
WHERE (keycol1,keycol2) IN (SELECT keycol1,keycol2 FROM owner.T2)

Simple, isn't it? This functionality has been available for a long time in
little db2; no doubt it will find its way into BIG DB2 soon...

Cheers, Ulrich

> -----Original Message-----
> From: Bell, Raymond W [SMTP:[login to unmask email]
> Sent: Thursday, December 14, 2000 10:40 AM
> To: [login to unmask email]
> Subject: Re: Simple but annoying SQL statement
>
> Greg/Steve/Massimo/Matthias/Rajeev/Lockwood/Jeff/Bill/anyone_else_I've_mis
> sed,
>
> Thanks for the excellent ideas on this 'illegitimate' SQL statement. The
> prize for finding the solution goes to... (drum roll, please)... Massimo
> and Jeff. Sorry guys, you'll have to share the T-shirt between you. The
> trick, as Jeff explained, was to create an 'IN' list for my two keys so
> DB2 could use this to access T1 efficiently. Still goes to T1 first
> though - according to (the way I'm reading) the plan_table anyway, which
> to me doesn't make sense - but with accesstype=N (in-list processing)
> using the unique index via matchcols=2. And I still needed the
> correlation to filter out the 'duplicates' from using each part of the key
> separately.
>
> Steve, I can't remove the correlation because there's two columns in the
> key and each on their own isn't unique. I'd get more deletions than I
> want.
... ... ...
> So, the statement the did the dirty was:
>
> SELECT * -- DELETE
> FROM owner.T1 A
> WHERE EXISTS (
> SELECT 1
> FROM owner.T2 B
> WHERE B.keycol1=A.keycol1
> AND B.keycol2=A.keycol2
> )
> AND A.keycol1 IN (SELECT B.keycol1 FROM owner.T2 B)
> AND A.keycol2 IN (SELECT B.keycol2 FROM owner.T2 B)
>
> Phew! Thanks everyone for your comments and valuable help.
>
> Cheers, Raymond
>

Raymond Bell

Re: Simple but annoying SQL statement
(in response to Ulrich KUHN)
Interesting. I guess, seeing as how both my key columns were chars (and
even if they weren't, thanks to some fancy column functinos) I could have
said 'where keycol1||keycol2 in (select keycol1||keycol2 from owner.t2)'.

Many ways to flay a feline, so to speak.


Raymond


> -----Original Message-----
> From: KUHN,Ulrich [SMTP:[login to unmask email]
> Sent: Thursday, 14 December 2000 3:48 pm
> To: [login to unmask email]
> Subject: Re: Simple but annoying SQL statement
>
> Just to add a last comment to this discussion - should anybody ever need
> to solve this type of problem in little db2 (Unix etc), there you CAN use
> something similar to Steve's suggestion since DB2 UNO allows expressions
> on either side of the IN predicate - i.e.
>
> SELECT * -- DELETE
> FROM owner.T1
> WHERE (keycol1,keycol2) IN (SELECT keycol1,keycol2 FROM owner.T2)
>
> Simple, isn't it? This functionality has been available for a long time
> in little db2; no doubt it will find its way into BIG DB2 soon...
>
> Cheers, Ulrich
>
> -----Original Message-----
> From: Bell, Raymond W [SMTP:[login to unmask email]
> Sent: Thursday, December 14, 2000 10:40 AM
> To: [login to unmask email]
> Subject: Re: Simple but annoying SQL statement
>
> Greg/Steve/Massimo/Matthias/Rajeev/Lockwood/Jeff/Bill/anyone_else_I've_mis
> sed,
>
> Thanks for the excellent ideas on this 'illegitimate' SQL statement. The
> prize for finding the solution goes to... (drum roll, please)... Massimo
> and Jeff. Sorry guys, you'll have to share the T-shirt between you. The
> trick, as Jeff explained, was to create an 'IN' list for my two keys so
> DB2 could use this to access T1 efficiently. Still goes to T1 first
> though - according to (the way I'm reading) the plan_table anyway, which
> to me doesn't make sense - but with accesstype=N (in-list processing)
> using the unique index via matchcols=2. And I still needed the
> correlation to filter out the 'duplicates' from using each part of the key
> separately.
>
> Steve, I can't remove the correlation because there's two columns in the
> key and each on their own isn't unique. I'd get more deletions than I
> want.
>
> ... ... ...
> So, the statement the did the dirty was:
>
> SELECT * -- DELETE
> FROM owner.T1 A
> WHERE EXISTS (
> SELECT 1
> FROM owner.T2 B
> WHERE B.keycol1=A.keycol1
> AND B.keycol2=A.keycol2
> )
> AND A.keycol1 IN (SELECT B.keycol1 FROM owner.T2 B)
> AND A.keycol2 IN (SELECT B.keycol2 FROM owner.T2 B)
>
> Phew! Thanks everyone for your comments and valuable help.
>
> Cheers, Raymond
>



Terry Purcell

Re: Simple but annoying SQL statement
(in response to Raymond Bell)
RE: Simple but annoying SQL statementUlrich,

As you pointed out, it is an excellent feature; known as a ROW Expression
which is FINALLY delivered in DB2 OS/390 in V7.

Raymond,

The performance improvement you are seeing from this revised query is due
to the V6 feature: indexability of non-correlated IN subqueries. In V6 this
feature has some bugs, but since you are on V5 with the APAR applied (call
it intuition), then it is working as designed.

The original correlated subquery you wrote will always process T1 before
attempting access to T2, and this is the performance issue. Once you
introduced the non-correlated IN subqueries, DB2 will always access the
non-correlated subqueries first (ie. T2 is now accessed first), and a build
a result set. With the V5 APAR (and in V6 if it worked correctly), DB2 can
flip the subquery to become the outer table of a nested loop "like" join
with T1 becoming the inner table. This will be seen in the plan table in
your example with a QUERYTYPE of NCOSUB on two of the subqueries, and an
ACCESSTYPE of N, with matchcols > 0 on T2.

Even though when ordering the plan table rows by QBLOCKNO, PLANNO etc will
show the non-correlated subqueries last, non-correlated subqueries are
always processed in their entirety first. Whether that remains stage 2, or
is indexable will depend on the subquery type and rules for subquery
transformation. The sequence in explain does not always indicate the order
of actual execution.

Regards and Enjoy,
Terry Purcell
[login to unmask email]
Yevich Lawson & Associates
WWW.YLASSOC.COM
WWW.DB2-PORTAL.COM
WWW.DB2-SYMPOSIUM.COM
-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
KUHN,Ulrich
Sent: Wednesday, December 13, 2000 10:48 PM
To: [login to unmask email]
Subject: Re: Simple but annoying SQL statement


Just to add a last comment to this discussion - should anybody ever need
to solve this type of problem in little db2 (Unix etc), there you CAN use
something similar to Steve's suggestion since DB2 UNO allows expressions on
either side of the IN predicate - i.e.

SELECT * -- DELETE
FROM owner.T1
WHERE (keycol1,keycol2) IN (SELECT keycol1,keycol2 FROM owner.T2)

Simple, isn't it? This functionality has been available for a long time
in little db2; no doubt it will find its way into BIG DB2 soon...

Cheers, Ulrich

Terry Purcell

Re: Simple but annoying SQL statement
(in response to Terry Purcell)
Raymond,

The difference between what you proposed and what Ulrich highlighted; is
that the V7 row expression is indexable, but your concatenation is stage 2
and not indexable. Otherwise you are correct, they are logically equivalent.

Regards
Terry Purcell
[login to unmask email]
Yevich Lawson & Associates
WWW.YLASSOC.COM
WWW.DB2-PORTAL.COM
WWW.DB2-SYMPOSIUM.COM

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
Bell, Raymond W
Sent: Thursday, December 14, 2000 12:46 AM
To: [login to unmask email]
Subject: Re: Simple but annoying SQL statement


Interesting. I guess, seeing as how both my key columns were chars (and
even if they weren't, thanks to some fancy column functinos) I could have
said 'where keycol1||keycol2 in (select keycol1||keycol2 from owner.t2)'.

Many ways to flay a feline, so to speak.


Raymond


> -----Original Message-----
> From: KUHN,Ulrich [SMTP:[login to unmask email]
> Sent: Thursday, 14 December 2000 3:48 pm
> To: [login to unmask email]
> Subject: Re: Simple but annoying SQL statement
>
> Just to add a last comment to this discussion - should anybody ever need
> to solve this type of problem in little db2 (Unix etc), there you CAN use
> something similar to Steve's suggestion since DB2 UNO allows expressions
> on either side of the IN predicate - i.e.
>
> SELECT * -- DELETE
> FROM owner.T1
> WHERE (keycol1,keycol2) IN (SELECT keycol1,keycol2 FROM owner.T2)
>
> Simple, isn't it? This functionality has been available for a long time
> in little db2; no doubt it will find its way into BIG DB2 soon...
>
> Cheers, Ulrich
>
> -----Original Message-----
> From: Bell, Raymond W [SMTP:[login to unmask email]
> Sent: Thursday, December 14, 2000 10:40 AM
> To: [login to unmask email]
> Subject: Re: Simple but annoying SQL statement
>
> Greg/Steve/Massimo/Matthias/Rajeev/Lockwood/Jeff/Bill/anyone_else_I've_mis
> sed,
>
> Thanks for the excellent ideas on this 'illegitimate' SQL statement. The
> prize for finding the solution goes to... (drum roll, please)... Massimo
> and Jeff. Sorry guys, you'll have to share the T-shirt between you. The
> trick, as Jeff explained, was to create an 'IN' list for my two keys so
> DB2 could use this to access T1 efficiently. Still goes to T1 first
> though - according to (the way I'm reading) the plan_table anyway, which
> to me doesn't make sense - but with accesstype=N (in-list processing)
> using the unique index via matchcols=2. And I still needed the
> correlation to filter out the 'duplicates' from using each part of the key
> separately.
>
> Steve, I can't remove the correlation because there's two columns in the
> key and each on their own isn't unique. I'd get more deletions than I
> want.
>
> ... ... ...
> So, the statement the did the dirty was:
>
> SELECT * -- DELETE
> FROM owner.T1 A
> WHERE EXISTS (
> SELECT 1
> FROM owner.T2 B
> WHERE B.keycol1=A.keycol1
> AND B.keycol2=A.keycol2
> )
> AND A.keycol1 IN (SELECT B.keycol1 FROM owner.T2 B)
> AND A.keycol2 IN (SELECT B.keycol2 FROM owner.T2 B)
>
> Phew! Thanks everyone for your comments and valuable help.
>
> Cheers, Raymond
>








Lockwood Lyon

Re: Simple but annoying SQL statement
(in response to Terry Purcell)
Raymond,

Glad you were able to solve this! Looks like this proves the worth of the list to us all (in case anyone needs an example to show management ...). I didn't really want a Team Telstra T-shirt (what's it look like, anyway?), but I remain a bit puzzled. When I Explain-ed your original query on our own system (actually a functional equivalent) I got a very nice indexed access to "T1"; hence, my wondering why on earth your original would do a TS scan. That's why my interest in your Plan-Table. I also wondered about the ClusterRatio of your T1-index (mine's 100%), and the Percent of Active Pages (mine's basically 100%). Weird numbers in either of these might cause DB2 to think that a scan of T1 might be just as cheap as using the index.

Anyway, glad it's fixed. Enjoy!

- - LL

>>> [login to unmask email] 12/13/00 06:40PM >>>
Greg/Steve/Massimo/Matthias/Rajeev/Lockwood/Jeff/Bill/anyone_else_I've_misse
d,
[...snip...]

And Lockwood, to answer your questions: DB2 for OS/390 V5; dynamic via QMF;
no reorg run; runstats very current; plan_table output viewed via QMF; no
rebind after runstats (dynamic SQL); no, I won't post the plan_table rows
'cause it's been solved.

So, the statement the did the dirty was:

SELECT * -- DELETE

FROM owner.T1 A
WHERE EXISTS (

SELECT 1

FROM owner.T2 B

WHERE B.keycol1=A.keycol1
AND B.keycol2=A.keycol2
)

AND A.keycol1 IN (SELECT B.keycol1 FROM owner.T2 B)
AND A.keycol2 IN (SELECT B.keycol2 FROM owner.T2 B)

Phew! Thanks everyone for your comments and valuable help.

Cheers,


Raymond


> -----Original Message-----
> From: Massimo Ceraso [ SMTP:[login to unmask email]
> Sent: Wednesday, 13 December 2000 6:10 pm
> To: [login to unmask email]
> Subject: Re: Simple but annoying SQL statement
>
> Hi Raymond.
>
> The solution is in the middle.
>
> Your SQL
> > DELECT
> > FROM owner.T1 A
> > WHERE EXISTS (
> > SELECT 1
> > FROM owner.T2 B
> > WHERE B.keycol1=A.keycol1
> > AND B.keycol2=A.keycol2 )
>
> New SQl
>
> > DELECT
> > FROM owner.T1 A
> > WHERE EXISTS (
> > SELECT 1
> > FROM owner.T2 B
> > WHERE B.keycol1=A.keycol1
> > AND B.keycol2=A.keycol2 )
> --- AND A.Keycol1 in
> --- ( Select B.Keycol1 from owner.T2 b)
> --- AND A.Keycol2 in
> --- ( Select B.Keycol2 from owner.T2 b)
>
> BYE
>


the DB2-L webpage at http://www.ryci.com/db2-l . The owners of the list can be reached at [login to unmask email] .


Raymond Bell

Re: Simple but annoying SQL statement
(in response to Lockwood Lyon)
Lockwood,

Can't tell you what a Telstra T-shirt looks like; I don't work for them. I
work for CPT Global, neither company having responsibility for what I may or
may not say on this list. Hmmm, sounds familiar... Anyway, T1 might have
had matchcols=2 if it was more than 58% clustered. I knew T2 was a better
table to go to first; I just didn't know how to tell DB2 that. But I do
now.

If you want your own T-shirt, I'll have to quote you without asking you
first. Given the pasting CDB got for that, I can't see it happening anytime
soon. :o)


Raymond


> -----Original Message-----
> From: Lockwood Lyon [SMTP:[login to unmask email]
> Sent: Friday, 15 December 2000 1:34 am
> To: [login to unmask email]
> Subject: Re: Simple but annoying SQL statement
>
> Raymond,
>
> Glad you were able to solve this! Looks like this proves the worth of the
> list to us all (in case anyone needs an example to show management ...).
> I didn't really want a Team Telstra T-shirt (what's it look like,
> anyway?), but I remain a bit puzzled. When I Explain-ed your original
> query on our own system (actually a functional equivalent) I got a very
> nice indexed access to "T1"; hence, my wondering why on earth your
> original would do a TS scan. That's why my interest in your Plan-Table.
> I also wondered about the ClusterRatio of your T1-index (mine's 100%), and
> the Percent of Active Pages (mine's basically 100%). Weird numbers in
> either of these might cause DB2 to think that a scan of T1 might be just
> as cheap as using the index.
>
> Anyway, glad it's fixed. Enjoy!
>
> - - LL