Oracle DBA Home

9/17/21

Use Analytical functions to optimize the sql code - Example-2

This is another example of using ANALYTICAL function to optimize the SQL Access.

Consider the following SQL

-- Table Columns:

TABLE MY_TABLE
( COLA,

COLB,

TXT_1,

TXT_2,

TXT_3,

SRCH_TXT_1,

SRCH_TXT_2,

SRCH_TXT3

) ;

SELECT a.*,
a.TXT_1 as FULL_TXT,

a.SRCH_TXT_1 as SRCH_TXT
FROM MY_TABLE a

UNION

SELECT a.*,
a.TXT_2 as FULL_TXT,

a.SRCH_TXT_2 as SRCH_TXT
FROM MY_TABLE a

UNION

SELECT a.*,
a.TXT_3 as FULL_TXT,

a.SRCH_TXT_3 as SRCH_TXT
FROM MY_TABLE a ;

Above query is going to get all the rows of the table 3 times with extra columns FULL_TXT and SRCH_TXT. It is basically conversion of rows into columns. Oracle provides UNPIOVT to achieve this.

SELECT DISTINCT COLA,

COLB,

U_TXT_1 as TXT_1,

U_TXT_2 as TXT_2,

U_TXT_3 as TXT_3,

U_SRCH_TXT_1 as SRCH_TXT_1,

U_SRCH_TXT_2 as SRCH_TXT_2,

U_SRCH_TXT_3 as SRCH_TXT_3,

FULL_TXT,

FULL_SRCH_TXT

(

SELECT *

FROM

( SELECT a.*,

a.TXT_1 as U_TXT_1,

a.TXT_2 as U_TXT_2,

a.TXT_3 as U_TXT_3,

a.SRCH_TXT_1 as U_SRCH_TXT_1,

a.SRCH_TXT_2 as U_SRCH_TXT_2,

a.SRCH_TXT_3 as U_SRCH_TXT_3

FROM MY_TABLE a

)

UNPIVOT ( ( FULL_TXT, FULL_SRCH_TXT) for node in ( (TXT_1, SRCH_TXT_1) as '1',

(TXT_2, SRCH_TXT_2) as '2',

(TXT_3, SRCH_TXT_3) as '3',

)

) ;

Use Analytical functions to optimize the sql code - Example-1

It is common practice to use corelated subquery to get the max record for a given value.

SELECT B.COLA,
B.COLB
FROM BIG_TABLE B
WHERE B.COLA IN (select VALUE from SMALL_TABLE d where d.colx = 'VALUE')
AND B.COLC = ( select max(COLC)
from BIG_TABLE C
where B.COLC = C.COLC
) ;

Problem with Above query is Oracle needs to query the BIG_TABLE twice.

We can rewrite the query as follows using analytical function to avoid scanning table twice.

SELECT COLA,
COLB
FROM ( SELECT B.COLA,
B.COLB
B.COLC,
MAX(B.COLC) OVER ( PARTITION BY B.COLA ) MAX_COLC
FROM BIG_TABLE B
WHERE B.COLA IN ( select VALUE from SMALL_TABLE d where d.colx = 'VALUE' )
)
WHERE COLC = MAX_COLC ;

11/22/20

How does range condition effects the performance ?

---------------------------------------------------------------

One of the select query is taking more time and the application changed the following

id <=2 to id < 3

select * from table where id <=2 --- original query

select * from table where id < 3 ---- new query , taking longer

both queries are using the index range scan i.e. same execution plan

both queries are semantically same and provides the same results but for true oltp systems it makes different.

for new query we are seeing more buffer gets than the old query

i.e. for new query buffer gets reduced dramatically, so we opened the sr with oracle and they provided following possible reason

id <=2 will scan the less number of blocks than the id < 3

:-)

Cache Buffer Chain Latches:

How to solve Cache buffers Chain Latch Contention :

-----------------------------------------------------------------

Since the Oracle SGA holds lots of information in various types of memory structures, it is not enough to have just one latch for each type of memory structure to be protected.

For example, if you have an 8 GB buffer cache for 8 KB blocks, then you have one million buffers in the buffer cache.

Reasonable concurrent access to all these million buffers can't be provided by a single latch (in this case a "cache buffers chains" latch).

Therefore, Oracle has split the memory structures that need protection into logical sub-pieces and each piece is protected by a separate sub-latch known as a child latch

For example, if you run a database with large buffer cache, you may have thousands of cache buffers chains child latches in use.

When accessing a data buffer block, Oracle will run a modulus function on the data block address (DBA) of the block

to determine which cache buffers chains latch to get to satisfy that access

Cache buffers chains (CBC) latch contention can occur due a number of reasons. One of the most common reasons is that you have an extremely hot block in the buffer cache

1) First, there are many CBC latches in an Oracle instance

SQL> select name, count(*) from v$latch_children where name like '%chains%' group by name;

NAME COUNT(*)

-------------------------------------------------- ----------

cache buffers chains 2048

enqueue hash chains 8

library cache hash chains 11

2) So, as a first step we should identify which CBC latches are experiencing the contention:

i.e. is the contention lightly spread across many CBC latch children or is there a single child latch experiencing much heavier contention compared to others?

V$LATCH_CHILDREN.WAIT_TIME --> find the latch children that has highest number of wait time

ex: in a sql trace we found following:

WAIT #1: nam='latch: cache buffers chains' ela= 204 address=15742234376 ...

WAIT #1: nam='latch: cache buffers chains' ela= 8 address=15742234376 ...

WAIT #1: nam='latch: cache buffers chains' ela= 2 address=15742234376 ...

WAIT #1: nam='latch: cache buffers chains' ela= 7 address=15742234376 ...

From address: (sql trace shows address and we need to change it to hexadecimal)

SQL> select name, gets, misses, sleeps from v$latch_children where addr = hextoraw(trim(to_char(15742234376, '0XXXXXXXXXXXXXXX')));

NAME GETS MISSES SLEEPS

------------------------- ---------- ---------- ----------

cache buffers chains 62178598 125541 805

from ASH:

SQL> SELECT *
FROM
( SELECT
event,
TRIM(TO_CHAR(p1, 'XXXXXXXXXXXXXXXX')) latch_addr,
TRIM(ROUND(RATIO_TO_REPORT(COUNT(*)) OVER () * 100, 1))||'%' PCT,
COUNT(*)
FROM
v$active_session_history
WHERE
event = 'latch: cache buffers chains'
AND session_state = 'WAITING'
GROUP BY
event,
p1
ORDER BY
COUNT(*) DESC
) WHERE ROWNUM <= 10;

EVENT LATCH_ADDR PCT COUNT(*)

----------------------------- ----------------- ------ ----------

latch: cache buffers chains 3AA4F6F08 4.1% 50

latch: cache buffers chains 3AA4D4908 3.7% 45

latch: cache buffers chains 3AA4E2558 3.4% 42

latch: cache buffers chains 3AA4F00E0 3.2% 39

latch: cache buffers chains 3AA519440 2.9% 36

latch: cache buffers chains 3AA527090 2.8% 34

latch: cache buffers chains 3AA4FDD30 2.8% 34

latch: cache buffers chains 3AA50B8B8 2.7% 33

latch: cache buffers chains 3AA4DB730 2.6% 32

latch: cache buffers chains 3AA534C18 2.6% 32

10 rows selected.

3) find the sql:

After getting the latch_children address we can find the session and corresponding sql.

SQL> desc v$latchholder;

Name Null? Type

----------------------------------------- -------- ------

PID NUMBER

SID NUMBER

LADDR RAW(8)

NAME VARCHAR2(64)

GETS NUMBER

9/2/12

SEMI/ANTI JOINS

SEMI JOIN

A “semi-join” between two tables returns rows from the first table where one or more matches
are found in the second table.

Semi-joins are written using the EXISTS or IN constructs.

a semi-join can be performed using the nested loops, hash join, or merge join algorithms

Oracle provides the NL_SJ, HASH_SJ, and MERGE_SJ hints in order for you to manipulate the semi-join process if you need to. The hint is applied to the subquery of the EXISTS or IN clause, not the main body of the query itself.

if the MAIN query is more selective i.e returns less number of rows then its better to use EXIST
if the subquery is more selective then its better to use "IN" operator.

ANTI JOIN:

An “anti-join” between two tables returns rows from the first table where no matches are found in the second table. Anti-joins are written using the NOT EXISTS or NOT IN constructs.

if the subquery of "NOT IN" results at least one NULL value then entire NOT IN will be false
and we will not get any results, But "NOT EXIST" will consider NULL as value and returns the value.

Restrictions on SEMI/ANTI joins:

1. If there is a DISTINCT clause in the query then oracle can’t use semi joins (there is one alternative for DISTINCT claue, see below example i.e using NO_MERGE hint)

2. The EXIST/IN is part of OR operation then oracle can’t use semi joins

3. If the query contains UNION set operator then oracle can’t use semi joins

If there is a DISTINCT clause in the query or the EXIST/IN is part of OR operation then
we can't use SEMI/ANTI joins.

SQL> select DISTINCT username from tab1 where exist
(select 1 from tab2 where tabb2.col1 = tab1.col1 );

-- The above query will not use SEMI JOIN because we have the DISTINCT operator in the query

Workaround:

SQL> select /*+ no_merge(tab) */
DISTINCT username
from (
select username from tab1 where exist
(select 1 from tab2 where tabb2.col1 = tab1.col1 )
) tab;

Example:

Create CUSTOMER table:

SQL> create table test_table_cust_123

(name varchar2(20), state varchar2(20), cust_id number);

Table created.

begin

for i in 1 .. 100

loop

insert into test_table_cust_123 values

(dbms_random.string('U', 10), dbms_random.string('U', 2),

round(dbms_random.value(0, 100)) );

end loop

commit;

end;

delete duplicate cust_id's:

SQL> delete from test_table_cust_123 where cust_id in

(select cust_id from (select cust_id, count(*) from test_table_cust_123

group by cust_id having count(*) > 2));

Create ORDER table:

SQL> create table test_table_order_123

(item varchar2(20), price number, order_date date, cust_id number);

Table created.

begin

for i in 1 .. 100

loop

insert into test_table_order_123 values (

dbms_random.string('U', 10), round(dbms_random.value(1000, 100000)),

sysdate - round(dbms_random.value(0, 100)), round(dbms_random.value(0, 100)) );

end loop

commit;

end;

query:

SELECT C.name,

C.cust_id

FROM test_table_cust_123 C

WHERE C.state = 'CA'

AND EXISTS

(

SELECT 1

FROM test_table_order_123 O

WHERE O.cust_id = C.cust_id

AND O.order_date > SYSDATE - 3

)

ORDER BY C.name;

The above query is using the HASH SEMI JOIN, the execution plan was shown below:

---------------------------------------------------------------------------------

---------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 59 | 6 (34)|

| 1 | SORT ORDER BY | | 1 | 59 | 6 (34)|

|* 2 | HASH JOIN SEMI | | 1 | 59 | 5 (20)|

|* 3 | TABLE ACCESS FULL| TEST_TABLE_CUST_123 | 2 | 74 | 2 (0)|

|* 4 | TABLE ACCESS FULL| TEST_TABLE_ORDER_123 | 1 | 22 | 2 (0)|

---------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("O"."CUST_ID"="C"."CUST_ID")

3 - filter("C"."STATE"='CA')

4 - filter("O"."ORDER_DATE">SYSDATE@!-3)

We can force the query to use Nested Loop SEMI Join by using the hint NL_SJ in the EXIST subquery block, The execution plan was shown below:

---------------------------------------------------------------------------------

---------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 59 | 5 (20)|

| 1 | SORT ORDER BY | | 1 | 59 | 5 (20)|

| 2 | NESTED LOOPS SEMI | | 1 | 59 | 4 (0)|

|* 3 | TABLE ACCESS FULL| TEST_TABLE_CUST_123 | 2 | 74 | 2 (0)|

|* 4 | TABLE ACCESS FULL| TEST_TABLE_ORDER_123 | 1 | 22 | 1 (0)|

---------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - filter("C"."STATE"='CA')

4 - filter("O"."ORDER_DATE">SYSDATE@!-3 AND "O"."CUST_ID"="C"."CUST_ID")

We can force the query to not use the SEMI join and to use FILTER operation followd by a SORT, This can be achived with NO_UNNEST hint in the EXIT subquery. Execution plan was shown below:

---------------------------------------------------------------------------------

---------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 37 | 5 (20)|

| 1 | SORT ORDER BY | | 1 | 37 | 5 (20)|

|* 2 | FILTER | | | | |

|* 3 | TABLE ACCESS FULL| TEST_TABLE_CUST_123 | 2 | 74 | 2 (0)|

|* 4 | TABLE ACCESS FULL| TEST_TABLE_ORDER_123 | 1 | 22 | 2 (0)|

---------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "TEST_TABLE_ORDER_123" "O"

WHERE "O"."CUST_ID"=:B1 AND "O"."ORDER_DATE">SYSDATE@!-3))

3 - filter("C"."STATE"='CA')

4 - filter("O"."CUST_ID"=:B1 AND "O"."ORDER_DATE">SYSDATE@!-3

8/18/12

Index Join Vs Index Combine

We have following query, which taking longtime to return the results.

SELECT object_id
FROM object_table
WHERE create_time > :B1
AND new_object = :B2
AND object_class IN (:B3, :B4)
ORDER BY new_object ASC;

Table Details:
-------------
Table: object_table
No of records: 900,000
Table Size: 2 Gb

Indexe Details: (four different single column indexes)
------------------------------------------------------
CREATETIME_IX on (create_time)
NEW_OBJECT_IDX on (new_object)
OBJECT_CLASS_IDX on (object_class))
OBJECTID_IDX on (object_id)

The above query was running long and using the full tablescan. The execution plan was shown below:

----------------------------------------------------------------------------

----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 10885 (100)|

| 1 | SORT ORDER BY | | 3 | 54 | 10885 (1)|

| 2 | TABLE ACCESS FULL| OBJECT_TABLE | 3 | 54 | 10884 (1)|

----------------------------------------------------------------------------

The query will always return very less number of rows (10-20 rows), So scanning whole table(900k rows) to get 10-20 rows was not a optimal access method.

How to eliminate the full table scan for above query?

Ans: INDEX JOIN or INDX COMBINE(Bitmap Conversion)

Index Join:

The SELECT statement involved in four different columns, and all the columns have separate indexes.
So If we join all the indexes we can eliminate the full tablescan. This can be done by using the INDEX_JOIN hint.

Index Joins will be used to avoid the expensive table scans by joining two or more indexes belonging to the same table.

Oracle will join the indexes only if all the columns referenced in the query must be covered in the indexes itself, So it can avoid the table visits.

Index joins will be used only with HASH joins but can use any available index access method.

I forced the query to use index join by using the hint INDEX_JOIN(table_name) , Execution plan was shown below:

Since by using index join, oracle joining the four indexes using the HASH joins.

--------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 10694 (100)|

| 1 | SORT ORDER BY | | 3 | 54 | 10694 (1)|

| 2 | VIEW | index$_join$_001 | 3 | 54 | 10693 (1)|

| 3 | HASH JOIN | | | | |

| 4 | HASH JOIN | | | | |

| 5 | HASH JOIN | | | | |

| 6 | INDEX RANGE SCAN | NEW_OBJECT_IDX | 3 | 54 | 688 (1)|

| 7 | INDEX RANGE SCAN | CREATETIME_IX | 3 | 54 | 1610 (1)|

| 8 | INLIST ITERATOR | | | | |

| 9 | INDEX RANGE SCAN | OBJECT_CLASS_IDX | 3 | 54 | 2670 (1)|

| 10 | INDEX FAST FULL SCAN| OBJECTID_IDX | 3 | 54 | 6670 (1)|

--------------------------------------------------------------------------------------

The query with index_join hint was running faster than the original one, but response time was still inacceptable to application.

So I have one more option to try, i.e BITMAP CONVERSION OF BTREE INDEXES

Bitmap Conversion for the B-tree Indexes:

Now look at the following two indexes:

CREATETIME_IX on (NextRunTime)
NEW_OBJECT_IDX on (SI_RUNNABLE_OBJECT)

So we have two single column indexes on the two of the columns referenced in the WHERE claue, So oracle can combine those indexes and make use of it.
Since we have another column was referenced in the table, it needs a visit to table if we use this method.

We can use following hint to force BITMAP CONVERSION plan of BTREE index: INDEX_COMBINE(table_name index_name1 index_name2)
.
It builds in-memory BITMAP IDEX by using the those two BTREE indexes and based on the rowid it will access the table and applies the filter condition.

The execution plan was shown below:

----------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 12945|

| 1 | SORT ORDER BY | | 2 | 36 | 12945|

| 2 | TABLE ACCESS BY INDEX ROWID | CMS_INFOOBJECTS6 | 2 | 36 | 12944|

| 3 | BITMAP CONVERSION TO ROWIDS | | | | |

| 4 | BITMAP AND | | | | |

| 5 | BITMAP CONVERSION FROM ROWIDS| | | | |

| 6 | INDEX RANGE SCAN | NEW_OBJECT_IDX | | 814 | |

| 7 | BITMAP CONVERSION FROM ROWIDS| | | | |

| 8 | SORT ORDER BY | | | | |

| 9 | INDEX RANGE SCAN | CREATETIME_IX | | | 736 |

----------------------------------------------------------------------------------------

Even though it shows more cost than other two execution plan, The response time was very low compared to other two methods and it solved the response
time problem.

So it looks like a bug in the optimizer while estimating the cost of BITMAP CONVERSION of BTREE INDEXES.

12/16/11

WHAT HAPPENS TO INDEX ENTRY WHEN WE DELETE/UPDATE CORRESPONDING ROW FROM TABLE ??

WHAT HAPPENS TO INDEX ENTRY WHEN WE DELETE/UPDATE CORRESPONDING ROW FROM TABLE ??

create a table and index:

SQL> create table test_table (id number, name varchar2(20));

Table created.

SQL> insert into test_table values (1, 'JUNK1');

1 row created.

SQL> insert into test_table values (2, 'JUNK2');

1 row created.

SQL> commit;

Commit complete.

SQL> create index test_table_idx on test_table (id);

Index created.

SQL> select object_id from dba_objects where

object_name='TEST_TABLE_IDX';

OBJECT_ID

----------

73515

Now dump the Index structure:

SQL> alter session set events

'immediate trace name treedump level 73515';

Session altered.

----- begin tree dump

leaf: 0x10007d3 16779219 (0: nrow: 2 rrow: 2)

----- end tree dump

nrow: number of all index entries including deleted entries

rrows: number of current index entries

DBA: 16779219 (DATA BLOCK ADDRESS)

OBSERVATION: there is only one leafblock/root block which has two row entries.(no deleted entries as nrow = rrow)

Dump the leafblock:

We have only one leafblock which is also a root block, and

rootblock is allways next to header block.

SQL> select header_file, header_block from dba_segments where

segment_name='TEST_TABLE_IDX';

HEADER_FILE HEADER_BLOCK

----------- ------------

4 2002

So root block# = 2002+1

or we can find the block address using DBA (Which we got from treedump)

SQL> select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(16779219),

DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(16779219) from dual;

File # Block#

----------------------------------------------- 4 2003

Sql> alter system dump datafile 4 block 2003;

Session altered

Leaf block dump

===============

header address 3086706276=0xb7fb6664

kdxcolev 0

KDXCOLEV Flags = - - -

kdxcolok 0

kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y

kdxconco 2

kdxcosdc 0

kdxconro 2

kdxcofbo 40=0x28

kdxcofeo 8008=0x1f48

kdxcoavs 7968

kdxlespl 0

kdxlende 0

kdxlenxt 0=0x0

kdxleprv 0=0x0

kdxledsz 0

kdxlebksz 8032

row#0[8020] flag: ------, lock: 0, len=12

col 0; len 2; (2): c1 02 à value of indexed column

col 1; len 6; (6): 01 00 07 cd 00 00 à rowid appended

row#1[8008] flag: ------, lock: 0, len=12

col 0; len 2; (2): c1 03 à value of indexes column

col 1; len 6; (6): 01 00 07 cd 00 01 à rowid appended

----- end of leaf block dump -----

End dump data blocks tsn: 4 file#: 4 minblk 2003 maxblk 2003

Now Delete one row from table:

OBSERVATION: leaf block contains the two rows entries (one for each row in table).

DELETE A ROW FROM TABLE:

Sql> delete from test_table where id=1;

1 row deleted

Sql> commit;

Commited

SQL> alter session set events

'immediate trace name treedump level 73515';

Session altered.

----- begin tree dump

leaf: 0x10007d3 16779219 (0: nrow: 2 rrow: 1)

----- end tree dump

Dumpfile indicates that we have only one current entry (rrow=1)

And total rows including marked as delete = 2 (nrow=2)

--Index has two rows entries even after we deleted a row from the table. i.e corresponding index is just marked as DELETE but not deleted physically.

Lets dump the leafblock:

SQL> alter system dump datafile 4 block 2003;

System altered

Leaf block dump

===============

header address 3086706276=0xb7fb6664

…………………………………………………………………………………………….

row#0[8020] flag: ---D--, lock: 2, len=12

col 0; len 2; (2): c1 02

col 1; len 6; (6): 01 00 07 cd 00 00 à deleted rowid

row#1[8008] flag: ------, lock: 0, len=12

col 0; len 2; (2): c1 03

col 1; len 6; (6): 01 00 07 cd 00 01 à current existing rowid

----- end of leaf block dump -----

End dump data blocks tsn: 4 file#: 4 minblk 2003 maxblk 2003

Observations:

First row entry is still there but marked as DELETE with flag “D”

Now lets see what if inserted a new row into the table:

SQL> insert into test_table values (3, 'JUNK2');

1 row created.

SQL> commit;

Commit complete.

SQL> alter system dump datafile 4 block 2003;

System altered

Leaf block dump

===============

header address 3086706276=0xb7fb6664

…………………………………………………………………………………………….

row#0[8008] flag: ------, lock: 0, len=12

col 0; len 2; (2): c1 03

col 1; len 6; (6): 01 00 07 cd 00 01 àold remaining rowid

row#1[7996] flag: ------, lock: 2, len=12

col 0; len 2; (2): c1 04

col 1; len 6; (6): 01 00 07 cd 00 02 à New rowid

----- end of leaf block dump -----

End dump data blocks tsn: 4 file#: 4 minblk 2003 maxblk 2003

Observation:

As soon as a new rowentry added to the leaf block the row entry which was marked as ‘DELETE’ was actually deleted from the leafblock.

So row entries that are marked as DELETE will be physically deleted when new row is inserted into the leaf block.

This behaviour is confirmed also by dumping the tree structure:

----- begin tree dump

leaf: 0x10007d3 16779219 (0: nrow: 2 rrow: 2) à no more deleted entry

----- end tree dump

What Happenes when the row updated ??

Now we know that if rows are deleted then corresponding index row entries are marked as DELETE initially and if any new row entries are inserted into the leaf blocks then it actually delets the row entries that are marked as DELETTE previosly.

But what happens to the index row entries when corresponding rows are updated ??

SQL> select * from test_table;

ID NAME

---------- --------------------

2 JUNK

3 JUNK2

SQL> alter system dump datafile 4 block 2003;

Leaf block dump

===============

header address 3086706276=0xb7fb6664

…………………………………………………………………………………………….

row#0[8008] flag: ------, lock: 0, len=12

col 0; len 2; (2): c1 03

col 1; len 6; (6): 01 00 07 cd 00 01

row#1[7996] flag: ------, lock: 2, len=12

col 0; len 2; (2): c1 04

col 1; len 6; (6): 01 00 07 cd 00 02

----- end of leaf block dump -----

End dump data blocks tsn: 4 file#: 4 minblk 2003 maxblk 2003

Now update the row:

SQL> update test_table set id=4 where name='JUNK2';

1 row updated.

SQL> commit;

Commit complete.

Leaf block dump

===============

header address 3086706276=0xb7fb6664

…………………………………………………………………………………………….

row#0[8008] flag: ------, lock: 0, len=12

col 0; len 2; (2): c1 03

col 1; len 6; (6): 01 00 07 cd 00 01

row#1[7996] flag: ---D--, lock: 2, len=12 à entry marked as delete

col 0; len 2; (2): c1 04

col 1; len 6; (6): 01 00 07 cd 00 02

row#2[7984] flag: ------, lock: 2, len=12 à New entry added

col 0; len 2; (2): c1 05

col 1; len 6; (6): 01 00 07 cd 00 02

----- end of leaf block dump -----

End dump data blocks tsn: 4 file#: 4 minblk 2003 maxblk 2003

Observations: Even for updates oracle will follows the same procedure as of deletes.

i.e It will mark the old as DELETE and inserts new row for the updated value. If there is any further row insertes into the same leaf block then it will delete the rows entries that are marked as DELETED previously.

It can be confirmed by treedump also:

----- begin tree dump

leaf: 0x10007d3 16779219 (0: nrow: 3 rrow: 2)

----- end tree dump

Current rows = 2

Total rows including that marked as DELETE = 3

Once new rows are inserted into the same leaf blocks then oracle will delete the rowentries that are marked as DELETE previously.