Chapter 7 

Page 369

ALTER TABLE easydw.purchases
 DROP PARTITION purchases_may97; 

Page 370

CREATE TABLESPACE purchases_apr2002 
     datafile 'E:\EASYDW\PURCHASESapr2002.f'
     size 5m reuse autoextend on default storage
    (initial 16k next 16k pctincrease 0 maxextents unlimited);


ALTER TABLE easydw.purchases
     ADD PARTITION purchases_apr2002 
	 values less than (TO_DATE('30-04-2002', 'DD-MM-YYYY'))
	 pctfree 0 pctused 99
 	 storage (initial 64k next 16k pctincrease 0) 
 	 tablespace purchases_apr2002 ;


ALTER TABLE easydw.purchases EXCHANGE PARTITION purchases_apr2002 WITH    TABLE apr_orders;


Page 371

CREATE TABLESPACE purchases_q1_2002 
  	DATAFILE 'c:\ora9ir2\oradata\orcl\PURCHASESQ12002.f' SIZE 5M
  	REUSE AUTOEXTEND ON
  	DEFAULT STORAGE
  	(INITIAL 64K NEXT 64K PCTINCREASE 0 MAXEXTENTS UNLIMITED);

ALTER TABLE purchases
           MERGE PARTITIONS purchases_feb2002,purchases_mar2002
           INTO PARTITION purchases_q12002
           TABLESPACE purchases_q1_2002

ALTER TABLE purchases
          MERGE PARTITIONS purchases_jan2002,purchases_q12002
          INTO PARTITION purchases_q12002
          TABLESPACE purchases_q1_2002

Page 372


SELECT INDEX_NAME, PARTITION_NAME, STATUS 
FROM USER_IND_PARTITIONS;

Page 373

ALTER INDEX purchase_product_index 
REBUILD PARTITION purchases_q12002
SQL> ALTER INDEX purchase_time_index 
REBUILD PARTITION purchases_q12002

ALTER TABLE purchases 
     SPLIT PARTITION purchases_q12002
     AT (TO_DATE('31-JAN-2002','dd-mon-yyyy'))
     INTO (PARTITION purchases_jan2002 
 	     TABLESPACE purchases_jan2002,
           PARTITION purchases_q12002)


ALTER TABLE purchases  
     SPLIT PARTITION purchases_q12002
     AT (TO_DATE('28-FEB-2002','dd-mon-yyyy'))
     INTO (PARTITION purchases_feb2002 
           TABLESPACE purchases_feb2002,
           PARTITION purchases_mar2002 
           TABLESPACE purchases_mar2002)


Page 374



SELECT INDEX_NAME, PARTITION_NAME, STATUS 
     FROM USER_IND_PARTITIONS;


Page 375

ALTER INDEX PURCHASE_PRODUCT_INDEX REBUILD PARTITION purchases_feb2002
ALTER INDEX purchase_time_index REBUILD PARTITION purchases_feb2002


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;

Page 376


SELECT COUNT(*) FROM purchases 
     WHERE time_key BETWEEN TO_DATE('01-JAN-2002') AND 
                            TO_DATE('31-JAN-2002')


ALTER TABLE PURCHASES 
     TRUNCATE PARTITION purchases_jan2002;

SELECT COUNT(*) FROM purchases 
     WHERE time_key BETWEEN TO_DATE('01-JAN-2002') AND 
                            TO_DATE('31-JAN-2002')
Page 379

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS ('EASYDW');

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

-- table statistics 
SELECT NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED
FROM USER_TABLES WHERE TABLE_NAME = 'PURCHASES';

-- column statistics
SQL> SELECT NUM_DISTINCT, NUM_NULLS, AVG_COL_LEN
FROM USER_TAB_COLS WHERE TABLE_NAME = 'PURCHASES';

Page 380

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


ALTER TABLE purchases ADD PARTITION purchases_jul2002
          values less than (TO_DATE('31-07-2002', 'DD-MM-YYYY'));

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

ALTER TABLE purchases MONITORING;

EXECUTE DBMS_STATS.ALTER_SCHEMA_TAB_MONITORING('easydw', TRUE);

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS ('easydw', options=>'GATHER STALE');


Page 395

SELECT STATUS, NAME, ERROR_MSG FROM DBA_RESUMABLE


SELECT EVENT, STATE FROM V$SESSION_WAIT;