Thanks for the question, Christian.

Asked: September 27, 2019 - 12:59 pm UTC

Last updated: October 02, 2019 - 3:24 pm UTC

Version: 12.1.0.2.0

Viewed 1000+ times

You Asked

We are using USER_DEPENDENCIES in order to generate up-to-date documentation of our application ETL layer in DOT-language style (see graphviz) which is being added as dynamic dependency graphs into our internal WIKI. While checking the resulting graph we identified an issue with the reported dependencies for materialized views (MV). Whenever a MV is selecting from another view, the dependencies of the view are shown as dependencies of the MV as well (observed in 12.1.0.2.0).

NOTE: The issue reported in https://asktom.oracle.com/pls/apex/asktom.search?tag=mview-dependencies refers to missing dependencies.

Test setup (see https://livesql.oracle.com/apex/livesql/s/i0pvsa3a1lyi6wu7ufykw06ok ):

create table t1 (c1 int);
create view v1 as select * from t1;
create materialized view v2 as select * from v1;

Querying the dependencies from USER_DEPENDENCIES

select name, type, referenced_name, referenced_type from user_dependencies where name in ('T1','V1','V2') order by name desc, referenced_name desc;

Result:

NAME,TYPE,REFERENCED_NAME,REFERENCED_TYPE
V2, MATERIALIZED VIEW, V2, TABLE
V2, MATERIALIZED VIEW, V1, VIEW
V2, MATERIALIZED VIEW, T1, TABLE    -- this dependencies seems to be wrong for me
V1, VIEW,              T1, TABLE

When using a view instead of a materialized view, the dependencies are as expected:

create view v2b as select * from v1;
select name, type, referenced_name, referenced_type from user_dependencies where name in ('T1','V1','V2B') order by name desc, referenced_name desc;

Result:

NAME,TYPE,REFERENCED_NAME,REFERENCED_TYPE
V2B, VIEW, V1, VIEW
V1,  VIEW, T1, TABLE

* Isn't the first result wrong?
* Is there any way to hinder showing dependencies of underlying views as dependencies of the MV?

with LiveSQL Test Case:

https://livesql.oracle.com/apex/livesql/s/i0pvsa3a1lyi6wu7ufykw06ok

and Chris said...

This is expected behaviour.

The database needs to know when you change the data a materialized view uses. So it has a dependency right down to the base tables uses. Even if you access these via a view.

This ensures it can track the freshness of an MV correctly.

Personally I'd want to keep this documented, so you know when you need to refresh an MV. Or which transactions will trigger a refresh if you have REFRESH FAST ON COMMIT.

If you must remove the tables, you could do something like:

- Find all the tables referenced in views
- Where the views are used in materialized views
- Then minus these from the MV dependencies

e.g.:

select name, type, referenced_name, referenced_type 
from   user_dependencies ud
where  name in ('V2') 
minus
select 'V2', 'MATERIALIZED VIEW', referenced_name, referenced_type  
from   user_dependencies ud
where  referenced_type = 'TABLE'
and    type = 'VIEW'
and    exists (
  select * from user_dependencies ud2
  where  ud2.referenced_name = ud.name
  and    ud2.type = 'MATERIALIZED VIEW'
);

NAME    TYPE                 REFERENCED_NAME    REFERENCED_TYPE   
V2      MATERIALIZED VIEW    V1                 VIEW               
V2      MATERIALIZED VIEW    V2                 TABLE

But this quickly gets a pain - what if you have MV -> VIEW -> VIEW -> VIEW -> TABLE?

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Another approach to get the dependency

Christian Zahl, October 02, 2019 - 1:42 pm UTC

Thanks Chris for the detailed answer and solution to get the dependency graph. As we are having quiet complex and layered view schema, we are getting into the issue with MV selecting from a views which selects from a view...

For anyone who is interested find below a different approach to limit the dependencies to the direct ones. The idea is to check if the reverenced view / table is also listed in the SQL query of the MV:

CREATE OR REPLACE FUNCTION mv_query(mv char) RETURN VARCHAR2 IS
  vc VARCHAR2(4000);
BEGIN
  SELECT query INTO vc FROM user_mviews WHERE mview_name=mv;
  RETURN vc;
END;
/

SELECT
 name, type, referenced_name, referenced_type
FROM
 ALL_DEPENDENCIES
WHERE
    type <> 'MATERIALIZED VIEW'
  OR REGEXP_LIKE (mv_query(d.name), '[, ^.(' || chr(10) || chr(9) || ']' || referenced_name || '($|[, $)' || chr(10) || chr(9) || '])', 'i')
START WITH
 d.owner=user
 AND d.NAME = 'V2'
CONNECT BY NOCYCLE
 PRIOR d.REFERENCED_NAME = d.NAME
 AND PRIOR d.REFERENCED_OWNER = d.OWNER
GROUP BY
 name, referenced_name, type, referenced_type
ORDER BY
 name, referenced_name, type, referenced_type
;

It's a little bit tricky as
- the query is of type LONG
- which cannot be directly used in the REGEXP_LIKE function, therefore we need the conversion function
- and we do not have an abstract syntax tree for the query
- and therefore have to search for the referenced_name as a "word" anywhere in the query
This is also not 100% bulletproof but provided very good results in our environment.

When replacing the example so that
- MV V2 selects from view V1
- view V1 selects from view V0
- view V0 selects from table T1
and running the query we get now what we want:

NAME  TYPE               REFERENCED_NAME  REFERENCED_TYPE
V0    VIEW               T1               TABLE
V1    VIEW               V0               VIEW
V2    MATERIALIZED VIEW  V1               VIEW

October 02, 2019 - 3:24 pm UTC

Nice work, thanks for sharing!

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Another approach to get the dependency

More to Explore

SQL

Too many USER_DEPENDENCIES for materialized views?

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Another approach to get the dependency

More to Explore

SQL