| Cost of an update [message #169019] |
Mon, 24 April 2006 13:25  |
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  |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
