I'm not trying to be cute... but is your SGA big enough? Clearly
the speed increase must be from cache, but there is a dictionary
cache in the shared pool that may not be large enough...just a
thought.. And although I'm not certain, it seems that
"and c.table_name = 'SALTB003' is a much more limiting expression than
"where c.contraint_name = cc.constraint_name" and therefore should
come first like..
from all_constraints c,all_cons_columns cc
where c.table_name = 'SALTB003'
order by 1,2,4
It is my understanding that limiting expressions should appear first
as the where clause is parsed left to right as oppossed to the from
clause that is parsed right to left...
Comments welcome... just a couple shots in the dark....
>I am having a problem with a business objects generated query. This
>comes from the universe designer rather than the users. The query goes
>something like this run from sqlplus
>from all_constraints c,all_cons_columns cc
>and c.table_name = 'SALTB003'
>order by 1,2,4
>This is not the exact business objects query, though suffers the same
>problem in that It will take 16-20 mins to run? Then if run again
>immediately afterwards will execute sub-second. There are about 2500
>rows in all_cons_columns and 2000 in all_constraints.
>Sent via Deja.com http://www.deja.com/
>Before you buy.