Re: Query performance improvement ?

David Jones wrote:
>
> Dear Lister:
>
> Is there any way to further improve the following query performance ?
>
> Thanks
>
> David Jones
> ITResource
>
> SQL> select distinct A.*
> >from POData A , InvData B where A.OWNERID=B.OWNERID AND A.ID =
> >B.PURCHASEORDERID
> >and B.Status = 12 and A.ownerId = 1 order by A.ID;
>
> ================================================================
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=398 Card=8971
> Bytes=663854)
> 1 0 SORT (UNIQUE) (Cost=284 Card=8971 Bytes=663854)
> 2 1 HASH JOIN (Cost=170 Card=8971 Bytes=663854)
> 3 2 INDEX (FAST FULL SCAN) OF 'INVDATA_2' (NON-UNIQUE) (Cost=99
> Card=8971 Bytes=71768)
> 4 2 TABLE ACCESS (FULL) OF 'PODATA' (Cost=36 Card=18152
> Bytes=1198032)
>

I would try something like

      select *
      from POData
      where ownerid = 1
        and id in (select purchaseorderid
                   from invdata
                   where status = 12
                     and ownerid = 1) 

-- 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).