Transportation Mechanisms in Data Warehouses

Transportation Mechanisms in Data Warehouses

Three basic choices for transporting data in warehouses:

• Transportation Using Flat Files

• Transportation Through Distributed Operations

• Transportation Using Transportable Tablespaces.

Ø  Transportation Using Transportable Tablespaces

Use the following steps to create a transportable tablespace:

1. Place the Data to be Transported into its own Tablespace

The current month's data must be placed into a separate tablespace in order to be transported

CREATE TABLE temp_jan_sales NOLOGGING TABLESPACE ts_temp_sales

AS SELECT * FROM sales

WHERE time_id BETWEEN '31-DEC-1999' AND '01-FEB-2000';

ALTER TABLESPACE ts_temp_sales READ ONLY;

2. Export the Metadata

EXP TRANSPORT_TABLESPACE=y TABLESPACES=ts_temp_sales FILE=jan_sales.dmp

3. Copy the Datafiles and Export File to the Target System

Copy the data files that make up ts_temp_sales, as well as the export file jan_sales.dmp to the data mart platform

4. Import the Metadata

IMP TRANSPORT_TABLESPACE=y DATAFILES='/db/tempjan.f' TABLESPACES=ts_temp_sales FILE=jan_sales.dmp;

You can insert the data from the temp_sales_jan table into the data mart's sales table in one of two ways:

• INSERT /*+ APPEND */ INTO sales SELECT * FROM temp_sales_jan;

OR

• ALTER TABLE sales ADD PARTITION sales_00jan

VALUES LESS THAN (TO_DATE('01-feb-2000','dd-mon-yyyy'));

ALTER TABLE sales EXCHANGE PARTITION sales_00jan

WITH TABLE temp_sales_jan INCLUDING INDEXES WITH VALIDATION;

Ø  Transportation Using Flat Files ( Using SQL * loader / External Table )

• Using SQL * Loader

The following shows the control file (sh_sales.ctl) loading the sales table:

LOAD DATA INFILE sh_sales.dat APPEND INTO TABLE sales

FIELDS TERMINATED BY "|"

(PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD)

It can be loaded with the following command:

$ sqlldr control=sh_sales.ctl direct=true

Username:

Password:

o    Using External Table

v  Difference between external tables and regular tables is that externally organized tables are read-only. No DML operations (UPDATE/INSERT/DELETE) are possible and no indexes can be created on them.

v  You can  load large amounts of compressed data without first uncompressing it on a disk.

CREATE TABLE sales_transactions_ext

(      PROD_ID NUMBER, CUST_ID NUMBER,

TIME_ID DATE, CHANNEL_ID NUMBER,

PROMO_ID NUMBER, QUANTITY_SOLD NUMBER,

AMOUNT_SOLD NUMBER(10,2), UNIT_COST NUMBER(10,2),

UNIT_PRICE NUMBER(10,2))

ORGANIZATION external

(

TYPE oracle_loader

DEFAULT DIRECTORY data_file_dir ACCESS PARAMETERS

(      RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII

PREPROCESSOR EXECDIR:'zcat'

BADFILE log_file_dir:'sh_sales.bad_xt'

LOGFILE log_file_dir:'sh_sales.log_xt'

FIELDS TERMINATED BY "|" LDRTRIM

( PROD_ID, CUST_ID,

  TIME_ID DATE(10) "YYYY-MM-DD",

  CHANNEL_ID, PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD,

  UNIT_COST, UNIT_PRICE

)

)

location ('sh_sales.gz')

)REJECT LIMIT UNLIMITED;