Re: Questions about Materialized View

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 6 Mar 2003 09:47:10 GMT
Message-ID: <b475eu$1t79u5$1@ID-82536.news.dfncis.de>

> Hi,
>
> I am pretty new to Oracle :-)
>
> How do I figure out if I am querying a regular table or a materialized
> view?
> e.g.: SELECT myID FROM myTable ...
>
> Is there any SQL command to tell if "myTable" is a regular table or a
> materialize view?
>
> Can I create an index on any column of a materialized view like if it
> was a regular table?
> e.g.: CREATE INDEX ...
>
> Thanks in advance,
> Bombadil.

Use good ol' explain plan.

First, make sure your user has been granted QUERY REWRITE. (grant QUERY REWRITE to <user>).

Create a table

    create table t_ (

      a number,
      b varchar2(10)

    );

fill it

    begin

      for i in 1 .. 1000 loop
        insert into t_ values (
          mod(i,trunc(dbms_random.value(1,i))),
          dbms_random.string('a',10));
      end loop;

    end;
    /

    commit;

    begin
    dbms_stats.gather_table_stats(

      user,'T_',
      method_opt=>'FOR ALL COLUMNS');

    end;
    /

create a materialized view

    create materialized view t_mat_

      build immediate
      refresh on commit
      enable query rewrite
      as
      select count(*),a from t_ group by(a);
    

    begin
    dbms_stats.gather_table_stats(

      user,'T_MAT_',
      method_opt=>'FOR ALL COLUMNS');

    end;
    /

Make sure the materialized view is not used by setting query_rewrite_enabled to false:

    alter session set query_rewrite_enabled=false;

Explain the plan

    @dp

    explain plan for
      select count(*),a from t_ group by(a);     @ep

that was expected:     

SELECT STATEMENT ()
 SORT (GROUP BY)
  TABLE ACCESS (FULL) T_ now, alter the session so that it hopefully uses the materialized view:

    alter session set query_rewrite_enabled=true;     alter session set query_rewrite_integrity=enforced;

and explain the plan again:

    @dp
    explain plan for
      select count(*),a from t_ group by(a);     @ep

and indeed, t_mat_ is used:

SELECT STATEMENT ()
 TABLE ACCESS (FULL) T_MAT_ hth

Rene Nyffenegger

-- 
  Projektleitung und Entwicklung in Oracle/C++/C# Projekten
  http://www.adp-gmbh.ch/cv.html

Received on Thu Mar 06 2003 - 03:47:10 CST