CREATE TABLE product_price_history ( 
  product_id          INTEGER NOT NULL, 
  price               INTEGER NOT NULL, 
  currency_code       VARCHAR2(3 CHAR) NOT NULL, 
  effective_from_date DATE NOT NULL, 
  effective_to_date   DATE, 
  CONSTRAINT product_price_history_pk 
    PRIMARY KEY (product_id, currency_code, effective_from_date) 
) PARTITION BY RANGE (effective_from_date) ( 
    PARTITION p0 VALUES less than (DATE'2015-01-02'), 
    PARTITION p1 VALUES less than (DATE'2015-01-03'), 
    PARTITION p2 VALUES less than (DATE'2015-01-04') 
)

 INSERT INTO product_price_history 
  WITH prices AS ( 
    SELECT 1, 100, 'USD', DATE'2015-01-01', DATE'2015-01-02' 
    FROM   dual UNION ALL 
    SELECT 1, 60, 'GBP', DATE'2015-01-01', DATE'2015-01-02' 
    FROM   dual UNION ALL 
    SELECT 1, 110, 'EUR', DATE'2015-01-01', DATE'2015-01-02' 
    FROM   dual UNION ALL 
    SELECT 1, 101, 'USD', DATE'2015-01-02', DATE'2015-01-03' 
    FROM   dual UNION ALL 
    SELECT 1, 62, 'GBP', DATE'2015-01-02', DATE'2015-01-03' 
    FROM   dual UNION ALL 
    SELECT 1, 109, 'EUR', DATE'2015-01-02', DATE'2015-01-03' 
    FROM   dual UNION ALL 
    SELECT 1, 105, 'USD', DATE'2015-01-03', NULL 
    FROM   dual UNION ALL 
    SELECT 1, 61, 'GBP', DATE'2015-01-03', NULL 
    FROM   dual UNION ALL 
    SELECT 1, 107, 'EUR', DATE'2015-01-03', NULL 
    FROM   dual UNION ALL 
    SELECT 2, 30, 'USD', DATE'2015-01-01', DATE'2015-01-03' 
    FROM   dual UNION ALL 
    SELECT 2, 33, 'USD', DATE'2015-01-03', NULL 
    FROM   dual UNION ALL 
    SELECT 3, 100, 'GBP', DATE'2015-01-03', NULL 
    FROM   dual 
  ) 
SELECT * 
FROM   prices

SELECT * FROM product_price_history 
ORDER  BY product_id, effective_from_date, currency_code

DELETE FROM product_price_history 
WHERE  product_id = 3

SELECT * FROM product_price_history 
ORDER  BY product_id, effective_from_date, currency_code

DECLARE 
  currency product_price_history.currency_code%TYPE; 
BEGIN 
  DELETE product_price_history 
  WHERE  product_id = 2 
  AND    effective_to_date IS NULL 
  returning currency_code INTO currency; 
      
  dbms_output.Put_line(currency); 
END; 

SELECT * FROM product_price_history 
ORDER  BY product_id, effective_from_date, currency_code

DELETE (SELECT * FROM product_price_history) 
WHERE  currency_code = 'EUR'

SELECT * FROM product_price_history 
ORDER  BY product_id, effective_from_date, currency_code

DELETE product_price_history pp 
WHERE  (product_id, currency_code, effective_from_date) 
   IN (SELECT product_id, currency_code, Max(effective_from_date) 
       FROM   product_price_history 
       GROUP BY product_id, currency_code)

SELECT * FROM product_price_history 
ORDER  BY product_id, effective_from_date, currency_code

DELETE product_price_history partition (p1)

SELECT * FROM product_price_history

DELETE product_price_history

SELECT * FROM product_price_history