your explanation clears things now.
I have created in an tablespace with autoallocate, so 64k initial extent
size - 8 blocks.
Thanks,
vishnu
On Wed, Nov 20, 2019 at 4:56 PM Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
wrote:
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
