On Feb 16, 5:01 pm, DA Morgan <damor..._at_psoug.org> wrote:
> dean wrote:
> > 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-Hidequoted 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.
>
> Then I would suggest building a key-preserved view is irrelevant and
> getting in the way of accomplishing that goal.
>
> > 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.
>
> Then why not treat this as a tuning problem and attack it with explain
> plan, autotrace, 10053 trace, ADDM, etc.
>
> I may be worng here but it seems you have determined a solution and are
> now trying to beat Oracle into submission absent any reason to believe
> it will either solve the problem or is possible.
>
> > I had couple of senior DBAs looking at this on
> > the client side and none of them could see or solve the issue.
>
> Then why don't you present the business problem to us rather than you
> proposed solution. Which, and again I may be wrong, seems unworkable.
>
> > 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.
>
> Again ... where's the explain plan? Where's the trace? Where are the
> statistics? Where is the StatsPack or AWR Report?
> --
> 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 -
The trace files are no longer available, and as I said I had them
looked at already. But what I would find advantageous is a better
technique for updating such joins as discussed, as there are several
tables of similar construct that prevent an updatable join.
Received on Sat Feb 17 2007 - 00:40:25 CST
