Re: Surprised at the optimizer...

Martijn Tonies wrote:

> Hi all,
>
> I'm trying to understand some Oracle Optimizer behaviour here...
>
> I have a database and this query returns 598 rows:
> select src.owner, src.name, src.type, src.line, src.text,
> proc.owner, proc.object_name, proc.aggregate, proc.pipelined,
> proc.impltypename, proc.parallel, proc.interface, proc.deterministic,
> proc.authid
> from all_source src join all_procedures proc on (proc.object_name = src.name
> and proc.owner = src.owner)
> where src.type = 'PROCEDURE'
>
> It takes about 4 seconds to execute and fetch all rows.
>
> If I add:
> and proc.owner in ('EXFSYS','DMSYS','SYS','ORDSYS','WKSYS','XDB')
>
> it takes about 1 second. But, all owners listed are the owners in the final
> resultset.
>
> Another one, this returns the same rows (!!) as the first query:
> select proc.owner, proc.object_name, proc.aggregate, proc.pipelined,
> proc.impltypename, proc.parallel, proc.interface, proc.deterministic,
> proc.authid,
> src.text
> from all_procedures proc
> join all_source src on (src.owner = proc.owner and src.name =
> proc.object_name and src.type = 'PROCEDURE')
> where proc.procedure_name is null
>
> This takes 13 seconds to complete and fetch all rows!
>
> Adding the AND PROC.OWNER clause again makes it as fast as the previous
> one...
>
>
> Any idea why?
>
> Should I test and rewrite my queries "the other way around" if they are
> slow(ish)?

Have you heard of indexes?
Have you heard of Explain Plan?

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)