BUILD DEFERRED takes ages

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 !

5 thoughts on “BUILD DEFERRED takes ages”

  1. 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

  2. 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

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>