-
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.
-
Are you using Oracle 7.3.4 or Oracle 8.0.3 ?
-
LDV_CONTRACT_FIN_DTL
Is this a view?
Tamil
-
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 10:46 AM.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|