Which "fundamentals" suggest ...
Arguably one could argue that fundamentals suggest you don't create something
you don't need until you really need it.
Oracle b-tree indexes have a root block that is always in the same place
relative to the start of the segment. If a table is so small that all its index
entries could fit in a single block then the root block will be structured as a
leaf block, if there are too many index entries for a single leaf block the
root block will be structured as a branch block holding pointers to leaf blocks.
Regards
Jonathan Lewis
P.S. Your comments about L1BMB etc. are correct in the case of segments with
small initial extents, which suggests you're looking at object in a tablespace
with system-managed extents. If you create an object in a locally managed
tablespace with (e.g.) 1MB uniform extents there will be several L1 bitmap
blocks before the L2 bitmap block.
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Vishnu Potukanuma <vishnupotukanuma@xxxxxxxxx>
Sent: 20 November 2019 07:47
To: oracle-l@xxxxxxxxxxxxx
Subject: Index with no branch blocks
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
--
//www.freelists.org/webpage/oracle-l
