DDL - Part 1 - Table level attributes

TABLE LEVEL ATTRIBUTES:

1. SET, MULTISET:  SET table does not allow duplicate row and multiset table allows duplicate rows.

In Teradata mode default is SET table and in ANSI mode default is MULTISET table.

We cannot change a SET table to MULTISET and vice versa without dropping and re creating the table. Alter table command cannot do this conversion.

2. DATABLOCKSIZE:  Used to specify the maximum multi-row data blocksize as part of table creation that allows smaller or larger blocks suitable for the processing environment.

DSS(Decision support systems) usually prefer larger blocksizes, while OLTP generally use smaller blocksizes.

DATABLOCKSIZE can be specified in BYTES or KILOBYTES.

• BYTES--> Rounded of to nearest sector .i.e. 512 bytes.

• KILOBYTES--> increments of 1024.
• MINIMUM DATABLOCKSIZE --> 6144 bytes

• MAXIMUM DATABLOCKSIZE --> 65024 bytes

We can alter the DATABLOCKSIZE of the table at any moment using the ALTER statement.

When we alter the DATABLOCKSIZE we have 2 options:

• Change the DATABLOCKSIZE immediately when the ALTER statement is executed OR

• Change the size later as new rows are added to the table(Deferred alteration)

Deferred alteration is the default and the blocksizes will be altered as new rows are added and blocks are updated. Note that default is not to alter the blocksize immediately

ALTER TABLE EDW_RESTORE_TABLES.TEST3, MINIMUM DATABLOCKSIZE ;

Immediate alteration will cause the blocksizes to be modified immediately. This is  requested using the IMMEDIATE option and is generally time extensive.

ALTER TABLE EDW_RESTORE_TABLES.TEST3, MINIMUM DATABLOCKSIZE IMMEDIATE;

3. FREESPACE:  This parameter indicates the cylinder fill factor during table loading.
    

Availability of freespace on the cylinder reduce the amount of effort the system must expend looking for empty space when rows are added or expanded as a result of file maintenance activities.

For Tables with frequent changes we can set this value higher and for tables with less changes we can set this value low.

We can alter the FREESPACE at any moment.

ALTER TABLE EDW_RESTORE_TABLES.TEST3, FREESPACE =40 PERCENT

Example:

CREATE TABLE MY_TEST_TABLES.TEST3,NO FALLBACK,

DATABLOCKSIZE = 65024 BYTES,

FREESPACE = 30 PERCENT

( COLUMN1 CHAR(1));