Oracle12 Materialized Views only latest records-CodePudding

Say you have a table of car owners (car_owners) :
car_id
person_id
registration_date ...

And each time someone buy's a car there is a record inserted into this table.

Now I would like to create a materialized view that only holds the newest registration for each vehicle, that is when a record is inserted, the materialized view updates the record for this vehicle (if it exists) with the new record from the base table. The materialized view only hold one record per car.

I tried something like this

create materialized view newest_owner
build immediately
refresh force on commit
select *
  from car_owners c
 where c.registration_date = (
         select max(cc.registration_date)
           from car_owners cc
          where cc.car_id = c.car_id
       );

It seems that materialized view do not like sub-selects.

Do you have any tips on how to do this or how to achieve this another way? I have another solution for now, use triggers to update a separate table to hold the newest values, but I was hoping that materialized view could do the trick.

Thanks.

CodePudding user response：

For this, you may have to use nested materialized views:

create table car_owners 
(pk_col number primary key
,car_id  number 
,person_id number 
,registration_date date
);
truncate table car_owners;
insert into car_owners
select rownum
      ,trunc(dbms_random.value(1,1000)) car_id
      ,mod(rownum,100000) person_id
      ,(sysdate-dbms_random.value(1,3000)) registration_date
from dual 
connect by rownum <= 1000000;
commit;
exec dbms_stats.gather_Table_stats(null,'car_owners')

create materialized view log on car_owners with sequence, rowid
(car_id, registration_date) including new values;

create materialized view latest_registration
refresh fast on commit enable query rewrite 
as
select c.car_id
    ,max(c.registration_date) max_registration_date
from car_owners c
group by c.car_id
/

create materialized view log on latest_registration with sequence, rowid
(car_id, max_registration_date) including new values;

create materialized view newest_owner
refresh fast on commit enable query rewrite 
as
select c.rowid row_id,cl.rowid cl_rowid, c.pk_col, c.car_id, c.person_id, c.registration_date
  from car_owners c
  join latest_registration  cl
   on   c.registration_date = cl.max_registration_date
  and   c.car_id = cl.car_id
/
select * from newest_owner where car_id = 25;

ROW_ID             CL_ROWID               PK_COL     CAR_ID  PERSON_ID REGISTRAT
------------------ ------------------ ---------- ---------- ---------- ---------
AAAUreAAMAAD/IxABS AAAUriAAMAAD TNACE     644158         25      44158 09-APR-22

insert into car_owners values (1000001, 25,-1,sysdate);
commit;
select * from newest_owner where car_id = 25;

ROW_ID             CL_ROWID               PK_COL     CAR_ID  PERSON_ID REGISTRAT
------------------ ------------------ ---------- ---------- ---------- ---------
AAAUreAAMAAD/pLAB1 AAAUriAAMAAD TNACE    1000001         25         -1 22-APR-22

explain plan for 
select c.rowid row_id,cl.rowid cl_rowid, c.pk_col, c.car_id, c.person_id, c.registration_date
  from car_owners c
  join latest_registration  cl
   on   c.registration_date = cl.max_registration_date
  and   c.car_id = cl.car_id
/
select * from dbms_xplan.display();

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |   999 | 41958 |     3   (0)| 00:00:01 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| NEWEST_OWNER |   999 | 41958 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Have a read of the docs: https://docs.oracle.com/en/database/oracle/oracle-database/21/dwhsg/basic-materialized-views.html#GUID-E087FDD0-B08C-4878-BBA9-DE56A705835E https://docs.oracle.com/en/database/oracle/oracle-database/21/dwhsg/basic-materialized-views.html#GUID-179C8C8A-585B-49E6-8970-09396DB53DE3 there are some restrictions that can slow down your refreshes (eg deletes).