Re: [SQL] how to avoid an inner join

"Anurag Varma" <avdbi_at_nospam.hotmail.com> wrote in message news:<wYop7.403$2n.150903_at_news02.optonline.net>...
>
> You did not avoid a inner join?
> A subquery like this, in essence is going to be converted to an inner join
> in oracle.
> The only way to prevent an inner join in this case is to do this
> procedurally!
>
> declare
> cursor x is select key,datum from xxx order by datum;
> ........
> ........
> ........
>
> Anurag

ok, weapons up :-)

select o.k,o.datum,o.t
from xxx o, xxx i
where o.k=1
and o.k=i.k
having
o.datum=max(i.datum)
group by o.k,o.datum,o.t

is an inner join

ACCESS_PATH                                                  SUBSTR('COST='
------------------------------------------------------------ --------------
SELECT STATEMENT    CHOOSE                                   Cost= 4
 FILTER                                                      Cost=
  SORT GROUP BY                                              Cost= 4
   MERGE JOIN CARTESIAN                                      Cost= 2
    TABLE ACCESS BY INDEX ROWID XXX  ANALYZED                Cost= 1
     INDEX RANGE SCAN XXX_I NON-UNIQUE ANALYZED              Cost= 1
    SORT JOIN                                                Cost= 3
     INDEX RANGE SCAN XXX_I NON-UNIQUE ANALYZED              Cost= 1

whereas

select k,datum,t
 from xxx o
where k=1
and datum =

    (select max(datum)

     from xxx i
     where i.k=o.k)


ACCESS_PATH                                                  SUBSTR('COST=
------------------------------------------------------------ -------------
SELECT STATEMENT    CHOOSE                                   Cost= 1
 TABLE ACCESS BY INDEX ROWID XXX  ANALYZED                   Cost= 1
  INDEX RANGE SCAN XXX_I NON-UNIQUE ANALYZED                 Cost= 1
   SORT AGGREGATE                                            Cost=
    FIRST ROW                                                Cost= 1
     INDEX RANGE SCAN (MIN/MAX) XXX_I NON-UNIQUE ANALYZED    Cost= 1

there is no join.
It's a subquery, but not a join.

Peace ...

Thomas Received on Tue Sep 18 2001 - 03:28:15 CDT