Oracle Cloud Infrastructure Documentation

Data Warehouse Workload with Autonomous Database

Autonomous Database configures and optimizes your database for you, based on your workload.

Characteristics of a database with Data Warehouse workload:

  • The default data and temporary tablespaces for the database are configured automatically. Adding, removing, or modifying tablespaces is not allowed. Autonomous Database creates one tablespace or multiple tablespaces automatically depending on the storage size.

  • The database character set is Unicode AL32UTF8. See Choose a Character Set for Autonomous Database for more information.

  • Compression is enabled by default. Autonomous Database uses Hybrid Columnar Compression for all tables by default. You can specify different compression methods for your tables using the compression clause in your CREATE TABLE or ALTER TABLE commands.

  • Oracle Database Result Cache is enabled by default for all SQL statements.

Accessing a database:

  • You do not have direct access to the database node. You can create and drop directories with CREATE DIRECTORY and DROP DIRECTORY, as described in Creating and Managing Directories on Autonomous Database.

    You can use DBMS_CLOUD procedures such as DBMS_CLOUD.DELETE_FILE, DBMS_CLOUD.GET_OBJECT, and DBMS_CLOUD.PUT_OBJECT with files and objects. You do not have direct access to the local file system.

Parallel Execution with Data Warehouse workload:

  • Parallelism is determined by the database service. See See Database Service Names for Autonomous Database for more information. for details for parallelism support for each database service.

  • When you want to disable parallel DML operations in your session, use the following SQL command:

    ALTER SESSION DISABLE PARALLEL DML;

    See VLDB and Partitioning Guide for more information on parallel DML operations.

Parent topic: About Autonomous Database for Experienced Oracle Database Users

Manage DML Performance and Compression for Data Warehouse Workloads

Autonomous Database with Data Warehouse workloads uses Hybrid Columnar Compression for all tables by default. This gives the best compression ratio and optimal performance for direct-path load operations like the loads done using the DBMS_CLOUD package. If you perform DML operations like UPDATE and MERGE on your tables these may cause the compression ratio for the affected rows to decrease leading to larger table sizes. These operations may also perform slower compared to the same operations on an uncompressed table.

For the best compression ratio and optimal performance Oracle recommends using bulk operations like direct-path loads and CREATE TABLE AS SELECT statements. But, if your workload requires frequent DML operations like UPDATE and MERGE on large parts of a table, you can create those tables as uncompressed tables to achieve better DML performance. For example, the following statement creates the table SALES as an uncompressed table: 

CREATE TABLE sales (
    prod_id             NUMBER          NOT NULL,
    cust_id             NUMBER          NOT NULL,
    time_id             DATE            NOT NULL,
    channel_id          NUMBER          NOT NULL,
    promo_id            NUMBER          NOT NULL,
    quantity_sold       NUMBER(10,2)    NOT NULL,
    amount_sold         NUMBER(10,2)    NOT NULL)
NOCOMPRESS;

At any point in time you can use the ALTER TABLE MOVE statement to compress these tables without impacting queries accessing them. For example, the following statement compresses the table SALES using Hybrid Columnar Compression. 

ALTER TABLE sales MOVE COLUMN STORE COMPRESS FOR QUERY HIGH;

Create Staging Tables for Data Warehouse Workloads

Autonomous Database supports staging tables that are optimized for loading data into a data warehouse.

A staging table is a table with the STAGING property set. This applies the following characteristics:

  • Any form of compression is explicitly turned off and disallowed on a staging table for any data load. The command ALTER TABLE COMPRESS is not allowed.

  • Setting the STAGING property on an existing table does not impact the storage of existing data but does impact future data loads.

  • Autonomous Database uses dynamic sampling for statistics for tables with the staging property set, and does not collect statistics on staging tables.

  • Dropping staging tables immediately removes the table, bypassing the recycle bin. Setting the recyclebin initialization parameter to the value ON does not enable the recycle bin.

The characteristics of Autonomous Database partitioned staging tables includes the above, plus the following:

  • Any form of compression is explicitly turned off and disallowed on all of the table's partitions and subpartitions.

  • You cannot change the default attributes of the table to use compress with ALTER TABLE MODIFY DEFAULT ATTRIBUTES.

  • You cannot perform partition maintenance operations that move data and compress the data. For example, the following are not allowed when you try to apply compression: ALTER TABLE with MOVE PARTITION, MERGE PARTITIONS, SPLIT PARTITION, or SPLIT SUBPARTITION.

  • You cannot repartition a table with ALTER TABLE MODIFY PARTITION and specify any resulting partition to be compressed.

Define staging tables when you create a table or by altering an existing table as follows:

  1. Create a table with the STAGING property.

    For example:

    CREATE TABLE staging_table (col1 number, col2 varchar2(100)) FOR STAGING;

CREATE TABLE part_staging_table (col1 number, col2 varchar2(100))
    PARTITION BYRANGE (col1)
          (PARTITION p1 VALUESLESS THAN (100), 
           PARTITION pmax VALUESLESS THAN (MAXVALUE)) FOR STAGING;
  2. Change an existing table to set the STAGING property.

    For example:

    ALTER TABLE staging_table FOR STAGING;
  3. You can verify the STAGING property for a table using one of the following views: USER_TABLES, ALL_TABLES, or DBA_TABLES.

    In these views, the STAGING column indicates the staging table property, a value YES indicates a STAGING table, a value NO is shown for all other tables.

You can alter a table to remove the STAGING property. For example: 

ALTER TABLE staging_table NOT FOR STAGING;

Note the following for altering a table with NOT FOR STAGING:

  • After you alter a table with NOT FOR STAGING, the compression attribute and existing data are not affected and are kept as uncompressed until you explicitly alter the table and specify compression. You can change the table compression and ALTER TABLE COMPRESS is allowed.

  • Altering a table with NOT FOR STAGING does not trigger statistics collection. After you change the table property with NOT FOR STAGING, you can collect statistics, either manually or automatically.

  • After you alter a table with NOT FOR STAGING, when the recycle bin is enabled dropping the table puts the table in the recycle bin.