-----------------------------------------------------------------------------
-- 4.2.2 Range Partitioning
-----------------------------------------------------------------------------

CREATE TABLE easydw.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 RANGE (time_key) 
 (partition purchases_jan2003
     values less than (TO_DATE('01-FEB-2003', 'DD-MON-YYYY'))
     tablespace purchases_jan2003,
  partition purchases_feb2003 
     values less than (TO_DATE('01-MAR-2003', 'DD-MON-YYYY'))
     tablespace purchases_feb2003,
  partition purchases_mar2003 
     values less than (TO_DATE('01-APR-2003', 'DD-MON-YYYY'))
     tablespace purchases_mar2003, 
  partition purchase_catchall
     values less than (MAXVALUE)
     tablespace purchases_maxvalue);

-----------------------------------------------------------------------------
-- 4.2.3 Hash Partitioning
-----------------------------------------------------------------------------

CREATE TABLE easydw.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 4;

-----------------------------------------------------------------------------
-- 4.2.4 List Partitioning
-----------------------------------------------------------------------------

CREATE TABLE easydw.regional_sales
(state                 varchar2(2),
 store_number          number,
 dept_number           number,
 dept_name             varchar2(10), 
 sales_amount          number (6,2)
) 
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));


-----------------------------------------------------------------------------
-- 4.2.5 Composite Partitioning
-----------------------------------------------------------------------------

-- Range-Hash
CREATE TABLE easydw.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 RANGE (time_key)
SUBPARTITION BY HASH(product_id)
SUBPARTITIONS 4
   (partition purchases_jan2003 
     values less than (TO_DATE('01-FEB-2003', 'DD-MON-YYYY'))
        STORE IN (purchases_jan2003_1, 
                  purchases_jan2003_2, 
                  purchases_jan2003_3,
                  purchases_jan2003_4),
    partition purchases_feb2003 
     values less than (TO_DATE('01-MAR-2003', 'DD-MON-YYYY'))
        STORE IN (purchases_feb2003_1, 
                  purchases_feb2003_2, 
                  purchases_feb2003_3,
                  purchases_feb2003_4),
    partition purchases_mar2003 
     values less than (TO_DATE('01-APR-2003', 'DD-MON-YYYY'))
       STORE IN  (purchases_mar2003_1, 
                  purchases_mar2003_2, 
                  purchases_mar2003_3,
                  purchases_mar2003_4));

-- Range-List
CREATE TABLE sales
(state                  varchar2(2),
 store_number           number,
 dept_number            number,
 dept_name              varchar2(10), 
 sales_amount           number (6,2),
 sale_date		      date,
 item_number            number (10)
) 
PARTITION BY RANGE (sale_date) 
SUBPARTITION BY LIST(state)
SUBPARTITION TEMPLATE 
(
 SUBPARTITION "NorthEast" 
    VALUES ('NH', 'VT', 'MA', 'RI', 'CT') 
    TABLESPACE sales_ne,
 SUBPARTITION "SouthEast" 
    VALUES ('NC', 'GA', 'FL') 
    TABLESPACE sales_se,
 SUBPARTITION "NorthWest" 
    VALUES ('WA', 'OR')
    TABLESPACE sales_nw,
 SUBPARTITION "MidWest"
    VALUES ('IL', 'WI', 'OH') 
    TABLESPACE sales_mw,
 SUBPARTITION "West" 
    VALUES ('CA', 'NV', 'AZ') 
    TABLESPACE sales_w
)
(
  PARTITION sales_jan_2003 
    VALUES LESS THAN (TO_DATE('01-FEB-2003', 'DD-MON-YYYY')), 
  PARTITION sales_feb_2003 
    VALUES LESS THAN (TO_DATE('01-MAR-2003', 'DD-MON-YYYY')), 
  PARTITION sales_mar_2003 
    VALUES LESS THAN (TO_DATE('01-APR-2003', 'DD-MON-YYYY'))
);

-- show partitions in SALES table
SELECT partition_name, subpartition_name 
FROM user_tab_subpartitions 
WHERE table_name = 'SALES';

-----------------------------------------------------------------------------
-- 4.2.6 Multi-column Partition Keys
-----------------------------------------------------------------------------

CREATE TABLE easydw.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 RANGE (time_key, product_id) 
 (
  partition purchases_jan2003_100 
   values less than (TO_DATE('31-JAN-2003','DD-MON-YYYY'), 100)
   tablespace purchases_jan2003_100,
  partition purchases_jan2003_200 
   values less than (TO_DATE('31-JAN-2003','DD-MON-YYYY'), 200)
   tablespace purchases_jan2003_200 ,
  partition purchases_feb2003_all
   values less than (TO_DATE('28-FEB-2003','DD-MON-YYYY'), 100)
   tablespace purchases_feb2003,
  partition purchases_mar2003_all 
   values less than (TO_DATE('31-MAR-2003','DD-MON-YYYY'), 100)
   tablespace purchases_mar2003
);

-- example showing behavior of multi-column partitioning keys

insert into purchases (product_id, time_key) 
       values (1, TO_DATE('15-JAN-2003', 'DD-MON-YYYY'));

insert into purchases (product_id, time_key) 
       values (150, TO_DATE('15-JAN-2003', 'DD-MON-YYYY'));

insert into purchases (product_id, time_key) 
       values (101, TO_DATE('31-JAN-2003', 'DD-MON-YYYY'));

SELECT product_id, time_key 
FROM purchases partition(purchases_jan2003_100);

SELECT product_id, time_key 
FROM purchases partition(purchases_jan2003_200);

-----------------------------------------------------------------------------
-- 4.3.1 Btree Indexes
-----------------------------------------------------------------------------

CREATE UNIQUE INDEX TIME_UK ON TIME(time_key);

-----------------------------------------------------------------------------
-- 4.3.2 Bitmap Indexes
-----------------------------------------------------------------------------

-- Query being tuned
SELECT customer_id, city 
FROM customer
WHERE gender = ‘F’ AND occupation = ‘Self-Employed’;

-- Bitmap Index on Gender
CREATE BITMAP INDEX easydw.customer_gender_index
ON customer(gender) tablespace indx;

-----------------------------------------------------------------------------
-- 4.3.3 Bitmap Join Indexes
-----------------------------------------------------------------------------

-- Query being tuned
SELECT sum(p.purchase_price) 
FROM purchases p, customer c
WHERE p.customer_id = c.customer_id 
      AND c.gender = ‘F’;

-- Bitmap Join Index 
CREATE BITMAP INDEX easydw.purchase_cust_index
ON
purchases (customer.gender)
FROM purchases, customer 
WHERE purchases.customer_id = customer.customer_id;

-----------------------------------------------------------------------------
-- 4.3.4 Function-based Indexes
-----------------------------------------------------------------------------

CREATE INDEX  prod_category_idx 
ON product (product_id, UPPER(category));

CREATE OR REPLACE FUNCTION TAX_RATE(state IN varchar2, 
                                    country IN varchar2)
RETURN NUMBER DETERMINISTIC IS
BEGIN
  return 5;
END;
/

CREATE INDEX  customer_tax_idx 
ON customer (TAX_RATE(state, country));

-----------------------------------------------------------------------------
-- 4.3.5  Partitioned Indexes
-----------------------------------------------------------------------------

-- Global Index

CREATE INDEX easydw.purchase_product_index on purchases
 (product_id)
 GLOBAL
 partition by range (product_id) 
 (partition sp1000 values less than ('SP1000') ,
  partition sp2000 values less than ('SP2000') ,
  partition other  values less than (maxvalue) );

-- Local Prefixed

CREATE  INDEX easydw.purchase_time_index 
 ON purchases (time_key, customer_id)
local 
 (partition indexJan2003 tablespace purchases_jan2003_idx, 
  partition indexFeb2003 tablespace purchases_feb2003_idx, 
  partition indexMar2003 tablespace purchases_mar2003_idx );

-- Local Non-prefixed

CREATE BITMAP INDEX easydw.purchase_product_index 
ON purchases  (product_id)
LOCAL 
 (partition indexJan2003 tablespace purchases_jan2003_idx, 
  partition indexFeb2003 tablespace purchases_feb2003_idx, 
  partition indexMar2003 tablespace purchases_mar2003_idx);

-----------------------------------------------------------------------------
-- 4.4.1 Index Organized Tables
-----------------------------------------------------------------------------

CREATE TABLE     prodcat
(product_id      number,
 category        varchar2(10),
 description     CLOB,
 detailed_specs  BLOB,
 constraint pk_prodcat PRIMARY KEY(product_id)
)
ORGANIZATION INDEX
INCLUDING category
PCTTHRESHOLD 30
TABLESPACE prodcat_idx
STORAGE (INITIAL 8k NEXT 8k PCTINCREASE 10)
OVERFLOW
STORAGE
(INITIAL 16k NEXT 16k PCTINCREASE 10);

-----------------------------------------------------------------------------
-- 4.4.2 Partitioning and Indexing an IOT
-----------------------------------------------------------------------------

-- create mapping table
ALTER TABLE prodcat MOVE MAPPING TABLE;

-- change mapping table storage options
ALTER TABLE prodcat MAPPING TABLE allocate extent (size 16k);

-----------------------------------------------------------------------------
-- 4.5.1 Table Compression
-----------------------------------------------------------------------------

CREATE TABLE easydw.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 RANGE (time_key) 
(
  partition purchases_jan2003 
    values less than (TO_DATE('01-FEB-2003', 'DD-MON-YYYY'))
    tablespace purchases_jan2003 COMPRESS,
  partition purchases_feb2003 
    values less than (TO_DATE('01-MAR-2003', 'DD-MON-YYYY'))
    tablespace purchases_feb2003 COMPRESS,
  partition purchases_mar2003 
    values less than (TO_DATE('01-APR-2003', 'DD-MON-YYYY'))
    tablespace purchases_mar2003 COMPRESS, 
  partition purchases_catchall
    values less than (MAXVALUE)
    tablespace purchases_maxvalue NOCOMPRESS );

-- compressing a partition

ALTER TABLE purchases MOVE PARTITION purchases_catchall COMPRESS;
-----------------------------------------------------------------------------