Working with Tablespaces for Work Tables and Work Table Indexes

To minimize disk contention and logging, you can create a tablespace with NoLogging for the work tables and indexes and store the data files for the tablespace on a separate physical disk. For example, consider the following command, which creates the HyperionWORK tablespace with NoLogging:

CREATE TABLESPACE HyperionWORK DATAFILE 
 ‘H:\ORACLE\ORADATA\HyperionWORK.ORA ‘ SIZE 5120M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
NOLOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
BLOCKSIZE 8Kb
SEGMENT SPACE MANAGEMENT AUTO;

Because work tables are created and dropped during data processing, creating a tablespace without logging for work tables and work table indexes can improve performance. After a tablespace without logging is created, Oracle Work TableSpaceName and Oracle Work Table Index TableSpaceName must be changed to the new tablespace name.

  To modify the Oracle Work TablespaceName and Oracle Work Table Index TableSpaceName configuration settings:

  1. Launch Workbench, and log on to the FDM application.

  2. Select Tools > Configuration Settings.

  3. Select Options > Oracle Work TableSpaceName.

  4. In Name, enter the name of the tablespace, and click Save.

  5. Select Options > Oracle Work Table Index TableSpaceName.

  6. In Name, enter the name you entered in step 4, and click Save.

  7. Select Options > Oracle Work Table Bitmap Index Switch, and set the value to Off for Oracle 10g and Oracle 11g.

  8. Click Save.

  9. Click Close.

All work tables and indexes that are created and dropped during data processing are now located in the new tablespace.

Note:

You can separate work tables and indexes into their own tablespaces, but no significant increase in performance can be expected.