Re: SQL statement performance

"Guang Mei" <gmei_at_proteome.com> wrote in message news:B_YL6.624$du2.59946_at_news.shore.net...
> Oracle : 8.0.5
> Platform : Sun
>
> SQL statement:
>
> select distinct(spid) spid, name, commonname from (
> select distinct queryspid spid from results union
> select distinct subjspid spid from results
> ) a, species
> where a.spid=species.id ;
>
> Table "Species" only has 33 records, while table "results" has about
 8000000
> records. There are indexes on queryspid and subjspid.
>
> This query is somehow slow. Is there any "easy" way to speed it up?
>
> Thanks.
>
>

Start removing the distincts as an union is a set, and hence by design is distinct unless you use an union all which you don't. Secondly: in your subquery you don't have any criteria, so you don't use indexes, and you don't use an index on the largest table of the two I would definitely try to do something like this select /*+ordered*/ id

         , queryspid
from species, results
where queryspid = species.id
union
select /*+ordered*/
subjspid
from species

      , results
where subsjspid = species.id

The design of the results table may be questioned.

Hth,

Sybrand Bakker, Oracle DBA Received on Mon May 14 2001 - 17:43:36 CDT