Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: Extending my question. Was: The relational model and relational algebra - why did SQL become the industry standard?

Re: Extending my question. Was: The relational model and relational algebra - why did SQL become the industry standard?

From: Paul Vernon <paul.vernon_at_ukk.ibmm.comm>
Date: Tue, 4 Mar 2003 13:17:13 -0000
Message-ID: <b42942$sb0$1@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 - 07:17:13 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US