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

sconover_at_gmail.com wrote:

> Sarcasm is understandable, what you're saying is a common reaction
> (part of why I didn't bring it up until now). Our app is the only one
> hitting this db, and with a test-driven app there's no real reason to
> have constraints other than a sanity -check - which I'm not opposed to,
> but it's not my top priority by any means.
>
> Ordinary O/S files don't do lock management, and they lack a query
> language as powerful as sql. There's obviously a lot of value to be
> had from a db (oo or relational) that has nothing to do with constraint
> checking.
>
> In any case, I put the correct primary and foreign key constraints on
> my tables and it has no effect on performance/plan/stats.
>
> Regards,
> Steve

Sorry if the sarcasm continues but I've spent hundreds, nay thousands of hours cleaning up these messes. I'll bet you a 12 year old bottle of scotch what you said above isn't true: "Our app is the only one hitting this db." It never is.

First time someone goes in there with SQL*Plus, TOAD, etc. they are free to make a huge a mess as they wish or can. Reference: Murphy's Law.

What you are claiming is that your DBAs never connect to your database. Sorry but that's not realistic. If you are saying your DBAs are perfect and never make mistakes I won't be buying that either at current market prices.

Building constraints (primary key and unique) create indexes. For those that are not created automatically as part of a proper design ... you need to perform the analysis and build them manually.

And that doesn't just mean throw B*Tree indexes at your application. That means understand the design and use bitmap, reverse key, descending, compressed, function based, local, global, etc. as required.

BTW: Want to reverse the bet as to whether your front-end coders are really perfectly guaranteeing data integrity? Call me after the app has been running for more than one year. ;-)

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