Re: Best practice question: Updating Aggregates

casey.kirkpatrick_at_gmail.com wrote:
> Daniel,
>
> We seem to be talking past each other. Let me be brief: I have an
> application that produces output based on aggregate data, but must
> update the source rows it pulls from.
>
> *****From the perspective of writing maintainable, clear, bug-free
> code******, is there any preference between:
> 1) Fetching the data in a single aggregate cursor, then updating it in
> a separate statement or
> 2) Fetching the data in a non-aggregate FOR UPDATE cursor, updating one
> record at a time, and aggregating the data in the PL/SQL layer.
>
> The one reason I present the issue is because I'm conflicted: my
> intuition to avoid coding logic in PL/SQL that which can be put in SQL
> points me towards #1; but my intuition to enforce data integrity points
> me towards# 2; my question is: does anyone know of a middle-ground
> solution to this, or a definitive reason to choose one over the other?
>
> And Daniel, a side question: in evaluating code, do you look at
> anything besides the code getting correct results and performing well?
> Would you teach a beginner about AUTOTRACE prior to teaching about
> referential integrity? I've had co-workers fix Catesian products by
> adding a DISTINCT clause and some parallelism... because they think
> only two things count in their code: corrctness and speed. I've always
> wondered where such a philosphy could have been seeded.

I would expect any student I get to already have basic database training so I never deal, at my level, with someone that doesn't already have a CS degree and a good working knowledge of normalization and referential integrity. Sort of like asking a calculus teacher whether they cover addition and subtraction. The assumption is that the student already knows it.

I would only lock if locking is essential to prevent some other session from altering the records while my processing was taking place. So if you need FOR UPDATE use it. If not don't.

Otherwise I would always fetch using BULK COLLECT INTO with the LIMIT clause tuned for optimal performance followed by the UPDATE using FORALL.

Does that help?

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)