Re: Index with no branch blocks

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