Chapter 11

-----------------------------------------------------
Section 11.4.2

Rolling Window Partition Maintenance

ALTER TABLE easydw.purchases DROP PARTITION purchases_jan00;

CREATE TABLESPACE purchases_dec04
DATFILE 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\EASYDW\PURCHASESdec2004.f'
SIZE 5m REUSE AUTOEXTEND ON DEFAULT STORAGE
(INITIAL 16k NEXT 16k
PCTINCREASE 0 MAXEXTENTS UNLIMITED);

ALTER TABLE easydw.purchases
ADD PARTITION purchases_dec2004
VALUES LESS THAN (TO_DATE('01-01-2005',
'DD-MM-YYYY'))
PCTFREE 0 PCTUSED 99
STORAGE (INITIAL 64k NEXT 16k PCTINCREASE 0)
TABLESPACE purchases_dec2004 ;

Exchanging a Partition

ALTER TABLE easydw.purchases
EXCHANGE PARTITION purchases_dec2004
WITH TABLE dec_orders;

Merging Partitions

CREATE TABLESPACE purchases_q2_2004
DATAFILE
'C:\ORACLE\PRODUCT\10.1.0\ORADATA\EASYDW\PURCHASESQ22004.f'
  SIZE 5M
  REUSE AUTOEXTEND ON
  DEFAULT STORAGE
    (INITIAL 64K NEXT 64K
     PCTINCREASE 0 MAXEXTENTS UNLIMITED);

ALTER TABLE purchases
  MERGE PARTITIONS purchases_may2004,purchases_jun2004
  INTO PARTITION purchases_q22004
  TABLESPACE purchases_q2_2004 ;

ALTER TABLE purchases
  MERGE PARTITIONS purchases_apr2004,
                   purchases_q22004
  INTO PARTITION purchases_q22004
  TABLESPACE purchases_q2_2004 ;

ALTER INDEX purchase_product_index
  REBUILD PARTITION purchases_q22004 ;
ALTER INDEX purchase_time_index
  REBUILD PARTITION purchases_q22004 ;
ALTER INDEX purchase_special_index
  REBUILD PARTITION purchases_q22004 ;
ALTER INDEX purchase_customer_index
  REBUILD PARTITION purchases_q22004 ;

Splitting Partitions

ALTER TABLE purchases
SPLIT PARTITION purchases_q22004
  AT (TO_DATE('01-MAY-2004','dd-mon-yyyy'))
  INTO (PARTITION purchases_apr2004
          TABLESPACE purchases_apr2004,
        PARTITION purchases_q22004) ;

ALTER TABLE purchases
SPLIT PARTITION purchases_q22004
  AT (TO_DATE('01-JUN-2004','dd-mon-yyyy'))
  INTO (PARTITION purchases_may2004
          TABLESPACE purchases_may2004,
        PARTITION purchases_jun2004
          TABLESPACE purchases_jun2004) ;

ALTER INDEX purchase_product_index
  REBUILD PARTITION purchases_apr2004 ;
ALTER INDEX purchase_time_index
  REBUILD PARTITION purchases_apr2004 ;
ALTER INDEX purchase_customer_index
  REBUILD PARTITION purchases_apr2004 ;
ALTER INDEX purchase_special_index
  REBUILD PARTITION purchases_apr2004 ;

Coalescing Hash Partitions

CREATE TABLE easydw.hash_purchases
(product_id varchar2(8),
 time_key date,
 customer_id varchar2(10),
 purchase_date date,
 purchase_time number(4,0),
 purchase_price number(6,2),
 shipping_charge number(5,2),
 today_special_offer varchar2(1))
PARTITION BY HASH(product_id)
PARTITIONS 10;

ALTER TABLE easydw.hash_purchases COALESCE PARTITION;


Truncating Partitions

SELECT COUNT(*) FROM purchases
WHERE time_key
        BETWEEN TO_DATE('01-JAN-2003', 'dd-mon-yyyy')
            AND TO_DATE('31-JAN-2003', 'dd-mon-yyyy') ;

ALTER TABLE PURCHASES TRUNCATE PARTITION purchases_jan2003;

Moving Partitions

ALTER TABLE purchases
  MOVE PARTITION purchases_jan2003
  TABLESPACE purchases_jan2003 ;

-----------------------------------------------------
Section 11.4.4 Online Redefinition of Tables

The postal code functions:

CREATE OR REPLACE
FUNCTION outer(postal_code IN VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
  IF instr(postal_code, ' ') = 0
  THEN return (postal_code);
  ELSE return (substr(postal_code,
                      1,
                      instr(postal_code, ' ')-1)) ;
  END IF ;
END ;


CREATE OR REPLACE
FUNCTION inner(postal_code IN VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
  IF instr(postal_code, ' ') = 0
  THEN return (null);
  ELSE return (substr(postal_code,
                      instr(postal_code, ' ')+1)) ;
  END IF ;
END ;

Step 1:
CREATE TABLE customer_interim
(
  CUSTOMER_ID VARCHAR2(10) NOT NULL,
  CITY VARCHAR2(15),
  STATE VARCHAR2(10),
  POSTAL_CODE_OUTER VARCHAR2(10),
  POSTAL_CODE_INNER VARCHAR2(10),
  GENDER VARCHAR2(1),
  REGION VARCHAR2(15),
  COUNTRY VARCHAR2(20),
  TAX_RATE NUMBER,
  OCCUPATION VARCHAR2(20)
)
PARTITION BY LIST(state)
(
  PARTITION northeast VALUES ('NH', 'VT', 'MA', 'RI', 'CT'),
  PARTITION southeast VALUES ('NC', 'GA', 'FL'),
  PARTITION northwest VALUES ('WA', 'OR'),
  PARTITION midwest VALUES ('IL', 'WI', 'OH'),
  PARTITION west VALUES ('CA', 'NV', 'AZ'),
  PARTITION otherstates VALUES (DEFAULT)
);

Step 2:
BEGIN
dbms_redefinition.start_redef_table
 (uname=>'EASYDW',
  orig_table=>'CUSTOMER',
  int_table=>'CUSTOMER_INTERIM',
  col_mapping=>'customer_id customer_id, '
               ||'city city, '
               ||'state state,'
               ||'outer(postal_code) postal_code_outer,'
               ||'inner(postal_code) postal_code_inner,'
               ||'gender gender, '
               ||'region region, '
               ||'country country, '
               ||'tax_rate tax_rate, '
               ||'occupation occupation'
 );
END;

Step 3:
set serveroutput on
DECLARE
  num_errors number;
BEGIN
  num_errors:= 0;
  dbms_redefinition.copy_table_dependents
    (uname=>'EASYDW',
     orig_table=>'CUSTOMER',
     int_table=>'CUSTOMER_INTERIM',
     num_errors=>num_errors);
  dbms_output.put_line
    ('Number of errors:'||to_char(num_errors));
END;

Step 4:
BEGIN
  dbms_redefinition.sync_interim_table
    (uname=>'EASYDW',
     orig_table=>'CUSTOMER',
     int_table=>'CUSTOMER_INTERIM');
END;

Step 5:
BEGIN
  dbms_redefinition.finish_redef_table
    (uname=>'EASYDW',
     orig_table=>'CUSTOMER',
     int_table=>'CUSTOMER_INTERIM');
END;

Step 6:
BEGIN
  dbms_stats.gather_table_stats(ownname=>'easydw',
                                tabname=>'customer',
                                cascade=true
                               );
END;

-----------------------------------------------------
Section 11.4.5 Online Segment Shrink

ALTER MATERIALIZED VIEW product_sum SHRINK SPACE;

-----------------------------------------------------
Section 11.6.2 Manual Statistics Collection

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS ('EASYDW');

EXECUTE DBMS_STATS.GATHER_TABLE_STATS ('EASYDW', 'PURCHASES');

SELECT num_rows, blocks, avg_row_len, last_analyzed
FROM user_tables
WHERE table_name = 'PURCHASES';

SELECT column_name, num_distinct, num_nulls, avg_col_len
FROM user_tab_cols
WHERE table_name = 'PURCHASES';

EXECUTE DBMS_STATS.GATHER_INDEX_STATS
                    ('easydw', 'purchase_customer_index');

ALTER TABLE purchases
  ADD PARTITION purchases_jan2005
  VALUES LESS THAN (TO_DATE('01-02-2005', 'DD-MM-YYYY'));

INSERT /*+APPEND */ INTO purchases ...

EXECUTE DBMS_STATS.GATHER_TABLE_STATS
               ('easydw', 'purchases', 'purchases_jan2005');