After all the data mentioned in Oracle Document, all data should be reused, this is the point of view of the redistribution of the block, that is, the PCTUSED = 0 equivalent to the index block, to be completely deleted After returning to freelist (ASSM does not exist freelist, it is represented by bitmap), but in the inside of the index block, it is not that the space being deleted cannot be reused. The so-called reuse should be for Block and should not be used for ROW, as long as Key Value can be reused within the block acceptable, without having to pay exactly the same. For records in Block, physical location is not related to Key order, may be made due to Delete / Update, etc.
Test is as follows
SQL> CREATE TABLE T AS SELECT * from DBA_Objects Where Object_id is not null; Table has been created. SQL> CREATE INDEX T_INDEX ON T (Object_ID); index has been created. SQL> DESC T name is empty? Type ------------------------------------------------------------------------------------------------------------------------------------------------- - - -------- ---------------------------- Owner varchar2 (30) Object_name varchar2 (128) Subobject_name varcha2 ( 30) OBJECT_ID NUMBERDATA_OBJECT_ID NUMBEROBJECT_TYPE VARCHAR2 (18) CREATED DATELAST_DDL_TIME DATETIMESTAMP VARCHAR2 (19) STATUS VARCHAR2 (7) TEMPORARY VARCHAR2 (1) GENERATED VARCHAR2 (1) SECONDARY VARCHAR2 (1) SQL> select FILE_ID, EXTENT_ID, BLOCK_ID from dba_extents where segment_name = ' T_index '; file_id eXtent_id block_id ---------- ---------- ---------- 9 0 5699 1 5779 2 5859 3 5939 4 6019 5 6099 6 6179 7 6259 8 6339 9 641 10 lines have been selected. SQL> SELECT MIN (Object_ID) from T; min (Object_ID) -------------- 2 Since the table space of the ASSM type, I found the beginning of the index leaf node to block_id. 573 SQL> ALTER SYSTEM DUMP DATAFILE 9 Block 573; KdxLeprv 0 = 0x0kdxledsz 0kdxlebksz 8036ROW # 0 [8024] Flag: -----, LOCK: 0 8024 Represents offset Bytes in Block, which is location COL 0; LEN 2 (2): C1 03 ------- This value is 2Col 1; LEN 6; (6): 02 40 00 6d 00 1Frow # 1 [8012] Flag: -----, Lock: 0col 0; LEN 2; (2): C1 04Col 1; LEN 6; (6): 02 40 02 2F 00 26ROW # 2 [8000] Flag: -----, Lock: 0col 0; LEN 2; (2) : C1 05COL 1; LEN 6; (6): 02 40 00 94 00 16SQL> Delete from T where Object_ID = 2; 1 line has been deleted. SQL> Commit; submit completion. SQL> ALTER SYSTEM DUMP DATAFILE 9 Block 573; the system has changed.
Row # 0 [8024] FLAG: --- D- LOCK: 2 --- D- Representation is removed COL 0; LEN 2; (2): C1 03COL 1; LEN 6; (6): 02 40 00 6d 00 1Frow # 1 [8012] Flag: -----, Lock: 0col 0; Len 2; (2): C1 04COL 1; LEN 6; (6): 02 40 02 2F 00 26ROW # 2 [8000] Flag : -----, Lock: 0Col 0; LEN 2; (2): C1 05Col 1; Len 6; (6): 02 40 00 94 00 16SQL> INSERT INTO T VALUES (1, 1, 1, 1, 1, 1, sysdate, sysdate, 1, 1, 1, 1, 1); 1 line has been created. SQL> Commit; submit completion. SQL> ALTER SYSTEM DUMP DATAFILE 9 Block 573; KdxLeprv 0 = 0x0kdxledsz 0kdxlebksz 8036ROW # 0 [1822] Flag: -----, Lock: 2 - The insertion position has changed, which is equivalent to UPDATE to reserve part of the PCTFREE reserved part of COL 0; Len 2; (2): C1 02COL 1; LEN 6; (6): 02 40 01 BE 00 00 00 # 1 [8012] Flag: ----- Lock: 0col 0; LEN 2; (2): C1 04Col 1; LEN 6; (6): 02 40 02 2F 00 26ROW # 2 [8000] Flag: -----, Lock: 0Col 0; LEN 2; (2): C1 05COL 1; LEN 6; (6 : 02 40 00 94 00 16SQL> Delete T where Object_ID = 1; 1 line has been deleted. SQL> Commit; submit completion. SQL> INSERT INTO T VALUES (1, 1, 1, 1, 1, 1, Sysdate, Sysdate, 1, 1, 1, 1, 1); 1 line has been created. SQL> Commit; submit completion. SQL> ROW # 0 [1810] Flag: -----, Lock: 2 We found that even if the same value is inserted immediately after deletion, it will not immediately reuse the previous space 1822, but still in the new location 1810col 0. Leen 2; (2): C1 02Col 1; LEN 6; (6): 02 40 01 be 00 01ROW # 1 [8012] Flag: -----, Lock: 0Col 0; LEN 2; (2): C1 04COL 1; LEN 6; (6): 02 40 02 2F 00 26ROW # 2 [8000] Flag: -----, LOCK: 0COL 0; LEN 2; (2): C1 05COL 1; LEN 6; 6): 02 40 00 94 00 16 If the block will be reorganized when it continues to test, the conclusion after further testing 1: The indexed PCTFree is used to insert a new Key that can accommodate this block acceptable. Value, of course, PCTFree will also be related to ITRRENS ---> Maxtrans 2: Remove the record in the index when the delete is marked as d, the record is in the case, if the record is present, if the next transaction is occurred on the block , The tagged delete record is immediately cleared
More please refer to: http://www.itpub.net/showthread.php?threadid=149768
