query rewrite question

From: Eric Spear <espear_at_deans.umd.edu>
Date: Wed, 23 Jun 1999 16:48:35 -0400
Message-ID: <37714822.AB2FF2CF@deans.umd.edu>

I'm having trouble getting usefulness out of the query rewrite option. I'm running 8.1.5 on Solaris.

My base "detail" table is called um_employee_detail. My materialized view is called um_employee_fact. It is defined here:

create materialized view um_employee_fact nologging parallel
build immediate
refresh complete on demand
enable query rewrite as
select term,

       a.employee_demographics_key,
       a.hr_dept_Cd,
       count(*) as cnt,
       sum(fte) fte,
       sum(annual_salary) annual_salary
from   um_employee_detail a,
       um_employee_org b,
       um_employee_demographics c

where a.hr_dept_cd = b.hr_dept_cd
and a.employee_demographics_key = c.employee_demographics_key group by

       term,
       a.employee_demographics_key,
       a.hr_dept_Cd

;

Okay. If I run this query,

select sum(annual_salary)
from um_employee_detail a,
um_employee_org b,
um_employee_demographics c
where a.hr_dept_cd = b.hr_dept_cd
and a.employee_demographics_key = c.employee_demographics_key

No problem, the plan is:

SELECT STATEMEN

 SORT           AGGREGATE
  SORT          AGGREGATE
   TABLE ACCESS FULL            UM_EMPLOYEE_FACT

As you can see, the query rewrite worked and the optimizer will use the materialized view um_employee_fact instead of um_employee_detail. Now, I throw a limit on one of the requested fields, term = 199808:

select sum(annual_salary)
from um_employee_detail a,
um_employee_org b,
um_employee_demographics c
where a.hr_dept_cd = b.hr_dept_cd
and a.employee_demographics_key = c.employee_demographics_key and term = '199808';

Now suddenly the query rewrite won't use the materialized view!

SELECT STATEMEN
 SORT AGGREGATE
  NESTED LOOPS
   NESTED LOOPS

    TABLE ACCES BY INDEX ROWID  UM_EMPLOYEE_DETAIL
     INDEX      RANGE SCAN      UM_EMP_DETAIL_NDX1
    INDEX       UNIQUE SCAN     UM_EMPLOYEE_ORG_PK
   INDEX        UNIQUE SCAN     UM_DEM_EMP_PK

Why?

I made sure to index the term field of the materialized view:

create bitmap index um_emp_fact_ndx6 on um_employee_fact(term)
tablespace umindex
nologging;

And everything has been analyzed.

Please help.
Thanks. Received on Wed Jun 23 1999 - 15:48:35 CDT