RE: How to check if index was updated?

From: Ric Van Dyke <ric.van.dyke_at_hotsos.com>
Date: Thu, 27 Apr 2006 06:25:50 -0500
Message-ID: <C970F08BBE1E164AA8063E01502A71CF51854B@WIN02.hotsos.com>

Ah this puts a new twist on the question! If the update has all the columns from the table in the set clause, then yes the indexed columns will get updated. There will in effect be no change, but the update activity will take place. This means all the latching, pinning, redo and undo generation of the indexed blocks will take place, even though the value in end stays the same.

It's best to write the update that only includes the values being updated. For example if the only field that changed is the Street Address, then only put the Street Address in the set clause of the update. Put what ever is necessary in the where to only get the row you wanted updated.

Ric Van Dyke
Hotsos Enterprises
Cell 248-705-0624

Hotsos Symposium March 4-8, 2007. Be there. -----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of rjamya Sent: Thursday, April 27, 2006 7:13 AM
To: Niall Litchfield
Cc: Oracle Discussion List
Subject: Re: How to check if index was updated?

my developers do, because they are designing a new application and almost all code will use this new syntax to update thw whole row at a time, albit only some columns may have been modified in a row.

Since they are assuming that about 90+% the indexes columns may not get updated, would it be wise to use this syntax if it will cause index operations that are completely un-necessary? Mind you, these guys sometimes write queries that *must* return within 400ms, so fast operation is the first priority.

I want ot find out if there is an easy way other than dumping blocks or logfiles that my developers can also test.

I am trying to find syntax to use bbed to show the dump, hopefully that would be prettier than blockdump.

Raj

On 4/26/06, Niall Litchfield <niall.litchfield_at_gmail.com> wrote:
> *easiest* way, trace it and look at the recursive sql.
> or you could dump the relevant index blocks to disk before and after.
>
> Me, I probably wouldn't care.
>
> On 4/26/06, rjamya <rjamya_at_gmail.com> wrote:
> > Got a query from my developers, they are trying to use the
> > "update xxx set row " syntax to modify a row using a record.
> >
> > How would I verify that pk index will not get updated if only non
> > indexed columns were changed in the record?
> >
> > is there an easy way? btw this is on 10104.
> >
> > TIA
> > Rjamya
> > ----------------------------------------------
> > Got RAC?
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
> >
> >
>
>
> --
> Niall Litchfield
> Oracle DBA
> http://www.orawin.info
>

--
----------------------------------------------
Got RAC?
--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Received on Thu Apr 27 2006 - 06:25:50 CDT