RE: How to check if index was updated?
- From: "Ric Van Dyke" <ric.van.dyke@xxxxxxxxxx>
- To: <rjamya@xxxxxxxxx>
- Date: Thu, 27 Apr 2006 06:25:50 -0500
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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@xxxxxxxxx> 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@xxxxxxxxx> 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?
> > --
> > //www.freelists.org/webpage/oracle-l
> >
> >
> >
>
>
> --
> Niall Litchfield
> Oracle DBA
> http://www.orawin.info
>
--
----------------------------------------------
Got RAC?
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
