Conducting tablespace management

Tablespace management is left to DBAs as it is considered a database administration task. DBAs are free to store PPM DMS data (documents contents, indexes, and metadata) on whichever tablespace they deem appropriate. Having dedicated tablespaces for the main elements of PPM Database DMS (content, TEXT indexes) has the following advantages:

• If intense attachments activity leads to using up all available space in a dedicated DMS tablespace, the rest of PPM application will not be impacted (unless it turns out to be a lack of space on the disk storage, and that PPM application tablespaces use the same disk storage).

• DBAs can choose to compress the tablespace storing DMS documents contents. This can result in significant space savings if most attachments are in a format that can be efficiently compressed (such as log files).

• If needed, DBAs can finely tune the performance of tablespaces according to their usage: tablespace storing documents contents should be optimized for heavy I/O operations (documents store/retrieve), while tablespace storing TEXT indexes should be optimized for fast records access.

Default Tablespaces

When using PPM Database DMS, by default, documents contents are stored in the default USER_CLOB PPM tablespace, while the metadata and the TEXT indexes (when created) are stored in default USER_DATA PPM tablespace. Indexes on the metadata tables are stored in the USER_INDEXES PPM tablespace. DBAs can change the tablespaces used by PPM Database DMS, for example, to save DB disk space by compressing documents contents, or by tuning tablespace-level settings that would provide better performance for their specific usage pattern.

When using PPM External Database DMS, there is no specific tablespaces instruction when the tables are created in the external schema. Tables are automatically created if not existing at the beginning of the migration process, when testing connection to the new DMS system.

Tablespaces customizations are supported for both PPM Database DMS and PPM External Database DMS, it is advised that you do such customizations before the migration is started, so that all data is stored in the intended tablespace as migration proceeds.

Changing the Tablespaces of the Documents Contents

Most of the space consumed by PPM (External) Database DMS in the database is used to store the documents contents (the actual attachment files). These contents are stored under their binary form in two BLOB columns:

• KNTA_DOCUMENT_TIP_CONTENTS.DOC_CONTENT, to store the latest version (i.e. tip version) of each file.

• KNTA_DOCUMENT_HISTORY_CONTENTS.DOC_CONTENT, to store all non-tip versions of each file.

If you want to change the tablespace where these contents are stored, move the DOC_CONTENT LOB columns to another tablespace (replace “X” below with either "TIP" or "HISTORY"):

ALTER TABLE KNTA_DOCUMENT_X_CONTENTS MOVE LOB(DOC_CONTENT) STORE AS (TABLESPACE NEW_TABLESPACE_NAME);

When a new version is added to a document, the contents of the old tip version will first be copied to the HISTORY table before the new tip contents are inserted in the TIP table. As a result, for better performance, it is advised that you store both TIP and HISTORY contents in the same tablespace.

It is considered a good practice to have a dedicated tablespace to store documents contents, especially if the size of attachments in PPM is very large (more than 50GB of attachments).

Changing the Tablespace of TEXT Indexes

Managing domain indexes (like the Oracle TEXT CONTEXT indexes) is quite different from managing “standard” Oracle indexes. After all, DOMAIN indexes are just a series of Oracle tables put together, and as such, the standard Oracle INDEX management does not apply to them.

Depending on the type of your attachments, the size of the TEXT index can represent a significant part of the size of the file attachments. The measurements on the R&D Sample Dataset show numbers ranging from 15% to 30% of the size of the attachments contents. However, if you have documents that are mostly in raw text formats (log, txt, etc.), then the size of your TEXT index can in theory exceed 100% of the size of the attachments contents. For this reason, it is a good practice to have a dedicated tablespace to store the TEXT index when using PPM (External) Database DMS.

The tablespace to be used for storing a TEXT index must be specified upon TEXT index creation. Here is an example on how to store an Oracle TEXT index in the XYZ tablespace. Before creating the TEXT index, you have to define a STORAGE parameter, and set the tablespace of all domain tables of the domain index to use the XYZ tablespace:

begin
   ctx_ddl.create_preference('MY_XYZ_TEXT_STORE', 'BASIC_STORAGE');
   ctx_ddl.set_attribute('MY_XYZ_TEXT_STORE', 'I_TABLE_CLAUSE', 'tablespace XYZ');
   ctx_ddl.set_attribute('MY_XYZ_TEXT_STORE', 'K_TABLE_CLAUSE', 'tablespace XYZ');
   ctx_ddl.set_attribute('MY_XYZ_TEXT_STORE', 'R_TABLE_CLAUSE', 'tablespace XYZ');
   ctx_ddl.set_attribute('MY_XYZ_TEXT_STORE', 'N_TABLE_CLAUSE', 'tablespace XYZ');
   ctx_ddl.set_attribute('MY_XYZ_TEXT_STORE', 'I_INDEX_CLAUSE', 'tablespace XYZ COMPRESS 2');
   ctx_ddl.set_attribute('MY_XYZ_TEXT_STORE', 'P_TABLE_CLAUSE', 'tablespace XYZ');
end;
/

You can then create the TEXT index and specify the storage to use as part of the parameters of the index creation:

EXECUTE IMMEDIATE 'CREATE INDEX DMS_TIP_META_IDX
    ON KNTA_DOCUMENT_TIP_CONTENTS(FULL_TEXT_META)
    INDEXTYPE IS CTXSYS.CONTEXT
    parameters (''LEXER PPM_DMS_LEXER DATASTORE PPM_DMS_TIP_DS STORAGE MY_XYZ_TEXT_STORE SYNC (every sysdate+1/144)'')';

For more information about available parameters (including tablespace management) when creating an Oracle TEXT index, see the Oracle documentation.