Home » RDBMS Server » Performance Tuning » Cost of an update
Cost of an update [message #169019] Mon, 24 April 2006 13:25 Go to next message
hrshah74
Messages: 2
Registered: November 2005
Junior Member
Say we have a table EMP
EMPID NUMBER(10),
EMPNAME VARCHAR2(100),
OCC_FLAG VARCHAR2(1)

A bitmap index is created on OCC_FLAG. I want to find out how much an update costs in following two scenarios.

Scenario 1: Initially OCC_FLAG is null.
UPDATE EMP
SET OCC_FLAG = 'X'
WHERE EMPID=1000;

Scenario 2: OCC_FLAG has a default value of 'N'
UPDATE EMP
SET OCC_FLAG = 'X'
WHERE EMPID=1000;

I understand initially when OCC_FLAG is null, the entry won't be there in the index tree. But I want to find out the statistics. What does oracle do in these two scenarios?

Thanks
Re: Cost of an update [message #169058 is a reply to message #169019] Mon, 24 April 2006 21:52 Go to previous message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Assuming that a bitmap index does not store a bitstring for the NULL value (and I'm not so sure about that, but I'm in a hurry to get to the footy, so I can't test it or check the doco), then (2) would have to do more IO.

- Find and update the table block ([1] and [2])
- Rewrite 1 block of the 'N' bitstring to "unset" the bit referencing that row ([2] only).
- Rewrite 1 block of the 'X' bitstring to "set" the bit referencing that row ([1] and [2]).

This is not an exact science though. What if the updated row was the last one with status 'N'? It would have to delete the remnants of the bit string. What if this was the first row to be updated to 'X'? It would have to initialise the 'X' bitstring.

Ross Leishman
Previous Topic: Data Gaurd 10g
Next Topic: use HASH instead of index range scan,why the CBO choose the wrong plan?!!
Goto Forum:
  


Current Time: Tue May 28 07:48:31 CDT 2024