Re: Materialized Views Driving Me Mad!

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 9 Apr 2002 08:34:28 -0700
Message-ID: <a8v1m401dv0@drn.newsguy.com>

In article <f7859e6f.0204090625.2771d9cc_at_posting.google.com>, george_at_41donny.freeserve.co.uk says...
>
>I have crated a mview using the following :

[snip]

>i.e. added a filter on the "LDG_PERIOD" field from the FACT table, the
>view is not used and the query is not rewritten - even though this
>field is present in the mview.
>
>Any ideas as having read the literature over and over I don't know how
>to make it work!?
>
>George

I did your example and cannot reproduce. Now, you did not have a version so I tried in 815, 816 and 817 -- achieving same results:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create table CAESAR_DELIVERY_LINE ( company_code varchar2(10), ldg_period varchar2(10), QUANTITY_DELIVERED int, 2 EURO_REVENUE int, EURO_COST int, MATERIAL_NO int ) ;

Table created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create table M_PRODUCTS ( BUSINESS_DESC varchar2(10), MAIN_UNIT int, ITEM_ID int );

Table created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> 
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> exec dbms_stats.set_table_stats( user,
'CAESAR_DELIVERY_LINE', numrows => 1000000, numblks => 100000 );

PL/SQL procedure successfully completed.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> exec dbms_stats.set_table_stats( user, 'M_PRODUCTS', numrows => 1000000, numblks => 100000 );

PL/SQL procedure successfully completed.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> 
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> 
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> CREATE SNAPSHOT CAESAR_DELIVERY_LINE_MV

  2 REFRESH FORCE
  3 ENABLE QUERY REWRITE
  4 AS
  5 SELECT CAESAR_DELIVERY_LINE.COMPANY_CODE,   6 CAESAR_DELIVERY_LINE.LDG_PERIOD,

  7    M_PRODUCTS.BUSINESS_DESC,
  8    sum(CAESAR_DELIVERY_LINE.QUANTITY_DELIVERED) as QUANTITY_DELIVERED,
  9    sum(CAESAR_DELIVERY_LINE.EURO_REVENUE) as EURO_REVENUE,
 10    sum(CAESAR_DELIVERY_LINE.EURO_COST) as EURO_COST,
 11    sum(CAESAR_DELIVERY_LINE.QUANTITY_DELIVERED * M_PRODUCTS.MAIN_UNIT)

12 as VOLUME_DELIVERED
13 FROM
14 CAESAR_DELIVERY_LINE,
15 M_PRODUCTS
16 WHERE
17 ( M_PRODUCTS.ITEM_ID=CAESAR_DELIVERY_LINE.MATERIAL_NO ) 18 GROUP BY
19 CAESAR_DELIVERY_LINE.COMPANY_CODE, 20 CAESAR_DELIVERY_LINE.LDG_PERIOD,
21 M_PRODUCTS.BUSINESS_DESC; Materialized view created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> exec dbms_stats.set_table_stats( user, 'CAESAR_DELIVERY_LINE_MV', numrows => 100, numblks => 10 );

PL/SQL procedure successfully completed.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> alter session set query_rewrite_enabled=true;

Session altered.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> alter session set query_rewrite_integrity=trusted;

Session altered.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> 
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> set autotrace traceonly explain
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> 
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> SELECT

CAESAR_DELIVERY_LINE.COMPANY_CODE,
2 CAESAR_DELIVERY_LINE.LDG_PERIOD,

  3    M_PRODUCTS.BUSINESS_DESC,
  4    sum(CAESAR_DELIVERY_LINE.QUANTITY_DELIVERED) as QUANTITY_DELIVERED,
  5    sum(CAESAR_DELIVERY_LINE.EURO_REVENUE) as EURO_REVENUE,
  6    sum(CAESAR_DELIVERY_LINE.EURO_COST) as EURO_COST,
  7    sum(CAESAR_DELIVERY_LINE.QUANTITY_DELIVERED * M_PRODUCTS.MAIN_UNIT)

8 as VOLUME_DELIVERED
9 FROM
10 CAESAR_DELIVERY_LINE,
11 M_PRODUCTS
12 WHERE
13 ( M_PRODUCTS.ITEM_ID=CAESAR_DELIVERY_LINE.MATERIAL_NO ) 14 GROUP BY
15 CAESAR_DELIVERY_LINE.COMPANY_CODE, 16 CAESAR_DELIVERY_LINE.LDG_PERIOD,
17 M_PRODUCTS.BUSINESS_DESC; Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=100 Bytes=7300) 1 0 TABLE ACCESS (FULL) OF 'CAESAR_DELIVERY_LINE_MV' (Cost=1 Card=100 Bytes=7300)

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> SELECT CAESAR_DELIVERY_LINE.COMPANY_CODE,
2 CAESAR_DELIVERY_LINE.LDG_PERIOD,

  3    M_PRODUCTS.BUSINESS_DESC,
  4    sum(CAESAR_DELIVERY_LINE.QUANTITY_DELIVERED) as QUANTITY_DELIVERED,
  5    sum(CAESAR_DELIVERY_LINE.EURO_REVENUE) as EURO_REVENUE,
  6    sum(CAESAR_DELIVERY_LINE.EURO_COST) as EURO_COST,
  7    sum(CAESAR_DELIVERY_LINE.QUANTITY_DELIVERED * M_PRODUCTS.MAIN_UNIT)

8 as VOLUME_DELIVERED
9 FROM
10 CAESAR_DELIVERY_LINE,
11 M_PRODUCTS
12 WHERE
13 ( M_PRODUCTS.ITEM_ID=CAESAR_DELIVERY_LINE.MATERIAL_NO ) 14 AND CAESAR_DELIVERY_LINE.LDG_PERIOD = '200202' 15 GROUP BY
16 CAESAR_DELIVERY_LINE.COMPANY_CODE, 17 CAESAR_DELIVERY_LINE.LDG_PERIOD,
18 M_PRODUCTS.BUSINESS_DESC; Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=73) 1 0 TABLE ACCESS (FULL) OF 'CAESAR_DELIVERY_LINE_MV' (Cost=1 Card=1 Bytes=73)

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> set autotrace off

So, in light of the fact we don't have the full schema -- perhaps I can suggest you try this:

0) turn on sql_trace+timed_statistics

run the query with 14 AND CAESAR_DELIVERY_LINE.LDG_PERIOD = '200202'
rerun it as a query directly agains the materialized view
exit sqlplus,use tkprof to format the trace file and see whats the difference between the two

what I'm thinking is you have indexes and such on the tables that makes the optimizer say "hey, Rather then full scanning this MV, I believe I'll go after these indexes". You don't have any timings or anything -- but maybe the optimizer is doing the right thing here? Use the result from tkprof to see which query is in fact "better"

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp

Received on Tue Apr 09 2002 - 10:34:28 CDT