|
X$BH
|
Hello everyone. In this issue, I will continue analyzing the topic I covered last time.
[4. Performing UPDATE at index scan]
*************************************************************
(1)Perform UPDATE at index scan in session B
*Data has already been deleted in session A.
SQL> update test set id2=1 where id1=1;
The status of the object on database buffer
OBJECT_NAME STATE DBABLK BA
-------------------- ----- ---------- --------
TEST xcur 70913 54DA0000
TEST xcur 70914 54D96000
TEST_IDX cr 96394 54D52000 <-created to ensure consistent read
TEST_IDX xcur 96394 54D98000
*BA of the current block in TEST_IDX is not changed after being updated.
*************************************************************
|
Target row (ROWID) is identified by the index (TEST_IDX) and the table (TEST)
is updated. CR block is created in the index to ensure consistent read.
This time, however, row value to which DELETE is performed is stored in the same
leaf block of the index, and this is why CR block is created.
If the value is stored in another block, CR block is not created.
Whether or not CR block is created depends on the version (SCN) of the leaf block.
Here, I want to focus on the fact that CR block of the table is not created.
ROWID to be updated is identified when the index is searched. Thus, it is not necessary
to create the CR block of the table. You can save the performance then.
[5. Performing UPDATE at full scan]
*************************************************************
(1)Perform DELETE in session A
SQL> delete from test where id1=2;
The status of the object on database buffer
OBJECT_NAME STATE DBABLK BA
-------------------- ----- ---------- --------
TEST xcur 70913 54E90000
TEST xcur 70914 54E86000 <-block to be updated
TEST_IDX xcur 96394 54E88000
(2)Perform UPDATE at full scan in session B
SQL> update /*+ FULL(test) */ test set id2=1 where id1=1;
[Specify full scan in hint]
The status of the object on database buffer
OBJECT_NAME STATE DBABLK BA
-------------------- ----- ---------- --------
TEST xcur 70913 54E90000
TEST cr 70914 54E44000
TEST xcur 70914 54E86000 <-No change in BA
TEST_IDX xcur 96394 54E88000
*************************************************************
|
Index (TEST_IDX) is not accessed at all. Instead, full scan is performed
to the table (TEST). CR block is created to ensure the consistent read.
Take a look at BA (Buffer Address) column.
When I performed UPDATE in a single session environment, the buffer address of the
current block to be updated was changed after being updated. I haven't yet figured out exactly
why the buffer address changed, but I assume that it is probably due to performance matter.
This is the analysis of how CR block being created in multiple sessions.
There is no significant difference in an action between a single session and multiple sessions.
CR block is created in the following situations:
CR block is created (multiple sessions)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
SELECT UPDATE INSERT
------------------------ ------- ------- -------
Full scan - TABLE Yes Yes --
- INDEX No No --
Index scan - TABLE Yes No --
- INDEX Yes Yes --
|
When UPDATE is performed at full scan, CR block of the table is created, whereas, when
UPDATE is performed at index scan, CR block of the index is created.
The following explains all.
Index scan:
1. Acquire ROWID by accessing the index.
2. With the ROWID, access the row of the block to be updated.
3. Perform UPDATE.
Full scan:
1. Identify the row to be updated by gathering all data of the block.
2. Perform UPDATE.
Index scan, here, means that the row to be updated can be identified
only by accessing the index. If you need to identify it by information
of the column except for the index after performing the range scan,
index scan works in the same way as full scan does.
Yoshihiro Uratsuji
|
|
