Problem: Extra blocks allocated during Initial Index Creation

From: Todd A. Wood <tawood_at_cimteam.cob.cummins.com>
Date: 1996/06/26
Message-ID: <31D13F00.41C6_at_cimteam.cob.cummins.com>#1/1

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
/

example 2:
Initial specifed as 47 Blocks and Oracle allocated 50 blocks mcr_no varchar2(20), work_flow varchar2(25), and seq_no number(2)

create unique index mcr_reviewers_prime
on mcr_reviewers(mcr_no,work_flow,seq_no) PCTFREE 5 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 96256 NEXT 963 MINEXTENTS 1 MAXEXTENTS 121 PCTINCREASE 0)
TABLESPACE MCR_IDX
/

example 3:
Initial specifed as 13 Blocks and Oracle allocated 15 blocks reviewer varchar2(5)

create index mcr_reviewers_idx01 on mcr_reviewers(reviewer) PCTFREE 5 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 26624 NEXT 266 MINEXTENTS 1 MAXEXTENTS 121 PCTINCREASE 0)
TABLESPACE MCR_IDX
/

---

select sum(INITIAL_EXTENT) from user_indexes = 1011712 bytes Tablespace MCR_IDX was created at 1064960 bytes and there is 0% free ???? The difference is my question.

Any help or enlightenment would be appreciated.

-- 
=======================================================================
   CCCCC     Todd A. Wood                tawood_at_cob.cummins.com     
 CC          Senior Systems Analyst      Cummins Engine Company, Inc.  
 CC ummins   Corporate CIM Team          500 Jackson Street, MC 60202  
 CC          Manufacturing Engineering   Columbus, IN  47201           
   CCCCC                                 812-377-4637                  
=======================================================================

Received on Wed Jun 26 1996 - 00:00:00 CEST