Re: Performance problem: anything else I can do to make this faster?

Steve C wrote:

> I'm finally getting the CBO to use indexes (on the count query, at
> least) using:
>
> alter session set optimizer_index_cost_adj = 50;
> alter session set optimizer_index_caching = 90;
>
> My query times aren't changed much though, for the count query it's 2
> seconds, for the record page query it's 5.
>
> One thing: I haven't put primary or foreign keys on my tables. As a
> rule we don't rely on the database to do constraint checking. Would
> this figure into the optimizer's decisions around join strategies, etc?

What you just posted is the equivalent to:

We didn't study database 100: Databases for Dummies. We ignored everything about databases we could. How come we are having problems?

Using anyting other than the database to guarantee integrity is as sensible as chewing on rocks and asking the dentist what's wrong with your teeth. That said ... build the constraints where they belong and build indexes where the are required. Geesh!

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