Materialized view with Oracle

Question

Hello !

I have the following line in a script and I don't understand what the "using" part is used for.
I couldn't find anything on google. Anybody familiar with that ?
Thanks a lot !!

CREATE MATERIALIZED VIEW "PVTRNDM"."DM_MVW_DAILY_CAL" 
 USING ("DM_MVW_DAILY_CAL", 
        (8, 'PLANVP.XXXX.INT', 1, 0, 0, "PVTRN", "DAILY_CAL", '2009-10-15 16:12:25', 8, 45073, '2009-10-15 16:12:25', '', 1, '0E', 6548400, 0, NULL), 
        2101313, 8, ('1950-01-01 12:00:00', 111, 0, 0, 6548400, 0, 2054, 2, NULL, NULL)) 
 REFRESH FORCE AS 
 select day_date, cal , sum(NVL(daily_du, 0)) AS daily_du FROM PVPROD.daily_cal GROUP BY day_date, cal ;

Answer 1

Hi,

the USING is somekind of JOIN.

Use "USING" for Joins

Answer 2

Their is a USING INDEX clause

Answer 3

Well, this was interesting. I took the code and plugged it into toad and made a few changes:

CREATE MATERIALIZED VIEW mv_jfhtesting USING ("mv_jfhtesting",("hi","by"))
REFRESH FORCE AS select "this","that" from dual

This resulted in: ORA-12037 - Unknown Export Format Which, when I looked it up was:

Cause: An attempt was made to import a materialized view exported by an unknown export version (e.g., from a newer release than the importing site)

Action: Re-export the file using a version of export known by the importing site.

So, my guess is that this clause imports data from another MV. (or even itself, maybe?) Sorry I don't have time to play with this more

Answer 4

Odd. Do you have the object in a database, and if so, what does DBMS_METADATA.GET_DDL give you for it. If that format of the SQL isn't there, I suspect someone has done an export of the schema and copied the SQL out of the DUMP file.

Answer 5

I think I've figured it out (or at least have a suggestion). I did an export from a user that had a REFRESH ON DEMAND materialized view, then I tried importing that into a user that had CREATE TABLE, but not CREATE MATERIALIZED VIEW privileges.

It created a table of that name, but errored out on the materialized view. My guess is that when doing an export, it exports the data in the MV as a table. This odd syntax for CREATE MATERIALIZED VIEW turns that table into an MV. I'd guess the dates are to do with when the MV was last refreshed on the source database (which may be relevant if there MV logs that can be applied for the next refresh).