materialized view + explain plan

[riki]

Hello All,

I have a set of queries which perform aggregate operations on a single table .The performance of the query(s) is very bad and no other option has worked .
So I have created a materialized view on the table.
Till now its fine but
The problem is when I execute the query and run the explain plan on it the MV is not getting picked up.
There is a FULL TABLE SCAN going on.

I have tried the MV with REFRESH ON COMMIT and REFRESH ON DEMAND and other options as well.Nothing works.
Any suggestions on what to be done .....


The MV is written as :

CREATE MATERIALIZED VIEW mv_test_table
PCTFREE 0
STORAGE (initial 8k next 8k pctincrease 0)
BUILD IMMEDIATE
REFRESH ON COMMIT
ENABLE QUERY REWRITE
AS
SELECT(clo1a,colb,...)
from test_table
group by cola;

Thanks,
riki

[BV1963]

What is valie ofr query_rewrite_enabled ?

[riki]

yes it is enabled.

[gopu_g]

Can you post the queries which perform aggregate operations
and post the explain plan also..

[PAVB]

Just a couple of notes.

1- MVs are ignored if being refresh at the time the query is issued; since your MV is set to refresh on commit the likelyhood of this to happen would be a function of the activity in your base table. I would test it by creating the MV as refresh on demand.

2- MV would be taken into consideration if the MV structure is aligned to what the query is looking for. Please post your actual MV creation statement and your actual query.

3- MV would be taken into consideration if Oracle finds it is cheaper to use it rather than using the base table. How many rows do you have on your base table?... how many rows do you have on your MV?

[riki]

Hello PAVB,

Thank you for your suggestions.
The MV is picking up.

Thanks,
riki