Surprised at the optimizer...

From: Martijn Tonies <m.tonies_at_upscene_nospam_.com>
Date: Thu, 7 Oct 2004 17:26:20 +0200
Message-ID: <10mansatadpsdab@corp.supernews.com>

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

-- 
With regards,

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

Received on Thu Oct 07 2004 - 10:26:20 CDT