Index with no branch blocks
Date: Wed, 20 Nov 2019 13:17:42 +0530
Message-ID: <CAP-RywyF7OqphC=+GsOYR-yTeY=DqxXbxNgenvOeP1aCOmDtRg_at_mail.gmail.com>
fundamentals suggest that the index contains both branch blocks and leaf blocks, branch blocks used for searching and leaf blocks containing the data.
but when the table is initially created, there is no extent associated with it and when we insert the data into the table the extent is allocated to the table and to its corresponding indexes if any. usually the L1bmb is the first block of the extent, L2bmb the second block and l3bmb third block of the extent (which is also the segments header). and the L1 BMB block of the initial extent of the index usually contains the blocks reserved for index critical section, pointing to the branch or leaf blocks.
When we insert only one row or few rows into the table the corresponding entries are also inserted into the index. here if we are inserting only one row or few rows into the table, oracle instead of creating a branch block and leaf blocks straight away creates a leaf block that is the fourth block of the extent immediately following the segment header and the L1BMB critical index section contains no details but the status of status of the blocks this L1BMB manages is as follows...
DBA Ranges :
0x01c021e0 Length: 8 Offset: 0
0:Metadata 1:Metadata 2:Metadata 3:FULL 4:unformatted 5:unformatted 6:unformatted 7:unformatted
Blocks reserved for critical index section - Total reserved: 0
0. db:0x0 state:0 1. db:0x0 state:0 2. db:0x0 state:0 3. db:0x0 state:0 4. db:0x0 state:0 5. db:0x0 state:0 6. db:0x0 state:0 7. db:0x0 state:0
is this usual behaviour that oracle simple doesn't create an branch blocks unless the number of rows in the table cross a particular threshold or am i missing anything.
can someone please explain this behaviour...
thanks,
vishnu
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Nov 20 2019 - 08:47:42 CET