X

Welcome to All Things Warehouse Builder

  • ETL
    August 2, 2011

OWB 11gR2 - Creating Interval Partitions

David Allan
Architect

Designing partitioned tables in OWB is done in the table editor in the partitioned tab, the partitions tab let’s you design and deploy complex partitioning strategies. Here we will see how to define an interval partition (see an example in the Oracle Database VLDB and Partitioning documentation here), we will partition the SALES fact table using a date column (TIMES) in the table below.

owb_partition_1

On the partitioning tab there is a table with a tree control inside, essentially there are 4 steps for this example; defining the partition type, define the key columns, define the interval expression and the initial partition details. The buttons Add/Add Subpartition/Add Hash Count/Delete get enabled when you select rows, so you can modify the definition.

owb_partition_2

Generating the code we can see the DDL for the Oracle partitioning clause has been included.

owb_partition_3

To create the table definition in OMB you can do something like the following – note there is some double quoting in the expressions.

OMBCREATE TABLE 'SALES_TAB' ADD COLUMN 'TIMES' SET PROPERTIES (DATATYPE) VALUES ('DATE')
# Plus the rest of your table definition....
OMBALTER TABLE 'SALES_TAB' ADD PARTITION_KEY 'TIMES' SET PROPERTIES (TYPE,INTERVAL) VALUES ('RANGE','NUMTOYMINTERVAL(1,''MONTH'')')
OMBALTER TABLE 'INTERVAL_TAB' ADD PARTITION 'PART_01' SET PROPERTIES (VALUES_LESS_THAN) VALUES ('TO_DATE(''01-NOV-2007'',''DD-MON-YYYY'')')

That’s it!

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.