Re: Problem: Extra blocks allocated during Initial Index Creation

Todd A. Wood wrote:
>
> I am having trouble figuring out why Oracle is allocating extra blocks
> on some of my indexes.
>
> I created a total of 18 indexes on 11 tables. Some of the indexes when
> created allocated an extra 2-3 block in their initial extent. While
> the other indexes allocated the specified initial extent. All initial
> extents are multiples of the db_block_size. I have three examples
> below. All the index create statements have identical syntax.
>
> I am running: Oracle7 Server Release 7.2.3.0.0
> with the 64-bit option and distributed option
> on Digital Unix V3.2C
> with a DB_BLOCK_SIZE of 2048
>
> I created each index one at a time and checked the tablespace free
> space after each create to find out what Oracle had extented.
>
> example 1:
> Initial specifed as 12 Blocks and Oracle allocated 15 blocks
> mcr_no varchar2(20) and team_no varchar2(15)
>
> create unique index mcr_groups_prime on mcr_groups(mcr_no,team_no)
> PCTFREE 5 INITRANS 2 MAXTRANS 255
> STORAGE(INITIAL 24576 NEXT 246 MINEXTENTS 1 MAXEXTENTS 121 PCTINCREASE
> 0)
> TABLESPACE MCR_IDX
> /
> <-snip->

I can’t remember all the details but if I remember right, Oracle does some rounding up, adds a few extra blocks, if the number of blocks you specified are not what it likes to use for boundaries. A fellow DBAer did the leg work and figured it out once. Where only talking about a couple of blocks here though, it’s not like you'll need to buy another disk drive or anything.

brian.maclean_at_teldta.com Received on Fri Jun 28 1996 - 00:00:00 CEST