Re: very poor SYS view performance (8i)

"Uwe Kuechler" <gg.3.ukuechle_at_spamgourmet.com> wrote in message news:beab228e.0312100223.6200e96c_at_posting.google.com...

> The same query on another, similar database does not have this issue,
> that's why I wonder whether the SYS tables could have been corrupted
> in one way or another.
>
> Here's on of those queries:
> select to_char(null), ac.owner, ac.table_name, acc.column_name,
> acc.position, ac.constraint_name
> from all_constraints ac, all_cons_columns acc
> where ac.owner=acc.owner
> and ac.constraint_type='P'
> and ac.constraint_name=acc.constraint_name
> and ac.owner like 'FCISZIP' escape '\'
> and ac.table_name like 'JOBTBL' escape '\'
> ;
> snippage...
> Ideas, any1?

Queries on a fragmented dictionary tend to produce some weird plans. I'd suggest you help the optimiser a bit by re-working your query to depend less on ALL_CONSTRAINTS.

All the query predicates you have can be gotten also from ALL_CONS_COLUMNS as it stores the owner and table name as well as the constraint name. The only thing it doesn't is the constraint_type and that is really all you need from ALL_CONSTRAINTS. Another thing is to ALWAYS use all possible join predicates that you must use, rather than rely on quirks to get a join to work. You are missing the "ac.table_name = acc.table_name" join condition!

Something along these lines might work a LOT faster:

select
to_char(null), "what the heck is this for?" acc.owner, acc.table_name, acc.column_name, acc.position, acc.constraint_name
from all_constraints ac, all_cons_columns acc  where ac.owner=acc.owner
 and ac.constraint_type='P'
 and ac.constraint_name=acc.constraint_name and ac.table_name = acc.table_name
and acc.owner like 'FCISZIP' escape '\'
 and acc.table_name like 'JOBTBL' escape '\' ;

HTH

-- 
Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam