Improved VLDB Support in Oracle Database 10g

Oracle 10g includes improved support for Very Large Databases (VLDBs) including:

• Bigfile Tablespaces
• Temporary Tablespace Groups
• Skip Unusable Indexes
• Hash-Partitioned Global Indexes

Bigfile Tablespaces

Bigfile tablespaces are tablespaces with a single large datafile. In contrast normal (smallfile) tablespaces can have several datafiles, but each is limited in size. The benefits of bigfile tablespaces are:

• An Oracle database can have a maximum of 64,000 datafiles which limits its total capacity. By allowing tablespaces to have a single large datafile the total capacity of the database is increased. A bigfile tablespace with 8K and 32K blocks can contain 32 terabyte and 128 terabyte datafiles respectively.
• Using fewer larger datafiles allows the DB_FILES and MAXDATAFILES parameters to be reduced, saving SGA and controlfile space.
• The ALTER TABLESPACE syntax has been updated to allow operations at the tablespace level, rather than datafile level.

Typically bigfile tablespaces must be locally managed with automatic segment-space management. Exceptions to this rule include temporary tablespaces, the SYSTEM tablespace and locally managed undo tablespaces which are all allowed to have manual segment-space management.

Bigfile tablespaces are create using the CREATE BIGFILE TABLESPACE command. The first two of the following statements have the same affect as the EXTENT MANAGEMENT LOCAL and SEGMENT SPACE MANAGEMENT AUTO clauses are the default actions, while the last statement would error.

-- Valid statement.
CREATE BIGFILE TABLESPACE mybigtbs 
  DATAFILE '/u01/oracle/MYSID/mybigtbs01.dbf' SIZE 100G;

-- Valid statement.
CREATE BIGFILE TABLESPACE mybigtbs 
  DATAFILE '/u01/oracle/MYSID/mybigtbs01.dbf' SIZE 100G
  EXTENT MANAGEMENT LOCAL
  SEGMENT SPACE MANAGEMENT AUTO;
  
-- invalid statement.
CREATE BIGFILE TABLESPACE mybigtbs 
  DATAFILE '/u01/oracle/MYSID/mybigtbs01.dbf' SIZE 100G
  EXTENT MANAGEMENT DICTIONARY
  SEGMENT SPACE MANAGEMENT MANUAL;

The size of the file can be specified in kilobytes (K), megabytes (M), gigabytes (G), or terabytes (T).

The default tablespace type is SMALLFILE unless specified otherwise in the CREATE DATABASE command. Once the database is created the ALTER DATABASE command can be used to dynamically alter this setting.

CREATE DATABASE mydb
  USER SYS IDENTIFIED BY password
  USER SYSTEM IDENTIFIED BY password
  SET DEFAULT BIGFILE TABLESPACE
  UNDO TABLESPACE undotbs
  DEFAULT TEMPORARY TABLESPACE temp;

ALTER DATABASE SET DEFAULT BIGFILE TABLESPACE;
ALTER DATABASE SET DEFAULT SMALLFILE TABLESPACE;

When the default tablespace type is set to bigfile the BIGFILE keyword is no longer needed in the CREATE TABLESPACE statement, but creation of of a smallfile tablespace requires the SMALLFILE keyword.

The ALTER TABLESPACE command can be used to modify the size and autoextend functionality of bigfile tablespaces.

ALTER TABLESPACE mybigtbs RESIZE 100G;
ALTER TABLESPACE mybigtbs AUTOEXTEND ON NEXT 10G;

A BIGFILE column (YES or NO) has been added to the DBA_TABLESPACES, USER_TABLESPACES and V$TABLESPACE views to indicate the tablespace type.

Some additional factors to consider before using bigfile tablespaces include:

• Bigfile tablespaces should be striped so that parallel operations are not adversely affected. Oracle expect bigfile tablespace to be used with Automatic Storage Management (ASM) or other logical volume managers that support striping or RAID.
• Bigfile tablespaces should not be used on platforms with filesize restrictions.

Temporary Tablespace Groups

Tablespace groups allow users to use more than one tablespace to store temporary segments. The tablespace group is created implicitly when the first tablespace is assigned to it:

-- Create group by adding existing tablespace. 
ALTER TABLESPACE temp TABLESPACE GROUP temp_ts_group;

-- Add a new tablespace to the group.
CREATE TEMPORARY TABLESPACE temp2
  TEMPFILE '/u01/app/oracle/oradata/DB10G/temp201.dbf' SIZE 20M
  TABLESPACE GROUP temp_ts_group;

The tablespaces assigned to a group can be viewed using:

SELECT * FROM dba_tablespace_groups;

GROUP_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TEMP_TS_GROUP                  TEMP
TEMP_TS_GROUP                  TEMP2

2 rows selected.

Once the group is created it can be assigned just like a tablespace to a user or as the default temporary tablespace:

-- Assign group as the temporary tablespace for a user.  
ALTER USER scott TEMPORARY TABLESPACE temp_ts_group;

-- Assign group as the default temporary tablespace.  
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_ts_group;

A tablespace can be removed from a group using:

ALTER TABLESPACE temp2 TABLESPACE GROUP '';

SELECT * FROM dba_tablespace_groups;

GROUP_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TEMP_TS_GROUP                  TEMP

1 row selected.

There is no theoretical maximum limit to the number of tablespaces in a tablespace group, but it must contain at least one. The group is implicitly dropped when the last member is removed. The last member of a group cannot be removed if the group is still assigned as the default temporary tablespace. In this example the following must be done to remove the last member from the group.

-- Switch from the group to a specific tablespace.
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

-- Remove the tablespace from the group.
ALTER TABLESPACE temp TABLESPACE GROUP '';

-- Check the group has gone.
SELECT * FROM dba_tablespace_groups;

no rows selected

Tablespace groups share the same namespace as tablespaces so a group and tablespace cannot share the same name.

Skip Unusable Indexes

In Oracle 10g the SKIP_UNUSABLE_INDEXES parameter can be set at instance level as well as session level and now defaults to TRUE. When set to TRUE Oracle will not attempt to use or report errors when an index is marked as unusable.

The parameter can be reset using the ALTER SYSTEM and ALTER SESSION as shown below.

ALTER SYSTEM SET SKIP_UNUSABLE_INDEXES = TRUE;
ALTER SYSTEM SET SKIP_UNUSABLE_INDEXES = FALSE;

ALTER SESSION SET SKIP_UNUSABLE_INDEXES = TRUE;
ALTER SESSION SET SKIP_UNUSABLE_INDEXES = FALSE;

This setting does not affect how indexes that support unique constraints are used as ignoring them may lead to constraint violation.

Hash-Partitioned Global Indexes

Global indexes can now be hash partitioned using the syntax shown below.

CREATE TABLE mytable (
  column_1  NUMBER(10),
  column_2  VARCHAR2(10),
  column_3  NUMBER(10),
  column_4  VARCHAR2(50)
);

CREATE INDEX myindex ON mytable (column_1, column_2, column_3) GLOBAL
  PARTITION BY HASH (column_1, column_2)
    (PARTITION myindex_part_1 TABLESPACE users,
     PARTITION myindex_part_2 TABLESPACE users,
     PARTITION myindex_part_3 TABLESPACE users,
     PARTITION myindex_part_4 TABLESPACE users);

Maintenance of hash partitioned global indexes can be performed using the following statements.

-- Add a new partition.
ALTER INDEX myindex ADD PARTITION mytable_part_5;

-- Reduce the number of partitions by 1.
ALTER INDEX myindex COALESCE PARTITION;

-- Rebuild partition.
ALTER INDEX myindex REBUILD PARTITION mytable_part_1 TABLESPACE users;

-- Rename partition.
ALTER INDEX myindex RENAME PARTITION mytable_part_1 TO mytable_part_a;

-- Clean up.
DROP INDEX myindex;
DROP TABLE mytable;

For further information see: