Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Using a Stale MV for rewrite?

Mike KutzFeb 3 2025 — edited Feb 3 2025

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

-- source table
create table meeting_attendees (
   meeting_id int
  ,person_id int
  ,primary key (meeting_id, person_id)
);

-- prebuilt MV table
create table meeting_attendees_ix (
  meeting_id int primary key,
  N int RESERVABLE
  -- add CHECK constraint for Assserting N <= <static value>
);

-- table maintenance code
-- note: Trigger shown for simplity; ODCIIndex method can handle Truncate commands
create or replace 
trigger meeting_people_count
  before insert or update or delete 
  on meeting_attendees 
  for each row
begin
    -- NOTE : insert/update is used due to MERGE-RESERVABLE bug (already posted)

    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
            -- short circut return
            if :old.meeting_id = :new.meeting_id then return; end if;

            -- update for :new
            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 for :old
            update meeting_attendees_ix set N = N - 1 where meeting_id = :old.meeting_id;
    end case;
end;
/

-- test to show insert at source does not block the emulated MV
-- session 1
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 );
-- RESERVABLE column values don't show until after commit
select * from MEETING_ATTENDEES_IX;

-- session 2
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;
-- RESERVABLE column values don't show until after commit
select * from MEETING_ATTENDEES_IX;

-- session 1
commit;

-- RESERVABLE column values don't show until after commit
select * from MEETING_ATTENDEES_IX;

-- expected results are rows: (1,6), (2,6)

-- add MV for CBO's sake
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;

-- staleness is UNKNOWN 
select mview_name, STALENESS from USER_MVIEWS;

-- DML causes MV to be stale
insert into MEETING_ATTENDEES values ( 3, 2 );
commit;
select mview_name, STALENESS from USER_MVIEWS;
This post has been answered by James Su on Feb 3 2025
Jump to Answer

Comments

chonewell Nov 12 2024

My Oracle Cloud tenant, cloud account, and secure email have no issues. Why haven't I received my password reset email for Oracle Cloud? This is very strange, and our attempts have not been able to solve the problem. May I ask who I should turn to for help?

L. Fernigrini Nov 12 2024

If your account is a paid one, open a Support ticket.

If it is a Free Tier then you will have to rely on help from the community. Most probable cause that you did not receive the password reset email is that your account has been stolen and the email has been changed.

chonewell Nov 13 2024

Thank you for your reply!
But when I chatted with the online customer service, they told me that my Oracle Cloud tenant, account, and email were all fine. So, there shouldn't be a problem of theft.
I have a free account, but who can I contact on the forum? I can only post, but no one on the forum can view my account permissions, right. I am currently trying to reset MFA, I don't know if it works.
It's quite ridiculous that I have a free account and can't enjoy any services, but how can I become a paid user if I can't log in to my account.

1 - 3

Post Details

Added on Feb 3 2025
3 comments
114 views