Oracle_Performance_Tips

Specify tablespace (so some partitions can be on FAST/flash disks, others on slow near line spinning storage)

create tablespace tsFAST datafile 'F:\FAST\FASTfil01.dbf' SIZE 10M autoextend on;

create tablespace tsFASTidx datafile 'F:\FAST\FASTidx01.dbf' SIZE 7M;

CREATE TABLE 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_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy')) TABLESPACE tsa,

  PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy')) TABLESPACE tsb,

  PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy')) TABLESPACE tsc,

  PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy')) TABLESPACE tsFAST

 );

So, a row w/time_id=01-JAN-2006 goes in partition sales_q1_2006.

Override storage for some/all partition(s)

CREATE TABLE 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)

  )

 STORAGE (INITIAL 100K NEXT 50K) LOGGING

 PARTITION BY RANGE (time_id)(

  PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy')) TABLESPACE tsa STORAGE (INITIAL 20K NEXT 10K),

  PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy')) TABLESPACE tsb,

  PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy')) TABLESPACE tsc,

  PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy')) TABLESPACE tsd

 ) ENABLE ROW MOVEMENT;

See table "STORAGE" parms and a LOGGING attr.

 override/replace tablespace defaults and are inherited by the range partitions.

  sales_q1_2006 is smaller ...as there was little business in the first quarter

ENABLE ROW MOVEMENT allows auto migration of a row to a new partition (upon key update)