I'm working on a PoC that emulates an MV refresh fast on statement
with an aggregate for the purpose of acting like an index (and/or an assertion)
This is being done with on prebuilt table never refresh
and having the table maintained by a Trigger or ODCIIndex intead.
The problem is: the staleness
of the MV starts off as "UNKNOWN" and any DML on the source table changes the state to "NEEDS_REBUILD"
QUESTIONS
- how do I tell Oracle to ignore the staleness?
- What hints do I need to use in order to test that the rewrite is actually working?
CODE
DB version: 23.5ai FREE (virtual box). 23ai+ needed due to reservable
column
create table meeting_attendees (
meeting_id int
,person_id int
,primary key (meeting_id, person_id)
);
create table meeting_attendees_ix (
meeting_id int primary key,
N int RESERVABLE
);
create or replace
trigger meeting_people_count
before insert or update or delete
on meeting_attendees
for each row
begin
case
when inserting then
update meeting_attendees_ix set N = N + 1 where meeting_id = :new.meeting_id;
if sql%rowcount = 0 then
insert into MEETING_ATTENDEES_IX (meeting_id,N) values ( :new.meeting_id, 1 );
end if;
when deleting then
update meeting_attendees_ix set N = N - 1 where meeting_id = :old.meeting_id;
when updating then
if :old.meeting_id = :new.meeting_id then return; end if;
update meeting_attendees_ix set N = N + 1 where meeting_id = :new.meeting_id;
if sql%rowcount = 0 then
insert into MEETING_ATTENDEES_IX (meeting_id,N) values ( :new.meeting_id, 1 );
end if;
update meeting_attendees_ix set N = N - 1 where meeting_id = :old.meeting_id;
end case;
end;
/
insert into MEETING_ATTENDEES values ( 1, 1 );
insert into MEETING_ATTENDEES values ( 1, 2 );
insert into MEETING_ATTENDEES values ( 1, 3 );
insert into MEETING_ATTENDEES values ( 2, 1 );
insert into MEETING_ATTENDEES values ( 2, 2 );
insert into MEETING_ATTENDEES values ( 2, 3 );
select * from MEETING_ATTENDEES_IX;
insert into MEETING_ATTENDEES values ( 1, 11 );
insert into MEETING_ATTENDEES values ( 1, 12 );
insert into MEETING_ATTENDEES values ( 1, 13 );
insert into MEETING_ATTENDEES values ( 2, 11 );
insert into MEETING_ATTENDEES values ( 2, 12 );
insert into MEETING_ATTENDEES values ( 2, 13 );
commit;
select * from MEETING_ATTENDEES_IX;
commit;
select * from MEETING_ATTENDEES_IX;
create materialized view MEETING_ATTENDEES_IX
on PREBUILT table with reduced precision
never REFRESH
enable query rewrite
AS
select meeting_id, count(*) N
from MEETING_ATTENDEES
group by meeting_id;
select mview_name, STALENESS from USER_MVIEWS;
insert into MEETING_ATTENDEES values ( 3, 2 );
commit;
select mview_name, STALENESS from USER_MVIEWS;