Partition Clause and table storage (Model Object)

Applies to
Platform: Oracle

Six Oracle model-object properties set how a generated table is stored and how its loader gathers statistics. They are Partition Clause, Table Compression, Tablespace Table, Tablespace Index, Create Table Statistics and Parallel Degree of Statistics Calculation. Set them when a table needs partitioning, compression, its own tablespaces or other statistics settings; the CREATE TABLE statement and the loader then carry them.

Supported objects

Set the properties in Edit Model Object, Technology group. They are offered on these model object types:

Stage: Stage
Data Store: Store Entity, Store Composite
Data Vault: every Raw Vault and Business Vault type, including PIT, RawVault Composite, BusinessVault Composite and Vault Out
Data Mart: Mart Composite, Mart Dimension, Mart Fact, Mart Out

Business Driving Key Link offers all of them except Partition Clause and Table Compression.

Properties

Partition Clause

Partition Clause (OraclePartitionClause) is a complete Oracle partition clause, empty by default. The text is added to the CREATE TABLE statement after the column list and the storage options, for example PARTITION BY HASH (HUB_HK) PARTITIONS 4. A model object with Implementation Type Virtual has no table, so the clause is ignored there. See Setting ignored for a Virtual model object (Business Rule). Since Generator 2.4.1, bitmap indexes on a table with a Partition Clause are created as local indexes. Local custom indexes are described in Add an Index.

Table Compression

Table Compression (TableCompression) adds an Oracle compression clause to the CREATE TABLE statement, written as the value reads. The values are:

None (default): no clause.
COMPRESS, ROW STORE COMPRESS ADVANCED
COMPRESS FOR QUERY LOW, COMPRESS FOR QUERY HIGH, COMPRESS FOR ARCHIVE LOW, COMPRESS FOR ARCHIVE HIGH
COLUMN STORE COMPRESS FOR QUERY LOW, COLUMN STORE COMPRESS FOR QUERY HIGH, COLUMN STORE COMPRESS FOR ARCHIVE LOW, COLUMN STORE COMPRESS FOR ARCHIVE HIGH

Tablespace Table

Tablespace Table (TableTablespace) adds TABLESPACE <value> to the CREATE TABLE statement. Empty (default) creates the table in the default tablespace of the layer user. The layer user needs quota on the tablespace you name.

Tablespace Index

Tablespace Index (IndexTablespace) adds TABLESPACE <value> to the indexes of the table: the primary-key and unique-key indexes and the default and custom indexes. Empty (default) uses the default tablespace of the layer user. Without quota on the tablespace, the deployment of the table fails with ORA-01950.

Create Table Statistics

Create Table Statistics (CreateTableStatistics) is a Boolean, true by default. With true the loader gathers table statistics after each load with dbms_stats.gather_table_stats. With false the loader gathers none, and the table keeps the statistics Oracle collected otherwise, for example at deployment.

Parallel Degree of Statistics Calculation

Parallel Degree of Statistics Calculation (StatisticParallelDegree) is a whole number from 1, empty by default. The loader sets it as the table preference DEGREE before it gathers statistics, so it applies only with Create Table Statistics true. Other values stop the generation with the validation error Model object has non numeric degree of parallelism for calculation of statistics configured or Model object has degree of parallelism smaller than 1 for calculation of statistics configured.

Example

A Hub has Partition Clause PARTITION BY HASH (HUB_HK) PARTITIONS 4, Table Compression COMPRESS, Tablespace Table USERS, Tablespace Index USERS_IDX and Parallel Degree of Statistics Calculation 4. The Raw Vault script creates its table like this, with <schema> for the Raw Vault schema:

CREATE TABLE <schema>.RDV_HUB_LOAD_CUSTOMER_HUB (
     BG_LOADTIMESTAMP TIMESTAMP NOT NULL
    ,HUB_HK RAW(20) NOT NULL
    ,BG_SOURCESYSTEM VARCHAR2(255)
    ,CUSTOMER_ID NUMBER NOT NULL
)
TABLESPACE USERS
COMPRESS
PARTITION BY HASH (HUB_HK) PARTITIONS 4
;

Its primary-key and unique-key indexes are created with TABLESPACE USERS_IDX. The loader ends with:

dbms_stats.set_table_prefs(ownname => '<schema>',
    tabname => 'RDV_HUB_LOAD_CUSTOMER_HUB',
    pname => 'DEGREE', pvalue => '4');
dbms_stats.gather_table_stats(ownname => '<schema>',
    tabname => 'RDV_HUB_LOAD_CUSTOMER_HUB', no_invalidate => FALSE);

After deployment and one load, the table has four hash partitions in USERS with basic compression, and both indexes are in USERS_IDX.

Changing the values on deployed tables

Partition Clause, Table Compression and the tablespaces take effect when the table is created. With the default Deployment Options, the next deployment recreates the table empty with the new settings. Without Generate Drop Table Statements, an existing table keeps its storage. The statistics properties change only the loader and take effect with the next deployment.