Home » SQL & PL/SQL » SQL & PL/SQL » Problem with Materialized View Query Rewrite.
Problem with Materialized View Query Rewrite. [message #404501] Fri, 22 May 2009 00:45 Go to next message
ramabhupal
Messages: 10
Registered: April 2009
Location: hyd
Junior Member
Hi All,

I have a an empty table EMP_EMPTY with there columns DEPTNO,JOB,SAL. and i have a one more table EMP_TEST with four columns DEPTNO,JOB,SAL,GID which contains the preaggregated data along with grouping id(GID).

Now i have registered a materialized view on EMP_TEST by querying with EMP_EMPTY. So if query EMP_EMPTY with some aggregation level it fetches the data from EMP_TEST if it exists or else it returns 0 rows.



SQL> select * from machrabh.emp_empty;

no rows selected

SQL> desc machrabh.emp_empty;
Name Null? Type
----------------------------------------------------------------------- -------- ------------------------------------------------
DEPTNO NOT NULL NUMBER(38)
JOB NOT NULL VARCHAR2(30)
SAL NOT NULL NUMBER

/**** Here i inserted the recoreds for each dept and job and also for all depts and job (i.e for grouping id 3, 0).
*******/

SQL> SELECT * FROM EMP_TEST;

DEPTNO JOB SAL GID
---------- --------- ---------- ----------
0 0 51025 3
10 CLERK 24150 0
20 ANALYST 8000 0
30 MANAGER 8275 0
40 SALESMAN 5600 0
50 PRESIDENT 5000 0

6 rows selected.

SQL> DROP MATERIALIZED VIEW MACHRABH.EMP_TEST;

Materialized view dropped.

SQL>
SQL> CREATE MATERIALIZED VIEW MACHRABH.EMP_TEST
2 ON PREBUILT TABLE WITH REDUCED PRECISION
3 REFRESH FORCE ON DEMAND
4 WITH PRIMARY KEY
5 ENABLE QUERY REWRITE
6 AS
7 SELECT deptno,
8 job,
9 COUNT (DISTINCT sal) sal,
10 GROUPING_ID (deptno, job) GID
11 FROM machrabh.emp_empty
12 GROUP BY GROUPING SETS ( (deptno,job),(deptno), ());

Materialized view created.

SQL> alter session set query_rewrite_enabled=force;

Session altered.

SQL> alter session set query_rewrite_integrity=stale_tolerated;

Session altered.

SQL> alter session set optimizer_mode=all_rows;

Session altered.

SQL> set autotrace on explain;
SQL> set serveroutput on size 1000000;
SQL> set lines 130;

/****** The following is working as expected. It is rewriting with MV and giving the expected results.. **********/

SQL> select deptno,job,count(distinct sal) from machrabh.emp_empty group by deptno,job;

DEPTNO JOB COUNT(DISTINCTSAL)
---------- --------- ------------------
10 CLERK 24150
20 ANALYST 8000
30 MANAGER 8275
40 SALESMAN 5600
50 PRESIDENT 5000


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=5 Bytes=80)
1 0 MAT_VIEW REWRITE ACCESS (FULL) OF 'EMP_TEST' (MAT_VIEW REW
RITE) (Cost=3 Card=5 Bytes=80)


/****** The following is working as expected. Since there is no data emp_test at deptno level **********/

SQL> select deptno,count(distinct sal) from machrabh.emp_empty group by deptno;

no rows selected


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=10)
1 0 MAT_VIEW REWRITE ACCESS (FULL) OF 'EMP_TEST' (MAT_VIEW REW
RITE) (Cost=3 Card=1 Bytes=10)


/****** The following is not working as expected. and Here i am expecting 51025 **********/

SQL> select count(distinct sal) from machrabh.emp_empty;

COUNT(DISTINCTSAL)
------------------
0


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=13)
1 0 SORT (GROUP BY)
2 1 TABLE ACCESS (FULL) OF 'EMP_EMPTY' (TABLE) (Cost=2 Card=
1 Bytes=13)




SQL>

Here i have changed Materialized view defintion.. Just i have removed the (deptno) grouping set from the definition and recreated it.

SQL> DROP MATERIALIZED VIEW MACHRABH.EMP_TEST;

Materialized view dropped.

SQL>
SQL> CREATE MATERIALIZED VIEW MACHRABH.EMP_TEST
2 ON PREBUILT TABLE WITH REDUCED PRECISION
3 REFRESH FORCE ON DEMAND
4 WITH PRIMARY KEY
5 ENABLE QUERY REWRITE
6 AS
7 SELECT deptno,
8 job,
9 COUNT (DISTINCT sal) sal,
10 GROUPING_ID (deptno, job) GID
11 FROM machrabh.emp_empty
12 GROUP BY GROUPING SETS ( (deptno,job), ());

Materialized view created.

SQL> alter session set query_rewrite_enabled=force;

Session altered.

SQL> alter session set query_rewrite_integrity=stale_tolerated;

Session altered.

SQL> alter session set optimizer_mode=all_rows;

Session altered.

SQL> set autotrace on explain;
SQL> set serveroutput on size 1000000;
SQL> set lines 130;
SQL>

/*****For this query there is a data in the emp_test now it is failed to rewrite. **********/

SQL> select deptno,job,count(distinct sal) from machrabh.emp_empty group by deptno,job;

no rows selected


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=43)
1 0 SORT (GROUP BY) (Cost=3 Card=1 Bytes=43)
2 1 TABLE ACCESS (FULL) OF 'EMP_EMPTY' (TABLE) (Cost=2 Card=
1 Bytes=43)


/*****Ofcourse it is same as above... **********/

SQL> select count(distinct sal) from machrabh.emp_empty;

COUNT(DISTINCTSAL)
------------------
0


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=13)
1 0 SORT (GROUP BY)
2 1 TABLE ACCESS (FULL) OF 'EMP_EMPTY' (TABLE) (Cost=2 Card=
1 Bytes=13)




My Question is For the red colored Queries, I am expecting oracle to rewrite with materialized view instead of table EMP_EMPTY.

So Please let me know the reasons failed to rewrite with materialized view.

Thanks In Advance.

Thanks,
Ram.

Re: Problem with Materialized View Query Rewrite. [message #404584 is a reply to message #404501] Fri, 22 May 2009 06:48 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I suspect that it's the way that you're grouping in the MV.

Re: Problem with Materialized View Query Rewrite. [message #404607 is a reply to message #404584] Fri, 22 May 2009 07:51 Go to previous messageGo to next message
ramabhupal
Messages: 10
Registered: April 2009
Location: hyd
Junior Member
Hi,

First, Thanks for your response..

But If you see there is data at GROUPING_ID(DEPTNO,JOB) AND GROUPING_ID() . So if i run the follwing statements it has to fetch the data from EMP_TEST.

1) select deptno,job,count(distinct sal) from emp group by deptno,job;

2) select count(distinct sal) from emp ;

Please let me know if any details required..

Thanks,
Ram.
Re: Problem with Materialized View Query Rewrite. [message #404613 is a reply to message #404607] Fri, 22 May 2009 08:07 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You're missing my point.

Query rewrite isn't driven off whether or not the data exists.

It's driven by whether the CBO thinks that the query that is being analyzed is sufficiently similar to the query that the MV is built on to allow it to simply substitute a select from theMV.

In this case you'd need to get as subset of the rows from the MV to use for the query rewrite (the ones providing the data grouped by deptno and job), and I reckon that that's beyond the CBO's level of understanding.
Re: Problem with Materialized View Query Rewrite. [message #404847 is a reply to message #404501] Sun, 24 May 2009 23:35 Go to previous message
ramabhupal
Messages: 10
Registered: April 2009
Location: hyd
Junior Member
Hi,

You are absolutely right..Even i am expecting the same..

I have created the MV with GROUP BY GROUPING SETS ( (deptno,job), ());

It is equal to

select deptno,job,count(distinct sal) from emp_empty group by deptno,job
union all
select null,null,count(distinct sal) from emp_empty

So i am expecting it should satisfy the following two queries.But it is failed..
1) select deptno,job,count(distinct sal) from emp_empty group by deptno,job
and also
2)select count(distinct sal) from emp_empty;


But if you see when i was tried MV with GROUP BY GROUPING SETS((deptno,job) , (deptno), ())

It was able to rewrite for the following queries.

1) select deptno,job,count(distinct sal) from emp_empty group by deptno,job

2) select deptno,count(distinct sal) from emp_empty group by deptno


But it is failed

3) select count(distinct sal) from emp_empty;

I would like to know the reason that is stopping to rewrite..

Thanks In Advance...


Thanks,
Ram.
Previous Topic: error access to database
Next Topic: Data Type Issue
Goto Forum:
  


Current Time: Wed Dec 07 13:03:25 CST 2016

Total time taken to generate the page: 0.17764 seconds