Tuesday Aug 02, 2011

OWB 11gR2 - Creating Interval Partitions

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!

About

ETL, CDC, Real-Time DI and Data Quality for the Oracle Database from the inside.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today