Fragmenting a table for distribution ORACLE SQL

Question

Is there any subset of sql that deals specifically with horizontally fragmenting a table for distribution?

I have to fragment a stock table to be distributed between stores. Im not looking for a complete solution just a nod in the right direction.

I have founs an IBM example that does similar to what I want (i think)

CREATE TABLE Tab1 (Col1 INT...)
   FRAGMENT BY RANGE (Col1 MIN 100 MAX 200)
      IN db1, db2, db3, db4;

but I need an example for Oracle (up to 11g2).

I think you want range partitioning in Oracle (http://docs.oracle.com/cd/B28359_01/server.111/b32024/partition.htm ) — tbone, May 18 '12 at 15:46

score 1 · Answer 1 · answered May 18 '12 at 16:02

If you want the data to be distributed between different Oracle databases (i.e. there is an Oracle database instance that is installed at each store), you would generally accomplish that by creating materialized views in each store's database that includes a predicate on whatever data the store needs access to.

If you want the data to distributed across multiple segments in the same database, you would use partitioning as @tbone suggested in his comment. From the CREATE TABLE documentation, for example, you can create a SALES table that is partitioned by quarter.

CREATE TABLE range_sales
    ( prod_id        NUMBER(6)
    , cust_id        NUMBER
    , time_id        DATE
    , channel_id     CHAR(1)
    , promo_id       NUMBER(6)
    , quantity_sold  NUMBER(3)
    , amount_sold         NUMBER(10,2)
    ) 
PARTITION BY RANGE (time_id)
  (PARTITION SALES_Q1_1998 VALUES LESS THAN (TO_DATE('01-APR-1998','DD-MON-YYYY')),
   PARTITION SALES_Q2_1998 VALUES LESS THAN (TO_DATE('01-JUL-1998','DD-MON-YYYY')),
   PARTITION SALES_Q3_1998 VALUES LESS THAN (TO_DATE('01-OCT-1998','DD-MON-YYYY')),
   PARTITION SALES_Q4_1998 VALUES LESS THAN (TO_DATE('01-JAN-1999','DD-MON-YYYY')),
   PARTITION SALES_Q1_1999 VALUES LESS THAN (TO_DATE('01-APR-1999','DD-MON-YYYY')),
   PARTITION SALES_Q2_1999 VALUES LESS THAN (TO_DATE('01-JUL-1999','DD-MON-YYYY')),
   PARTITION SALES_Q3_1999 VALUES LESS THAN (TO_DATE('01-OCT-1999','DD-MON-YYYY')),
   PARTITION SALES_Q4_1999 VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY')),
   PARTITION SALES_Q1_2000 VALUES LESS THAN (TO_DATE('01-APR-2000','DD-MON-YYYY')),
   PARTITION SALES_Q2_2000 VALUES LESS THAN (TO_DATE('01-JUL-2000','DD-MON-YYYY')),
   PARTITION SALES_Q3_2000 VALUES LESS THAN (TO_DATE('01-OCT-2000','DD-MON-YYYY')),
   PARTITION SALES_Q4_2000 VALUES LESS THAN (MAXVALUE))
;

Fragmenting a table for distribution ORACLE SQL

1 Answers1