Re: Index with no branch blocks

Thanks Jonathan for your timely response.

Thanks,
Vishnu

On Wed, Nov 20, 2019 at 5:15 PM Vishnu Potukanuma < vishnupotukanuma_at_gmail.com> wrote:

> 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_at_jlcomp.demon.co.uk> 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_at_freelists.org <oracle-l-bounce_at_freelists.org> on
>> behalf of Vishnu Potukanuma <vishnupotukanuma_at_gmail.com>
>> Sent: 20 November 2019 07:47
>> To: oracle-l_at_freelists.org
>> 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
>>
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>>

--
http://www.freelists.org/webpage/oracle-l