Oracle Database Storage Parameters in the ABAP Dictionary

Use

The database utility is the interface between the ABAP Dictionary and the relational database underlying the SAP System. You can use it to create, convert, and delete ABAP Dictionary objects, as well as database table, database views, and other SAP objects. You can do this online or in the background.

In a conversion, the definition of a table in the database is adapted to its changed definition in the ABAP Dictionary. You can also implement various analysis options, for example, to display table and index definitions, or check the consistency of objects.

For some SAP objects, particularly transparent tables, you can set user-specific database parameters, such as INITIAL EXTENT, NEXT EXTENT, MINIMUM EXTENTS, MAXIMUM EXTENTS, TABLESPACE, FREELIST GROUPS, FREELISTS, PCT FREE, PCT USED, INDEX ORGANIZED, PARTITION, COLUMN LIST. You can also set flags to determine which parameters you want to apply the next time a table is created (deleted and created, converted) and which ones you want to take effect immediately. However, you cannot change all the values immediately. You can only change MAXEXTENTS, NEXT, PCTFREE, and PCTUSED immediately (these values are valid if a new storage area is requested for the object).

Prerequisites

To use the database utility, you need an authorization for authorization object S_DDIC_OBJ, such as S_DDIC_ALL. Make sure that the authorizations in your system are set so that only the database administrator is authorized to configure the database parameters. Check the settings and change them if necessary.

Features

Note the following parameters:

·        INDEX ORGANIZED

This parameter lets you build a table in the same way as an index (that is, using a b*tree), so saving space.

The advantages of index-organized tables are:

Ў        Less storage, since data only stored in b*tree instead of in table and index

Ў        Rowid not stored in index entry of b*tree

Ў        Faster key-based access to table data

The disadvantages of index-organized tables are:

Ў        UNIQUE constraints not allowed

Ў        Cannot be stored in a cluster

Ў        Cannot contain LONG columns (but LOB columns are possible)

·        PARTITION BY

This allows you to partition tables by using a range expression.

The advantages of partitioned tables are:

Ў        Logical attributes (such as table or indexed columns, constraints) are same in all partitions

Ў        Physical attributes (tablespace, storage parameters) might differ in the partitions

Ў        Data of partitioned objects can be handled in the same way as unpartitioned tables

Ў        Partitions of a table can be separately exported, imported, dropped, set offline, backed up, and so on

Ў        Queries can be performed only on specific partitions

The disadvantages of partitioned tables are:

Ў        Bitmap indexes on partitioned tables can only be local

Ў        Rule-based optimizer is not available for partitions

For more information on the other parameters, see the Oracle documentation.

Activities

For more information on how to call the database utility and set storage parameters, see:

·        Database Utility

·        Storage Parameters