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
sachin_dbaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
SujithData ArchitectCommented:
>>  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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.