change fields in materialized view - oracle

Question

Goodnight,

I have a working materialized view,to which I will add 15 left join similar to the ones in the example lines below, is there a way to edit it and hide the rowids?,If I replace the rowid with primary key, could I omit them?

because I would have to put 15 additional rowids, and it would be visually impractical. is there a way to make it only show the fields I want and not the rowsid,

How else could I change it to not have unnecessary columns, is there a way to handle incremental loads without having to do all a complex stored procedures? this way is quite simple for handling incremental loads, it is mandatory that the materialized view be incremental (refresh fast)

thank you very much. Regards.

create materialized view vm_prueba2

refresh fast on demand

with rowid as

select 
   o.rowid o_rowid,
   c.rowid c_rowid,
   e.rowid e_rowid,
   ordenid,
   o.empleadoid,
   o.clienteid,
   fechaorden,
   descuento,
   nvl(c.desccripcion,'') as ddesc,
   e.desccripcion

from ordenes o, tabla_hija c, tabla_hija e

where
 
  ( o.clienteid=c.valor(+) and c.id_tabla=1 or c.valor is null ) and
   ( o.empleadoid=e.valor(+) and e.id_tabla=2 or e.valor is null )

user_9097906 · Answer

create a materialized view, with all the rowids
create a view on a top on that materialized view which shows only columns you want
- that way, you'd "hide" everything you don't need (rowids, right?)
  - which also means that you'd actually use the view in selects, not the materialized view
- materialized view refresh would work as usual

For example:

create materialized view vm_prueba2
  refresh fast on demand
  with rowid as
select 
   o.rowid o_rowid,
  ...
from ...;

-- a view would select only some columns from the materialized view:
create or replace view v_vm_prueba2 as
  select empleadoid,
         clienteid,
         fechaorden
from vm_prueba2;

change fields in materialized view - oracle

1 Answers