Re: Storage Clause in Create Table Statement.

In article <39AE6F35.118D8FF8_at_interaccess.nl>,   frankbo_at_interaccess.nl wrote:
> similar thread already answered, but here it goes:
> 1) there's no storage clause on the table, so your tablespace
> defaults will be used - check these!
> 2) pctincrease=0 - as someone once said, picture yourself
> building a house with bricks all different in size, whould you
> like that?
> 3) initial=(align with block size, or 64k boundary), next same.
> If you have an estimate for sizes, you could try a best estimate
> initial, and keep next about 1/10th of that (but aligned: e.g.
> initial 5M, next 512k).
> If you have no clue, consider that fact the table might be empty,
> or just contain a few records; initial 16k, next 512k.
> Rationale: you'll need at least 2 blocks (defaults 8k @ 4k blocksize),
> allow some slack. Next 512k: don't waste time allocating new blocks.
>
> hth,
> Frank
>
> Troy Simpson wrote:
> >
> > I'm trying to understand how to allocate space to a table segment
 in the
> > Storage Clause of the Create Table Statement. I have read what
 appears
> > to be conflicting information. If I know before creating the table
 that
> > I have 8 Megs of data to add to the table, should I set the Initial
> > Extent to equal 8 Megs? Or, should I set all extent to be the same
> > size? I have also read that the number blocks per extent may effect
> > performance due to the operating system's I/O buffer size and the
> > parameter db_file_multiblock_read_count in the init.ora file.
> >
> > Here is a sample of a create table statment that I'm working with.
> >
> > Create Table Employee
> > (
> > id Number(11) NOT NULL,
> > fname VarChar(30),
> > lname VarChar(30),
> > CONSTRAINT Employee_id_pk PRIMARY KEY ( id ) USING INDEX
 TABLESPACE
> > Data_Indexes
> > )
> > Storage
> > (
> > Initial 10240
> > Next 10240
> > MinExtents 1
> > MaxExtents unlimited
> > PctIncrease 50
> > )
> > Tablespace Data_Tables
> >
> > Any other comments would also be appreciated.
> >
> > Thanks,
> > Troy
> >

Troy,

The proper size for your extents depends on your space management policies. But as a general rule I believe 10240 bytes is too small and if your Oracle block size is not 2K it is a bad size to choose to begin with. Your minimum extent size should proably match your db_file_multiblock_read_count parameter size so that the extent is at least as large as the amount of data that can be read in one IO during full table scans or index builds.

With a table that you expect to be about 8M after initial load I would think that using an extent size of 512K to 2M for initial and next with pctincrease = 0 would be appropriate.

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.