Home » Developer & Programmer » Data Integration » Materialized views
Materialized views [message #93361] Mon, 30 August 2004 20:36
PagInit
Messages: 9
Registered: January 2004
Junior Member
Hi,

Could any one please throw some light on my problem.

I have a Materialized view.

Create Materialized view test007
enable query rewrite as
SELECT
BOOKING_LINE.FISCAL_WEEK,
BOOKING_LINE.NET_SALES,
BOOKING_LINE.TRS_CURR,
CUSTOMER_MASTER_SOLD.CUST_ID,
CUSTOMER_MASTER_SOLD.TRD_INTRACO,         
BOOKING_LINE.NET_SALES * BOOKING_LINE.EXRT_EURO
FROM
  BOOKING_LINE,
  CUSTOMER_MASTER   CUSTOMER_MASTER_SOLD
Where
    CUSTOMER_MASTER_SOLD.CUST_ID=BOOKING_LINE.CUST_SOL  D_ID
AND CUSTOMER_MASTER_SOLD.TRD_INTRACO IN( 'TRADE','INTERCO')

 

When i used the below query , oracle rewrite the query only when
the condition (EXCHANGE_RATES_YEAR.FISCAL_YEAR)=substr((BOOKING_ LINE.FISCAL_WEEK),1,4)
is commented. When it is uncommented oracle is not using the Mat View.
May be some where i am wrong, could any one please help me to figure out my problem.

I tried with all options i.e enforced,thrusted and stale_tolerated.
I also tried by including column substr((BOOKING_LINE.FISCAL_WEEK),1,4) in the Materialized view.

SELECT
CUSTOMER_MASTER_SOLD.CUST_ID,
sum(BOOKING_LINE.NET_SALES * EXCHANGE_RATES_FLOAT_YEAR.EXCHANGE_RATE),
sum(BOOKING_LINE.NET_SALES * BOOKING_LINE.EXRT_EURO),
sum(BOOKING_LINE.NET_SALES * EXCHANGE_RATES_YEAR.EXCHANGE_RATE)
FROM
  BOOKING_LINE,
  EXCHANGE_RATES_YEAR  EXCHANGE_RATES_FLOAT_YEAR,
  EXCHANGE_RATES_YEAR,
  CUSTOMER_MASTER   CUSTOMER_MASTER_SOLD
WHERE
  EXCHANGE_RATES_YEAR.CURR_CODE=BOOKING_LINE.TRS_CUR  R
  --And (EXCHANGE_RATES_YEAR.FISCAL_YEAR)=substr((BOOKING_  LINE.FISCAL_WEEK),1,4)
  AND  (CUSTOMER_MASTER_SOLD.CUST_ID=BOOKING_LINE.CUST_SO  LD_ID)
  AND (CUSTOMER_MASTER_SOLD.TRD_INTRACO IN( 'TRADE','INTERCO'))
  AND  ( EXCHANGE_RATES_FLOAT_YEAR.CURR_CODE= BOOKING_LINE.TRS_CURR )
GROUP BY
  CUSTOMER_MASTER_SOLD.CUST_ID

 

test@orcl> select * from V$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE    9.2.0.1.0       Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

test@orcl> show parameter query
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_enabled                string      TRUE
query_rewrite_integrity              string      TRUSTED
test@orcl> delete from plan_table;

test@orcl> explain plan for
  2   SELECT
  3   CUSTOMER_MASTER_SOLD.CUST_ID,
  4   sum(BOOKING_LINE.NET_SALES * EXCHANGE_RATES_FLOAT_YEAR.EXCHANGE_RATE),
  5   sum(BOOKING_LINE.NET_SALES * BOOKING_LINE.EXRT_EURO),
  6   sum(BOOKING_LINE.NET_SALES * EXCHANGE_RATES_YEAR.EXCHANGE_RATE)
  7   FROM
  8    BOOKING_LINE,
  9    EXCHANGE_RATES_YEAR  EXCHANGE_RATES_FLOAT_YEAR,
10    EXCHANGE_RATES_YEAR,
11    CUSTOMER_MASTER   CUSTOMER_MASTER_SOLD
12   WHERE
13    EXCHANGE_RATES_YEAR.CURR_CODE=BOOKING_LINE.TRS_CUR  R
14    --And (EXCHANGE_RATES_YEAR.FISCAL_YEAR)=substr((BOOKING_  LINE.FISCAL_WEEK),1,4)
15    AND  (CUSTOMER_MASTER_SOLD.CUST_ID=BOOKING_LINE.CUST_SO  LD_ID)
16    AND (CUSTOMER_MASTER_SOLD.TRD_INTRACO IN( 'TRADE','INTERCO'))
17    AND  ( EXCHANGE_RATES_FLOAT_YEAR.CURR_CODE= BOOKING_LINE.TRS_CURR )
18  GROUP BY
19    CUSTOMER_MASTER_SOLD.CUST_ID;

Explained.
-------------------------------------------------------------------------------------
| Id  | Operation            &#124  Name                | Rows  | Bytes &#124TempSpc| Cost  |
-------------------------------------------------------------------------------------
&#124   0 | SELECT STATEMENT     &#124                      &#124  2776 | 94384 &#124       &#124   230K|
&#124   1 &#124  SORT GROUP BY       &#124                      &#124  2776 | 94384 &#124   276M&#124   230K|
&#124*  2 &#124   HASH JOIN          &#124                      &#124  6541K&#124   212M&#124       &#124    62 |
&#124   3 &#124    TABLE ACCESS FULL | EXCHANGE_RATES_YEAR  &#124   297 &#124  2376 &#124       &#124     2 |
&#124*  4 &#124    HASH JOIN         &#124                      &#124   726K&#124    18M&#124       &#124    48 |
&#124   5 &#124     TABLE ACCESS FULL| EXCHANGE_RATES_YEAR  &#124   297 &#124  2376 &#124       &#124     2 |
&#124   6 &#124     TABLE ACCESS FULL| TEST007              | 80764 &#124  1419K&#124       &#124    44 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EXCHANGE_RATES_YEAR"."CURR_CODE"="TEST007"."TRS_CURR")
   4 - access("EXCHANGE_RATES_FLOAT_YEAR"."CURR_CODE"="TEST007"."TRS_CURR")

test@orcl>  explain plan for
  2   SELECT
  3            CUSTOMER_MASTER_SOLD.CUST_ID,
  4             sum(BOOKING_LINE.NET_SALES * EXCHANGE_RATES_FLOAT_YEAR.EXCHANGE_RATE),
  5     sum(BOOKING_LINE.NET_SALES * BOOKING_LINE.EXRT_EURO),
  6     sum(BOOKING_LINE.NET_SALES * EXCHANGE_RATES_YEAR.EXCHANGE_RATE)
  7   FROM
  8     BOOKING_LINE,
  9     EXCHANGE_RATES_YEAR  EXCHANGE_RATES_FLOAT_YEAR,
10     EXCHANGE_RATES_YEAR,
11     CUSTOMER_MASTER   CUSTOMER_MASTER_SOLD
12   WHERE
13        EXCHANGE_RATES_YEAR.CURR_CODE=BOOKING_LINE.TRS_CUR  R
14     And (EXCHANGE_RATES_YEAR.FISCAL_YEAR)=substr((BOOKING_  LINE.FISCAL_WEEK),1,4)
15     AND  (CUSTOMER_MASTER_SOLD.CUST_ID=BOOKING_LINE.CUST_SO  LD_ID)
16     AND (CUSTOMER_MASTER_SOLD.TRD_INTRACO IN( 'TRADE','INTERCO'))
17     AND  ( EXCHANGE_RATES_FLOAT_YEAR.CURR_CODE= BOOKING_LINE.TRS_CURR )
18   GROUP BY
19           CUSTOMER_MASTER_SOLD.CUST_ID
20  ;

Explained.
---------------------------------------------------------------------------------------
| Id  | Operation              &#124  Name                | Rows  | Bytes &#124TempSpc| Cost  |
---------------------------------------------------------------------------------------
&#124   0 | SELECT STATEMENT       &#124                      &#124  2982 &#124   139K&#124       | 30148 |
&#124   1 &#124  SORT GROUP BY NOSORT  &#124                      &#124  2982 &#124   139K&#124       | 30148 |
&#124   2 &#124   MERGE JOIN           &#124                      &#124   899K&#124    41M&#124       | 30148 |
&#124   3 &#124    SORT JOIN           &#124                      &#124   900K&#124    32M&#124    82M| 29386 |
&#124*  4 &#124     HASH JOIN          &#124                      &#124   900K&#124    32M&#124       &#124   736 |
&#124   5 &#124      TABLE ACCESS FULL | EXCHANGE_RATES_YEAR  &#124   297 &#124  2376 &#124       &#124     2 |
&#124*  6 &#124      HASH JOIN         &#124                      &#124   100K&#124  2929K&#124       &#124   732 |
&#124   7 &#124       TABLE ACCESS FULL| EXCHANGE_RATES_YEAR  &#124   297 &#124  3267 &#124       &#124     2 |
&#124   8 &#124       TABLE ACCESS FULL| BOOKING_LINE         &#124   100K&#124  1855K&#124       &#124   728 |
&#124*  9 &#124    SORT JOIN           &#124                      | 29312 &#124   286K&#124  1160K&#124   762 |
&#124* 10 &#124     TABLE ACCESS FULL  | CUSTOMER_MASTER      | 29312 &#124   286K&#124       &#124   667 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("EXCHANGE_RATES_FLOAT_YEAR"."CURR_CODE"="BOOKING_LINE"."TRS_CURR")
   6 - access("EXCHANGE_RATES_YEAR"."CURR_CODE"="BOOKING_LINE"."TRS_CURR" AND "EXCHA
              NGE_RATES_YEAR"."FISCAL_YEAR"=TO_NUMBER(SUBSTR(TO_CHAR("BOOKING_LINE".
              "FISCAL_WEEK"),1,4))
   9 - access("CUSTOMER_MASTER_SOLD"."CUST_ID"="BOOKING_LINE"."CUST_SOLD_ID")
       filter("CUSTOMER_MASTER_SOLD"."CUST_ID"="BOOKING_LINE"."CUST_SOLD_ID")
  10 - filter("CUSTOMER_MASTER_SOLD"."TRD_INTRACO"='INTERCO' OR "CUSTOMER_MASTER_SOL
              D"."TRD_INTRACO"='TRADE')

 

Thanks in Advance

Report message to a moderator

Previous Topic: about cogons
Next Topic: Question about sql*loader:
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Nov 23 03:24:36 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.