Table Definition

As you probably have thousands of tables in your current Oracle data warehouse, you should automate the task of converting the Table Definitions (DDL) of Oracle tables as much as possible. The recommended approach is to use the Oracle to Exasol migration script from the database migration script repository on GitHub.

This script generates the Exasol DDLs and import statements for the initial load by connecting to the Oracle database and reading the Oracle system tables. It also maps the Oracle data types to the Exasol data types; see the Data Type Mapping section for more information.

To use the script, you need direct network connectivity from your Exasol database to your Oracle instance.

DDL Migration

After you have the prerequisites in place, go to the Exasol Database Migration repository on GitHub and download the Oracle to Exasol script.

Next, create the script ORACLE_TO_EXASOL in a schema called DATABASE_MIGRATION in your Exasol database. When the script has been created, you can execute it in Exasol. The script will generate the corresponding schema and table DDL commands, including the appropriate data type mapping, and the IMPORT commands to import the data from Oracle to Exasol. The following examples show you the call of the script and the output it generates.

execute script database_migration.oracle_to_exasol( 
   'ORACLE_OCI',  -- connection name 
   true,          -- case insensitivity flag 
   'C##DB_MIG',   -- schema name filter 
   'DIM_DATE, DIM_PRODUCT, DIM_STORE, SALES, SALES_POSITION', -- table name filter 
   4,             -- degree of parallelism for the import statements
   false,  -- flag for primary key generation 
   false,  -- flag for foreign key generation 
   true -- flag for creation and loading of checking tables
);

The following example shows you the translation of an Oracle DDL to an Exasol DDL, omitting everything not needed in Exasol anymore.

Oracle DDL 
CREATE TABLE "C##DB_MIG"."DIM_PRODUCT"(    
    "PRODUCT_ID" NUMBER(6,0) GENERATED ALWAYS AS IDENTITY, 
    "PRODUCT_NAME" VARCHAR2(255), 
    "GTIN" NUMBER(12,0), 
    "LIST_PRICE" NUMBER(7,2), 
    "SALES_PRICE" NUMBER(7,2), 
    "BRAND" VARCHAR2(255), 
    "CATEGORY_ID" NUMBER(6,0), 
    "CATEGORY" VARCHAR2(32), 
    "SUB_CATEGORY_ID" NUMBER(6,0), 
    "SUB_CATEGORY" VARCHAR2(48), 
    "SUB_SUB_CATEGORY_ID" NUMBER(6,0), 
    "SUB_SUB_CATEGORY" VARCHAR2(64)
) 
SEGMENT CREATION IMMEDIATE 
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" 
;
Exasol DDL 
create or replace table "C##DB_MIG"."DIM_PRODUCT"(
"PRODUCT_ID" decimal(6,0) IDENTITY NOT NULL, 
"PRODUCT_NAME" varchar(255), 
"GTIN" decimal (12,0), 
"LIST_PRICE" decimal(7,2), 
"SALES_PRICE" decimal(7,2), 
"BRAND" varchar(255), 
"CATEGORY_ID" decimal(6,0), 
"CATEGORY" varchar(32), 
"SUB_CATEGORY_ID" decimal(6,0), 
"SUB_CATEGORY" varchar(48), 
"SUB_SUB_CATEGORY_ID" decimal(6,0), 
"SUB_SUB_CATEGORY" varchar(64)
);

The length definitions in Exasol for VARCHAR are always in characters, not bytes. For more details, see Data Type Mapping.

Like Oracle, Exasol also supports Identity Columns to generate auto-increments, but supports fewer options. For example, you cannot specify the increment size.

Apart from the immediate column definitions, most of the other Oracle syntax elements such as, SEGMENT CREATION, PCTFREE, NOCOMPRESS, NOLOGGING, STORAGE, TABLESPACE, IDENTITY OPTIONS, INDEX, INMEMORY OPTIONS are not needed and supported in Exasol anymore. For more details on table definitions, see CREATE TABLE.

In some data warehouses materialized views can be found as well. Those materialized views persist the data of their base query. These structures are oftentimes used to fight performance issues in Oracle, e.g., when views are too slow. Materialized views are not supported in Exasol, but they can be migrated as views or tables with their refresh queries as ETL jobs. While views in Oracle might result in inacceptable query times, in Exasol they can be a viable option.

Here is an example of how a materialized view can be migrated to Exasol.

This materialized view refreshes completely on demand.

create materialized view mv_sales
build immediate 
refresh complete
on demand
as 
select    state_name, d_week_of_year_monday sales_week, category,
sum(product_price) sum_total_price
from sales s 
join dim_date d on s.sales_date = d.d_date
join dim_store ds on s.store_id = ds.store_id
join sales_position sp on s.sales_id = sp.sales_id
join dim_product p on sp.product_id = p.product_id
group by state_name, d_week_of_year_monday, category
;

The equivalent behavior to that materialized view in Exasol would be a create table as select statement.

create or replace table mv_sales
as 
select    state_name, d_week_of_year_monday sales_week, category,
sum(product_price) sum_total_price 
from sales s 
join dim_date d on s.sales_date = d.d_date
join dim_store ds on s.store_id = ds.store_id
join sales_position sp on s.sales_id = sp.sales_id
join dim_product p on sp.product_id = p.product_id
group by state_name, d_week_of_year_monday, category
;

Other methods to convert Oracle DDL to Exasol

If you are using a data modeling tool, you can export the DDL into an ANSI SQL format.

You can set up an Exasol virtual schema to Oracle. For more information, refer to the Oracle Virtual Schema GitHub repository.