Object Creation parameters 3

[prosenjit]

<<
CREATE TABLE CC_ACCOUNTS (
ACCOUNTNO NUMBER (20) NOT NULL,
ACCOUNTDESC VARCHAR2 (100),
FIRSTNAME VARCHAR2 (50) NOT NULL,
MIDDLENAME VARCHAR2 (50),
LASTNAME VARCHAR2 (50),
ADDRESS1 VARCHAR2 (100) NOT NULL,
CITYID NUMBER (20) NOT NULL,
EMAILID VARCHAR2 (40),
FAXNO VARCHAR2 (20),
CONSTRAINT PK_ACCOUNT
PRIMARY KEY ( ACCOUNTNO )
USING INDEX
TABLESPACE USR PCTFREE 10
STORAGE ( INITIAL 1000K NEXT 1000K PCTINCREASE 1 ))
TABLESPACE USR
PCTUSED 40
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 1024000
NEXT 1024000
PCTINCREASE 1
MINEXTENTS 1
MAXEXTENTS 1500
FREELISTS 1 FREELIST GROUPS 1 )
NOCACHE;
>>

We all generally create a table or other database objects like index by specifying the column names and the datatypoe alone rarely specifying the storage and other parameters. Can any expert tell me the meaning of these parameters and a rule to calculate these values.

 

[MikeJones]

As far as I'm aware they mean the following...

PCTUSED this is the % amount the DB writer will fill a block before it creates another one. How high you want this depends on how much information is present at insert. Basically the less of the record you insert the higher this needs to be.

INITRANS Number of segments to assign at object creation time. We alsways have this as 1. A segment is part of a tablespace. When a segment fills up Oracle assigns another one.
MAXTRANS Max number of segments a object can fill up. if you have an table taking up 255 segments and you insert a load of records that causes it to need another, Boom, your insert falls over.

STORAGE This is just a keyword encompassing the rest

INITIAL size of the first segment to create in bytes in your example 1000K links up with initrans etc.

NEXT base size of future extents in bytes. So you say I want my first segment to be this big and all future ones to be this big

PCTINCREASE Each time you get a new segment this specifies the percentage increase in the segment. so in your example the second segment would be 1010K, the third 1020.1 (10% bigger than 1010k) etc.
I've no idea about FREELISTS and NOCACHE I've never seen them on Table creates.

As for advice I'd always specify your INITIAL and NEXT the same unless your gonna build your tabvle in one big hit and then insert more records more slowly.

I'd also have the PCTINCREASE as zero so that you create your segments in uniform sizes, otherwise your tablespaces are gonna get real fragmented.

 

[karluk]

FREELISTS - This parameter has to do with the way data is inserted into a table. Every data segment maintains at least one list of blocks with available space. When a row is inserted the freelist is searched for an available block. The FREELISTS parameter allows one to create more than one freelist for a table. This can be useful for tables in an OLTP environment that have a lot of insert activity. Extra free lists make it less likely that one transaction will have to wait for a freelist to become available.

PCTFREE, PCTUSED - These two parameters control how data blocks are added to and removed from the freelist(s). The defaults are PCTFREE=10 and PCTUSED=40. With these values, a block remains on the freelist until it is more than 90% full - i.e. the available space is less than PCTFREE. At this point it is removed from the freelist and is not put back on it until it is less than 40% full (i.e. the amount of used space is less than PCTUSED). Adjusting these values can use space much more efficiently, and consequently improve performance, especially on queries that do full table scans. For example, if a table is rarely updated you could consider setting PCTUSED higher than the default.

STORAGE, INITIAL, NEXT, PCTINCREASE - Mike has a good explanation of these parameters.

INITRANS, MAXTRANS - These two parameters control how space is allocated in a block header. Two or more transactions could easily try to update a block simultaneously (for example, each might be updating a different row, both of which are stored on the same block.) Oracle needs space in the block header for each transaction. If the header space isn't already allocated Oracle will try to allocate it from the available free space in the block. Setting INITRANS > 1 simply does this allocation when the block is first created, rather than dynamically during the transaction. This wastes space if simultaneous transactions are unlikely, but can improve performance in a busy OLTP environment. MAXTRANS limits the number of simultaneous updates within the block. The default of 255 is usually more than adequate.

MINEXTENTS, MAXEXTENTS - MINEXTENTS is the number of extents allocated at object creation time. The main purpose of setting it higher than 1 is to preallocate space for objects that aren't filled immediately. A good example is rollback segments. MAXEXTENTS limits the total amount of extent, and hence the total space, that can be allocated to the object.

CACHE, NOCACHE - These parameters affect where the object's data blocks are placed on the most recently used list of the db buffer cache after they are read from disk during a table scan. NOCACHE is the default and means that the blocks are placed at the end of the LRU list. As a result the blocks will be aged out very quickly as other blocks are read in. This is normally a good choice - the average block read by a full table scan isn't likely to be needed again very quickly. CACHE means that the blocks are placed at the beginning of the LRU list. This keeps the blocks in the db buffer cache longer before being aged out. CACHE would be a good choice for small lookup tables that are read frequently.