• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1985
  • Last Modified:

Additional columns in MATERIALIZED VIEW

We need to refresh data from few tables so we are planning to create a MATERIALIZED VIEW with required refresh options.

Next requirment is to add few columns which are not part of the tables included in MView.

Can we create a Mview with additional columns say Username and Password which remain null at the time of refresh and later we can update Mview for values. The other column we need to add is a sequence so new insert row can have a Primary key.

Any other work around for this?

Regards
Sachin
0
sachin_dba
Asked:
sachin_dba
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Can we create a Mview with additional columns say Username and Password which remain null at the time of refresh and later we can update Mview for values

no. a MV cannot be "updated"

>The other column we need to add is a sequence so new insert row can have a Primary key.
again, no.

now, why does it have to be a materialized view? I guess a plain table will have to do, with some triggers around to get/fetch/update the data...


0
 
sachin_dbaAuthor Commented:
We need to fetch data from 3 differtent tables with few condition where Mview is best to refresh.

But now the requirement is to leave few columns blank which need to update later and one sequence as well. How can we get this. A plain table will not be refresh autometically.

Triggers are possible on the base tables but require complete data model, any other work around?

Sachin

0
 
sujith80Commented:
>>  Username and Password which remain null at the time of refresh and later we can update Mview for values
Cant you have these stored in the table itself, update it and then refresh the MV?

>> The other column we need to add is a sequence so new insert row can have a Primary key
You can generate a "virtual" PK, but this generated value may differ from refresh to refresh depending on your data. Basically, you cannot reply on this key for joining with other tables.

See the below.
SQL> select * from tbl1;
 
        ID VAL
---------- --------------------
       100 tbl1-one
       200 tbl1-two
       400 tbl1-four
 
SQL> select * from tbl2;
 
        ID VAL
---------- --------------------
       100 tbl2-one
       200 tbl2-two
 
SQL> 
SQL> drop materialized view test_mv;
 
Materialized view dropped.
 
SQL> 
SQL> create materialized view test_mv
  2  refresh complete
  3  as 
  4  select row_number() over (order by a.id) my_pk, a.id, a.val a_val, b.val b_val, 'TEST' test_col
  5  from tbl1 a LEFT OUTER JOIN tbl2 b
  6  ON a.id = b.id;
 
Materialized view created.
 
SQL> 
SQL> select * from test_mv;
 
     MY_PK         ID A_VAL                B_VAL                TEST
---------- ---------- -------------------- -------------------- ----
         1        100 tbl1-one             tbl2-one             TEST
         2        200 tbl1-two             tbl2-two             TEST
         3        400 tbl1-four                                 TEST
 
SQL> 

Open in new window

0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now