Re: Surprised at the optimizer...

Hello Daniel,

> > 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?

Yes - but these are system views.

> Have you heard of Explain Plan?

Yes. But why does a " where proc.procedure_name is null " matter such a lot on an _inner join_ where there can be no such rows?

(obviously, I left out that part of the WHERE in my final query).

-- 
With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com