Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Help with SQL constraint

Re: Help with SQL constraint

From: dean <deanbrown3d_at_yahoo.com>
Date: 16 Feb 2007 12:48:40 -0800
Message-ID: <1171658920.373548.89540@p10g2000cwp.googlegroups.com>


On Feb 16, 11:32 am, DA Morgan <damor..._at_psoug.org> wrote:
> dean wrote:
> >> As I presume you intend to put this into production there is no more
> >> help available from me. YOYO. There is one and only one solution ...
> >> correctly model your business requirement.
> >> --
> >> Daniel A. Morgan
> >> University of Washington
> >> damor..._at_x.washington.edu
> >> (replace x with u to respond)
> >> Puget Sound Oracle Users Groupwww.psoug.org-Hide quoted text -
>
> >> - Show quoted text -
>
> > Hocus-pocus trick, no. Magic, maybe. It is unique data for one
> > particular value of the stated field.
>
> I disagree. I understand your intent but I disagree with trying to play
> games with relational integrity. Remodel it.
>
> >> There is one and only one solution ... correctly model your business requirement.
>
> > Why do you think that productions systems are always so easily
> > changed? How many companies have you consulted for that could just
> > clean-sweep their database design and not go out of business? I work
> > with a set of railroad companies that are still using 1960s
> > technologies on proprietory mainframe systems, maybe I should suggest
> > they upgrade some time!
>
> I've so far worked for small insignificant firms like AT&T, Boeing,
> Washington Mutual Bank, and the like. Somehow they've succeeded in
> working within a relational database without playing such games and I've
> no doubt a poll of the vast majority here would similarly demonstrate
> that they have had no problem properly modeling using constraints.
>
> I may be totally off-base and if so I apologize. But it seems you have
> determined the solution absent the tool and now are trying to make the
> tool fit your solution.
>
> > The table in question is at the heart of a large system. I'm running
> > some update queries on the table, and I think if I could join it
> > properly to another table I could get the update to run faster. It
> > would be an updatable join rather than an 'exists' query.
>
> I think you can't. Or at least not the way you are trying to do it.
> Reconsider alternative methods of accomplishing the goal.
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

> I may be totally off-base and if so I apologize. But it seems you have
> determined the solution absent the tool and now are trying to make the
> tool fit your solution.

I'm trying to optimize queries, without changing the database structure too drastically. The query in question is an update query that was working in 10 seconds flat when outside of a long transaction, but was taking 40 minutes to run when placed at the end of a large transaction. I had couple of senior DBAs looking at this on the client side and none of them could see or solve the issue. We were not running out of rollback space, or memory, as far as we could tell (there were no error messages), it was just slower when put in a long transaction. The execution plan was identical in both cases. Maybe this is a topic for a separate thread. Received on Fri Feb 16 2007 - 14:48:40 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US