Materialized view

PHOTO EMBED

Sun Jan 23 2022 19:00:28 GMT+0000 (Coordinated Universal Time)

Saved by @javanegas #sql 

create materialized view MV1    
build immediate    
refresh fast on commit    
as     
     
create materialized view log on <table>    
with sequence, rowId (...)    
including new values    
     
create table <table_name> (    
        DateMonth Date not null,    
        DateYear Date not null,    
        Name varchar(256) not null,    
        Total integer not null);    
     
insert into <table_name> () (select ...);    
     
create trigger trigger    
after insert or insert of on <table>    
for each row     
declare     
     
begin       
end    


create materialized view GROUPBYMonthYearMuseum
build immediate
refresh FAST ON COMMIT
--enable query rewrite
as
SELECT Month, Year, ticket_type , SUM(num_tickets) as NumTickets,
 SUM(revenue) as TotRevenue
FROM museums_tickets mt, timedim t
WHERE mt.id_time = t.id_time
GROUP BY Month, Year, ticket_type;

CREATE MATERIALIZED VIEW LOG ON museums_tickets
WITH SEQUENCE, ROWID (id_time, ticket_type, num_tickets, Revenue)
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON TIMEDIM
WITH SEQUENCE, ROWID (id_time, Month, Year)
INCLUDING NEW VALUES;
---- Trigger Manually
CREATE TABLE VM1 (
DateMonth DATE NOT NULL),
DateYear INTEGER NOT NULL,
Ticket_Type VARCHAR(20) NOT NULL,
TOT_NumberOfTickets INTEGER,
TOT_Revenue INTEGER);

INSERT INTO VM1 (DateMonth, DateYear, Ticket_Type, TOT_NumberOfTickets,
TOT_Revenue)
(SELECT Month, Year, Ticket_Type,
SUM(NumberOfTickets), SUM(Revenue)
FROM museums_tickets mt, timedim t
WHERE mt.id_time = t.id_time
GROUP BY Month, Year, Ticket_Type);
create TRIGGER TriggerForViewVM1
AFTER INSERT ON museums_tickets
FOR EACH ROW
DECLARE
N NUMBER;
VAR_DateMonth DATE;
VAR_DateYear NUMBER;
BEGIN
content_copyCOPY