Re: Which query is best?

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Tue, 15 Dec 2009 13:22:46 +0000
Message-ID: <7765c8970912150522k1007f929gde3f0615fcf9785a_at_mail.gmail.com>



comments in line

On Tue, Dec 15, 2009 at 9:50 AM, Stephane Faroult <sfaroult_at_roughsea.com>wrote:

>
> And although I don't agree on everything with Celko, I have always found
> he hits the nail on the head here
> http://www.information-management.com/issues/20050601/1028737-1.html
>
> Four years old article, and still fresh ...

It is a nice article, sadly I suspect it will still apply in 10 years from now.

> SF
>
> Syed Jaffar Hussain wrote:
> > My main idea behind looking for an alternative sql was to reduce the
> > waiting time for the select time to avoid long locking period for the
> > record.
>

snipped to hopefully avoid over quoting.

Any chance of sharing the update and it's plan?, and maybe how it is called - I often see this sort of thing in a loop in pl/sql (or worse in the client language)

<pseudocode>
for <some loop condition> loop
select <key values> where <current loop condition values> update something where key = <key values from previous select> end loop
</pseudocode>

nearly always what is meant is

update something where <loop condition>

:(

It looks like your logic might be a little different though. As Stephane implies understanding the whole of the transaction and thinking through the appropriate business approach is often the best approach in cases like these. Incidentally do you have very many more iterations of this logic than you have user sessions (assuming it is an interactive query)

Niall

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Dec 15 2009 - 07:22:46 CST

Original text of this message