Skip to main content

Oracle configurations

Use parallel hint

Table materialization supports specifying the number of parallel executions as shown below

-- Create a dbt model using 4 parallel executions
{{config(materialized='table', parallel=4}}
SELECT c.cust_id, c.cust_first_name, c.cust_last_name
from {{ source('sh_database', 'customers') }} c

Use table_compression_clause

Table materialization supports different compression clauses as shown below

Advanced Row Compression

With Advanced compression enabled, Oracle Database maintains compression during all types of data manipulation operations, including conventional DML such as INSERT and UPDATE. ROW STORE COMPRESS ADVANCED is recommended in OLTP systems.

-- Advanced Row compression
{{config(materialized='table', table_compression_clause='ROW STORE COMPRESS ADVANCED')}}
SELECT c.cust_id, c.cust_first_name, c.cust_last_name
from {{ source('sh_database', 'customers') }} c

Hybrid Columnar Compression

Querying

COLUMN STORE COMPRESS FOR QUERY is useful in data warehouse environments. Valid values are HIGH or LOW, with HIGH providing a higher compression ratio. The default is HIGH

{{config(materialized='table', table_compression_clause='COLUMN STORE COMPRESS FOR QUERY LOW')}}
SELECT c.cust_id, c.cust_first_name, c.cust_last_name
from {{ source('sh_database', 'customers') }} c

or

{{config(materialized='table', table_compression_clause='COLUMN STORE COMPRESS FOR QUERY HIGH')}}
SELECT c.cust_id, c.cust_first_name, c.cust_last_name
from {{ source('sh_database', 'customers') }} c

Archival

COLUMN STORE COMPRESS FOR ARCHIVE supports a higher compression ratio than COLUMN STORE COMPRESS FOR QUERY and is useful for archival. Valid values are HIGH or LOW with HIGH providing the highest compression ratio. The default is LOW

{{config(materialized='table', table_compression_clause='COLUMN STORE COMPRESS FOR ARCHIVE LOW')}}
SELECT c.cust_id, c.cust_first_name, c.cust_last_name
from {{ source('sh_database', 'customers') }} c

or

{{config(materialized='table', table_compression_clause='COLUMN STORE COMPRESS FOR ARCHIVE HIGH')}}
SELECT c.cust_id, c.cust_first_name, c.cust_last_name
from {{ source('sh_database', 'customers') }} c

Partitioning

Table and Incremental materialization configuration supports adding a partitioning clause:

{
config(
materialized='incremental',
unique_key='group_id',
parallel=4,
partition_config={"clause": "PARTITION BY HASH(PROD_NAME) PARTITIONS 4"},
table_compression_clause='COLUMN STORE COMPRESS FOR QUERY LOW')
}}
SELECT *
FROM {{ source('sh_database', 'sales') }}

Session info in v$session

Custom session information can be supplied under session_info in profile.yml

dbt_test:
target: dev
outputs:
dev:
type: oracle
user: "{{ env_var('DBT_ORACLE_USER') }}"
pass: "{{ env_var('DBT_ORACLE_PASSWORD') }}"
database: "{{ env_var('DBT_ORACLE_DATABASE') }}"
tns_name: "{{ env_var('DBT_ORACLE_TNS_NAME') }}"
schema: "{{ env_var('DBT_ORACLE_SCHEMA') }}"
threads: 4
session_info:
action: "dbt run"
client_identifier: "dbt-unique-client-uuid"
client_info: "dbt Python3.9 thin driver"
module: "dbt-oracle-1.8.x"

This helps to track dbt sessions in the Database view V$SESSION

0