select query isn't using materialized view-CodePudding

I am using Oracle 10g Entreprise Edition, I wrote this query:

Select dc.codetypecl, dc.libtypecl, sum(ft.nbtransactions) as nb_transactions
from ftransaction ft, dclient dc
where (ft.codecl=dc.codecl)
and dc.libtypecl='Entreprise'
group by dc.codetypecl, dc.libtypecl, ft.nbtransactions;

Then created this materialized view to execute the first query for the second time:

CREATE MATERIALIZED VIEW VMTCL
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
enable query rewrite
AS
select dc.codetypecl, dc.libtypecl, sum(ft.nbtransactions) as nb_transactions
from ftransaction ft, dclient dc
where (ft.codecl=dc.codecl)
group by dc.codetypecl, dc.libtypecl, ft.nbtransactions;

Execute DBMS_MVIEW.REFRESH('VMTCL');

alter system flush shared_pool;
alter system flush buffer_cache;

Select dc.codetypecl, dc.libtypecl, sum(ft.nbtransactions) as nb_transactions
from ftransaction ft, dclient dc
where (ft.codecl=dc.codecl)
and dc.libtypecl='Entreprise'
group by dc.codetypecl, dc.libtypecl, ft.nbtransactions;

Everything had been executed correctly without any errors, the problem is that the second query isn't using the materialized view VMTCL, this is the plan I get:

Plan hash value: 1387742792
--------------------------------------------------------------------------------------------
| Id  | Operation           | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |              |     2 |    56 |       | 11689   (2)| 00:02:21 |
|   1 |  HASH GROUP BY      |              |     2 |    56 |       | 11689   (2)| 00:02:21 |
|*  2 |   HASH JOIN         |              |  1374K|    36M|    12M| 11625   (1)| 00:02:20 |
|*  3 |    TABLE ACCESS FULL| DCLIENT      |   400K|  7815K|       |  1821   (1)| 00:00:22 |
|   4 |    TABLE ACCESS FULL| FTRANSACTION |  2665K|    20M|       |  6648   (1)| 00:01:20 |
--------------------------------------------------------------------------------------------

In the column Name there's no VMTCL, I couldn't understand why or how to fix this problem. Thank you for your help.

I tried to execute a query after creating a materialized view, I didn't find the name of this materialized view in the column Name of the exection plan, the query execution isn't using this view even if everything is executed correctly without errors.

CodePudding user response：

Of course it does not use the materialized view as you have told the SQL engine to select from the underlying tables and not from the materialized view. If you want to use the materialized view then use:

SELECT *
FROM   vmtcl
WHERE  libtypecl='Entreprise'

As an aside, are you sure you want to include ft.nbtransactions in the GROUP BY clause as that is the column you are aggregating?

If you do have it then your query is effectively:

SELECT dc.codetypecl,
       dc.libtypecl,
       ft.nbtransactions * COUNT(*) as nb_transactions
FROM   ftransaction ft
       INNER JOIN dclient dc
       ON (ft.codecl=dc.codecl)
WHERE  dc.libtypecl='Entreprise'
GROUP BY
       dc.codetypecl,
       dc.libtypecl,
       ft.nbtransactions;

And I would not think that was the intended behaviour.