MV query rewrite
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: MV query rewrite

  1. #1
    Join Date
    Dec 2000
    Posts
    126

    MV query rewrite

    Hi,

    I have this MV LDV_CONTRACT_FIN_DTL created


    SQL> DROP MATERIALIZED VIEW S42LD.LDMV_CONTRACT_FIN_DTL;

    Materialized view dropped.


    1 CREATE MATERIALIZED VIEW S42LD.LDMV_CONTRACT_FIN_DTL
    2 TABLESPACE S42LD_DATA
    3 BUILD IMMEDIATE
    4 REFRESH FORCE ON DEMAND
    5 WITH PRIMARY KEY
    6 ENABLE QUERY REWRITE
    7 AS
    8 (
    9 SELECT *
    10 FROM LDV_CONTRACT_FIN_DTL
    11* )
    SQL> /

    Materialized view created.

    SQL> CREATE BITMAP INDEX S42LD.LDMV_CONT_FIN_DTL_ST_TRN_BIDX ON S42LD.LDMV_CONTRACT_FIN_DTL
    2 (STATE, TRN_TYPE)
    3 LOGGING
    4 TABLESPACE S42LD_INDX
    5 PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT );

    Index created.

    SQL> CREATE BITMAP INDEX S42LD.LDMV_CONT_FIN_DTL_CCY_BIDX ON S42LD.LDMV_CONTRACT_FIN_DTL
    2 (CCY)
    3 LOGGING
    4 TABLESPACE S42LD_INDX
    5 PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT );

    Index created.

    SQL> exec sys.dbms_stats.gather_schema_stats('s42ld')

    PL/SQL procedure successfully completed.

    When I query this view, qurery is rewritten to use MV

    SQL> select * from LDV_CONTRACT_FIN_DTL
    2 /

    337837 rows selected.


    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2 Card=100 Bytes
    =5800)

    1 0 MAT_VIEW REWRITE ACCESS (FULL) OF 'LDMV_CONTRACT_FIN_DTL'
    (MAT_VIEW REWRITE) (Cost=2 Card=100 Bytes=5800)


    But if I add WHERE condition, MV is NOT used.


    SQL> select * from LDV_CONTRACT_FIN_DTL
    2 where ccy='USD'
    3 /

    no rows selected


    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=13 Card=100 Byte
    s=13200)

    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'FLOW' (TABLE) (Cost=1 Ca
    rd=12 Bytes=22404)

    2 1 NESTED LOOPS (Cost=13 Card=100 Bytes=13200)
    3 2 NESTED LOOPS (Cost=5 Card=8 Bytes=736)
    4 3 NESTED LOOPS (Cost=3 Card=9 Bytes=504)
    5 4 NESTED LOOPS (OUTER) (Cost=2 Card=1 Bytes=35)
    6 5 TABLE ACCESS (BY INDEX ROWID) OF 'CURRENCY' (TAB
    LE) (Cost=1 Card=1 Bytes=9)

    7 6 INDEX (UNIQUE SCAN) OF 'IX_CURRENCY' (INDEX (U
    NIQUE)) (Cost=0 Card=1)

    8 5 TABLE ACCESS (BY INDEX ROWID) OF 'EXCHANGE_RATE'
    (TABLE) (Cost=1 Card=1 Bytes=26)

    9 8 INDEX (RANGE SCAN) OF 'FK_EXCHANGE_RATE_CURREN
    CY' (INDEX) (Cost=0 Card=1)

    10 4 TABLE ACCESS (BY INDEX ROWID) OF 'CONTRACT_CFV' (T
    ABLE) (Cost=1 Card=9 Bytes=16992)

    11 10 INDEX (RANGE SCAN) OF 'FK_CONTRACT_CFV_CONTRACT_
    CFD' (INDEX) (Cost=1 Card=9)

    12 3 TABLE ACCESS (BY INDEX ROWID) OF 'CONTRACT' (TABLE)
    (Cost=1 Card=1 Bytes=36)

    13 12 INDEX (UNIQUE SCAN) OF 'PK_CONTRACT' (INDEX (UNIQU
    E)) (Cost=0 Card=1)

    14 2 INDEX (RANGE SCAN) OF 'IX_FLOW' (INDEX) (Cost=2 Card=4
    0)


    Any suggestion is appreciated.

  2. #2
    Join Date
    Mar 2002
    Posts
    534
    Are you using Oracle 7.3.4 or Oracle 8.0.3 ?

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    LDV_CONTRACT_FIN_DTL

    Is this a view?

    Tamil

  4. #4
    Join Date
    Dec 2000
    Posts
    126

    Oracle 10.1.0.2.0

    Hi ,

    Thank you for your rely.

    Sorry about obmission.

    Here are extra information .

    I'm using 10.1.0.2.0


    SQL*Plus: Release 10.1.0.2.0 - Production on Thu Feb 16 11:10:29 2006

    Copyright (c) 1982, 2004, Oracle. All rights reserved.


    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - 64bit Production
    With the Partitioning, OLAP and Data Mining options

    and LDV_CONTRACT_FIN_DTL is a view
    Code:
    SQL> CREATE OR REPLACE VIEW LDV_CONTRACT_FIN_DTL
      2  (CONTRACT_ID, REFERENCE, EXPECTED_DT, CCY, REPAY, 
      3   STATUS, TRN_TYPE, STATE, INT_RATE, COF_RATE, 
      4   XRATE)
      5  AS 
      6  (
      7  SELECT CONTRACT.CONTRACT_ID, CONTRACT.REFERENCE
      8  ,FLOW.EXPECTED_DT
      9  , ccy.code CCY
     10  /* ,CASE WHEN flow.amt_matched=0 THEN  FLOW.AMT_PRINCIPAL ELSE
     11      CASE WHEN flow.amt_gross - flow.amt_matched > flow.amt_principal
     12    THEN FLOW.AMT_PRINCIPAL ELSE flow.amt_gross - flow.amt_matched - flow.amt_principal
     13      END
     14   END repay */
     15  , flow.amt_principal - flow.amt_matched_principal repay
     16  ,CASE WHEN flow.collection_state = 14801 THEN 'Expected' ELSE
     17     CASE WHEN flow.collection_state = 14802 THEN 'Overdue' ELSE 'CFlow' END END status
     18  ,CASE WHEN CONTRACT.FIXED_FLOATING = 4300 THEN 'Fixed' else 'Float' END trn_type
     19  ,CONTRACT_CFV.FIELD_VALUE STATE
     20  ,CONTRACT.INTEREST_RATE int_rate
     21  ,CONTRACT.COF_RATE
     22  ,rate.rate XRate
     23  FROM axiom.contract, axiom.flow,axiom.contract_cfv
     24  ,axiom.currency ccy , axiom.exchange_rate rate
     25  WHERE contract.CONTRACT_ID=flow.CONTRACT_ID
     26  AND contract.CONTRACT_ID=contract_cfv.CONTRACT_ID
     27  AND CONTRACT_CFV.contract_cfd_id=14
     28  AND flow.currency_id = ccy.currency_id
     29  AND ccy.currency_id = rate.currency_id (+)
     30  /* 1. Live contracts only */
     31  AND CONTRACT.Contract_State IN (11130, 11140 ) --: Complete Activated  /  Partially Terminated.
    
     32  /* 2. Instalment */
     33  AND FLOW.FLOW_TYPE = '1003'  /*INSTALLMENT */
     34  AND FLOW.IS_CASH = 1    /*CASH FLOW */
     35  AND FLOW.AMT_PRINCIPAL > 0  /*POSITIVE AMOUNT indicates INFLOW */
     36  AND FLOW.STATUS in (2100, 2102, 2103)     /* PENDING, RELEASED, REJECTED */
     37  AND COLLECTION_STATE <> 14803        /* OUTFLOW NOT RECEIVED */
     38  )
     39  /
    View created.
    Last edited by tamilselvan; 02-16-2006 at 09:46 AM.

  5. #5
    Join Date
    Dec 2000
    Posts
    126
    Problem solved.

    I ran dbms_mview.explain_write to find the reason why MV is not used.

    Thank you all for reply.

Posting Permissions

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



Click Here to Expand Forum to Full Width