Oracle Database Discussions

Hello everybody,

I'm currently learning how to use Oracle (10G Enterprise) and in particular, Materialized Views.

I seem to have a problem making the optimizer use a materialized view. I have already set the OPTIMIZER_MODE, QUERY_REWRITE_ENABLED and QUERY_REWRITE_INTEGRITY as needed.

I need to create a materialized view for the following query:

Q1:
SELECT PS_SUPPKEY, PS_PARTKEY, PS_SUPPCOST
FROM PARTSUPPLIER E, PART WHERE PS_PARTKEY=P_PARTKEY and (lower(P_COMMENT) LIKE ''_o_a\%'' or lower(P_COMMENT) LIKE ''_o_u\%'')
and PS_SUPPCOST =
(SELECT min( PS_SUPPCOST)
FROM PARTSUPPLIER I
WHERE E.PS_PARTKEY=I.PS_PARTKEY)'

I created it using the following code:

CREATE MATERIALIZED VIEW mv_q1
ENABLE QUERY REWRITE
AS SELECT PS_SUPPKEY, PS_PARTKEY, PS_SUPPCOST
FROM PARTSUPPLIER E JOIN PART ON (PS_PARTKEY=P_PARTKEY)
WHERE lower(P_COMMENT) LIKE '_o_a%' or lower(P_COMMENT) LIKE '_o_u%'
and PS_SUPPCOST=
(SELECT min( PS_SUPPCOST)
FROM PARTSUPPLIER I
WHERE E.PS_PARTKEY=I.PS_PARTKEY);

I have created the statistics using:

execute dbms_stats.gather_table_stats('frandres',' mv_q1');
execute dbms_stats.gather_table_stats('frandres','PARTSUPPLIER');
execute dbms_stats.gather_table_stats('frandres','PART');

Both partsupplier and part are tables and not views.

When executing Q1, the plan does not use the materialized view. Furthermore, when using explain rewrite:

DECLARE
qrytxt VARCHAR2(3000) := 'SELECT PS_SUPPKEY, PS_PARTKEY, PS_SUPPCOST
FROM PARTSUPPLIER E, PART WHERE PS_PARTKEY=P_PARTKEY and (lower(P_COMMENT) LIKE ''_o_a\%'' or lower(P_COMMENT) LIKE ''_o_u\%'')
and PS_SUPPCOST =
(SELECT min( PS_SUPPCOST)
FROM PARTSUPPLIER I
WHERE E.PS_PARTKEY=I.PS_PARTKEY)';
BEGIN
dbms_mview.EXPLAIN_REWRITE
(qrytxt,'MV_Q1','MV_Q1');
END;
/

I get the following message:

MESSAGE
--------------------------------------------------------------------------------
QSM-01150: query did not rewrite
QSM-01263: query rewrite not possible when query references a dictionary table o
r view

QSM-01219: no suitable materialized view found to rewrite this query

What I can't understand is why it says I am referencing the dictionary or a view?

If I remove the (lower(P_COMMENT) LIKE ''_o_a\%'' or lower(P_COMMENT) LIKE ''_o_u\%'') condition to the query (using the same materialized view), I get the following message from EXPLAIN_REWRITE:


MESSAGE
--------------------------------------------------------------------------------
QSM-01150: query did not rewrite
QSM-01219: no suitable materialized view found to rewrite this query

Which is reasonable.

I don't know if the like condition is messing up my materialized view. Can anyone please help?

Thanks a lot in advance.

Edited by: user12072111 on Oct 29, 2009 9:43 PM