I have the below query, i wan to create materialized view based on the below query.
Database version is oracle 11g.
SQL> set timing on
SQL> set autotrace trace
SQL> SELECT Count(*)
2 --A.Comp_Code,
3 /*
4 A.Zone_Code,
5 A.Region_Code,
6 A.Branch_Code,
7 A.Location_Code,
8 A.Division_Code,*/
9 --A.Proposal_No,
10 --A.Party_Code,
11 --B.Party_Name
12 /*,
13 C.Agent_Code AS Last_Repo_Agent,
14 C.Agent_Name AS Repo_Agent_Name,
15 C.Auth_Letter_Date,
16 C.Auth_Letter_Valid_Till,
17 E.Asset_Serial_No,
18 E.Asset_Code,
19 DECODE (NVL (C.To_Stock, 'N'), 'Y', 'I', 'N') AS Repo_Status,
20 C.Repo_Date AS Repo_Date,
21 C.Approve_Date,
22 C.Multiple_Agent,
23 C.Sl_No,
24 G.Service_Location_Code*/
25 FROM Trans.Tb_Proposal_Main A,
26 Master.Tb_Party_Mas B,
27 (SELECT S.Comp_Code,
28 S.Proposal_No,
29 S.Asset_Serial_No,
30 S.Repo_Date,
31 S.Sl_No Slno,
32 S.To_Stock,
33 S.Agent_Code,
34 S.Agent_Name,
35 S.Auth_Letter_Date,
36 S.Auth_Letter_Valid_Till,
37 S.Approve_Date,
38 U.Multiple_Agent,
39 S.Sl_No Sl_No
40 FROM Trans.Tb_Proposal_Repossessed_Assets S,
41 Trans.Tb_Proposal_To_Be_Repo_Assets U
42 WHERE S.Dispossal_Tag IS NULL
43 AND S.Comp_Code = U.Comp_Code(+)
44 AND S.Proposal_No = U.Proposal_No(+)
45 AND S.Asset_Serial_No = U.Asset_Serial_No(+)
46 AND S.Sl_No = U.Sl_No(+)
47 AND S.Sl_No =
48 (SELECT MAX (T.Sl_No)
49 FROM Trans.
Tb_Proposal_Repossessed_Assets T
50 WHERE T.Comp_Code = S.
Comp_Code
51 AND T.Proposal_No = S.
Proposal_No
52 AND T.Asset_Serial_No = S.
Asset_Serial_No
53 AND (T.Proposal_No,
54 T.Asset_Serial_No,
55 T.Sl_No) NOT IN
56 (SELECT X.Refin_Proposal_No,
57 X.Asset_Serial_No,
58 X.Serial_No
59 FROM Trans.Tb_Stock_Of_Old_Assets X
60 WHERE NVL (X.Sale_Id, 'N') =
61 'Y'
62 OR NVL (X.Released_Tag,
63 'N') IN
64 ('Y', 'N')
65 AND X.Released_Date IS NOT NULL
66 AND X.Refin_Proposal_No IS NOT NULL
67 AND X.Comp_Code =
68 S.Comp_Code
69 AND X.Refin_Proposal_No =
70 S.Proposal_No
71 AND X.Asset_Serial_No =
72 S.Asset_Serial_No))) C,
73 Trans.Tb_Proposal_Asset_Details E,
74 Trans.Tb_Proposal_Main_Secondary G,
75 Master.Tb_Asset_Mas Z
76 WHERE A.Comp_Code = B.Comp_Code
77 AND A.Party_Code = B.Party_Code
78 AND A.Comp_Code = C.Comp_Code
79 AND A.Proposal_No = C.Proposal_No
80 AND C.Comp_Code = E.Comp_Code
81 AND C.Proposal_No = E.Proposal_No
82 AND C.Asset_Serial_No = E.Asset_Serial_No
83 AND E.Asset_Code = Z.Asset_Code
84 AND NVL (Z.Body_Funding, 'N') = 'N'
85 AND A.Proposal_No = G.Proposal_No
86 AND A.Comp_Code = G.Comp_Code
87 AND A.Agreement_No IS NOT NULL;
Elapsed: 00:07:39.52
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=21366149 Card=1 By tes
=231)
1 0 SORT (AGGREGATE)
2 1 FILTER
3 2 HASH JOIN (Cost=59636 Card=60232 Bytes=13913592)
4 3 TABLE ACCESS (FULL) OF 'TB_ASSET_MAS' (TABLE) (Cost=71
Card=12991 Bytes=90937)
5 3 NESTED LOOPS (OUTER) (Cost=59564 Card=59313 Bytes=
13286112)
6 5 HASH JOIN (Cost=59561 Card=59313 Bytes=11210157)
7 6 HASH JOIN (Cost=41647 Card=59315 Bytes=9015880)
8 7 HASH JOIN (Cost=36660 Card=59315 Bytes=7355060)
9 8 HASH JOIN (Cost=29824 Card=59315 Bytes=5990815)
10 9 TABLE ACCESS (FULL) OF
'TB_PROPOSAL_REPOSSESSED_ASSETS' (TABLE) (Cost=681 Card=61744 Bytes=2284528
)
11 9 TABLE ACCESS (FULL) OF 'TB_PROPOSAL_MAIN'
(TABLE) (Cost=24161 Card=1344551 Bytes=86051264)
12 8 INDEX (FAST FULL SCAN) OF 'PK_PARTY_MAS' (INDEX
(UNIQUE)) (Cost=2848 Card=2210532 Bytes=50842236)
13 7 INDEX (FAST FULL SCAN) OF
'PK_PROPOSAL_MAIN_SECONDARY' (INDEX (UNIQUE)) (Cost=1963 Card=1393334 Bytes
=39013352)
14 6 TABLE ACCESS (FULL) OF 'TB_PROPOSAL_ASSET_DETAILS'
(TABLE) (Cost=14310 Card=1353846 Bytes=50092302)
15 5 INDEX (UNIQUE SCAN) OF 'PK_TO_BE_REPO_ASSETS' (INDEX
(UNIQUE)) (Cost=0 Card=1 Bytes=35)
16 2 SORT (AGGREGATE)
17 16 FILTER
18 17 TABLE ACCESS (BY INDEX ROWID) OF
'TB_PROPOSAL_REPOSSESSED_ASSETS' (TABLE) (Cost=4 Card=1 Bytes=35)
19 18 INDEX (RANGE SCAN) OF
'PK_PROPOSAL_REPOSSESSED_ASSETS' (INDEX (UNIQUE)) (Cost=3 Card=1)
20 17 TABLE ACCESS (FULL) OF 'TB_STOCK_OF_OLD_ASSETS' (TABLE)
(Cost=361 Card=1 Bytes=47)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
44044538 consistent gets
140007 physical reads
0 redo size
600 bytes sent via SQL*Net to client
1823 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed