--CREATE TABLES
DROP TABLE BACKUP_T_SHIPMENTS;
CREATE TABLE BACKUP_T_SHIPMENTS
AS
SELECT *
FROM T_SHIPMENTS ;

DROP TABLE BACKUP_T_DELIVERY;
CREATE TABLE BACKUP_T_DELIVERY
AS
SELECT *
FROM T_DELIVERY ;

DROP TABLE BACKUP_T_ADDRESSES;
CREATE TABLE BACKUP_T_ADDRESSES
AS
SELECT *
FROM T_ADDRESSES;

DROP TABLE BACKUP_T_CITIES;
CREATE TABLE BACKUP_T_CITIES
AS
SELECT *
FROM T_CITIES;
----
--CREATE LOG ON TABLES
DROP MATERIALIZED VIEW LOG ON "u_3nf"."BACKUP_T_CITIES";
CREATE MATERIALIZED VIEW LOG ON "u_3nf"."BACKUP_T_CITIES"
WITH ROWID, SEQUENCE ("CITY_ID","CITY")
INCLUDING NEW VALUES;

DROP MATERIALIZED VIEW LOG ON "u_3nf"."BACKUP_T_SHIPMENTS";
CREATE MATERIALIZED VIEW LOG ON "u_3nf"."BACKUP_T_SHIPMENTS"
WITH ROWID, SEQUENCE("DELIVERY_ID","PRICE")
INCLUDING NEW VALUES;

DROP MATERIALIZED VIEW LOG ON "u_3nf"."BACKUP_T_ADDRESSES";
CREATE MATERIALIZED VIEW LOG ON "u_3nf"."BACKUP_T_ADDRESSES"
WITH ROWID, SEQUENCE("ADDRESS_ID","CITY_ID")
INCLUDING NEW VALUES;

DROP MATERIALIZED VIEW LOG ON "u_3nf"."BACKUP_T_DELIVERY";
CREATE MATERIALIZED VIEW LOG ON "u_3nf"."BACKUP_T_DELIVERY"
WITH ROWID, SEQUENCE("DELIVERY_ID","DEPARTURE_POINT")
INCLUDING NEW VALUES;
----
--CREATE VIEW WITH FAST REFRESH
DROP MATERIALIZED VIEW BACKUP_REVENUE_BY_CITY;
CREATE MATERIALIZED VIEW BACKUP_REVENUE_BY_CITY
REFRESH FAST
ON COMMIT
DISABLE QUERY REWRITE AS
SELECT
SUM(PRICE),
TC.CITY,
count(*)
FROM BACKUP_T_SHIPMENTS TS
JOIN BACKUP_T_DELIVERY TD
ON TS.DELIVERY_ID = TD.DELIVERY_ID
JOIN BACKUP_T_ADDRESSES TA
ON TA.ADDRESS_ID = TD.DEPARTURE_POINT
JOIN BACKUP_T_CITIES TC
ON TC.CITY_ID = TA.CITY_ID
GROUP BY TC.CITY
ORDER BY 1 ;

SELECT * FROM BACKUP_REVENUE_BY_CITY WHERE CITY  = 'Ivye';

DELETE FROM BACKUP_T_SHIPMENTS
WHERE DELIVERY_ID  IN
(SELECT
TD.DELIVERY_ID
FROM BACKUP_T_SHIPMENTS TS
JOIN BACKUP_T_DELIVERY TD
ON TS.DELIVERY_ID = TD.DELIVERY_ID
JOIN BACKUP_T_ADDRESSES TA
ON TA.ADDRESS_ID = TD.DEPARTURE_POINT
JOIN BACKUP_T_CITIES TC
ON TC.CITY_ID = TA.CITY_ID
WHERE TC.CITY = 'Ivye')
AND ROWNUM < 999999999999999999999;
COMMIT;

EXECUTE dbms_mview.refresh ( list=>'BACKUP_REVENUE_BY_CITY', method=>'F' )
;
--