Re: Extending my question. Was: The relational model and relational algebra - why did SQL become the industry standard?
Date: Tue, 4 Mar 2003 13:17:13 -0000
Message-ID: <b42942$sb0$1_at_sp15at20.hursley.ibm.com>
"Lauri Pietarinen" <lauri.pietarinen_at_atbusiness.com> wrote in message
news:3E649C97.9080901_at_atbusiness.com...
>
>
> Paul Vernon wrote:
>
> >FYI. DB2 does do some optimisation (I.e. does not join to S), but does
> not go
>
> >the whole hog in it's rewritten SQL (see below)
> >However it does set it's 'Early Out Flag' in the access plan on the join
> >between P and SP, which would AKAIK have the same effect as the EXISTS
check
> >in the 'more fully' optimised SQL suggest above.
> >
>
> I agree. I think that "early out" flag means that it will stop as soon
> as it has found
> one qualifying SP-row, so in essence it equates to the EXISTS-version.
>
> Also the missing SORT implies that it does not have to eliminate duplicates.
>
> But why does it do a HASH-join?
Pass, but it's interesting to note that given the EXISTS form of the query,
DB2 optimises it to the same query as before.
Original Statement:
SELECT P#, PNAME
FROM P
WHERE EXISTS
(SELECT *
FROM SP
WHERE P.P# = SP.P#)
Optimized Statement:
SELECT DISTINCT Q2.P# AS "P#", Q2.PNAME AS "PNAME" FROM DB2.SP AS Q1, DB2.P AS Q2
WHERE (Q2.P# = Q1.P#) Access Plan:
Total Cost: 25.338
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
88
HSJOIN
( 2)
25.338
1
/------+-----\
88 137
TBSCAN IXSCAN
( 3) ( 4)
25.1328 0.182339
1 0
| |
88 137
TABLE: DB2 INDEX: SYSIBM
P SQL0303031750371
Regards
Paul Vernon
Business Intelligence, IBM Global Services
Received on Tue Mar 04 2003 - 14:17:13 CET