Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Location
    India
    Posts
    191

    Unanswered: Materialized views

    Hi,

    Could any one please throw some light on my problem.

    I have a Materialized view.
    PHP Code:
    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_SOLD_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.
    PHP Code:
     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_CURR
      
    --And (EXCHANGE_RATES_YEAR.FISCAL_YEAR)=substr((BOOKING_LINE.FISCAL_WEEK),1,4)
      AND  (
    CUSTOMER_MASTER_SOLD.CUST_ID=BOOKING_LINE.CUST_SOLD_ID)
      AND (
    CUSTOMER_MASTER_SOLD.TRD_INTRACO IN'TRADE','INTERCO'))
      AND  ( 
    EXCHANGE_RATES_FLOAT_YEAR.CURR_CODEBOOKING_LINE.TRS_CURR )
    GROUP BY
      CUSTOMER_MASTER_SOLD
    .CUST_ID 
    PHP Code:
    test@orclselect 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 WindowsVersion 9.2.0.1.0 Production
    NLSRTL Version 9.2.0.1.0 
    Production

    test
    @orclshow parameter query
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    query_rewrite_enabled                string      TRUE
    query_rewrite_integrity              string      TRUSTED
    test
    @orcldelete from plan_table;

    test@orclexplain 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_CURR
     14    
    --And (EXCHANGE_RATES_YEAR.FISCAL_YEAR)=substr((BOOKING_LINE.FISCAL_WEEK),1,4)
     
    15    AND  (CUSTOMER_MASTER_SOLD.CUST_ID=BOOKING_LINE.CUST_SOLD_ID)
     
    16    AND (CUSTOMER_MASTER_SOLD.TRD_INTRACO IN'TRADE','INTERCO'))
     
    17    AND  ( EXCHANGE_RATES_FLOAT_YEAR.CURR_CODEBOOKING_LINE.TRS_CURR )
     
    18  GROUP BY
     19    CUSTOMER_MASTER_SOLD
    .CUST_ID;

    Explained.
    -------------------------------------------------------------------------------------
    Id  Operation            |  Name                Rows  Bytes |TempSpcCost  |
    -------------------------------------------------------------------------------------
    |   
    SELECT STATEMENT     |                      |  2776 94384 |       |   230K|
    |   
    |  SORT GROUP BY       |                      |  2776 94384 |   276M|   230K|
    |*  
    |   HASH JOIN          |                      |  6541K|   212M|       |    62 |
    |   
    |    TABLE ACCESS FULL EXCHANGE_RATES_YEAR  |   297 |  2376 |       |     |
    |*  
    |    HASH JOIN         |                      |   726K|    18M|       |    48 |
    |   
    |     TABLE ACCESS FULLEXCHANGE_RATES_YEAR  |   297 |  2376 |       |     |
    |   
    |     TABLE ACCESS FULLTEST007              80764 |  1419K|       |    44 |
    -------------------------------------------------------------------------------------

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

       
    access("EXCHANGE_RATES_YEAR"."CURR_CODE"="TEST007"."TRS_CURR")
       
    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_CURR
     14     
    And (EXCHANGE_RATES_YEAR.FISCAL_YEAR)=substr((BOOKING_LINE.FISCAL_WEEK),1,4)
     
    15     AND  (CUSTOMER_MASTER_SOLD.CUST_ID=BOOKING_LINE.CUST_SOLD_ID)
     
    16     AND (CUSTOMER_MASTER_SOLD.TRD_INTRACO IN'TRADE','INTERCO'))
     
    17     AND  ( EXCHANGE_RATES_FLOAT_YEAR.CURR_CODEBOOKING_LINE.TRS_CURR )
     
    18   GROUP BY
     19           CUSTOMER_MASTER_SOLD
    .CUST_ID
     20  
    ;

    Explained.
    ---------------------------------------------------------------------------------------
    Id  Operation              |  Name                Rows  Bytes |TempSpcCost  |
    ---------------------------------------------------------------------------------------
    |   
    SELECT STATEMENT       |                      |  2982 |   139K|       | 30148 |
    |   
    |  SORT GROUP BY NOSORT  |                      |  2982 |   139K|       | 30148 |
    |   
    |   MERGE JOIN           |                      |   899K|    41M|       | 30148 |
    |   
    |    SORT JOIN           |                      |   900K|    32M|    82M29386 |
    |*  
    |     HASH JOIN          |                      |   900K|    32M|       |   736 |
    |   
    |      TABLE ACCESS FULL EXCHANGE_RATES_YEAR  |   297 |  2376 |       |     |
    |*  
    |      HASH JOIN         |                      |   100K|  2929K|       |   732 |
    |   
    |       TABLE ACCESS FULLEXCHANGE_RATES_YEAR  |   297 |  3267 |       |     |
    |   
    |       TABLE ACCESS FULLBOOKING_LINE         |   100K|  1855K|       |   728 |
    |*  
    |    SORT JOIN           |                      | 29312 |   286K|  1160K|   762 |
    |* 
    10 |     TABLE ACCESS FULL  CUSTOMER_MASTER      29312 |   286K|       |   667 |
    ---------------------------------------------------------------------------------------

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

       
    access("EXCHANGE_RATES_FLOAT_YEAR"."CURR_CODE"="BOOKING_LINE"."TRS_CURR")
       
    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))
       
    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
    Pagnint
    (No need to search web before posting new question)

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    looks like it is less efficient to use the MV.

    cost = 230k vs. non-MV cost of 30k

    how about exploring some indexing on EXCHANGE_RATES_YEAR & BOOKING_LINE??
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Jan 2004
    Location
    India
    Posts
    191
    This is only dummy Mat views..I have not created any indexes.
    Atpresent my issue is, why oracle is not rewriting the query for some condition



    Thanks & Regards,
    Pagnint
    (No need to search web before posting new question)

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    because you are using a function in the where clause.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    this article answers your questions.
    Code:
    http://www.oracle.com/technology/oramag/oracle/03-sep/o53business.html
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Jan 2004
    Location
    India
    Posts
    191
    Hi,

    First of all thanks for the response.

    I think we can used function in the materialized views. As i have created some
    Materialized views with function in where clause and oracle is rewriting it.

    Thanks,
    Pagnint
    (No need to search web before posting new question)

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    ok, forget about the function, but read the article.
    It lists a ton of possibilities why your MV is not being used.
    MAINLY, if Oracle determines it COSTS less to use the table, then it uses the table. If it costs less to use the MV, then it uses the MV.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •