SQL Snippets ™: Tutorials for Oracle Developers

When a materialized view is created Oracle may add system generated indexes to its underlying table (i.e. the table containing the results of the query, not to be confused with a base table). In the following example note how Oracle automatically adds an index to implement the system generated primary key we saw in the preceding topic, Constraints.

create materialized view mv
  as select key, val from t
;

column index_name  format a15
column column_name format a15

select
  index_name ,
  i.uniqueness ,
  ic.column_name
from
  user_indexes i
    inner join user_ind_columns ic
    using ( index_name )
where
  i.table_name = 'MV'
;
 

INDEX_NAME      UNIQUENES COLUMN_NAME
--------------- --------- ---------------
SYS_C0019959    UNIQUE    KEY
 

In the next example Oracle automatically generates a function based index.

drop materialized view mv ;

create materialized view log on t2
  with primary key, rowid, sequence ( t_key, amt )
  including new values
;

create materialized view mv
  refresh fast on commit
  as
    select t_key, COUNT(*) ROW_COUNT
    from t2
    group by t_key
;

column column_expression format a35

select
  index_name ,
  i.uniqueness ,
  ic.column_name ,
  ie.column_expression
from
  user_indexes i
    inner join user_ind_columns ic
      left outer join user_ind_expressions ie
      using ( index_name )
    using ( index_name )
where
  ic.table_name = 'MV'
;
 

INDEX_NAME      UNIQUENES COLUMN_NAME     COLUMN_EXPRESSION
--------------- --------- --------------- -----------------------------------
I_SNAP$_MV      UNIQUE    SYS_NC00003$    SYS_OP_MAP_NONNULL("T_KEY")
 

(Note that SYS_OP_MAP_NONNULL is an undocumented Oracle function. Do not attempt to use it in your own code. See Nulls and Equality: SQL Only for additional info.)

Adding Your Own Indexes

We can add out own indexes to MV just as we would a regular table. In the following example we will add an index on the T_KEY column.

create index MY_INDEX on mv ( T_KEY ) ;

select
  index_name ,
  i.uniqueness ,
  ic.column_name
from
  user_indexes i
    inner join user_ind_columns ic
    using ( index_name )
where
  i.table_name = 'MV'
;
 

INDEX_NAME      UNIQUENES COLUMN_NAME
--------------- --------- ---------------
I_SNAP$_MV      UNIQUE    SYS_NC00003$
MY_INDEX        NONUNIQUE T_KEY
 

To confirm that Oracle uses our index in queries let's turn SQL*Plus's Autotrace feature on and execute a query.

set autotrace on explain
set linesize 95

select *
from   mv
where  t_key = 2 ;

     T_KEY  ROW_COUNT
---------- ----------
         2          2

Execution Plan
----------------------------------------------------------
Plan hash value: 2793437614

-------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |     1 |    26 |     2   (0)| 00:00:01 |
|   1 |  MAT_VIEW ACCESS BY INDEX ROWID| MV       |     1 |    26 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN             | MY_INDEX |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T_KEY"=2)

Note
-----
   - dynamic sampling used for this statement

 

Note how the optimizer chose an INDEX RANGE SCAN from MY_INDEX in step 2.

Cleanup

drop materialized view mv ;

drop materialized view log on t2 ;