>>-SET--+-TABLE_TYPE--TO--+-STANDARD----+----+-----------------><
        |                 +-OPERATIONAL-+    |
        |                 +-RAW---------+    |
        |                 +-STATIC------+    |
        |                 +-IMMUTABLE---+    |
        |                 '-MUTABLE-----'    |
        '-TEMP TABLE_TYPE--TO--+-SCRATCH---+-'
                               +-DEFAULT---+
                               +-IMMUTABLE-+
                               '-MUTABLE---'
 

If CREATE TABLE specifies no table type, the default table type is STANDARD. The SET TABLE_TYPE statement can change this default for subsequent CREATE TABLE statements in the current session. Similarly, you can use the SET TEMP TABLE_TYPE to change the default temporary table type.

These statements have no effect on tables for which you explicitly specify a table type in the statement that creates the new table or temporary table.

Because the CREATE Temporary TABLE statement requires an explicit table type, the SET TEMP TABLE_TYPE statement only affects SQL operations that create a temporary implicitly, such as in executing join operations, SELECT statements with the GROUP BY or ORDER BY clause, and index builds.

The effect of SET Default Table Type persists until the end of the session, or until you issue another SET Default Table Type statement to specify a new default table type.

The SET TABLE_TYPE TO STANDARD statement and the SET TEMP TABLE_TYPE TO DEFAULT statements restore the default behavior.

The DBA can use the keyword MUTABLE or IMMUTABLE to enable (with MUTABLE) or to prevent (with IMMUTABLE) changes by users to the default table type during a user session. The DBA typically sets the default table type in a sysdbopen procedure and specifies IMMUTABLE if it should not be changed during a user session.

Although the scope of these statements is the current session, they can be used to have a database-wide effect. The next example shows how to do this by using SPL routines to establish a default table type at connect time:

CREATE PROCEDURE public.sysdbopen()
   SET TABLE_TYPE TO RAW;
   SET TEMP TABLE_TYPE TO SCRATCH;
   SET TABLE_SPACE TO other_tables;
...
END PROCEDURE;

CREATE PROCEDURE helene.sysdbopen()
   EXECUTE PROCEDURE public.sysdbopen();
   SET ROLE marketing;
   SET TABLE_SPACE TO marketing_dbslice;
END PROCEDURE;

Related statements: CREATE TABLE , CREATE TEMP TABLE , SET ALL_MUTABLES, SET Default Table Space .

For more information on table types that can be specified in the CREATE TABLE statement, see CREATE TABLE . For more information about temporary tables, see CREATE Temporary TABLE .