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
orALTER 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
andDROP DIRECTORY
, as described in Creating and Managing Directories on Autonomous Database.You can use
DBMS_CLOUD
procedures such asDBMS_CLOUD.DELETE_FILE
,DBMS_CLOUD.GET_OBJECT
, andDBMS_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.
- Manage DML Performance and Compression for Data Warehouse Workloads
- Create Staging Tables for Data Warehouse Workloads
Autonomous Database supports staging tables that are optimized for loading data into a data warehouse.
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;
Parent topic: Data Warehouse Workload with Autonomous Database
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 valueON
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
withMOVE PARTITION
,MERGE PARTITIONS
,SPLIT PARTITION
, orSPLIT 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:
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 andALTER TABLE COMPRESS
is allowed. -
Altering a table with
NOT FOR STAGING
does not trigger statistics collection. After you change the table property withNOT 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.
Parent topic: Data Warehouse Workload with Autonomous Database