When building a materialized view, you may want to postpone the loading to a later phase, for instance you install a new release, and the refresh happends every night.
BUILD DEFERRED allow you to build an empty materialized view and refresh it later. But this may still takes ages.
SQL> create materialized view mv1 build deferred as select count(*) c from emp,emp,emp,emp,emp,emp,emp;
Materialized view created.
Elapsed: 00:00:17.28
SQL> select * from mv1;
no rows selected
No data collected, but still a long time (17sec here, but much worst in real life)
A workaround is to use ON PREBUILT TABLE on an empty table, just add a few WHERE 1=0 in your subqueries
SQL> create table mv1 as select * from (select count(*) c from emp,emp,emp,emp,emp,emp,emp where 1=0) where 1=0;
Table created.
Elapsed: 00:00:00.04
SQL> create materialized view mv1 on prebuilt table as select count(*) c from emp,emp,emp,emp,emp,emp,emp;
Materialized view created.
Elapsed: 00:00:00.15
SQL> select * from mv1;
no rows selected
Elapsed: 00:00:00.00
Much faster !
very interesting !
Oracle doesn’t seem to be clever enough to “see” that
select * from (select count(*) c from emp,emp,emp,emp,emp,emp,emp) where 1=0
cannot return anything
🙂 indeed, that was my first try
especially it is crazy to see that BUILD DEFERRED is taking hours to build an empty table!
Oracle is clever enough to see, that the equivalent query
select count(*) c from emp,emp,emp,emp,emp,emp,emp where 1=0
does not return anything
does not return anything
Are you sure?
SQL> select count(*) c from emp,emp,emp,emp,emp,emp,emp where 1=0;
C
----------
0
🙂
no, I’m not