Dear All,
Below is the production database explain plan -
Toggle Spoiler
SQL> explain plan set statement_id='q1' for
2 select distinct A.NUM_CLAIM_NO,
3 B.NUM_INTERMEDIARY_CD,
4 B.NUM_SURVEY_TYPE,
5 a.txt_master_claim_no ||
6 get_child_serial_no(A.NUM_SERIAL_NO) CLAIM_NUMBER,
7 A.DAT_REGISTRATION_DATE,
8 A.DAT_LOSS_DATE,
9 a.Txt_Remarks LOSS_LOCATION,
10 (select sd.txt_info2
11 from gc_clmmst_generic_value sd
12 where sd.num_master_cd = 75
13 and sd.txt_info1 =
14 (SELECT XC.TXT_INFO31
15 FROM GC_CLM_GEN_INFO_EXTRA XC
16 WHERE XC.NUM_CLAIM_NO = A.NUM_CLAIM_NO
17 AND XC.NUM_UPDATE_NO =
18 (SELECT MAX(VCC.NUM_UPDATE_NO)
19 FROM GC_CLM_GEN_INFO_EXTRA VCC
20 WHERE XC.NUM_CLAIM_NO = VCC.NUM_CLAIM_NO)
21 AND XC.NUM_EXTRA_INFO_TYPE_CD = 2)) LOSSDESCRIPTION,
22 A.TXT_POLICY_NO_CHAR,
23 C.DAT_POLICY_EFF_FROMDATE || ' ' ||
24 C.TXT_POLICY_EFF_FROMHOUR POLICYINCEPTIONFROM,
25 C.DAT_POLICY_EFF_TODATE || ' ' || C.TXT_POLICY_EFF_TOHOUR POLICYEXPIRYTO,
26 F.TXT_FIRSTNAME,
27 F.TXT_EMAIL,
28 case
29 WHEN F.TXT_IND_CORP_FLAG = 'C' THEN
30 F.TXT_CUSTOMER_NAME
31 ELSE
32 F.TXT_LASTNAME
33 END TXT_LASTNAME,
34 F.TXT_TELEPHONE,
35 F.TXT_MOBILE,
36 F.TXT_FAX_NUMBER,
37 CASE
38 WHEN A.NUM_PRODUCT_CODE = '3121' THEN
39 D.INFORMATION6 || D.INFORMATION102 || D.INFORMATION104 ||
40 D.INFORMATION106
41 WHEN A.NUM_PRODUCT_CODE = '3122' THEN
42 D.INFORMATION33 || D.INFORMATION34 || D.INFORMATION35 ||
43 D.INFORMATION36
44 WHEN A.NUM_PRODUCT_CODE = '3124' THEN
45 D.INFORMATION15 || D.INFORMATION21 || D.INFORMATION22 ||
46 D.INFORMATION23
47 END REGISTRATION_NUMBER,
48 CASE
49 WHEN A.NUM_PRODUCT_CODE = '3121' THEN
50 D.INFORMATION3
51 WHEN A.NUM_PRODUCT_CODE = '3122' THEN
52 D.INFORMATION25
53 WHEN A.NUM_PRODUCT_CODE = '3124' THEN
54 D.INFORMATION11
55 END REGISTRATION_DATE,
56 CASE
57 WHEN A.NUM_PRODUCT_CODE = '3121' THEN
58 '01/' || lpad(D.INFORMATION42, 2, '0') || '/' ||
59 D.INFORMATION51
60 WHEN A.NUM_PRODUCT_CODE = '3122' THEN
61 '01/' || lpad(D.INFORMATION27, 2, '0') || '/' ||
62 D.INFORMATION28
63 WHEN A.NUM_PRODUCT_CODE = '3124' THEN
64 '01/' || lpad(D.INFORMATION37, 2, '0') || '/' ||
65 D.INFORMATION10
66 END MENU_MONTH_YEAR,
67 DECODE(A.NUM_PRODUCT_CODE,
68 '3121',
69 D.INFORMATION38,
70 '3122',
71 D.INFORMATION22,
72 '3124',
73 D.INFORMATION36) L_MAKE,
74 DECODE(A.NUM_PRODUCT_CODE,
75 '3121',
76 D.INFORMATION36,
77 '3122',
78 D.INFORMATION23,
79 '3124',
80 D.INFORMATION35) L_MODEL,
81 DECODE(A.NUM_PRODUCT_CODE,
82 '3121',
83 D.INFORMATION14,
84 '3122',
85 D.INFORMATION37,
86 '3124',
87 D.INFORMATION16) ENGIN_NO,
88 DECODE(A.NUM_PRODUCT_CODE,
89 '3121',
90 D.INFORMATION16,
91 '3122',
92 D.INFORMATION38,
93 '3124',
94 D.INFORMATION17) CHESSIS_NO,
95 CASE
96 WHEN A.NUM_PRODUCT_CODE = '3121' THEN
97 D.INFORMATION12
98 WHEN A.NUM_PRODUCT_CODE = '3122' THEN
99 D.INFORMATION26
100 WHEN A.NUM_PRODUCT_CODE = '3124' THEN
101 D.INFORMATION14
102 END DATEOFPURCHASE,
103 (select xccc.txt_info2
104 from GC_CLM_MOT_ADDITIONAL xccc
105 where xccc.num_claim_no = a.num_claim_no
106 and xccc.num_additional_info_type_cd = 5
107 and xccc.num_update_no =
108 (select max(dc.num_update_no)
109 from gc_clm_mot_additional dc
110 where xccc.num_claim_no = dc.num_claim_no)
111 and xccc.yn_active_inactive = 'Y') GRAGE_NAME,
112 DECODE(A.NUM_PRODUCT_CODE,
113 '3121',
114 D.INFORMATION52,
115 '3122',
116 D.INFORMATION39,
117 '3124',
118 D.INFORMATION181) Cubic_capa,
119 nvl((select d.txt_info29
120 from gc_clm_mot_additional d
121 where d.num_claim_no = a.num_claim_no
122 and d.num_additional_info_type_cd = 16
123 and d.yn_active_inactive = 'Y'
124 and d.num_update_no =
125 (select max(xc.num_update_no)
126 from gc_clm_mot_additional xc
127 where xc.num_claim_no = d.num_claim_no
128 and d.num_additional_info_type_cd =
129 xc.NUM_ADDITIONAL_INFO_TYPE_CD)),
130 (select d.txt_info19
131 from gc_clm_mot_additional d
132 where d.num_claim_no = a.num_claim_no
133 and d.num_additional_info_type_cd = 2
134 and d.yn_active_inactive = 'Y'
135 and d.num_update_no =
136 (select max(xcc.num_update_no)
137 from gc_clm_mot_additional xcc
138 where xcc.num_claim_no = d.num_claim_no
139 and d.num_additional_info_type_cd =
140 xcc.num_additional_info_type_cd))) DRIVER_NAME,
141 DECODE(A.NUM_PRODUCT_CODE,
142 '3121',
143 D.INFORMATION8,
144 '3122',
145 D.INFORMATION65,
146 '3124',
147 D.INFORMATION19) IDV,
148 DECODE(A.NUM_PRODUCT_CODE,
149 '3121',
150 D.INFORMATION20,
151 '3122',
152 D.INFORMATION96,
153 '3124',
154 0) VoluntaryDeductible,
155 DECODE(A.NUM_PRODUCT_CODE,
156 '3121',
157 D.INFORMATION168,
158 '3122',
159 D.INFORMATION89,
160 '3124',
161 D.INFORMATION117) NCB,
162 (SELECT ER.TXT_INFO17
163 FROM GC_CLM_GEN_INFO_EXTRA ER
164 WHERE ER.NUM_CLAIM_NO = A.NUM_CLAIM_NO
165 AND ER.NUM_EXTRA_INFO_TYPE_CD = 2
166 AND ER.NUM_UPDATE_NO =
167 (SELECT MAX(DRS.NUM_UPDATE_NO)
168 FROM GC_CLM_GEN_INFO_EXTRA DRS
169 WHERE DRS.NUM_CLAIM_NO = ER.NUM_CLAIM_NO
170 AND DRS.NUM_EXTRA_INFO_TYPE_CD =
171 ER.NUM_EXTRA_INFO_TYPE_CD)) ANALYZER
172 from gc_clm_gen_info a,
173 gc_clm_surveyor b,
174 gen_prop_information_tab c,
175 risk_headers d,
176 GENMST_CUSTOMER F,
177 CNFGTR_USER_DTLS Z
178 where a.num_claim_no = b.num_claim_no
179 AND A.TXT_INSURED_ID(+) = F.TXT_CUSTOMER_CD
180 and a.txt_policy_no_char = c.txt_policy_no_char
181 and c.num_reference_number = a.num_reference_no
182 and c.dat_reference_date = a.dat_reference_date
183 and c.num_reference_number = d.reference_num
184 and c.dat_reference_date = d.reference_date
185 and TO_CHAR(b.num_intermediary_cd) = Z.CODE
186 and b.num_serial_no =
187 (select max(h.num_serial_no)
188 from gc_clm_surveyor h
189 where b.num_claim_no = h.num_claim_no
190 and h.num_intermediary_cd = b.num_intermediary_cd)
191 and b.num_update_no =
192 (select max(ht.num_update_no)
193 from gc_clm_surveyor ht
194 where b.num_claim_no = ht.num_claim_no
195 and ht.num_intermediary_cd = b.num_intermediary_cd)
196 and b.txt_intermediary_status = 'A'
197 and a.num_update_no = 0
198 AND upper(Z.USERID) = 'koliver'
199 AND B.NUM_SURVEY_TYPE <> 1
200 AND NOT EXISTS
201 (SELECT 1
202 FROM (select *
203 from GC_PDA_CLAIM_DELIVERED RT
204 WHERE NOT EXISTS
205 (SELECT 1
206 FROM GC_PDA_CLAIM_REASSIGN ST
207 WHERE ST.NUM_CLAIM_NO = RT.NUM_CLAIM_NO
208 AND ST.NUM_SURVEYOR_CODE = RT.NUM_SURVEYOR_CODE
209 AND ST.NUM_SURVEY_TYPE = RT.NUM_SURVEY_TYPE
210 AND ST.YN_IS_DELIVERED = 'N')) XCC
211 WHERE XCC.NUM_CLAIM_NO = b.num_claim_no
212 AND XCC.NUM_SURVEYOR_CODE = B.num_intermediary_cd
213 AND XCC.NUM_SURVEY_TYPE = B.NUM_SURVEY_TYPE);
Explained.
SQL> select * from table(dbms_xplan.display('plan_table','q1','all'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3754237311
-----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 563 | 124K (2)| 00:24:58 | | |
|* 1 | INDEX RANGE SCAN | PK_GC_CLMMST_GENERIC_VALUE | 1 | 26 | 2 (0)| 00:00:01 | | |
| 2 | NESTED LOOPS | | | | | | | |
| 3 | NESTED LOOPS | | 1 | 47 | 3 (0)| 00:00:01 | | |
| 4 | VIEW | VW_SQ_1 | 1 | 26 | 2 (0)| 00:00:01 | | |
| 5 | SORT GROUP BY | | 1 | 15 | 2 (0)| 00:00:01 | | |
|* 6 | INDEX RANGE SCAN | IND_CLM_EXTRA_NEW | 2 | 30 | 2 (0)| 00:00:01 | | |
|* 7 | INDEX UNIQUE SCAN | PK_GC_CLM_GEN_INFO_EXTRA | 1 | | 0 (0)| 00:00:01 | | |
| 8 | TABLE ACCESS BY INDEX ROWID | GC_CLM_GEN_INFO_EXTRA | 1 | 21 | 1 (0)| 00:00:01 | | |
| 9 | NESTED LOOPS | | | | | | | |
| 10 | NESTED LOOPS | | 1 | 51 | 4 (0)| 00:00:01 | | |
| 11 | VIEW | VW_SQ_2 | 1 | 26 | 2 (0)| 00:00:01 | | |
| 12 | SORT GROUP BY | | 1 | 15 | 2 (0)| 00:00:01 | | |
|* 13 | INDEX RANGE SCAN | PK_GC_CLM_MOT_ADDITIONAL | 5 | 75 | 2 (0)| 00:00:01 | | |
|* 14 | INDEX RANGE SCAN | PK_GC_CLM_MOT_ADDITIONAL | 1 | | 1 (0)| 00:00:01 | | |
|* 15 | TABLE ACCESS BY INDEX ROWID | GC_CLM_MOT_ADDITIONAL | 1 | 25 | 2 (0)| 00:00:01 | | |
| 16 | NESTED LOOPS | | | | | | | |
| 17 | NESTED LOOPS | | 1 | 67 | 4 (0)| 00:00:01 | | |
| 18 | VIEW | VW_SQ_3 | 1 | 39 | 2 (0)| 00:00:01 | | |
| 19 | SORT GROUP BY | | 1 | 18 | 2 (0)| 00:00:01 | | |
|* 20 | INDEX RANGE SCAN | PK_GC_CLM_MOT_ADDITIONAL | 2 | 36 | 2 (0)| 00:00:01 | | |
|* 21 | INDEX RANGE SCAN | PK_GC_CLM_MOT_ADDITIONAL | 1 | | 1 (0)| 00:00:01 | | |
|* 22 | TABLE ACCESS BY INDEX ROWID | GC_CLM_MOT_ADDITIONAL | 1 | 28 | 2 (0)| 00:00:01 | | |
| 23 | NESTED LOOPS | | | | | | | |
| 24 | NESTED LOOPS | | 1 | 65 | 4 (0)| 00:00:01 | | |
| 25 | VIEW | VW_SQ_4 | 1 | 39 | 2 (0)| 00:00:01 | | |
| 26 | SORT GROUP BY | | 1 | 18 | 2 (0)| 00:00:01 | | |
|* 27 | INDEX RANGE SCAN | PK_GC_CLM_MOT_ADDITIONAL | 1 | 18 | 2 (0)| 00:00:01 | | |
|* 28 | INDEX RANGE SCAN | PK_GC_CLM_MOT_ADDITIONAL | 1 | | 1 (0)| 00:00:01 | | |
|* 29 | TABLE ACCESS BY INDEX ROWID | GC_CLM_MOT_ADDITIONAL | 1 | 26 | 2 (0)| 00:00:01 | | |
| 30 | NESTED LOOPS | | | | | | | |
| 31 | NESTED LOOPS | | 1 | 73 | 3 (0)| 00:00:01 | | |
| 32 | VIEW | VW_SQ_5 | 1 | 39 | 2 (0)| 00:00:01 | | |
| 33 | SORT GROUP BY | | 1 | 18 | 2 (0)| 00:00:01 | | |
|* 34 | INDEX RANGE SCAN | PK_GC_CLM_GEN_INFO_EXTRA | 2 | 36 | 2 (0)| 00:00:01 | | |
|* 35 | INDEX UNIQUE SCAN | PK_GC_CLM_GEN_INFO_EXTRA | 1 | | 0 (0)| 00:00:01 | | |
| 36 | TABLE ACCESS BY INDEX ROWID | GC_CLM_GEN_INFO_EXTRA | 1 | 34 | 1 (0)| 00:00:01 | | |
| 37 | HASH UNIQUE | | 1 | 563 | 124K (2)| 00:24:58 | | |
|* 38 | FILTER | | | | | | | |
|* 39 | HASH JOIN | | 1 | 563 | 124K (2)| 00:24:58 | | |
|* 40 | HASH JOIN | | 1 | 498 | 113 (5)| 00:00:02 | | |
| 41 | NESTED LOOPS | | | | | | | |
| 42 | NESTED LOOPS | | 1 | 476 | 43 (10)| 00:00:01 | | |
| 43 | NESTED LOOPS | | 1 | 251 | 40 (10)| 00:00:01 | | |
| 44 | NESTED LOOPS | | 3 | 582 | 34 (12)| 00:00:01 | | |
|* 45 | HASH JOIN | | 2 | 176 | 32 (13)| 00:00:01 | | |
|* 46 | HASH JOIN | | 26 | 1508 | 25 (8)| 00:00:01 | | |
|* 47 | TABLE ACCESS FULL | GC_CLM_SURVEYOR | 475 | 13300 | 18 (0)| 00:00:01 | | |
| 48 | VIEW | VW_SQ_7 | 2071 | 62130 | 6 (17)| 00:00:01 | | |
| 49 | HASH GROUP BY | | 2071 | 41420 | 6 (17)| 00:00:01 | | |
| 50 | INDEX FAST FULL SCAN | PK_GC_CLM_SURVEYOR | 2071 | 41420 | 5 (0)| 00:00:01 | | |
| 51 | VIEW | VW_SQ_6 | 2071 | 62130 | 6 (17)| 00:00:01 | | |
| 52 | HASH GROUP BY | | 2071 | 41420 | 6 (17)| 00:00:01 | | |
| 53 | INDEX FAST FULL SCAN | PK_GC_CLM_SURVEYOR | 2071 | 41420 | 5 (0)| 00:00:01 | | |
| 54 | TABLE ACCESS BY GLOBAL INDEX ROWID| GC_CLM_GEN_INFO | 1 | 106 | 1 (0)| 00:00:01 | ROWID | ROWID |
|* 55 | INDEX UNIQUE SCAN | PK_GC_CLM_GEN_INFO | 1 | | 0 (0)| 00:00:01 | | |
|* 56 | TABLE ACCESS BY GLOBAL INDEX ROWID | GEN_PROP_INFORMATION_TAB | 1 | 57 | 2 (0)| 00:00:01 | ROWID | ROWID |
|* 57 | INDEX UNIQUE SCAN | PK_GEN_PROP_INFO | 1 | | 1 (0)| 00:00:01 | | |
|* 58 | INDEX RANGE SCAN | PK_RISK_HEADER_REF | 1 | | 2 (0)| 00:00:01 | | |
| 59 | TABLE ACCESS BY GLOBAL INDEX ROWID | RISK_HEADERS | 1 | 225 | 3 (0)| 00:00:01 | ROWID | ROWID |
|* 60 | TABLE ACCESS FULL | CNFGTR_USER_DTLS | 150 | 3300 | 70 (2)| 00:00:01 | | |
| 61 | PARTITION RANGE ALL | | 11M| 715M| 124K (2)| 00:24:55 | 1 | 6 |
| 62 | TABLE ACCESS FULL | GENMST_CUSTOMER | 11M| 715M| 124K (2)| 00:24:55 | 1 | 6 |
|* 63 | HASH JOIN ANTI | | 1 | 63 | 6 (17)| 00:00:01 | | |
|* 64 | TABLE ACCESS FULL | GC_PDA_CLAIM_DELIVERED | 1 | 22 | 3 (0)| 00:00:01 | | |
|* 65 | TABLE ACCESS FULL | GC_PDA_CLAIM_REASSIGN | 1 | 41 | 2 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$2 / SD@SEL$2
2 - SEL$0CEE953B
4 - SEL$8F9407EC / VW_SQ_1@SEL$2F12090E
5 - SEL$8F9407EC
6 - SEL$8F9407EC / VCC@SEL$4
7 - SEL$0CEE953B / XC@SEL$3
8 - SEL$0CEE953B / XC@SEL$3
9 - SEL$BC53533F
11 - SEL$CE1D94FA / VW_SQ_2@SEL$0A1A5A0C
12 - SEL$CE1D94FA
13 - SEL$CE1D94FA / DC@SEL$6
14 - SEL$BC53533F / XCCC@SEL$5
15 - SEL$BC53533F / XCCC@SEL$5
16 - SEL$21D54C12
18 - SEL$3512B053 / VW_SQ_3@SEL$23A33D4B
19 - SEL$3512B053
20 - SEL$3512B053 / XC@SEL$8
21 - SEL$21D54C12 / D@SEL$7
22 - SEL$21D54C12 / D@SEL$7
23 - SEL$15BD0953
25 - SEL$0DDF58A2 / VW_SQ_4@SEL$778796C4
26 - SEL$0DDF58A2
27 - SEL$0DDF58A2 / XCC@SEL$10
28 - SEL$15BD0953 / D@SEL$9
29 - SEL$15BD0953 / D@SEL$9
30 - SEL$E6A22765
32 - SEL$32487103 / VW_SQ_5@SEL$ACC926E7
33 - SEL$32487103
34 - SEL$32487103 / DRS@SEL$12
35 - SEL$E6A22765 / ER@SEL$11
36 - SEL$E6A22765 / ER@SEL$11
37 - SEL$B8733860
47 - SEL$B8733860 / B@SEL$1
48 - SEL$AD4650EE / VW_SQ_7@SEL$09F83E1D
49 - SEL$AD4650EE
50 - SEL$AD4650EE / HT@SEL$14
51 - SEL$4CC056F3 / VW_SQ_6@SEL$D1FFACD9
52 - SEL$4CC056F3
53 - SEL$4CC056F3 / H@SEL$13
54 - SEL$B8733860 / A@SEL$1
55 - SEL$B8733860 / A@SEL$1
56 - SEL$B8733860 / C@SEL$1
57 - SEL$B8733860 / C@SEL$1
58 - SEL$B8733860 / D@SEL$1
59 - SEL$B8733860 / D@SEL$1
60 - SEL$B8733860 / Z@SEL$1
62 - SEL$B8733860 / F@SEL$1
63 - SEL$8B2AE9CD
64 - SEL$8B2AE9CD / RT@SEL$16
65 - SEL$8B2AE9CD / ST@SEL$17
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("SD"."NUM_MASTER_CD"=75 AND "SD"."TXT_INFO1"= (SELECT "XC"."TXT_INFO31" FROM "GC_CLM_GEN_INFO_EXTRA" "XC", (SELECT
MAX("VCC"."NUM_UPDATE_NO") "MAX(VCC.NUM_UPDATE_NO)","VCC"."NUM_CLAIM_NO" "ITEM_1" FROM "GC_CLM_GEN_INFO_EXTRA" "VCC" WHERE
"VCC"."NUM_CLAIM_NO"=:B1 GROUP BY "VCC"."NUM_CLAIM_NO") "VW_SQ_1" WHERE "XC"."NUM_EXTRA_INFO_TYPE_CD"=2 AND
"XC"."NUM_UPDATE_NO"="MAX(VCC.NUM_UPDATE_NO)" AND "XC"."NUM_CLAIM_NO"="ITEM_1" AND "XC"."NUM_CLAIM_NO"=:B2))
6 - access("VCC"."NUM_CLAIM_NO"=:B1)
7 - access("XC"."NUM_CLAIM_NO"="ITEM_1" AND "XC"."NUM_UPDATE_NO"="MAX(VCC.NUM_UPDATE_NO)" AND "XC"."NUM_EXTRA_INFO_TYPE_CD"=2)
filter("XC"."NUM_CLAIM_NO"=:B1)
13 - access("DC"."NUM_CLAIM_NO"=:B1)
14 - access("XCCC"."NUM_CLAIM_NO"="ITEM_2" AND "XCCC"."NUM_UPDATE_NO"="MAX(DC.NUM_UPDATE_NO)" AND
"XCCC"."NUM_ADDITIONAL_INFO_TYPE_CD"=5)
filter("XCCC"."NUM_CLAIM_NO"=:B1)
15 - filter("XCCC"."YN_ACTIVE_INACTIVE"='Y')
20 - access("XC"."NUM_CLAIM_NO"=:B1 AND "XC"."NUM_ADDITIONAL_INFO_TYPE_CD"=16)
filter("XC"."NUM_ADDITIONAL_INFO_TYPE_CD"=16)
21 - access("ITEM_3"="D"."NUM_CLAIM_NO" AND "D"."NUM_UPDATE_NO"="MAX(XC.NUM_UPDATE_NO)" AND
"D"."NUM_ADDITIONAL_INFO_TYPE_CD"=16)
filter("D"."NUM_CLAIM_NO"=:B1)
22 - filter("D"."YN_ACTIVE_INACTIVE"='Y')
27 - access("XCC"."NUM_CLAIM_NO"=:B1 AND "XCC"."NUM_ADDITIONAL_INFO_TYPE_CD"=2)
filter("XCC"."NUM_ADDITIONAL_INFO_TYPE_CD"=2)
28 - access("ITEM_5"="D"."NUM_CLAIM_NO" AND "D"."NUM_UPDATE_NO"="MAX(XCC.NUM_UPDATE_NO)" AND
"D"."NUM_ADDITIONAL_INFO_TYPE_CD"=2)
filter("D"."NUM_CLAIM_NO"=:B1)
29 - filter("D"."YN_ACTIVE_INACTIVE"='Y')
34 - access("DRS"."NUM_CLAIM_NO"=:B1 AND "DRS"."NUM_EXTRA_INFO_TYPE_CD"=2)
filter("DRS"."NUM_EXTRA_INFO_TYPE_CD"=2)
35 - access("ITEM_7"="ER"."NUM_CLAIM_NO" AND "ER"."NUM_UPDATE_NO"="MAX(DRS.NUM_UPDATE_NO)" AND "ER"."NUM_EXTRA_INFO_TYPE_CD"=2)
filter("ER"."NUM_CLAIM_NO"=:B1)
38 - filter( NOT EXISTS (SELECT /*+ <not feasible>)
39 - access("A"."TXT_INSURED_ID"=INTERNAL_FUNCTION("F"."TXT_CUSTOMER_CD"))
40 - access("Z"."CODE"=TO_CHAR("B"."NUM_INTERMEDIARY_CD"))
45 - access("B"."NUM_SERIAL_NO"="MAX(H.NUM_SERIAL_NO)" AND "B"."NUM_CLAIM_NO"="ITEM_9" AND "ITEM_10"="B"."NUM_INTERMEDIARY_CD")
46 - access("B"."NUM_UPDATE_NO"="MAX(HT.NUM_UPDATE_NO)" AND "B"."NUM_CLAIM_NO"="ITEM_11" AND
"ITEM_12"="B"."NUM_INTERMEDIARY_CD")
47 - filter("B"."TXT_INTERMEDIARY_STATUS"='A' AND "B"."NUM_SURVEY_TYPE"<>1)
55 - access("A"."NUM_CLAIM_NO"="B"."NUM_CLAIM_NO" AND "A"."NUM_UPDATE_NO"=0)
56 - filter("A"."TXT_POLICY_NO_CHAR"="C"."TXT_POLICY_NO_CHAR")
57 - access("C"."NUM_REFERENCE_NUMBER"="A"."NUM_REFERENCE_NO" AND "C"."DAT_REFERENCE_DATE"="A"."DAT_REFERENCE_DATE")
58 - access("C"."NUM_REFERENCE_NUMBER"="D"."REFERENCE_NUM" AND "C"."DAT_REFERENCE_DATE"="D"."REFERENCE_DATE")
60 - filter(UPPER("Z"."USERID")='koliver')
63 - access("ST"."NUM_CLAIM_NO"="RT"."NUM_CLAIM_NO" AND "ST"."NUM_SURVEYOR_CODE"="RT"."NUM_SURVEYOR_CODE" AND
"ST"."NUM_SURVEY_TYPE"="RT"."NUM_SURVEY_TYPE")
64 - filter("RT"."NUM_CLAIM_NO"=:B1 AND "RT"."NUM_SURVEYOR_CODE"=:B2 AND "RT"."NUM_SURVEY_TYPE"=:B3)
65 - filter("ST"."YN_IS_DELIVERED"='N' AND "ST"."NUM_CLAIM_NO"=:B1 AND "ST"."NUM_SURVEYOR_CODE"=:B2 AND
"ST"."NUM_SURVEY_TYPE"=:B3)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "SD"."NUM_MASTER_CD"[NUMBER,22], "SD"."TXT_INFO1"[VARCHAR2,200], "SD"."TXT_INFO2"[VARCHAR2,200]
2 - (#keys=0) "XC"."TXT_INFO31"[VARCHAR2,1000]
3 - (#keys=0) "XC".ROWID[ROWID,10]
4 - "MAX(VCC.NUM_UPDATE_NO)"[NUMBER,22], "ITEM_1"[NUMBER,22]
5 - (#keys=1) "VCC"."NUM_CLAIM_NO"[NUMBER,22], MAX("VCC"."NUM_UPDATE_NO")[22]
6 - "VCC"."NUM_CLAIM_NO"[NUMBER,22], "VCC"."NUM_UPDATE_NO"[NUMBER,22]
7 - "XC".ROWID[ROWID,10]
8 - "XC"."TXT_INFO31"[VARCHAR2,1000]
9 - (#keys=0) "XCCC"."TXT_INFO2"[VARCHAR2,1000]
10 - (#keys=0) "XCCC".ROWID[ROWID,10]
11 - "MAX(DC.NUM_UPDATE_NO)"[NUMBER,22], "ITEM_2"[NUMBER,22]
12 - (#keys=1) "DC"."NUM_CLAIM_NO"[NUMBER,22], MAX("DC"."NUM_UPDATE_NO")[22]
13 - "DC"."NUM_CLAIM_NO"[NUMBER,22], "DC"."NUM_UPDATE_NO"[NUMBER,22]
14 - "XCCC".ROWID[ROWID,10]
15 - "XCCC"."TXT_INFO2"[VARCHAR2,1000]
16 - (#keys=0) "D"."TXT_INFO29"[VARCHAR2,1000]
17 - (#keys=0) "D".ROWID[ROWID,10]
18 - "MAX(XC.NUM_UPDATE_NO)"[NUMBER,22], "ITEM_3"[NUMBER,22]
19 - (#keys=2) "XC"."NUM_CLAIM_NO"[NUMBER,22], "XC"."NUM_ADDITIONAL_INFO_TYPE_CD"[NUMBER,22], MAX("XC"."NUM_UPDATE_NO")[22]
20 - "XC"."NUM_CLAIM_NO"[NUMBER,22], "XC"."NUM_UPDATE_NO"[NUMBER,22], "XC"."NUM_ADDITIONAL_INFO_TYPE_CD"[NUMBER,22]
21 - "D".ROWID[ROWID,10]
22 - "D"."TXT_INFO29"[VARCHAR2,1000]
23 - (#keys=0) "D"."TXT_INFO19"[VARCHAR2,1000]
24 - (#keys=0) "D".ROWID[ROWID,10]
25 - "MAX(XCC.NUM_UPDATE_NO)"[NUMBER,22], "ITEM_5"[NUMBER,22]
26 - (#keys=2) "XCC"."NUM_CLAIM_NO"[NUMBER,22], "XCC"."NUM_ADDITIONAL_INFO_TYPE_CD"[NUMBER,22], MAX("XCC"."NUM_UPDATE_NO")[22]
27 - "XCC"."NUM_CLAIM_NO"[NUMBER,22], "XCC"."NUM_UPDATE_NO"[NUMBER,22], "XCC"."NUM_ADDITIONAL_INFO_TYPE_CD"[NUMBER,22]
28 - "D".ROWID[ROWID,10]
29 - "D"."TXT_INFO19"[VARCHAR2,1000]
30 - (#keys=0) "ER"."TXT_INFO17"[VARCHAR2,1000]
31 - (#keys=0) "ER".ROWID[ROWID,10]
32 - "MAX(DRS.NUM_UPDATE_NO)"[NUMBER,22], "ITEM_7"[NUMBER,22]
33 - (#keys=2) "DRS"."NUM_CLAIM_NO"[NUMBER,22], "DRS"."NUM_EXTRA_INFO_TYPE_CD"[NUMBER,22], MAX("DRS"."NUM_UPDATE_NO")[22]
34 - "DRS"."NUM_CLAIM_NO"[NUMBER,22], "DRS"."NUM_UPDATE_NO"[NUMBER,22], "DRS"."NUM_EXTRA_INFO_TYPE_CD"[NUMBER,22]
35 - "ER".ROWID[ROWID,10]
36 - "ER"."TXT_INFO17"[VARCHAR2,1000]
37 - (#keys=32) "A"."NUM_CLAIM_NO"[NUMBER,22], "B"."NUM_INTERMEDIARY_CD"[NUMBER,22], "B"."NUM_SURVEY_TYPE"[NUMBER,22],
"A"."TXT_MASTER_CLAIM_NO"||"GET_CHILD_SERIAL_NO"("A"."NUM_SERIAL_NO")[4000], "A"."DAT_REGISTRATION_DATE"[DATE,7],
"A"."DAT_LOSS_DATE"[DATE,7], "A"."TXT_REMARKS"[VARCHAR2,2000], (SELECT "SD"."TXT_INFO2" FROM "GC_CLMMST_GENERIC_VALUE" "SD"
WHERE "SD"."TXT_INFO1"= (SELECT "XC"."TXT_INFO31" FROM "GC_CLM_GEN_INFO_EXTRA" "XC", (SELECT MAX("VCC"."NUM_UPDATE_NO")
"MAX(VCC.NUM_UPDATE_NO)","VCC"."NUM_CLAIM_NO" "ITEM_1" FROM "GC_CLM_GEN_INFO_EXTRA" "VCC" WHERE "VCC"."NUM_CLAIM_NO"=:B1 GROUP
BY "VCC"."NUM_CLAIM_NO") "VW_SQ_1" WHERE "XC"."NUM_EXTRA_INFO_TYPE_CD"=2 AND "XC"."NUM_UPDATE_NO"="MAX(VCC.NUM_UPDATE_NO)" AND
"XC"."NUM_CLAIM_NO"="ITEM_1" AND "XC"."NUM_CLAIM_NO"=:B2) AND "SD"."NUM_MASTER_CD"=75)[200],
"A"."TXT_POLICY_NO_CHAR"[VARCHAR2,30], INTERNAL_FUNCTION("C"."DAT_POLICY_EFF_FROMDATE")||' '||"C"."TXT_POLICY_EFF_FROMHOUR"[40],
INTERNAL_FUNCTION("C"."DAT_POLICY_EFF_TODATE")||' '||"C"."TXT_POLICY_EFF_TOHOUR"[40], "F"."TXT_FIRSTNAME"[VARCHAR2,180],
"F"."TXT_EMAIL"[VARCHAR2,100], CASE WHEN "F"."TXT_IND_CORP_FLAG"='C' THEN "F"."TXT_CUSTOMER_NAME" ELSE "F"."TXT_LASTNAME" END
[350], INTERNAL_FUNCTION("F"."TXT_TELEPHONE")[50], INTERNAL_FUNCTION("F"."TXT_MOBILE")[50], "F"."TXT_FAX_NUMBER"[VARCHAR2,50],
CASE "A"."NUM_PRODUCT_CODE" WHEN 3121 THEN "D"."INFORMATION6"||"D"."INFORMATION102"||"D"."INFORMATION104"||"D"."INFORMATION106"
WHEN 3122 THEN "D"."INFORMATION33"||"D"."INFORMATION34"||"D"."INFORMATION35"||"D"."INFORMATION36" WHEN 3124 THEN
"D"."INFORMATION15"||"D"."INFORMATION21"||"D"."INFORMATION22"||"D"."INFORMATION23" END [4000], CASE "A"."NUM_PRODUCT_CODE" WHEN
3121 THEN "D"."INFORMATION3" WHEN 3122 THEN "D"."INFORMATION25" WHEN 3124 THEN "D"."INFORMATION11" END [4000], CASE
"A"."NUM_PRODUCT_CODE" WHEN 3121 THEN '01/'||LPAD("D"."INFORMATION42",2,'0')||'/'||"D"."INFORMATION51" WHEN 3122 THEN
'01/'||LPAD("D"."INFORMATION27",2,'0')||'/'||"D"."INFORMATION28" WHEN 3124 THEN
'01/'||LPAD("D"."INFORMATION37",2,'0')||'/'||"D"."INFORMATION10" END [4000],
DECODE(TO_CHAR("A"."NUM_PRODUCT_CODE"),'3121',"D"."INFORMATION38",'3122',"D"."INFORMATION22",'3124',"D"."INFORMATION36")[4000],
DECODE(TO_CHAR("A"."NUM_PRODUCT_CODE"),'3121',"D"."INFORMATION36",'3122',"D"."INFORMATION23",'3124',"D"."INFORMATION35")[4000],
DECODE(TO_CHAR("A"."NUM_PRODUCT_CODE"),'3121',"D"."INFORMATION14",'3122',"D"."INFORMATION37",'3124',"D"."INFORMATION16")[4000],
DECODE(TO_CHAR("A"."NUM_PRODUCT_CODE"),'3121',"D"."INFORMATION16",'3122',"D"."INFORMATION38",'3124',"D"."INFORMATION17")[4000],
CASE "A"."NUM_PRODUCT_CODE" WHEN 3121 THEN "D"."INFORMATION12" WHEN 3122 THEN "D"."INFORMATION26" WHEN 3124 THEN
"D"."INFORMATION14" END [4000], (SELECT "XCCC"."TXT_INFO2" FROM "GC_CLM_MOT_ADDITIONAL" "XCCC", (SELECT
MAX("DC"."NUM_UPDATE_NO") "MAX(DC.NUM_UPDATE_NO)","DC"."NUM_CLAIM_NO" "ITEM_2" FROM "GC_CLM_MOT_ADDITIONAL" "DC" WHERE
"DC"."NUM_CLAIM_NO"=:B3 GROUP BY "DC"."NUM_CLAIM_NO") "VW_SQ_2" WHERE "XCCC"."NUM_ADDITIONAL_INFO_TYPE_CD"=5 AND
"XCCC"."NUM_UPDATE_NO"="MAX(DC.NUM_UPDATE_NO)" AND "XCCC"."NUM_CLAIM_NO"="ITEM_2" AND "XCCC"."YN_ACTIVE_INACTIVE"='Y' AND
"XCCC"."NUM_CLAIM_NO"=:B4)[1000], DECODE(TO_CHAR("A"."NUM_PRODUCT_CODE"),'3121',"D"."INFORMATION52",'3122',"D"."INFORMATION39",'3
124',"D"."INFORMATION181")[4000], NVL( (SELECT "D"."TXT_INFO29" FROM "GC_CLM_MOT_ADDITIONAL" "D", (SELECT
MAX("XC"."NUM_UPDATE_NO") "MAX(XC.NUM_UPDATE_NO)","XC"."NUM_CLAIM_NO" "ITEM_3","XC"."NUM_ADDITIONAL_INFO_TYPE_CD" "ITEM_4" FROM
"GC_CLM_MOT_ADDITIONAL" "XC" WHERE "XC"."NUM_CLAIM_NO"=:B5 AND "XC"."NUM_ADDITIONAL_INFO_TYPE_CD"=16 GROUP BY
"XC"."NUM_CLAIM_NO","XC"."NUM_ADDITIONAL_INFO_TYPE_CD") "VW_SQ_3" WHERE "D"."NUM_ADDITIONAL_INFO_TYPE_CD"=16 AND
"D"."NUM_UPDATE_NO"="MAX(XC.NUM_UPDATE_NO)" AND "ITEM_3"="D"."NUM_CLAIM_NO" AND "D"."YN_ACTIVE_INACTIVE"='Y' AND
"D"."NUM_CLAIM_NO"=:B6), (SELECT "D"."TXT_INFO19" FROM "GC_CLM_MOT_ADDITIONAL" "D", (SELECT MAX("XCC"."NUM_UPDATE_NO")
"MAX(XCC.NUM_
38 - "B"."NUM_INTERMEDIARY_CD"[NUMBER,22], "B"."NUM_SURVEY_TYPE"[NUMBER,22], "A"."NUM_CLAIM_NO"[NUMBER,22],
"A"."TXT_MASTER_CLAIM_NO"[VARCHAR2,20], "A"."NUM_SERIAL_NO"[NUMBER,22], "A"."NUM_PRODUCT_CODE"[NUMBER,22],
"A"."DAT_REGISTRATION_DATE"[DATE,7], "A"."DAT_LOSS_DATE"[DATE,7], "A"."TXT_POLICY_NO_CHAR"[VARCHAR2,30],
"A"."TXT_REMARKS"[VARCHAR2,2000], "C"."DAT_POLICY_EFF_FROMDATE"[DATE,7], "C"."TXT_POLICY_EFF_FROMHOUR"[VARCHAR2,30],
"C"."DAT_POLICY_EFF_TODATE"[DATE,7], "C"."TXT_POLICY_EFF_TOHOUR"[VARCHAR2,30], "D"."INFORMATION3"[VARCHAR2,4000],
"D"."INFORMATION6"[VARCHAR2,4000], "D"."INFORMATION8"[VARCHAR2,4000], "D"."INFORMATION10"[VARCHAR2,4000],
"D"."INFORMATION11"[VARCHAR2,4000], "D"."INFORMATION12"[VARCHAR2,4000], "D"."INFORMATION14"[VARCHAR2,4000],
"D"."INFORMATION15"[VARCHAR2,4000], "D"."INFORMATION16"[VARCHAR2,4000], "D"."INFORMATION17"[VARCHAR2,4000],
"D"."INFORMATION19"[VARCHAR2,4000], "D"."INFORMATION20"[VARCHAR2,4000], "D"."INFORMATION21"[VARCHAR2,4000],
"D"."INFORMATION22"[VARCHAR2,4000], "D"."INFORMATION23"[VARCHAR2,4000], "D"."INFORMATION25"[VARCHAR2,4000],
"D"."INFORMATION26"[VARCHAR2,4000], "D"."INFORMATION27"[VARCHAR2,4000], "D"."INFORMATION28"[VARCHAR2,4000],
"D"."INFORMATION33"[VARCHAR2,4000], "D"."INFORMATION34"[VARCHAR2,4000], "D"."INFORMATION35"[VARCHAR2,4000],
"D"."INFORMATION36"[VARCHAR2,4000], "D"."INFORMATION37"[VARCHAR2,4000], "D"."INFORMATION38"[VARCHAR2,4000],
"D"."INFORMATION39"[VARCHAR2,4000], "D"."INFORMATION42"[VARCHAR2,4000], "D"."INFORMATION51"[VARCHAR2,4000],
"D"."INFORMATION52"[VARCHAR2,4000], "D"."INFORMATION65"[VARCHAR2,4000], "D"."INFORMATION89"[VARCHAR2,4000],
"D"."INFORMATION96"[VARCHAR2,4000], "D"."INFORMATION102"[VARCHAR2,4000], "D"."INFORMATION104"[VARCHAR2,4000],
"D"."INFORMATION106"[VARCHAR2,4000], "D"."INFORMATION117"[VARCHAR2,4000], "D"."INFORMATION168"[VARCHAR2,4000],
"D"."INFORMATION181"[VARCHAR2,4000], "F"."TXT_CUSTOMER_NAME"[VARCHAR2,350], "F"."TXT_IND_CORP_FLAG"[CHARACTER,1],
"F"."TXT_EMAIL"[VARCHAR2,100], "F"."TXT_TELEPHONE"[VARCHAR2,118], "F"."TXT_MOBILE"[VARCHAR2,118],
"F"."TXT_FAX_NUMBER"[VARCHAR2,50], "F"."TXT_FIRSTNAME"[VARCHAR2,180], "F"."TXT_LASTNAME"[VARCHAR2,90]
39 - (#keys=1) "B"."NUM_CLAIM_NO"[NUMBER,22], "B"."NUM_INTERMEDIARY_CD"[NUMBER,22], "B"."NUM_SURVEY_TYPE"[NUMBER,22],
"A"."NUM_CLAIM_NO"[NUMBER,22], "A"."TXT_MASTER_CLAIM_NO"[VARCHAR2,20], "A"."NUM_SERIAL_NO"[NUMBER,22],
"A"."NUM_PRODUCT_CODE"[NUMBER,22], "A"."DAT_REGISTRATION_DATE"[DATE,7], "A"."DAT_LOSS_DATE"[DATE,7],
"A"."TXT_POLICY_NO_CHAR"[VARCHAR2,30], "A"."TXT_REMARKS"[VARCHAR2,2000], "C"."DAT_POLICY_EFF_FROMDATE"[DATE,7],
"C"."TXT_POLICY_EFF_FROMHOUR"[VARCHAR2,30], "C"."DAT_POLICY_EFF_TODATE"[DATE,7], "C"."TXT_POLICY_EFF_TOHOUR"[VARCHAR2,30],
"D"."INFORMATION3"[VARCHAR2,4000], "D"."INFORMATION6"[VARCHAR2,4000], "D"."INFORMATION8"[VARCHAR2,4000],
"D"."INFORMATION10"[VARCHAR2,4000], "D"."INFORMATION11"[VARCHAR2,4000], "D"."INFORMATION12"[VARCHAR2,4000],
"D"."INFORMATION14"[VARCHAR2,4000], "D"."INFORMATION15"[VARCHAR2,4000], "D"."INFORMATION16"[VARCHAR2,4000],
"D"."INFORMATION17"[VARCHAR2,4000], "D"."INFORMATION19"[VARCHAR2,4000], "D"."INFORMATION20"[VARCHAR2,4000],
"D"."INFORMATION21"[VARCHAR2,4000], "D"."INFORMATION22"[VARCHAR2,4000], "D"."INFORMATION23"[VARCHAR2,4000],
"D"."INFORMATION25"[VARCHAR2,4000], "D"."INFORMATION26"[VARCHAR2,4000], "D"."INFORMATION27"[VARCHAR2,4000],
"D"."INFORMATION28"[VARCHAR2,4000], "D"."INFORMATION33"[VARCHAR2,4000], "D"."INFORMATION34"[VARCHAR2,4000],
"D"."INFORMATION35"[VARCHAR2,4000], "D"."INFORMATION36"[VARCHAR2,4000], "D"."INFORMATION37"[VARCHAR2,4000],
"D"."INFORMATION38"[VARCHAR2,4000], "D"."INFORMATION39"[VARCHAR2,4000], "D"."INFORMATION42"[VARCHAR2,4000],
"D"."INFORMATION51"[VARCHAR2,4000], "D"."INFORMATION52"[VARCHAR2,4000], "D"."INFORMATION65"[VARCHAR2,4000],
"D"."INFORMATION89"[VARCHAR2,4000], "D"."INFORMATION96"[VARCHAR2,4000], "D"."INFORMATION102"[VARCHAR2,4000],
"D"."INFORMATION104"[VARCHAR2,4000], "D"."INFORMATION106"[VARCHAR2,4000], "D"."INFORMATION117"[VARCHAR2,4000],
"D"."INFORMATION168"[VARCHAR2,4000], "D"."INFORMATION181"[VARCHAR2,4000], "F"."TXT_CUSTOMER_NAME"[VARCHAR2,350],
"F"."TXT_IND_CORP_FLAG"[CHARACTER,1], "F"."TXT_EMAIL"[VARCHAR2,100], "F"."TXT_TELEPHONE"[VARCHAR2,118],
"F"."TXT_MOBILE"[VARCHAR2,118], "F"."TXT_FAX_NUMBER"[VARCHAR2,50], "F"."TXT_FIRSTNAME"[VARCHAR2,180],
"F"."TXT_LASTNAME"[VARCHAR2,90]
40 - (#keys=1) "B"."NUM_CLAIM_NO"[NUMBER,22], "B"."NUM_INTERMEDIARY_CD"[NUMBER,22], "B"."NUM_SURVEY_TYPE"[NUMBER,22],
"A"."NUM_CLAIM_NO"[NUMBER,22], "A"."TXT_MASTER_CLAIM_NO"[VARCHAR2,20], "A"."NUM_SERIAL_NO"[NUMBER,22],
"A"."NUM_PRODUCT_CODE"[NUMBER,22], "A"."DAT_REGISTRATION_DATE"[DATE,7], "A"."DAT_LOSS_DATE"[DATE,7],
"A"."TXT_POLICY_NO_CHAR"[VARCHAR2,30], "A"."TXT_INSURED_ID"[VARCHAR2,168], "A"."TXT_REMARKS"[VARCHAR2,2000],
"C"."DAT_POLICY_EFF_FROMDATE"[DATE,7], "C"."TXT_POLICY_EFF_FROMHOUR"[VARCHAR2,30], "C"."DAT_POLICY_EFF_TODATE"[DATE,7],
"C"."TXT_POLICY_EFF_TOHOUR"[VARCHAR2,30], "D"."INFORMATION3"[VARCHAR2,4000], "D"."INFORMATION6"[VARCHAR2,4000],
"D"."INFORMATION8"[VARCHAR2,4000], "D"."INFORMATION10"[VARCHAR2,4000], "D"."INFORMATION11"[VARCHAR2,4000],
"D"."INFORMATION12"[VARCHAR2,4000], "D"."INFORMATION14"[VARCHAR2,4000], "D"."INFORMATION15"[VARCHAR2,4000],
"D"."INFORMATION16"[VARCHAR2,4000], "D"."INFORMATION17"[VARCHAR2,4000], "D"."INFORMATION19"[VARCHAR2,4000],
"D"."INFORMATION20"[VARCHAR2,4000], "D"."INFORMATION21"[VARCHAR2,4000], "D"."INFORMATION22"[VARCHAR2,4000],
"D"."INFORMATION23"[VARCHAR2,4000], "D"."INFORMATION25"[VARCHAR2,4000], "D"."INFORMATION26"[VARCHAR2,4000],
"D"."INFORMATION27"[VARCHAR2,4000], "D"."INFORMATION28"[VARCHAR2,4000], "D"."INFORMATION33"[VARCHAR2,4000],
"D"."INFORMATION34"[VARCHAR2,4000], "D"."INFORMATION35"[VARCHAR2,4000], "D"."INFORMATION36"[VARCHAR2,4000],
"D"."INFORMATION37"[VARCHAR2,4000], "D"."INFORMATION38"[VARCHAR2,4000], "D"."INFORMATION39"[VARCHAR2,4000],
"D"."INFORMATION42"[VARCHAR2,4000], "D"."INFORMATION51"[VARCHAR2,4000], "D"."INFORMATION52"[VARCHAR2,4000],
"D"."INFORMATION65"[VARCHAR2,4000], "D"."INFORMATION89"[VARCHAR2,4000], "D"."INFORMATION96"[VARCHAR2,4000],
"D"."INFORMATION102"[VARCHAR2,4000], "D"."INFORMATION104"[VARCHAR2,4000], "D"."INFORMATION106"[VARCHAR2,4000],
"D"."INFORMATION117"[VARCHAR2,4000], "D"."INFORMATION168"[VARCHAR2,4000], "D"."INFORMATION181"[VARCHAR2,4000]
41 - (#keys=0) "B"."NUM_CLAIM_NO"[NUMBER,22], "B"."NUM_INTERMEDIARY_CD"[NUMBER,22], "B"."NUM_SURVEY_TYPE"[NUMBER,22],
"A"."NUM_CLAIM_NO"[NUMBER,22], "A"."TXT_MASTER_CLAIM_NO"[VARCHAR2,20], "A"."NUM_SERIAL_NO"[NUMBER,22],
"A"."NUM_PRODUCT_CODE"[NUMBER,22], "A"."DAT_REGISTRATION_DATE"[DATE,7], "A"."DAT_LOSS_DATE"[DATE,7],
"A"."TXT_POLICY_NO_CHAR"[VARCHAR2,30], "A"."TXT_INSURED_ID"[VARCHAR2,168], "A"."TXT_REMARKS"[VARCHAR2,2000],
"C"."DAT_POLICY_EFF_FROMDATE"[DATE,7], "C"."TXT_POLICY_EFF_FROMHOUR"[VARCHAR2,30], "C"."DAT_POLICY_EFF_TODATE"[DATE,7],
"C"."TXT_POLICY_EFF_TOHOUR"[VARCHAR2,30], "D"."INFORMATION3"[VARCHAR2,4000], "D"."INFORMATION6"[VARCHAR2,4000],
"D"."INFORMATION8"[VARCHAR2,4000], "D"."INFORMATION10"[VARCHAR2,4000], "D"."INFORMATION11"[VARCHAR2,4000],
"D"."INFORMATION12"[VARCHAR2,4000], "D"."INFORMATION14"[VARCHAR2,4000], "D"."INFORMATION15"[VARCHAR2,4000],
"D"."INFORMATION16"[VARCHAR2,4000], "D"."INFORMATION17"[VARCHAR2,4000], "D"."INFORMATION19"[VARCHAR2,4000],
"D"."INFORMATION20"[VARCHAR2,4000], "D"."INFORMATION21"[VARCHAR2,4000], "D"."INFORMATION22"[VARCHAR2,4000],
"D"."INFORMATION23"[VARCHAR2,4000], "D"."INFORMATION25"[VARCHAR2,4000], "D"."INFORMATION26"[VARCHAR2,4000],
"D"."INFORMATION27"[VARCHAR2,4000], "D"."INFORMATION28"[VARCHAR2,4000], "D"."INFORMATION33"[VARCHAR2,4000],
"D"."INFORMATION34"[VARCHAR2,4000], "D"."INFORMATION35"[VARCHAR2,4000], "D"."INFORMATION36"[VARCHAR2,4000],
"D"."INFORMATION37"[VARCHAR2,4000], "D"."INFORMATION38"[VARCHAR2,4000], "D"."INFORMATION39"[VARCHAR2,4000],
"D"."INFORMATION42"[VARCHAR2,4000], "D"."INFORMATION51"[VARCHAR2,4000], "D"."INFORMATION52"[VARCHAR2,4000],
"D"."INFORMATION65"[VARCHAR2,4000], "D"."INFORMATION89"[VARCHAR2,4000], "D"."INFORMATION96"[VARCHAR2,4000],
"D"."INFORMATION102"[VARCHAR2,4000], "D"."INFORMATION104"[VARCHAR2,4000], "D"."INFORMATION106"[VARCHAR2,4000],
"D"."INFORMATION117"[VARCHAR2,4000], "D"."INFORMATION168"[VARCHAR2,4000], "D"."INFORMATION181"[VARCHAR2,4000]
42 - (#keys=0) "B"."NUM_CLAIM_NO"[NUMBER,22], "B"."NUM_INTERMEDIARY_CD"[NUMBER,22], "B"."NUM_SURVEY_TYPE"[NUMBER,22],
"A"."NUM_CLAIM_NO"[NUMBER,22], "A"."TXT_MASTER_CLAIM_NO"[VARCHAR2,20], "A"."NUM_SERIAL_NO"[NUMBER,22],
"A"."NUM_PRODUCT_CODE"[NUMBER,22], "A"."DAT_REGISTRATION_DATE"[DATE,7], "A"."DAT_LOSS_DATE"[DATE,7],
"A"."TXT_POLICY_NO_CHAR"[VARCHAR2,30], "A"."TXT_INSURED_ID"[VARCHAR2,168], "A"."TXT_REMARKS"[VARCHAR2,2000],
"C"."DAT_POLICY_EFF_FROMDATE"[DATE,7], "C"."TXT_POLICY_EFF_FROMHOUR"[VARCHAR2,30], "C"."DAT_POLICY_EFF_TODATE"[DATE,7],
"C"."TXT_POLICY_EFF_TOHOUR"[VARCHAR2,30], "D".ROWID[ROWID,10]
43 - (#keys=0) "B"."NUM_CLAIM_NO"[NUMBER,22], "B"."NUM_INTERMEDIARY_CD"[NUMBER,22], "B"."NUM_SURVEY_TYPE"[NUMBER,22],
"A"."NUM_CLAIM_NO"[NUMBER,22], "A"."TXT_MASTER_CLAIM_NO"[VARCHAR2,20], "A"."NUM_SERIAL_NO"[NUMBER,22],
"A"."NUM_PRODUCT_CODE"[NUMBER,22], "A"."DAT_REGISTRATION_DATE"[DATE,7], "A"."DAT_LOSS_DATE"[DATE,7],
"A"."TXT_POLICY_NO_CHAR"[VARCHAR2,30], "A"."TXT_INSURED_ID"[VARCHAR2,168], "A"."TXT_REMARKS"[VARCHAR2,2000],
"C"."NUM_REFERENCE_NUMBER"[NUMBER,22], "C"."DAT_REFERENCE_DATE"[DATE,7], "C"."DAT_POLICY_EFF_FROMDATE"[DATE,7],
"C"."TXT_POLICY_EFF_FROMHOUR"[VARCHAR2,30], "C"."DAT_POLICY_EFF_TODATE"[DATE,7], "C"."TXT_POLICY_EFF_TOHOUR"[VARCHAR2,30]
44 - (#keys=0) "B"."NUM_CLAIM_NO"[NUMBER,22], "B"."NUM_INTERMEDIARY_CD"[NUMBER,22], "B"."NUM_SURVEY_TYPE"[NUMBER,22],
"A"."NUM_CLAIM_NO"[NUMBER,22], "A"."TXT_MASTER_CLAIM_NO"[VARCHAR2,20], "A"."NUM_SERIAL_NO"[NUMBER,22],
"A"."NUM_PRODUCT_CODE"[NUMBER,22], "A"."DAT_REGISTRATION_DATE"[DATE,7], "A"."DAT_LOSS_DATE"[DATE,7],
"A"."TXT_POLICY_NO_CHAR"[VARCHAR2,30], "A"."NUM_REFERENCE_NO"[NUMBER,22], "A"."DAT_REFERENCE_DATE"[DATE,7],
"A"."TXT_INSURED_ID"[VARCHAR2,168], "A"."TXT_REMARKS"[VARCHAR2,2000]
45 - (#keys=3) "B"."NUM_CLAIM_NO"[NUMBER,22], "B"."NUM_INTERMEDIARY_CD"[NUMBER,22], "B"."NUM_SURVEY_TYPE"[NUMBER,22]
46 - (#keys=3) "B"."NUM_CLAIM_NO"[NUMBER,22], "B"."NUM_INTERMEDIARY_CD"[NUMBER,22], "B"."NUM_SURVEY_TYPE"[NUMBER,22],
"B"."NUM_SERIAL_NO"[NUMBER,22]
47 - "B"."NUM_CLAIM_NO"[NUMBER,22], "B"."NUM_UPDATE_NO"[NUMBER,22], "B"."NUM_SERIAL_NO"[NUMBER,22],
"B"."NUM_INTERMEDIARY_CD"[NUMBER,22], "B"."NUM_SURVEY_TYPE"[NUMBER,22]
48 - "MAX(HT.NUM_UPDATE_NO)"[NUMBER,22], "ITEM_11"[NUMBER,22], "ITEM_12"[NUMBER,22]
49 - (#keys=2) "HT"."NUM_CLAIM_NO"[NUMBER,22], "HT"."NUM_INTERMEDIARY_CD"[NUMBER,22], MAX("HT"."NUM_UPDATE_NO")[22]
50 - "HT"."NUM_CLAIM_NO"[NUMBER,22], "HT"."NUM_UPDATE_NO"[NUMBER,22], "HT"."NUM_INTERMEDIARY_CD"[NUMBER,22]
51 - "MAX(H.NUM_SERIAL_NO)"[NUMBER,22], "ITEM_9"[NUMBER,22], "ITEM_10"[NUMBER,22]
52 - (#keys=2) "H"."NUM_CLAIM_NO"[NUMBER,22], "H"."NUM_INTERMEDIARY_CD"[NUMBER,22], MAX("H"."NUM_SERIAL_NO")[22]
53 - "H"."NUM_CLAIM_NO"[NUMBER,22], "H"."NUM_SERIAL_NO"[NUMBER,22], "H"."NUM_INTERMEDIARY_CD"[NUMBER,22]
54 - "A"."NUM_CLAIM_NO"[NUMBER,22], "A"."TXT_MASTER_CLAIM_NO"[VARCHAR2,20], "A"."NUM_SERIAL_NO"[NUMBER,22],
"A"."NUM_PRODUCT_CODE"[NUMBER,22], "A"."DAT_REGISTRATION_DATE"[DATE,7], "A"."DAT_LOSS_DATE"[DATE,7],
"A"."TXT_POLICY_NO_CHAR"[VARCHAR2,30], "A"."NUM_REFERENCE_NO"[NUMBER,22], "A"."DAT_REFERENCE_DATE"[DATE,7],
"A"."TXT_INSURED_ID"[VARCHAR2,168], "A"."TXT_REMARKS"[VARCHAR2,2000]
55 - "A".ROWID[ROWID,10], "A"."NUM_CLAIM_NO"[NUMBER,22]
56 - "C"."NUM_REFERENCE_NUMBER"[NUMBER,22], "C"."DAT_REFERENCE_DATE"[DATE,7], "C"."DAT_POLICY_EFF_FROMDATE"[DATE,7],
"C"."TXT_POLICY_EFF_FROMHOUR"[VARCHAR2,30], "C"."DAT_POLICY_EFF_TODATE"[DATE,7], "C"."TXT_POLICY_EFF_TOHOUR"[VARCHAR2,30]
57 - "C".ROWID[ROWID,10], "C"."NUM_REFERENCE_NUMBER"[NUMBER,22], "C"."DAT_REFERENCE_DATE"[DATE,7]
58 - "D".ROWID[ROWID,10]
59 - "D"."INFORMATION3"[VARCHAR2,4000], "D"."INFORMATION6"[VARCHAR2,4000], "D"."INFORMATION8"[VARCHAR2,4000],
"D"."INFORMATION10"[VARCHAR2,4000], "D"."INFORMATION11"[VARCHAR2,4000], "D"."INFORMATION12"[VARCHAR2,4000],
"D"."INFORMATION14"[VARCHAR2,4000], "D"."INFORMATION15"[VARCHAR2,4000], "D"."INFORMATION16"[VARCHAR2,4000],
"D"."INFORMATION17"[VARCHAR2,4000], "D"."INFORMATION19"[VARCHAR2,4000], "D"."INFORMATION20"[VARCHAR2,4000],
"D"."INFORMATION21"[VARCHAR2,4000], "D"."INFORMATION22"[VARCHAR2,4000], "D"."INFORMATION23"[VARCHAR2,4000],
"D"."INFORMATION25"[VARCHAR2,4000], "D"."INFORMATION26"[VARCHAR2,4000], "D"."INFORMATION27"[VARCHAR2,4000],
"D"."INFORMATION28"[VARCHAR2,4000], "D"."INFORMATION33"[VARCHAR2,4000], "D"."INFORMATION34"[VARCHAR2,4000],
"D"."INFORMATION35"[VARCHAR2,4000], "D"."INFORMATION36"[VARCHAR2,4000], "D"."INFORMATION37"[VARCHAR2,4000],
"D"."INFORMATION38"[VARCHAR2,4000], "D"."INFORMATION39"[VARCHAR2,4000], "D"."INFORMATION42"[VARCHAR2,4000],
"D"."INFORMATION51"[VARCHAR2,4000], "D"."INFORMATION52"[VARCHAR2,4000], "D"."INFORMATION65"[VARCHAR2,4000],
"D"."INFORMATION89"[VARCHAR2,4000], "D"."INFORMATION96"[VARCHAR2,4000], "D"."INFORMATION102"[VARCHAR2,4000],
"D"."INFORMATION104"[VARCHAR2,4000], "D"."INFORMATION106"[VARCHAR2,4000], "D"."INFORMATION117"[VARCHAR2,4000],
"D"."INFORMATION168"[VARCHAR2,4000], "D"."INFORMATION181"[VARCHAR2,4000]
60 - "Z"."CODE"[VARCHAR2,20]
61 - "F"."TXT_CUSTOMER_CD"[VARCHAR2,20], "F"."TXT_CUSTOMER_NAME"[VARCHAR2,350], "F"."TXT_IND_CORP_FLAG"[CHARACTER,1],
"F"."TXT_EMAIL"[VARCHAR2,100], "F"."TXT_TELEPHONE"[VARCHAR2,118], "F"."TXT_MOBILE"[VARCHAR2,118],
"F"."TXT_FAX_NUMBER"[VARCHAR2,50], "F"."TXT_FIRSTNAME"[VARCHAR2,180], "F"."TXT_LASTNAME"[VARCHAR2,90]
62 - "F"."TXT_CUSTOMER_CD"[VARCHAR2,20], "F"."TXT_CUSTOMER_NAME"[VARCHAR2,350], "F"."TXT_IND_CORP_FLAG"[CHARACTER,1],
"F"."TXT_EMAIL"[VARCHAR2,100], "F"."TXT_TELEPHONE"[VARCHAR2,118], "F"."TXT_MOBILE"[VARCHAR2,118],
"F"."TXT_FAX_NUMBER"[VARCHAR2,50], "F"."TXT_FIRSTNAME"[VARCHAR2,180], "F"."TXT_LASTNAME"[VARCHAR2,90]
63 - (#keys=3) "RT"."NUM_CLAIM_NO"[NUMBER,22], "ST"."NUM_CLAIM_NO"[NUMBER,22], "RT"."NUM_SURVEYOR_CODE"[NUMBER,22],
"ST"."NUM_SURVEYOR_CODE"[NUMBER,22], "RT"."NUM_SURVEY_TYPE"[NUMBER,22], "ST"."NUM_SURVEY_TYPE"[NUMBER,22]
64 - "RT"."NUM_CLAIM_NO"[NUMBER,22], "RT"."NUM_SURVEYOR_CODE"[NUMBER,22], "RT"."NUM_SURVEY_TYPE"[NUMBER,22]
65 - "ST"."NUM_CLAIM_NO"[NUMBER,22], "ST"."NUM_SURVEYOR_CODE"[NUMBER,22], "ST"."NUM_SURVEY_TYPE"[NUMBER,22]
388 rows selected.
Why "INTERNAL_FUNCTION" is appearing on my explain plan?
39 - access("A"."TXT_INSURED_ID"=INTERNAL_FUNCTION("F"."TXT_CUSTOMER_CD"))
Again, I have generated explain plan for the query in my local database and it's not appearing.
Toggle Spoiler
SQL> explain plan set statement_id='q1' for
2 select distinct A.NUM_CLAIM_NO,
3 B.NUM_INTERMEDIARY_CD,
4 B.NUM_SURVEY_TYPE,
5 a.txt_master_claim_no ||
6 get_child_serial_no(A.NUM_SERIAL_NO) CLAIM_NUMBER,
7 A.DAT_REGISTRATION_DATE,
8 A.DAT_LOSS_DATE,
9 a.Txt_Remarks LOSS_LOCATION,
10 (select sd.txt_info2
11 from gc_clmmst_generic_value sd
12 where sd.num_master_cd = 75
13 and sd.txt_info1 =
14 (SELECT XC.TXT_INFO31
15 FROM GC_CLM_GEN_INFO_EXTRA XC
16 WHERE XC.NUM_CLAIM_NO = A.NUM_CLAIM_NO
17 AND XC.NUM_UPDATE_NO =
18 (SELECT MAX(VCC.NUM_UPDATE_NO)
19 FROM GC_CLM_GEN_INFO_EXTRA VCC
20 WHERE XC.NUM_CLAIM_NO = VCC.NUM_CLAIM_NO)
21 AND XC.NUM_EXTRA_INFO_TYPE_CD = 2)) LOSSDESCRIPTION,
22 A.TXT_POLICY_NO_CHAR,
23 C.DAT_POLICY_EFF_FROMDATE || ' ' ||
24 C.TXT_POLICY_EFF_FROMHOUR POLICYINCEPTIONFROM,
25 C.DAT_POLICY_EFF_TODATE || ' ' || C.TXT_POLICY_EFF_TOHOUR POLICYEXPIRYTO,
26 F.TXT_FIRSTNAME,
27 F.TXT_EMAIL,
28 case
29 WHEN F.TXT_IND_CORP_FLAG = 'C' THEN
30 F.TXT_CUSTOMER_NAME
31 ELSE
32 F.TXT_LASTNAME
33 END TXT_LASTNAME,
34 F.TXT_TELEPHONE,
35 F.TXT_MOBILE,
36 F.TXT_FAX_NUMBER,
37 CASE
38 WHEN A.NUM_PRODUCT_CODE = '3121' THEN
39 D.INFORMATION6 || D.INFORMATION102 || D.INFORMATION104 ||
40 D.INFORMATION106
41 WHEN A.NUM_PRODUCT_CODE = '3122' THEN
42 D.INFORMATION33 || D.INFORMATION34 || D.INFORMATION35 ||
43 D.INFORMATION36
44 WHEN A.NUM_PRODUCT_CODE = '3124' THEN
45 D.INFORMATION15 || D.INFORMATION21 || D.INFORMATION22 ||
46 D.INFORMATION23
47 END REGISTRATION_NUMBER,
48 CASE
49 WHEN A.NUM_PRODUCT_CODE = '3121' THEN
50 D.INFORMATION3
51 WHEN A.NUM_PRODUCT_CODE = '3122' THEN
52 D.INFORMATION25
53 WHEN A.NUM_PRODUCT_CODE = '3124' THEN
54 D.INFORMATION11
55 END REGISTRATION_DATE,
56 CASE
57 WHEN A.NUM_PRODUCT_CODE = '3121' THEN
58 '01/' || lpad(D.INFORMATION42, 2, '0') || '/' ||
59 D.INFORMATION51
60 WHEN A.NUM_PRODUCT_CODE = '3122' THEN
61 '01/' || lpad(D.INFORMATION27, 2, '0') || '/' ||
62 D.INFORMATION28
63 WHEN A.NUM_PRODUCT_CODE = '3124' THEN
64 '01/' || lpad(D.INFORMATION37, 2, '0') || '/' ||
65 D.INFORMATION10
66 END MENU_MONTH_YEAR,
67 DECODE(A.NUM_PRODUCT_CODE,
68 '3121',
69 D.INFORMATION38,
70 '3122',
71 D.INFORMATION22,
72 '3124',
73 D.INFORMATION36) L_MAKE,
74 DECODE(A.NUM_PRODUCT_CODE,
75 '3121',
76 D.INFORMATION36,
77 '3122',
78 D.INFORMATION23,
79 '3124',
80 D.INFORMATION35) L_MODEL,
81 DECODE(A.NUM_PRODUCT_CODE,
82 '3121',
83 D.INFORMATION14,
84 '3122',
85 D.INFORMATION37,
86 '3124',
87 D.INFORMATION16) ENGIN_NO,
88 DECODE(A.NUM_PRODUCT_CODE,
89 '3121',
90 D.INFORMATION16,
91 '3122',
92 D.INFORMATION38,
93 '3124',
94 D.INFORMATION17) CHESSIS_NO,
95 CASE
96 WHEN A.NUM_PRODUCT_CODE = '3121' THEN
97 D.INFORMATION12
98 WHEN A.NUM_PRODUCT_CODE = '3122' THEN
99 D.INFORMATION26
100 WHEN A.NUM_PRODUCT_CODE = '3124' THEN
101 D.INFORMATION14
102 END DATEOFPURCHASE,
103 (select xccc.txt_info2
104 from GC_CLM_MOT_ADDITIONAL xccc
105 where xccc.num_claim_no = a.num_claim_no
106 and xccc.num_additional_info_type_cd = 5
107 and xccc.num_update_no =
108 (select max(dc.num_update_no)
109 from gc_clm_mot_additional dc
110 where xccc.num_claim_no = dc.num_claim_no)
111 and xccc.yn_active_inactive = 'Y') GRAGE_NAME,
112 DECODE(A.NUM_PRODUCT_CODE,
113 '3121',
114 D.INFORMATION52,
115 '3122',
116 D.INFORMATION39,
117 '3124',
118 D.INFORMATION181) Cubic_capa,
119 nvl((select d.txt_info29
120 from gc_clm_mot_additional d
121 where d.num_claim_no = a.num_claim_no
122 and d.num_additional_info_type_cd = 16
123 and d.yn_active_inactive = 'Y'
124 and d.num_update_no =
125 (select max(xc.num_update_no)
126 from gc_clm_mot_additional xc
127 where xc.num_claim_no = d.num_claim_no
128 and d.num_additional_info_type_cd =
129 xc.NUM_ADDITIONAL_INFO_TYPE_CD)),
130 (select d.txt_info19
131 from gc_clm_mot_additional d
132 where d.num_claim_no = a.num_claim_no
133 and d.num_additional_info_type_cd = 2
134 and d.yn_active_inactive = 'Y'
135 and d.num_update_no =
136 (select max(xcc.num_update_no)
137 from gc_clm_mot_additional xcc
138 where xcc.num_claim_no = d.num_claim_no
139 and d.num_additional_info_type_cd =
140 xcc.num_additional_info_type_cd))) DRIVER_NAME,
141 DECODE(A.NUM_PRODUCT_CODE,
142 '3121',
143 D.INFORMATION8,
144 '3122',
145 D.INFORMATION65,
146 '3124',
147 D.INFORMATION19) IDV,
148 DECODE(A.NUM_PRODUCT_CODE,
149 '3121',
150 D.INFORMATION20,
151 '3122',
152 D.INFORMATION96,
153 '3124',
154 0) VoluntaryDeductible,
155 DECODE(A.NUM_PRODUCT_CODE,
156 '3121',
157 D.INFORMATION168,
158 '3122',
159 D.INFORMATION89,
160 '3124',
161 D.INFORMATION117) NCB,
162 (SELECT ER.TXT_INFO17
163 FROM GC_CLM_GEN_INFO_EXTRA ER
164 WHERE ER.NUM_CLAIM_NO = A.NUM_CLAIM_NO
165 AND ER.NUM_EXTRA_INFO_TYPE_CD = 2
166 AND ER.NUM_UPDATE_NO =
167 (SELECT MAX(DRS.NUM_UPDATE_NO)
168 FROM GC_CLM_GEN_INFO_EXTRA DRS
169 WHERE DRS.NUM_CLAIM_NO = ER.NUM_CLAIM_NO
170 AND DRS.NUM_EXTRA_INFO_TYPE_CD =
171 ER.NUM_EXTRA_INFO_TYPE_CD)) ANALYZER
172 from gc_clm_gen_info a,
173 gc_clm_surveyor b,
174 gen_prop_information_tab c,
175 risk_headers d,
176 GENMST_CUSTOMER F,
177 CNFGTR_USER_DTLS Z
178 where a.num_claim_no = b.num_claim_no
179 AND A.TXT_INSURED_ID(+) = F.TXT_CUSTOMER_CD
180 and a.txt_policy_no_char = c.txt_policy_no_char
181 and c.num_reference_number = a.num_reference_no
182 and c.dat_reference_date = a.dat_reference_date
183 and c.num_reference_number = d.reference_num
184 and c.dat_reference_date = d.reference_date
185 and TO_CHAR(b.num_intermediary_cd) = Z.CODE
186 and b.num_serial_no =
187 (select max(h.num_serial_no)
188 from gc_clm_surveyor h
189 where b.num_claim_no = h.num_claim_no
190 and h.num_intermediary_cd = b.num_intermediary_cd)
191 and b.num_update_no =
192 (select max(ht.num_update_no)
193 from gc_clm_surveyor ht
194 where b.num_claim_no = ht.num_claim_no
195 and ht.num_intermediary_cd = b.num_intermediary_cd)
196 and b.txt_intermediary_status = 'A'
197 and a.num_update_no = 0
198 AND upper(Z.USERID) = 'koliver'
199 AND B.NUM_SURVEY_TYPE <> 1
200 AND NOT EXISTS
201 (SELECT 1
202 FROM (select *
203 from GC_PDA_CLAIM_DELIVERED RT
204 WHERE NOT EXISTS
205 (SELECT 1
206 FROM GC_PDA_CLAIM_REASSIGN ST
207 WHERE ST.NUM_CLAIM_NO = RT.NUM_CLAIM_NO
208 AND ST.NUM_SURVEYOR_CODE = RT.NUM_SURVEYOR_CODE
209 AND ST.NUM_SURVEY_TYPE = RT.NUM_SURVEY_TYPE
210 AND ST.YN_IS_DELIVERED = 'N')) XCC
211 WHERE XCC.NUM_CLAIM_NO = b.num_claim_no
212 AND XCC.NUM_SURVEYOR_CODE = B.num_intermediary_cd
213 AND XCC.NUM_SURVEY_TYPE = B.NUM_SURVEY_TYPE);
Explained.
SQL> select * from table(dbms_xplan.display('plan_table','q1','all'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 4066997730
----------------------------------------------------------------------------------------------------
-------------------------------------
| Id | Operation | Name | Rows | Bytes | Co
st (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
-------------------------------------
| 0 | SELECT STATEMENT | | 4 | 1948 |
101 (3)| 00:00:02 | | |
|* 1 | INDEX RANGE SCAN | PK_GC_CLMMST_GENERIC_VALUE | 1 | 26 |
2 (0)| 00:00:01 | | |
| 2 | NESTED LOOPS | | 1 | 34 |
6 (0)| 00:00:01 | | |
| 3 | TABLE ACCESS BY INDEX ROWID | GC_CLM_GEN_INFO_EXTRA | 1 | 21 |
4 (0)| 00:00:01 | | |
|* 4 | INDEX RANGE SCAN | PK_GC_CLM_GEN_INFO_EXTRA | 1 | |
2 (0)| 00:00:01 | | |
|* 5 | VIEW PUSHED PREDICATE | VW_SQ_1 | 1 | 13 |
2 (0)| 00:00:01 | | |
| 6 | SORT AGGREGATE | | 1 | 15 |
| | | |
| 7 | FIRST ROW | | 1 | 15 |
2 (0)| 00:00:01 | | |
|* 8 | INDEX RANGE SCAN (MIN/MAX) | IND_CLM_EXTRA_NEW | 1 | 15 |
2 (0)| 00:00:01 | | |
| 9 | NESTED LOOPS | | 1 | 37 |
5 (0)| 00:00:01 | | |
|* 10 | TABLE ACCESS BY INDEX ROWID | GC_CLM_MOT_ADDITIONAL | 1 | 24 |
3 (0)| 00:00:01 | | |
|* 11 | INDEX RANGE SCAN | PK_GC_CLM_MOT_ADDITIONAL | 1 | |
2 (0)| 00:00:01 | | |
|* 12 | VIEW PUSHED PREDICATE | VW_SQ_2 | 1 | 13 |
2 (0)| 00:00:01 | | |
| 13 | SORT AGGREGATE | | 1 | 15 |
| | | |
| 14 | FIRST ROW | | 1 | 15 |
2 (0)| 00:00:01 | | |
|* 15 | INDEX RANGE SCAN (MIN/MAX) | PK_GC_CLM_MOT_ADDITIONAL | 1 | 15 |
2 (0)| 00:00:01 | | |
| 16 | NESTED LOOPS | | 1 | 38 |
5 (0)| 00:00:01 | | |
|* 17 | TABLE ACCESS BY INDEX ROWID | GC_CLM_MOT_ADDITIONAL | 1 | 25 |
3 (0)| 00:00:01 | | |
|* 18 | INDEX RANGE SCAN | PK_GC_CLM_MOT_ADDITIONAL | 1 | |
2 (0)| 00:00:01 | | |
|* 19 | VIEW PUSHED PREDICATE | VW_SQ_3 | 1 | 13 |
2 (0)| 00:00:01 | | |
| 20 | SORT AGGREGATE | | 1 | 18 |
| | | |
| 21 | FIRST ROW | | 1 | 18 |
2 (0)| 00:00:01 | | |
|* 22 | INDEX RANGE SCAN (MIN/MAX) | PK_GC_CLM_MOT_ADDITIONAL | 1 | 18 |
2 (0)| 00:00:01 | | |
| 23 | NESTED LOOPS | | 1 | 37 |
5 (0)| 00:00:01 | | |
|* 24 | TABLE ACCESS BY INDEX ROWID | GC_CLM_MOT_ADDITIONAL | 1 | 24 |
3 (0)| 00:00:01 | | |
|* 25 | INDEX RANGE SCAN | PK_GC_CLM_MOT_ADDITIONAL | 1 | |
2 (0)| 00:00:01 | | |
|* 26 | VIEW PUSHED PREDICATE | VW_SQ_4 | 1 | 13 |
2 (0)| 00:00:01 | | |
| 27 | SORT AGGREGATE | | 1 | 18 |
| | | |
| 28 | FIRST ROW | | 1 | 18 |
2 (0)| 00:00:01 | | |
|* 29 | INDEX RANGE SCAN (MIN/MAX) | PK_GC_CLM_MOT_ADDITIONAL | 1 | 18 |
2 (0)| 00:00:01 | | |
| 30 | NESTED LOOPS | | 1 | 43 |
6 (0)| 00:00:01 | | |
| 31 | TABLE ACCESS BY INDEX ROWID | GC_CLM_GEN_INFO_EXTRA | 1 | 30 |
4 (0)| 00:00:01 | | |
|* 32 | INDEX RANGE SCAN | PK_GC_CLM_GEN_INFO_EXTRA | 1 | |
2 (0)| 00:00:01 | | |
|* 33 | VIEW PUSHED PREDICATE | VW_SQ_5 | 1 | 13 |
2 (0)| 00:00:01 | | |
| 34 | SORT AGGREGATE | | 1 | 18 |
| | | |
| 35 | FIRST ROW | | 1 | 18 |
2 (0)| 00:00:01 | | |
|* 36 | INDEX RANGE SCAN (MIN/MAX) | PK_GC_CLM_GEN_INFO_EXTRA | 1 | 18 |
2 (0)| 00:00:01 | | |
| 37 | HASH UNIQUE | | 4 | 1948 |
101 (3)| 00:00:02 | | |
|* 38 | FILTER | | | |
| | | |
| 39 | NESTED LOOPS | | 4 | 1948 |
86 (2)| 00:00:02 | | |
| 40 | NESTED LOOPS | | 1 | 474 |
84 (2)| 00:00:02 | | |
| 41 | NESTED LOOPS | | 1 | 461 |
82 (2)| 00:00:01 | | |
| 42 | NESTED LOOPS | | 1 | 244 |
80 (2)| 00:00:01 | | |
| 43 | NESTED LOOPS | | 13 | 2470 |
67 (2)| 00:00:01 | | |
| 44 | NESTED LOOPS | | 13 | 1794 |
54 (2)| 00:00:01 | | |
|* 45 | HASH JOIN | | 13 | 559 |
41 (3)| 00:00:01 | | |
|* 46 | TABLE ACCESS FULL | CNFGTR_USER_DTLS | 3 | 42 |
4 (0)| 00:00:01 | | |
|* 47 | TABLE ACCESS FULL | GC_CLM_SURVEYOR | 245 | 7105 |
36 (0)| 00:00:01 | | |
| 48 | TABLE ACCESS BY GLOBAL INDEX ROWID| GC_CLM_GEN_INFO | 1 | 95 |
1 (0)| 00:00:01 | ROWID | ROWID |
|* 49 | INDEX UNIQUE SCAN | PK_GC_CLM_GEN_INFO | 1 | |
0 (0)| 00:00:01 | | |
| 50 | TABLE ACCESS BY GLOBAL INDEX ROWID | GENMST_CUSTOMER | 1 | 52 |
1 (0)| 00:00:01 | ROWID | ROWID |
|* 51 | INDEX UNIQUE SCAN | GENMST_CUSTOMER_PK | 1 | |
0 (0)| 00:00:01 | | |
|* 52 | TABLE ACCESS BY GLOBAL INDEX ROWID | GEN_PROP_INFORMATION_TAB | 1 | 54 |
1 (0)| 00:00:01 | ROWID | ROWID |
|* 53 | INDEX UNIQUE SCAN | PK_GEN_PROP_INFO | 1 | |
0 (0)| 00:00:01 | | |
| 54 | TABLE ACCESS BY GLOBAL INDEX ROWID | RISK_HEADERS | 1 | 217 |
2 (0)| 00:00:01 | ROWID | ROWID |
|* 55 | INDEX RANGE SCAN | PK_RISK_HEADER_REF | 1 | |
1 (0)| 00:00:01 | | |
|* 56 | VIEW PUSHED PREDICATE | VW_SQ_6 | 1 | 13 |
2 (0)| 00:00:01 | | |
| 57 | SORT AGGREGATE | | 1 | 21 |
| | | |
|* 58 | INDEX RANGE SCAN | PK_GC_CLM_SURVEYOR | 1 | 21 |
2 (0)| 00:00:01 | | |
|* 59 | VIEW PUSHED PREDICATE | VW_SQ_7 | 1 | 13 |
2 (0)| 00:00:01 | | |
| 60 | SORT AGGREGATE | | 1 | 21 |
| | | |
| 61 | FIRST ROW | | 1 | 21 |
2 (0)| 00:00:01 | | |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|* 62 | INDEX RANGE SCAN (MIN/MAX) | PK_GC_CLM_SURVEYOR | 1 | 21 |
2 (0)| 00:00:01 | | |
|* 63 | HASH JOIN ANTI | | 1 | 39 |
7 (15)| 00:00:01 | | |
|* 64 | TABLE ACCESS FULL | GC_PDA_CLAIM_DELIVERED | 1 | 19 |
3 (0)| 00:00:01 | | |
|* 65 | TABLE ACCESS FULL | GC_PDA_CLAIM_REASSIGN | 1 | 20 |
3 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------
-------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$2 / SD@SEL$2
2 - SEL$0CEE953B
3 - SEL$0CEE953B / XC@SEL$3
4 - SEL$0CEE953B / XC@SEL$3
5 - SEL$B531425F / VW_SQ_1@SEL$2F12090E
6 - SEL$B531425F
8 - SEL$B531425F / VCC@SEL$4
9 - SEL$BC53533F
10 - SEL$BC53533F / XCCC@SEL$5
11 - SEL$BC53533F / XCCC@SEL$5
12 - SEL$13AC9F9F / VW_SQ_2@SEL$0A1A5A0C
13 - SEL$13AC9F9F
15 - SEL$13AC9F9F / DC@SEL$6
16 - SEL$21D54C12
17 - SEL$21D54C12 / D@SEL$7
18 - SEL$21D54C12 / D@SEL$7
19 - SEL$0C8B7B2B / VW_SQ_3@SEL$23A33D4B
20 - SEL$0C8B7B2B
22 - SEL$0C8B7B2B / XC@SEL$8
23 - SEL$15BD0953
24 - SEL$15BD0953 / D@SEL$9
25 - SEL$15BD0953 / D@SEL$9
26 - SEL$9214D63E / VW_SQ_4@SEL$778796C4
27 - SEL$9214D63E
29 - SEL$9214D63E / XCC@SEL$10
30 - SEL$E6A22765
31 - SEL$E6A22765 / ER@SEL$11
32 - SEL$E6A22765 / ER@SEL$11
33 - SEL$CBA73870 / VW_SQ_5@SEL$ACC926E7
34 - SEL$CBA73870
36 - SEL$CBA73870 / DRS@SEL$12
37 - SEL$ED490FAE
46 - SEL$ED490FAE / Z@SEL$1
47 - SEL$ED490FAE / B@SEL$1
48 - SEL$ED490FAE / A@SEL$1
49 - SEL$ED490FAE / A@SEL$1
50 - SEL$ED490FAE / F@SEL$1
51 - SEL$ED490FAE / F@SEL$1
52 - SEL$ED490FAE / C@SEL$1
53 - SEL$ED490FAE / C@SEL$1
54 - SEL$ED490FAE / D@SEL$1
55 - SEL$ED490FAE / D@SEL$1
56 - SEL$A0FCFBFC / VW_SQ_6@SEL$AE825B72
57 - SEL$A0FCFBFC
58 - SEL$A0FCFBFC / H@SEL$13
59 - SEL$67104784 / VW_SQ_7@SEL$7654B849
60 - SEL$67104784
62 - SEL$67104784 / HT@SEL$14
63 - SEL$8B2AE9CD
64 - SEL$8B2AE9CD / RT@SEL$16
65 - SEL$8B2AE9CD / ST@SEL$17
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("SD"."NUM_MASTER_CD"=75 AND "SD"."TXT_INFO1"= (SELECT "XC"."TXT_INFO31" FROM "INS"."GC
_CLM_GEN_INFO_EXTRA" "XC",
(SELECT MAX("VCC"."NUM_UPDATE_NO") "MAX(VCC.NUM_UPDATE_NO)" FROM "INS"."GC_CLM_GEN_INF
O_EXTRA" "VCC" WHERE
"VCC"."NUM_CLAIM_NO"="XC"."NUM_CLAIM_NO") "VW_SQ_1" WHERE "XC"."NUM_UPDATE_NO"="MAX(VC
C.NUM_UPDATE_NO)" AND
"XC"."NUM_CLAIM_NO"=:B1 AND "XC"."NUM_EXTRA_INFO_TYPE_CD"=2))
4 - access("XC"."NUM_CLAIM_NO"=:B1 AND "XC"."NUM_EXTRA_INFO_TYPE_CD"=2)
filter("XC"."NUM_EXTRA_INFO_TYPE_CD"=2)
5 - filter("XC"."NUM_UPDATE_NO"="MAX(VCC.NUM_UPDATE_NO)")
8 - access("VCC"."NUM_CLAIM_NO"="XC"."NUM_CLAIM_NO")
10 - filter("XCCC"."YN_ACTIVE_INACTIVE"='Y')
11 - access("XCCC"."NUM_CLAIM_NO"=:B1 AND "XCCC"."NUM_ADDITIONAL_INFO_TYPE_CD"=5)
filter("XCCC"."NUM_ADDITIONAL_INFO_TYPE_CD"=5)
12 - filter("XCCC"."NUM_UPDATE_NO"="MAX(DC.NUM_UPDATE_NO)")
15 - access("DC"."NUM_CLAIM_NO"="XCCC"."NUM_CLAIM_NO")
17 - filter("D"."YN_ACTIVE_INACTIVE"='Y')
18 - access("D"."NUM_CLAIM_NO"=:B1 AND "D"."NUM_ADDITIONAL_INFO_TYPE_CD"=16)
filter("D"."NUM_ADDITIONAL_INFO_TYPE_CD"=16)
19 - filter("D"."NUM_UPDATE_NO"="MAX(XC.NUM_UPDATE_NO)")
22 - access("XC"."NUM_CLAIM_NO"="D"."NUM_CLAIM_NO")
filter("XC"."NUM_ADDITIONAL_INFO_TYPE_CD"="D"."NUM_ADDITIONAL_INFO_TYPE_CD")
24 - filter("D"."YN_ACTIVE_INACTIVE"='Y')
25 - access("D"."NUM_CLAIM_NO"=:B1 AND "D"."NUM_ADDITIONAL_INFO_TYPE_CD"=2)
filter("D"."NUM_ADDITIONAL_INFO_TYPE_CD"=2)
26 - filter("D"."NUM_UPDATE_NO"="MAX(XCC.NUM_UPDATE_NO)")
29 - access("XCC"."NUM_CLAIM_NO"="D"."NUM_CLAIM_NO")
filter("XCC"."NUM_ADDITIONAL_INFO_TYPE_CD"="D"."NUM_ADDITIONAL_INFO_TYPE_CD")
32 - access("ER"."NUM_CLAIM_NO"=:B1 AND "ER"."NUM_EXTRA_INFO_TYPE_CD"=2)
filter("ER"."NUM_EXTRA_INFO_TYPE_CD"=2)
33 - filter("ER"."NUM_UPDATE_NO"="MAX(DRS.NUM_UPDATE_NO)")
36 - access("DRS"."NUM_CLAIM_NO"="ER"."NUM_CLAIM_NO")
filter("DRS"."NUM_EXTRA_INFO_TYPE_CD"="ER"."NUM_EXTRA_INFO_TYPE_CD")
38 - filter( NOT EXISTS (SELECT /*+ <not feasible>)
45 - access("Z"."CODE"=TO_CHAR("B"."NUM_INTERMEDIARY_CD"))
46 - filter(UPPER("Z"."USERID")='koliver')
47 - filter("B"."TXT_INTERMEDIARY_STATUS"='A' AND "B"."NUM_SURVEY_TYPE"<>1)
49 - access("SYS_ALIAS_4"."NUM_CLAIM_NO"="B"."NUM_CLAIM_NO" AND "SYS_ALIAS_4"."NUM_UPDATE_NO"=0)
51 - access("SYS_ALIAS_4"."TXT_INSURED_ID"="F"."TXT_CUSTOMER_CD")
52 - filter("C"."TXT_POLICY_NO_CHAR" IS NOT NULL AND "SYS_ALIAS_4"."TXT_POLICY_NO_CHAR"="C"."TXT_P
OLICY_NO_CHAR")
53 - access("C"."NUM_REFERENCE_NUMBER"="SYS_ALIAS_4"."NUM_REFERENCE_NO" AND
"C"."DAT_REFERENCE_DATE"="SYS_ALIAS_4"."DAT_REFERENCE_DATE")
55 - access("C"."NUM_REFERENCE_NUMBER"="D"."REFERENCE_NUM" AND "C"."DAT_REFERENCE_DATE"="D"."REFER
ENCE_DATE")
56 - filter("B"."NUM_SERIAL_NO"="MAX(H.NUM_SERIAL_NO)")
58 - access("H"."NUM_CLAIM_NO"="B"."NUM_CLAIM_NO" AND "H"."NUM_INTERMEDIARY_CD"="B"."NUM_INTERMEDI
ARY_CD")
filter("H"."NUM_INTERMEDIARY_CD"="B"."NUM_INTERMEDIARY_CD")
59 - filter("B"."NUM_UPDATE_NO"="MAX(HT.NUM_UPDATE_NO)")
62 - access("HT"."NUM_CLAIM_NO"="B"."NUM_CLAIM_NO")
filter("HT"."NUM_INTERMEDIARY_CD"="B"."NUM_INTERMEDIARY_CD")
63 - access("ST"."NUM_CLAIM_NO"="RT"."NUM_CLAIM_NO" AND "ST"."NUM_SURVEYOR_CODE"="RT"."NUM_SURVEYO
R_CODE" AND
"ST"."NUM_SURVEY_TYPE"="RT"."NUM_SURVEY_TYPE")
64 - filter("RT"."NUM_CLAIM_NO"=:B1 AND "RT"."NUM_SURVEYOR_CODE"=:B2 AND "RT"."NUM_SURVEY_TYPE"=:B
3)
65 - filter("ST"."YN_IS_DELIVERED"='N' AND "ST"."NUM_CLAIM_NO"=:B1 AND "ST"."NUM_SURVEYOR_CODE"=:B
2 AND
"ST"."NUM_SURVEY_TYPE"=:B3)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "SD"."NUM_MASTER_CD"[NUMBER,22], "SD"."TXT_INFO1"[VARCHAR2,200], "SD"."TXT_INFO2"[VARCHAR2,20
0]
2 - (#keys=0) "XC"."TXT_INFO31"[VARCHAR2,1000]
3 - "XC"."NUM_CLAIM_NO"[NUMBER,22], "XC"."NUM_UPDATE_NO"[NUMBER,22], "XC"."TXT_INFO31"[VARCHAR2,1
000]
4 - "XC".ROWID[ROWID,10], "XC"."NUM_CLAIM_NO"[NUMBER,22], "XC"."NUM_UPDATE_NO"[NUMBER,22]
5 - "MAX(VCC.NUM_UPDATE_NO)"[NUMBER,22]
6 - (#keys=0) MAX("VCC"."NUM_UPDATE_NO")[22]
7 - "VCC"."NUM_UPDATE_NO"[NUMBER,22]
8 - "VCC"."NUM_UPDATE_NO"[NUMBER,22]
9 - (#keys=0) "XCCC"."TXT_INFO2"[VARCHAR2,1000]
10 - "XCCC"."NUM_CLAIM_NO"[NUMBER,22], "XCCC"."NUM_UPDATE_NO"[NUMBER,22], "XCCC"."TXT_INFO2"[VARCH
AR2,1000]
11 - "XCCC".ROWID[ROWID,10], "XCCC"."NUM_CLAIM_NO"[NUMBER,22], "XCCC"."NUM_UPDATE_NO"[NUMBER,22]
12 - "MAX(DC.NUM_UPDATE_NO)"[NUMBER,22]
13 - (#keys=0) MAX("DC"."NUM_UPDATE_NO")[22]
14 - "DC"."NUM_UPDATE_NO"[NUMBER,22]
15 - "DC"."NUM_UPDATE_NO"[NUMBER,22]
16 - (#keys=0) "D"."TXT_INFO29"[VARCHAR2,1000]
17 - "D"."NUM_CLAIM_NO"[NUMBER,22], "D"."NUM_UPDATE_NO"[NUMBER,22], "D"."NUM_ADDITIONAL_INFO_TYPE_
CD"[NUMBER,22],
"D"."TXT_INFO29"[VARCHAR2,1000]
18 - "D".ROWID[ROWID,10], "D"."NUM_CLAIM_NO"[NUMBER,22], "D"."NUM_UPDATE_NO"[NUMBER,22],
"D"."NUM_ADDITIONAL_INFO_TYPE_CD"[NUMBER,22]
19 - "MAX(XC.NUM_UPDATE_NO)"[NUMBER,22]
20 - (#keys=0) MAX("XC"."NUM_UPDATE_NO")[22]
21 - "XC"."NUM_UPDATE_NO"[NUMBER,22]
22 - "XC"."NUM_UPDATE_NO"[NUMBER,22]
23 - (#keys=0) "D"."TXT_INFO19"[VARCHAR2,1000]
24 - "D"."NUM_CLAIM_NO"[NUMBER,22], "D"."NUM_UPDATE_NO"[NUMBER,22], "D"."NUM_ADDITIONAL_INFO_TYPE_
CD"[NUMBER,22],
"D"."TXT_INFO19"[VARCHAR2,1000]
25 - "D".ROWID[ROWID,10], "D"."NUM_CLAIM_NO"[NUMBER,22], "D"."NUM_UPDATE_NO"[NUMBER,22],
"D"."NUM_ADDITIONAL_INFO_TYPE_CD"[NUMBER,22]
26 - "MAX(XCC.NUM_UPDATE_NO)"[NUMBER,22]
27 - (#keys=0) MAX("XCC"."NUM_UPDATE_NO")[22]
28 - "XCC"."NUM_UPDATE_NO"[NUMBER,22]
29 - "XCC"."NUM_UPDATE_NO"[NUMBER,22]
30 - (#keys=0) "ER"."TXT_INFO17"[VARCHAR2,1000]
31 - "ER"."NUM_CLAIM_NO"[NUMBER,22], "ER"."NUM_UPDATE_NO"[NUMBER,22], "ER"."NUM_EXTRA_INFO_TYPE_CD
"[NUMBER,22],
"ER"."TXT_INFO17"[VARCHAR2,1000]
32 - "ER".ROWID[ROWID,10], "ER"."NUM_CLAIM_NO"[NUMBER,22], "ER"."NUM_UPDATE_NO"[NUMBER,22],
"ER"."NUM_EXTRA_INFO_TYPE_CD"[NUMBER,22]
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
33 - "MAX(DRS.NUM_UPDATE_NO)"[NUMBER,22]
34 - (#keys=0) MAX("DRS"."NUM_UPDATE_NO")[22]
35 - "DRS"."NUM_UPDATE_NO"[NUMBER,22]
36 - "DRS"."NUM_UPDATE_NO"[NUMBER,22]
37 - (#keys=32) "SYS_ALIAS_4"."NUM_CLAIM_NO"[NUMBER,22], "B"."NUM_INTERMEDIARY_CD"[NUMBER,22],
"B"."NUM_SURVEY_TYPE"[NUMBER,22], "SYS_ALIAS_4"."TXT_MASTER_CLAIM_NO"||"GET_CHILD_SERIAL_NO"(
"SYS_ALIAS_4"."NUM_SERIAL_NO")[4000]
, "SYS_ALIAS_4"."DAT_REGISTRATION_DATE"[DATE,7], "SYS_ALIAS_4"."DAT_LOSS_DATE"[DATE,7],
"SYS_ALIAS_4"."TXT_REMARKS"[VARCHAR2,2000], (SELECT "SD"."TXT_INFO2" FROM "INS"."GC_CLMMST_G
ENERIC_VALUE" "SD" WHERE
"SD"."TXT_INFO1"= (SELECT "XC"."TXT_INFO31" FROM "INS"."GC_CLM_GEN_INFO_EXTRA" "XC", (SELECT
MAX("VCC"."NUM_UPDATE_NO")
"MAX(VCC.NUM_UPDATE_NO)" FROM "INS"."GC_CLM_GEN_INFO_EXTRA" "VCC" WHERE "VCC"."NUM_CLAIM_NO"=
"XC"."NUM_CLAIM_NO") "VW_SQ_1"
WHERE "XC"."NUM_UPDATE_NO"="MAX(VCC.NUM_UPDATE_NO)" AND "XC"."NUM_CLAIM_NO"=:B1 AND "XC"."NUM
_EXTRA_INFO_TYPE_CD"=2) AND
"SD"."NUM_MASTER_CD"=75)[200], "SYS_ALIAS_4"."TXT_POLICY_NO_CHAR"[VARCHAR2,30],
INTERNAL_FUNCTION("C"."DAT_POLICY_EFF_FROMDATE")||' '||"C"."TXT_POLICY_EFF_FROMHOUR"[41],
INTERNAL_FUNCTION("C"."DAT_POLICY_EFF_TODATE")||' '||"C"."TXT_POLICY_EFF_TOHOUR"[41], "F"."TX
T_FIRSTNAME"[VARCHAR2,180],
"F"."TXT_EMAIL"[VARCHAR2,100], CASE WHEN "F"."TXT_IND_CORP_FLAG"='C' THEN "F"."TXT_CUSTOMER_
NAME" ELSE "F"."TXT_LASTNAME" END
[350], "F"."TXT_TELEPHONE"[VARCHAR2,15], "F"."TXT_MOBILE"[VARCHAR2,15], "F"."TXT_FAX_NUMBER"[
VARCHAR2,15], CASE
"SYS_ALIAS_4"."NUM_PRODUCT_CODE" WHEN 3121 THEN "D"."INFORMATION6"||"D"."INFORMATION102"||"D"
."INFORMATION104"||"D"."INFORMATION1
06" WHEN 3122 THEN "D"."INFORMATION33"||"D"."INFORMATION34"||"D"."INFORMATION35"||"D"."INFORM
ATION36" WHEN 3124 THEN
"D"."INFORMATION15"||"D"."INFORMATION21"||"D"."INFORMATION22"||"D"."INFORMATION23" END [4000]
, CASE
"SYS_ALIAS_4"."NUM_PRODUCT_CODE" WHEN 3121 THEN "D"."INFORMATION3" WHEN 3122 THEN "D"."INFORM
ATION25" WHEN 3124 THEN
"D"."INFORMATION11" END [4000], CASE "SYS_ALIAS_4"."NUM_PRODUCT_CODE" WHEN 3121 THEN
'01/'||LPAD("D"."INFORMATION42",2,'0')||'/'||"D"."INFORMATION51" WHEN 3122 THEN
'01/'||LPAD("D"."INFORMATION27",2,'0')||'/'||"D"."INFORMATION28" WHEN 3124 THEN
'01/'||LPAD("D"."INFORMATION37",2,'0')||'/'||"D"."INFORMATION10" END [4000],
DECODE(TO_CHAR("SYS_ALIAS_4"."NUM_PRODUCT_CODE"),'3121',"D"."INFORMATION38",'3122',"D"."INFOR
MATION22",'3124',"D"."INFORMATION36"
)[4000], DECODE(TO_CHAR("SYS_ALIAS_4"."NUM_PRODUCT_CODE"),'3121',"D"."INFORMATION36",'3122',"
D"."INFORMATION23",'3124',"D"."INFOR
MATION35")[4000], DECODE(TO_CHAR("SYS_ALIAS_4"."NUM_PRODUCT_CODE"),'3121',"D"."INFORMATION14"
,'3122',"D"."INFORMATION37",'3124',"
D"."INFORMATION16")[4000], DECODE(TO_CHAR("SYS_ALIAS_4"."NUM_PRODUCT_CODE"),'3121',"D"."INFOR
MATION16",'3122',"D"."INFORMATION38"
,'3124',"D"."INFORMATION17")[4000], CASE "SYS_ALIAS_4"."NUM_PRODUCT_CODE" WHEN 3121 THEN "D".
"INFORMATION12" WHEN 3122 THEN
"D"."INFORMATION26" WHEN 3124 THEN "D"."INFORMATION14" END [4000], (SELECT "XCCC"."TXT_INFO2
" FROM
"INS"."GC_CLM_MOT_ADDITIONAL" "XCCC", (SELECT MAX("DC"."NUM_UPDATE_NO") "MAX(DC.NUM_UPDATE_NO
)" FROM
"INS"."GC_CLM_MOT_ADDITIONAL" "DC" WHERE "DC"."NUM_CLAIM_NO"="XCCC"."NUM_CLAIM_NO") "VW_SQ_2"
WHERE
"XCCC"."NUM_UPDATE_NO"="MAX(DC.NUM_UPDATE_NO)" AND "XCCC"."NUM_CLAIM_NO"=:B2 AND "XCCC"."YN_A
CTIVE_INACTIVE"='Y' AND
"XCCC"."NUM_ADDITIONAL_INFO_TYPE_CD"=5)[1000], DECODE(TO_CHAR("SYS_ALIAS_4"."NUM_PRODUCT_CODE
"),'3121',"D"."INFORMATION52",'3122'
,"D"."INFORMATION39",'3124',"D"."INFORMATION181")[4000], NVL( (SELECT "D"."TXT_INFO29" FROM "
INS"."GC_CLM_MOT_ADDITIONAL" "D",
(SELECT MAX("XC"."NUM_UPDATE_NO") "MAX(XC.NUM_UPDATE_NO)" FROM "INS"."GC_CLM_MOT_ADDITIONAL"
"XC" WHERE
"XC"."NUM_CLAIM_NO"="D"."NUM_CLAIM_NO" AND "XC"."NUM_ADDITIONAL_INFO_TYPE_CD"="D"."NUM_ADDITI
ONAL_INFO_TYPE_CD") "VW_SQ_3" WHERE
"D"."NUM_UPDATE_NO"="MAX(XC.NUM_UPDATE_NO)" AND "D"."NUM_CLAIM_NO"=:B3 AND "D"."YN_ACTIVE_INA
CTIVE"='Y' AND
"D"."NUM_ADDITIONAL_INFO_TYPE_CD"=16), (SELECT "D"."TXT_INFO19" FROM "INS"."GC_CLM_MOT_ADDITI
ONAL" "D", (SELECT
MAX("XCC"."NUM_UPDATE_NO") "MAX(XCC.NUM_UPDATE_NO)" FROM "INS"."GC_CLM_MOT_ADDITIONAL" "XCC"
WHERE "XCC"."NUM_CLAIM_NO"="D"."NU
38 - "B"."NUM_INTERMEDIARY_CD"[NUMBER,22], "B"."NUM_SURVEY_TYPE"[NUMBER,22], "SYS_ALIAS_4"."NUM_CL
AIM_NO"[NUMBER,22],
"SYS_ALIAS_4"."TXT_MASTER_CLAIM_NO"[VARCHAR2,20], "SYS_ALIAS_4"."NUM_SERIAL_NO"[NUMBER,22],
"SYS_ALIAS_4"."NUM_PRODUCT_CODE"[NUMBER,22], "SYS_ALIAS_4"."DAT_REGISTRATION_DATE"[DATE,7],
"SYS_ALIAS_4"."DAT_LOSS_DATE"[DATE,7], "SYS_ALIAS_4"."TXT_POLICY_NO_CHAR"[VARCHAR2,30],
"SYS_ALIAS_4"."TXT_REMARKS"[VARCHAR2,2000], "F"."TXT_CUSTOMER_NAME"[VARCHAR2,350], "F"."TXT_I
ND_CORP_FLAG"[CHARACTER,1],
"F"."TXT_EMAIL"[VARCHAR2,100], "F"."TXT_TELEPHONE"[VARCHAR2,15], "F"."TXT_MOBILE"[VARCHAR2,15
],
"F"."TXT_FAX_NUMBER"[VARCHAR2,15], "F"."TXT_FIRSTNAME"[VARCHAR2,180], "F"."TXT_LASTNAME"[VARC
HAR2,90],
"C"."DAT_POLICY_EFF_FROMDATE"[DATE,7], "C"."TXT_POLICY_EFF_FROMHOUR"[VARCHAR2,30], "C"."DAT_P
OLICY_EFF_TODATE"[DATE,7],
"C"."TXT_POLICY_EFF_TOHOUR"[VARCHAR2,30], "D"."INFORMATION3"[VARCHAR2,4000], "D"."INFORMATION
6"[VARCHAR2,4000],
"D"."INFORMATION8"[VARCHAR2,4000], "D"."INFORMATION10"[VARCHAR2,4000], "D"."INFORMATION11"[VA
RCHAR2,4000],
"D"."INFORMATION12"[VARCHAR2,4000], "D"."INFORMATION14"[VARCHAR2,4000], "D"."INFORMATION15"[V
ARCHAR2,4000],
"D"."INFORMATION16"[VARCHAR2,4000], "D"."INFORMATION17"[VARCHAR2,4000], "D"."INFORMATION19"[V
ARCHAR2,4000],
"D"."INFORMATION20"[VARCHAR2,4000], "D"."INFORMATION21"[VARCHAR2,4000], "D"."INFORMATION22"[V
ARCHAR2,4000],
"D"."INFORMATION23"[VARCHAR2,4000], "D"."INFORMATION25"[VARCHAR2,4000], "D"."INFORMATION26"[V
ARCHAR2,4000],
"D"."INFORMATION27"[VARCHAR2,4000], "D"."INFORMATION28"[VARCHAR2,4000], "D"."INFORMATION33"[V
ARCHAR2,4000],
"D"."INFORMATION34"[VARCHAR2,4000], "D"."INFORMATION35"[VARCHAR2,4000], "D"."INFORMATION36"[V
ARCHAR2,4000],
"D"."INFORMATION37"[VARCHAR2,4000], "D"."INFORMATION38"[VARCHAR2,4000], "D"."INFORMATION39"[V
ARCHAR2,4000],
"D"."INFORMATION42"[VARCHAR2,4000], "D"."INFORMATION51"[VARCHAR2,4000], "D"."INFORMATION52"[V
ARCHAR2,4000],
"D"."INFORMATION65"[VARCHAR2,4000], "D"."INFORMATION89"[VARCHAR2,4000], "D"."INFORMATION96"[V
ARCHAR2,4000],
"D"."INFORMATION102"[VARCHAR2,4000], "D"."INFORMATION104"[VARCHAR2,4000], "D"."INFORMATION106
"[VARCHAR2,4000],
"D"."INFORMATION117"[VARCHAR2,4000], "D"."INFORMATION168"[VARCHAR2,4000], "D"."INFORMATION181
"[VARCHAR2,4000]
39 - (#keys=0) "B"."NUM_CLAIM_NO"[NUMBER,22], "B"."NUM_INTERMEDIARY_CD"[NUMBER,22], "B"."NUM_SURVE
Y_TYPE"[NUMBER,22],
"SYS_ALIAS_4"."NUM_CLAIM_NO"[NUMBER,22], "SYS_ALIAS_4"."TXT_MASTER_CLAIM_NO"[VARCHAR2,20],
"SYS_ALIAS_4"."NUM_SERIAL_NO"[NUMBER,22], "SYS_ALIAS_4"."NUM_PRODUCT_CODE"[NUMBER,22],
"SYS_ALIAS_4"."DAT_REGISTRATION_DATE"[DATE,7], "SYS_ALIAS_4"."DAT_LOSS_DATE"[DATE,7],
"SYS_ALIAS_4"."TXT_POLICY_NO_CHAR"[VARCHAR2,30], "SYS_ALIAS_4"."TXT_REMARKS"[VARCHAR2,2000],
"F"."TXT_CUSTOMER_NAME"[VARCHAR2,350], "F"."TXT_IND_CORP_FLAG"[CHARACTER,1], "F"."TXT_EMAIL"[
VARCHAR2,100],
"F"."TXT_TELEPHONE"[VARCHAR2,15], "F"."TXT_MOBILE"[VARCHAR2,15], "F"."TXT_FAX_NUMBER"[VARCHAR
2,15],
"F"."TXT_FIRSTNAME"[VARCHAR2,180], "F"."TXT_LASTNAME"[VARCHAR2,90], "C"."DAT_POLICY_EFF_FROMD
ATE"[DATE,7],
"C"."TXT_POLICY_EFF_FROMHOUR"[VARCHAR2,30], "C"."DAT_POLICY_EFF_TODATE"[DATE,7], "C"."TXT_POL
ICY_EFF_TOHOUR"[VARCHAR2,30],
"D"."INFORMATION3"[VARCHAR2,4000], "D"."INFORMATION6"[VARCHAR2,4000], "D"."INFORMATION8"[VARC
HAR2,4000],
"D"."INFORMATION10"[VARCHAR2,4000], "D"."INFORMATION11"[VARCHAR2,4000], "D"."INFORMATION12"[V
ARCHAR2,4000],
"D"."INFORMATION14"[VARCHAR2,4000], "D"."INFORMATION15"[VARCHAR2,4000], "D"."INFORMATION16"[V
ARCHAR2,4000],
"D"."INFORMATION17"[VARCHAR2,4000], "D"."INFORMATION19"[VARCHAR2,4000], "D"."INFORMATION20"[V
ARCHAR2,4000],
"D"."INFORMATION21"[VARCHAR2,4000], "D"."INFORMATION22"[VARCHAR2,4000], "D"."INFORMATION23"[V
ARCHAR2,4000],
"D"."INFORMATION25"[VARCHAR2,4000], "D"."INFORMATION26"[VARCHAR2,4000], "D"."INFORMATION27"[V
ARCHAR2,4000],
"D"."INFORMATION28"[VARCHAR2,4000], "D"."INFORMATION33"[VARCHAR2,4000], "D"."INFORMATION34"[V
ARCHAR2,4000],
"D"."INFORMATION35"[VARCHAR2,4000], "D"."INFORMATION36"[VARCHAR2,4000], "D"."INFORMATION37"[V
ARCHAR2,4000],
"D"."INFORMATION38"[VARCHAR2,4000], "D"."INFORMATION39"[VARCHAR2,4000], "D"."INFORMATION42"[V
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
ARCHAR2,4000],
"D"."INFORMATION51"[VARCHAR2,4000], "D"."INFORMATION52"[VARCHAR2,4000], "D"."INFORMATION65"[V
ARCHAR2,4000],
"D"."INFORMATION89"[VARCHAR2,4000], "D"."INFORMATION96"[VARCHAR2,4000], "D"."INFORMATION102"[
VARCHAR2,4000],
"D"."INFORMATION104"[VARCHAR2,4000], "D"."INFORMATION106"[VARCHAR2,4000], "D"."INFORMATION117
"[VARCHAR2,4000],
"D"."INFORMATION168"[VARCHAR2,4000], "D"."INFORMATION181"[VARCHAR2,4000]
40 - (#keys=0) "B"."NUM_CLAIM_NO"[NUMBER,22], "B"."NUM_UPDATE_NO"[NUMBER,22], "B"."NUM_INTERMEDIAR
Y_CD"[NUMBER,22],
"B"."NUM_SURVEY_TYPE"[NUMBER,22], "SYS_ALIAS_4"."NUM_CLAIM_NO"[NUMBER,22], "SYS_ALIAS_4"."TXT
_MASTER_CLAIM_NO"[VARCHAR2,20],
"SYS_ALIAS_4"."NUM_SERIAL_NO"[NUMBER,22], "SYS_ALIAS_4"."NUM_PRODUCT_CODE"[NUMBER,22],
"SYS_ALIAS_4"."DAT_REGISTRATION_DATE"[DATE,7], "SYS_ALIAS_4"."DAT_LOSS_DATE"[DATE,7],
"SYS_ALIAS_4"."TXT_POLICY_NO_CHAR"[VARCHAR2,30], "SYS_ALIAS_4"."TXT_REMARKS"[VARCHAR2,2000],
"F"."TXT_CUSTOMER_NAME"[VARCHAR2,350], "F"."TXT_IND_CORP_FLAG"[CHARACTER,1], "F"."TXT_EMAIL"[
VARCHAR2,100],
"F"."TXT_TELEPHONE"[VARCHAR2,15], "F"."TXT_MOBILE"[VARCHAR2,15], "F"."TXT_FAX_NUMBER"[VARCHAR
2,15],
"F"."TXT_FIRSTNAME"[VARCHAR2,180], "F"."TXT_LASTNAME"[VARCHAR2,90], "C"."DAT_POLICY_EFF_FROMD
ATE"[DATE,7],
"C"."TXT_POLICY_EFF_FROMHOUR"[VARCHAR2,30], "C"."DAT_POLICY_EFF_TODATE"[DATE,7], "C"."TXT_POL
ICY_EFF_TOHOUR"[VARCHAR2,30],
"D"."INFORMATION3"[VARCHAR2,4000], "D"."INFORMATION6"[VARCHAR2,4000], "D"."INFORMATION8"[VARC
HAR2,4000],
"D"."INFORMATION10"[VARCHAR2,4000], "D"."INFORMATION11"[VARCHAR2,4000], "D"."INFORMATION12"[V
ARCHAR2,4000],
"D"."INFORMATION14"[VARCHAR2,4000], "D"."INFORMATION15"[VARCHAR2,4000], "D"."INFORMATION16"[V
ARCHAR2,4000],
"D"."INFORMATION17"[VARCHAR2,4000], "D"."INFORMATION19"[VARCHAR2,4000], "D"."INFORMATION20"[V
ARCHAR2,4000],
"D"."INFORMATION21"[VARCHAR2,4000], "D"."INFORMATION22"[VARCHAR2,4000], "D"."INFORMATION23"[V
ARCHAR2,4000],
"D"."INFORMATION25"[VARCHAR2,4000], "D"."INFORMATION26"[VARCHAR2,4000], "D"."INFORMATION27"[V
ARCHAR2,4000],
"D"."INFORMATION28"[VARCHAR2,4000], "D"."INFORMATION33"[VARCHAR2,4000], "D"."INFORMATION34"[V
ARCHAR2,4000],
"D"."INFORMATION35"[VARCHAR2,4000], "D"."INFORMATION36"[VARCHAR2,4000], "D"."INFORMATION37"[V
ARCHAR2,4000],
"D"."INFORMATION38"[VARCHAR2,4000], "D"."INFORMATION39"[VARCHAR2,4000], "D"."INFORMATION42"[V
ARCHAR2,4000],
"D"."INFORMATION51"[VARCHAR2,4000], "D"."INFORMATION52"[VARCHAR2,4000], "D"."INFORMATION65"[V
ARCHAR2,4000],
"D"."INFORMATION89"[VARCHAR2,4000], "D"."INFORMATION96"[VARCHAR2,4000], "D"."INFORMATION102"[
VARCHAR2,4000],
"D"."INFORMATION104"[VARCHAR2,4000], "D"."INFORMATION106"[VARCHAR2,4000], "D"."INFORMATION117
"[VARCHAR2,4000],
"D"."INFORMATION168"[VARCHAR2,4000], "D"."INFORMATION181"[VARCHAR2,4000]
41 - (#keys=0) "B"."NUM_CLAIM_NO"[NUMBER,22], "B"."NUM_UPDATE_NO"[NUMBER,22], "B"."NUM_SERIAL_NO"[
NUMBER,22],
"B"."NUM_INTERMEDIARY_CD"[NUMBER,22], "B"."NUM_SURVEY_TYPE"[NUMBER,22], "SYS_ALIAS_4"."NUM_CL
AIM_NO"[NUMBER,22],
"SYS_ALIAS_4"."TXT_MASTER_CLAIM_NO"[VARCHAR2,20], "SYS_ALIAS_4"."NUM_SERIAL_NO"[NUMBER,22],
"SYS_ALIAS_4"."NUM_PRODUCT_CODE"[NUMBER,22], "SYS_ALIAS_4"."DAT_REGISTRATION_DATE"[DATE,7],
"SYS_ALIAS_4"."DAT_LOSS_DATE"[DATE,7], "SYS_ALIAS_4"."TXT_POLICY_NO_CHAR"[VARCHAR2,30],
"SYS_ALIAS_4"."TXT_REMARKS"[VARCHAR2,2000], "F"."TXT_CUSTOMER_NAME"[VARCHAR2,350], "F"."TXT_I
ND_CORP_FLAG"[CHARACTER,1],
"F"."TXT_EMAIL"[VARCHAR2,100], "F"."TXT_TELEPHONE"[VARCHAR2,15], "F"."TXT_MOBILE"[VARCHAR2,15
],
"F"."TXT_FAX_NUMBER"[VARCHAR2,15], "F"."TXT_FIRSTNAME"[VARCHAR2,180], "F"."TXT_LASTNAME"[VARC
HAR2,90],
"C"."DAT_POLICY_EFF_FROMDATE"[DATE,7], "C"."TXT_POLICY_EFF_FROMHOUR"[VARCHAR2,30], "C"."DAT_P
OLICY_EFF_TODATE"[DATE,7],
"C"."TXT_POLICY_EFF_TOHOUR"[VARCHAR2,30], "D"."INFORMATION3"[VARCHAR2,4000], "D"."INFORMATION
6"[VARCHAR2,4000],
"D"."INFORMATION8"[VARCHAR2,4000], "D"."INFORMATION10"[VARCHAR2,4000], "D"."INFORMATION11"[VA
RCHAR2,4000],
"D"."INFORMATION12"[VARCHAR2,4000], "D"."INFORMATION14"[VARCHAR2,4000], "D"."INFORMATION15"[V
ARCHAR2,4000],
"D"."INFORMATION16"[VARCHAR2,4000], "D"."INFORMATION17"[VARCHAR2,4000], "D"."INFORMATION19"[V
ARCHAR2,4000],
"D"."INFORMATION20"[VARCHAR2,4000], "D"."INFORMATION21"[VARCHAR2,4000], "D"."INFORMATION22"[V
ARCHAR2,4000],
"D"."INFORMATION23"[VARCHAR2,4000], "D"."INFORMATION25"[VARCHAR2,4000], "D"."INFORMATION26"[V
ARCHAR2,4000],
"D"."INFORMATION27"[VARCHAR2,4000], "D"."INFORMATION28"[VARCHAR2,4000], "D"."INFORMATION33"[V
ARCHAR2,4000],
"D"."INFORMATION34"[VARCHAR2,4000], "D"."INFORMATION35"[VARCHAR2,4000], "D"."INFORMATION36"[V
ARCHAR2,4000],
"D"."INFORMATION37"[VARCHAR2,4000], "D"."INFORMATION38"[VARCHAR2,4000], "D"."INFORMATION39"[V
ARCHAR2,4000],
"D"."INFORMATION42"[VARCHAR2,4000], "D"."INFORMATION51"[VARCHAR2,4000], "D"."INFORMATION52"[V
ARCHAR2,4000],
"D"."INFORMATION65"[VARCHAR2,4000], "D"."INFORMATION89"[VARCHAR2,4000], "D"."INFORMATION96"[V
ARCHAR2,4000],
"D"."INFORMATION102"[VARCHAR2,4000], "D"."INFORMATION104"[VARCHAR2,4000], "D"."INFORMATION106
"[VARCHAR2,4000],
"D"."INFORMATION117"[VARCHAR2,4000], "D"."INFORMATION168"[VARCHAR2,4000], "D"."INFORMATION181
"[VARCHAR2,4000]
42 - (#keys=0) "B"."NUM_CLAIM_NO"[NUMBER,22], "B"."NUM_UPDATE_NO"[NUMBER,22], "B"."NUM_SERIAL_NO"[
NUMBER,22],
"B"."NUM_INTERMEDIARY_CD"[NUMBER,22], "B"."NUM_SURVEY_TYPE"[NUMBER,22], "SYS_ALIAS_4"."NUM_CL
AIM_NO"[NUMBER,22],
"SYS_ALIAS_4"."TXT_MASTER_CLAIM_NO"[VARCHAR2,20], "SYS_ALIAS_4"."NUM_SERIAL_NO"[NUMBER,22],
"SYS_ALIAS_4"."NUM_PRODUCT_CODE"[NUMBER,22], "SYS_ALIAS_4"."DAT_REGISTRATION_DATE"[DATE,7],
"SYS_ALIAS_4"."DAT_LOSS_DATE"[DATE,7], "SYS_ALIAS_4"."TXT_POLICY_NO_CHAR"[VARCHAR2,30],
"SYS_ALIAS_4"."TXT_REMARKS"[VARCHAR2,2000], "F"."TXT_CUSTOMER_NAME"[VARCHAR2,350], "F"."TXT_I
ND_CORP_FLAG"[CHARACTER,1],
"F"."TXT_EMAIL"[VARCHAR2,100], "F"."TXT_TELEPHONE"[VARCHAR2,15], "F"."TXT_MOBILE"[VARCHAR2,15
],
"F"."TXT_FAX_NUMBER"[VARCHAR2,15], "F"."TXT_FIRSTNAME"[VARCHAR2,180], "F"."TXT_LASTNAME"[VARC
HAR2,90],
"C"."NUM_REFERENCE_NUMBER"[NUMBER,22], "C"."DAT_REFERENCE_DATE"[DATE,7], "C"."DAT_POLICY_EFF_
FROMDATE"[DATE,7],
"C"."TXT_POLICY_EFF_FROMHOUR"[VARCHAR2,30], "C"."DAT_POLICY_EFF_TODATE"[DATE,7], "C"."TXT_POL
ICY_EFF_TOHOUR"[VARCHAR2,30]
43 - (#keys=0) "B"."NUM_CLAIM_NO"[NUMBER,22], "B"."NUM_UPDATE_NO"[NUMBER,22], "B"."NUM_SERIAL_NO"[
NUMBER,22],
"B"."NUM_INTERMEDIARY_CD"[NUMBER,22], "B"."NUM_SURVEY_TYPE"[NUMBER,22], "SYS_ALIAS_4"."NUM_CL
AIM_NO"[NUMBER,22],
"SYS_ALIAS_4"."TXT_MASTER_CLAIM_NO"[VARCHAR2,20], "SYS_ALIAS_4"."NUM_SERIAL_NO"[NUMBER,22],
"SYS_ALIAS_4"."NUM_PRODUCT_CODE"[NUMBER,22], "SYS_ALIAS_4"."DAT_REGISTRATION_DATE"[DATE,7],
"SYS_ALIAS_4"."DAT_LOSS_DATE"[DATE,7], "SYS_ALIAS_4"."TXT_POLICY_NO_CHAR"[VARCHAR2,30],
"SYS_ALIAS_4"."NUM_REFERENCE_NO"[NUMBER,22], "SYS_ALIAS_4"."DAT_REFERENCE_DATE"[DATE,7],
"SYS_ALIAS_4"."TXT_REMARKS"[VARCHAR2,2000], "F"."TXT_CUSTOMER_NAME"[VARCHAR2,350], "F"."TXT_I
ND_CORP_FLAG"[CHARACTER,1],
"F"."TXT_EMAIL"[VARCHAR2,100], "F"."TXT_TELEPHONE"[VARCHAR2,15], "F"."TXT_MOBILE"[VARCHAR2,15
],
"F"."TXT_FAX_NUMBER"[VARCHAR2,15], "F"."TXT_FIRSTNAME"[VARCHAR2,180], "F"."TXT_LASTNAME"[VARC
HAR2,90]
44 - (#keys=0) "B"."NUM_CLAIM_NO"[NUMBER,22], "B"."NUM_UPDATE_NO"[NUMBER,22], "B"."NUM_SERIAL_NO"[
NUMBER,22],
"B"."NUM_INTERMEDIARY_CD"[NUMBER,22], "B"."NUM_SURVEY_TYPE"[NUMBER,22], "SYS_ALIAS_4"."NUM_CL
AIM_NO"[NUMBER,22],
"SYS_ALIAS_4"."TXT_MASTER_CLAIM_NO"[VARCHAR2,20], "SYS_ALIAS_4"."NUM_SERIAL_NO"[NUMBER,22],
"SYS_ALIAS_4"."NUM_PRODUCT_CODE"[NUMBER,22], "SYS_ALIAS_4"."DAT_REGISTRATION_DATE"[DATE,7],
"SYS_ALIAS_4"."DAT_LOSS_DATE"[DATE,7], "SYS_ALIAS_4"."TXT_POLICY_NO_CHAR"[VARCHAR2,30],
"SYS_ALIAS_4"."NUM_REFERENCE_NO"[NUMBER,22], "SYS_ALIAS_4"."DAT_REFERENCE_DATE"[DATE,7],
"SYS_ALIAS_4"."TXT_INSURED_ID"[VARCHAR2,100], "SYS_ALIAS_4"."TXT_REMARKS"[VARCHAR2,2000]
45 - (#keys=1) "B"."NUM_CLAIM_NO"[NUMBER,22], "B"."NUM_UPDATE_NO"[NUMBER,22], "B"."NUM_SERIAL_NO"[
NUMBER,22],
"B"."NUM_INTERMEDIARY_CD"[NUMBER,22], "B"."NUM_SURVEY_TYPE"[NUMBER,22]
46 - "Z"."CODE"[VARCHAR2,20]
47 - "B"."NUM_CLAIM_NO"[NUMBER,22], "B"."NUM_UPDATE_NO"[NUMBER,22], "B"."NUM_SERIAL_NO"[NUMBER,22]
,
"B"."NUM_INTERMEDIARY_CD"[NUMBER,22], "B"."NUM_SURVEY_TYPE"[NUMBER,22]
48 - "SYS_ALIAS_4"."NUM_CLAIM_NO"[NUMBER,22], "SYS_ALIAS_4"."TXT_MASTER_CLAIM_NO"[VARCHAR2,20],
"SYS_ALIAS_4"."NUM_SERIAL_NO"[NUMBER,22], "SYS_ALIAS_4"."NUM_PRODUCT_CODE"[NUMBER,22],
"SYS_ALIAS_4"."DAT_REGISTRATION_DATE"[DATE,7], "SYS_ALIAS_4"."DAT_LOSS_DATE"[DATE,7],
"SYS_ALIAS_4"."TXT_POLICY_NO_CHAR"[VARCHAR2,30], "SYS_ALIAS_4"."NUM_REFERENCE_NO"[NUMBER,22],
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
"SYS_ALIAS_4"."DAT_REFERENCE_DATE"[DATE,7], "SYS_ALIAS_4"."TXT_INSURED_ID"[VARCHAR2,100],
"SYS_ALIAS_4"."TXT_REMARKS"[VARCHAR2,2000]
49 - "SYS_ALIAS_4".ROWID[ROWID,10], "SYS_ALIAS_4"."NUM_CLAIM_NO"[NUMBER,22]
50 - "F"."TXT_CUSTOMER_NAME"[VARCHAR2,350], "F"."TXT_IND_CORP_FLAG"[CHARACTER,1], "F"."TXT_EMAIL"[
VARCHAR2,100],
"F"."TXT_TELEPHONE"[VARCHAR2,15], "F"."TXT_MOBILE"[VARCHAR2,15], "F"."TXT_FAX_NUMBER"[VARCHAR
2,15],
"F"."TXT_FIRSTNAME"[VARCHAR2,180], "F"."TXT_LASTNAME"[VARCHAR2,90]
51 - "F".ROWID[ROWID,10]
52 - "C"."NUM_REFERENCE_NUMBER"[NUMBER,22], "C"."DAT_REFERENCE_DATE"[DATE,7], "C"."DAT_POLICY_EFF_
FROMDATE"[DATE,7],
"C"."TXT_POLICY_EFF_FROMHOUR"[VARCHAR2,30], "C"."DAT_POLICY_EFF_TODATE"[DATE,7], "C"."TXT_POL
ICY_EFF_TOHOUR"[VARCHAR2,30]
53 - "C".ROWID[ROWID,10], "C"."NUM_REFERENCE_NUMBER"[NUMBER,22], "C"."DAT_REFERENCE_DATE"[DATE,7]
54 - "D"."INFORMATION3"[VARCHAR2,4000], "D"."INFORMATION6"[VARCHAR2,4000], "D"."INFORMATION8"[VARC
HAR2,4000],
"D"."INFORMATION10"[VARCHAR2,4000], "D"."INFORMATION11"[VARCHAR2,4000], "D"."INFORMATION12"[V
ARCHAR2,4000],
"D"."INFORMATION14"[VARCHAR2,4000], "D"."INFORMATION15"[VARCHAR2,4000], "D"."INFORMATION16"[V
ARCHAR2,4000],
"D"."INFORMATION17"[VARCHAR2,4000], "D"."INFORMATION19"[VARCHAR2,4000], "D"."INFORMATION20"[V
ARCHAR2,4000],
"D"."INFORMATION21"[VARCHAR2,4000], "D"."INFORMATION22"[VARCHAR2,4000], "D"."INFORMATION23"[V
ARCHAR2,4000],
"D"."INFORMATION25"[VARCHAR2,4000], "D"."INFORMATION26"[VARCHAR2,4000], "D"."INFORMATION27"[V
ARCHAR2,4000],
"D"."INFORMATION28"[VARCHAR2,4000], "D"."INFORMATION33"[VARCHAR2,4000], "D"."INFORMATION34"[V
ARCHAR2,4000],
"D"."INFORMATION35"[VARCHAR2,4000], "D"."INFORMATION36"[VARCHAR2,4000], "D"."INFORMATION37"[V
ARCHAR2,4000],
"D"."INFORMATION38"[VARCHAR2,4000], "D"."INFORMATION39"[VARCHAR2,4000], "D"."INFORMATION42"[V
ARCHAR2,4000],
"D"."INFORMATION51"[VARCHAR2,4000], "D"."INFORMATION52"[VARCHAR2,4000], "D"."INFORMATION65"[V
ARCHAR2,4000],
"D"."INFORMATION89"[VARCHAR2,4000], "D"."INFORMATION96"[VARCHAR2,4000], "D"."INFORMATION102"[
VARCHAR2,4000],
"D"."INFORMATION104"[VARCHAR2,4000], "D"."INFORMATION106"[VARCHAR2,4000], "D"."INFORMATION117
"[VARCHAR2,4000],
"D"."INFORMATION168"[VARCHAR2,4000], "D"."INFORMATION181"[VARCHAR2,4000]
55 - "D".ROWID[ROWID,10]
56 - "MAX(H.NUM_SERIAL_NO)"[NUMBER,22]
57 - (#keys=0) MAX("H"."NUM_SERIAL_NO")[22]
58 - "H"."NUM_SERIAL_NO"[NUMBER,22]
59 - "MAX(HT.NUM_UPDATE_NO)"[NUMBER,22]
60 - (#keys=0) MAX("HT"."NUM_UPDATE_NO")[22]
61 - "HT"."NUM_UPDATE_NO"[NUMBER,22]
62 - "HT"."NUM_UPDATE_NO"[NUMBER,22]
63 - (#keys=3) "RT"."NUM_CLAIM_NO"[NUMBER,22], "ST"."NUM_CLAIM_NO"[NUMBER,22], "RT"."NUM_SURVEYOR_
CODE"[NUMBER,22],
"ST"."NUM_SURVEYOR_CODE"[NUMBER,22], "RT"."NUM_SURVEY_TYPE"[NUMBER,22], "ST"."NUM_SURVEY_TYPE
"[NUMBER,22]
64 - "RT"."NUM_CLAIM_NO"[NUMBER,22], "RT"."NUM_SURVEYOR_CODE"[NUMBER,22], "RT"."NUM_SURVEY_TYPE"[N
UMBER,22]
65 - "ST"."NUM_CLAIM_NO"[NUMBER,22], "ST"."NUM_SURVEYOR_CODE"[NUMBER,22], "ST"."NUM_SURVEY_TYPE"[N
UMBER,22]
421 rows selected.
Though data type of both the columns are varchar2 but data available in number.
Below are the table structure -
Toggle Spoiler
SQL> desc GENMST_CUSTOMER
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
TXT_CUSTOMER_CD NOT NULL VARCHAR2(20)
TXT_CUSTOMER_NAME VARCHAR2(350)
TXT_PARENT_CUSTOMER_CD VARCHAR2(20)
TXT_IND_CORP_FLAG CHAR(1)
TXT_GENDER VARCHAR2(15)
TXT_BLOOD_GROUP CHAR(3)
DAT_BIRTH_DT DATE
NUM_OCCUPATION_CD NUMBER(4)
TXT_EMAIL VARCHAR2(100)
TXT_TELEPHONE VARCHAR2(15)
TXT_MOBILE VARCHAR2(15)
TXT_MOTHER_NAME VARCHAR2(80)
NUM_PAIDUP_CAPTITAL NUMBER(15)
NUM_MAIL_LOCATION_CD NUMBER(25)
NUM_PERMANENT_LOCATION_CD NUMBER(25)
TXT_CONTACT_PERSON VARCHAR2(120)
TXT_CUSTOMER_STATUS VARCHAR2(15)
TXT_ISD_CODE VARCHAR2(8)
TXT_STD_CODE VARCHAR2(8)
TXT_MOBILE_ISD_CODE VARCHAR2(8)
TXT_TELEPHONE1 VARCHAR2(15)
TXT_ISD_CODE1 VARCHAR2(8)
TXT_STD_CODE1 VARCHAR2(8)
NUM_YN_VIP NUMBER(1)
TXT_CUSTOMER_DUP_STATUS VARCHAR2(10)
NUM_YN_PARENT NUMBER(1)
NUM_INSERT_TRANS_ID NUMBER(15)
NUM_MODIFY_TRANS_ID NUMBER(15)
DAT_INSERT_DATE DATE
DAT_MODIFY_DATE DATE
NUM_CUSTOMER_TIER NUMBER(2)
NUM_BANK_CODE NUMBER(8)
TXT_BANK_NAME VARCHAR2(80)
TXT_BANK_ACC_NUMBER VARCHAR2(20)
NUM_BANKBRANCH_CODE NUMBER(8)
TXT_BANKBRANCH_NAME VARCHAR2(80)
NUM_ISJAPANESE NUMBER(1)
NUM_ISGLOBALCLIENT NUMBER(1)
TXT_SALUTATION VARCHAR2(15)
TXT_DESIGNATION VARCHAR2(30)
TXT_DOMAIN_NAME VARCHAR2(30)
TXT_REMARKS VARCHAR2(30)
TXT_FAX_STD VARCHAR2(8)
TXT_FAX_NUMBER VARCHAR2(15)
TXT_FIRSTNAME VARCHAR2(180)
TXT_MIDDLENAME VARCHAR2(50)
TXT_LASTNAME VARCHAR2(90)
TXT_FAX_ISD VARCHAR2(8)
TXT_PAN_NO VARCHAR2(60)
NUM_YN_DND NUMBER(1)
TXT_OFF_EXTN VARCHAR2(20)
TXT_CUST_UNIQUE_ID VARCHAR2(50)
TXT_MAIDEN_NAME VARCHAR2(100)
TXT_INITIALS VARCHAR2(10)
TXT_MARITAL_STATUS VARCHAR2(15)
TXT_NATIONALITY VARCHAR2(25)
TXT_COUNTRY_CD VARCHAR2(3)
TXT_TYPE_OF_COMPANY VARCHAR2(25)
TXT_REG_NUMBER VARCHAR2(100)
TXT_REG_OFFICE VARCHAR2(100)
TXT_IS_COMPANY_EMPLOYEE VARCHAR2(1)
TXT_COMPANY_EMP_NUMBER VARCHAR2(15)
TXT_INDUSTRY_CD VARCHAR2(3)
TXT_INDUSTRY_OTHERS VARCHAR2(100)
TXT_TAN_NO VARCHAR2(30)
TXT_SERVICE_TAX_REG_NUMBER VARCHAR2(30)
TXT_BUSINESS_NAME VARCHAR2(100)
TXT_ANUAL_INCOME VARCHAR2(40)
NUM_PINCODE NUMBER(8)
DAT_START_DT DATE
DAT_END_DT DATE
NUM_ACCOUNT_TYPE NUMBER(2)
TXT_USER_ID VARCHAR2(20)
TXT_IP_ADDRESS VARCHAR2(20)
TXT_IS_THIN_CUSTOMER VARCHAR2(1)
TXT_PASSPORT_NO VARCHAR2(15)
TXT_DRIVING_LICENSE_NO VARCHAR2(15)
TXT_AFFILIATION_FLAG VARCHAR2(2)
TXT_CHECKACCEPTANCE VARCHAR2(10)
TXT_PAYMENT_MODE VARCHAR2(10)
TXT_PINCODE_LOCALITY VARCHAR2(80)
TXT_EMAIL1 VARCHAR2(60)
NUM_ID_PROOF NUMBER(2)
TXT_MOBILE1 VARCHAR2(12)
TXT_MOBILE_ISD_CODE1 VARCHAR2(3)
TXT_ID_PROOF_DTLS VARCHAR2(50)
NUM_QALIFICATION NUMBER(6)
TXT_SOURCE_OF_FUND VARCHAR2(15)
TXT_OFF_EXTN1 VARCHAR2(20)
TXT_MODE_OF_COMMUNICATION VARCHAR2(1)
NUM_YN_DND_ALTERNATE NUMBER(1)
TXT_TELEPHONE2 VARCHAR2(15)
TXT_TELEPHONE3 VARCHAR2(15)
TXT_ISD_CODE2 VARCHAR2(8)
TXT_ISD_CODE3 VARCHAR2(8)
TXT_STD_CODE2 VARCHAR2(8)
TXT_STD_CODE3 VARCHAR2(8)
TXT_OFF_EXTN2 VARCHAR2(20)
TXT_OFF_EXTN3 VARCHAR2(20)
TXT_MOBILE2 VARCHAR2(12)
TXT_MOBILE_ISD_CODE2 VARCHAR2(3)
TXT_MOBILE3 VARCHAR2(12)
TXT_MOBILE_ISD_CODE3 VARCHAR2(3)
NUM_YN_DND_PERMANENT VARCHAR2(1)
NUM_YN_DND_PERMANENT_ALTR VARCHAR2(1)
TXT_FAX_STD1 VARCHAR2(8)
TXT_FAX_ISD1 VARCHAR2(8)
TXT_FAX_NUMBER1 VARCHAR2(15)
TXT_CUSTOMER_DUP_ID VARCHAR2(12)
TXT_DISP_DUP_ID VARCHAR2(12)
NUM_EDI_UNIQUE_NO NUMBER(15)
SQL> desc gc_clm_gen_info
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
NUM_CLAIM_NO NUMBER(20)
NUM_UPDATE_NO NUMBER(4)
DAT_NOTIFICATION_DATE DATE
DAT_UPDATE_DATE DATE
NUM_CLAIM_YEAR NUMBER(4)
TXT_MASTER_CLAIM_NO VARCHAR2(20)
NUM_SERIAL_NO NUMBER(4)
TXT_ISSUE_OFFICE_CD VARCHAR2(10)
TXT_SERVICING_OFFICE_CD VARCHAR2(10)
NUM_DEPARTMENT_CODE NUMBER(2)
NUM_PRODUCT_CODE NUMBER(5)
NUM_NATURE_OF_LOSS NUMBER(5)
TXT_NATURE_OF_LOSS VARCHAR2(100)
DAT_REGISTRATION_DATE DATE
DAT_LOSS_DATE DATE
TXT_LOSS_LOC_LANDMARK VARCHAR2(100)
TXT_ACCIDENT_ADDRESS VARCHAR2(100)
NUM_PIN_CD NUMBER(25)
NUM_LOSS_LOCATION_CD NUMBER(25)
TXT_ACCIDENT_DESC VARCHAR2(100)
TXT_POLICY_NO_CHAR VARCHAR2(30)
NUM_REFERENCE_NO NUMBER(15)
DAT_REFERENCE_DATE DATE
TXT_INSURED_ID VARCHAR2(100)
TXT_NAME_OF_INSURED VARCHAR2(500)
CUR_LOSS_ESTIMATE NUMBER(20,2)
CUR_NET_LOSS NUMBER(20,2)
CUR_MAX_CLAIM NUMBER(20,2)
TXT_CLAIM_STATUS VARCHAR2(2)
TXT_COVER_FLAG CHAR(1)
NUM_VOUCHER_NO NUMBER(20)
NUM_PROXIMITY_DAYS NUMBER(4)
YN_CATASTROPHE CHAR(1)
NUM_CATASTROPHE_CD NUMBER(15)
NUM_DEPRECIATION NUMBER(20,2)
NUM_SALVAGE NUMBER(20,2)
NUM_EXCESS NUMBER(20,2)
CUR_RESERVE_AMOUNT NUMBER(20,2)
NUM_INTEREST NUMBER(20,2)
NUM_TRANS_ID NUMBER(15)
DAT_TRANS_DATE DATE
NUM_EX_GRATIA NUMBER(8,2)
YN_REINSTATEMENT CHAR(1)
YN_CASHLESS CHAR(1)
TXT_COVERNOTE_NO VARCHAR2(20)
NUM_REPUDIATION_REASON_ID NUMBER(15)
CUR_EXPENCE_RESERVE_AMOUNT NUMBER(20,2)
TXT_COINS VARCHAR2(10)
TXT_PKG_SEC_CODE NUMBER(5)
TXT_EXP_CURRENCY_TYPE VARCHAR2(10)
NUM_EXP_CURRENCY_RATE NUMBER(20,2)
CUR_EXP_RESERVE_CURRENCY NUMBER(20,2)
YN_OTH_VEHICLE_INVOLVED CHAR(1)
TXT_USER_ID VARCHAR2(51)
TXT_REMARKS VARCHAR2(2000)
TXT_INFO1 VARCHAR2(500)
TXT_INFO2 VARCHAR2(500)
TXT_INFO3 VARCHAR2(500)
TXT_INFO4 VARCHAR2(500)
TXT_INFO5 VARCHAR2(500)
TXT_INFO6 VARCHAR2(500)
TXT_INFO7 VARCHAR2(500)
TXT_INFO8 VARCHAR2(500)
NUM_INSERT_TRANS_ID NUMBER(15)
NUM_MODIFY_TRANS_ID NUMBER(15)
DAT_INSERT_DATE DATE
DAT_MODIFY_DATE DATE
YN_CLOSE_PROXIMITY CHAR(1)
YN_SURV_APP_STATUS CHAR(1)
TXT_CERT_NO VARCHAR2(50)
TXT_MASTER_CLAIM_NO_NEW VARCHAR2(3000)
Please let me know why there is different behaviour in explain plan?
Need your valuable inputs..
Regards,
Ashish
Edited by Lalit, added spoiler tags
[Updated on: Fri, 14 November 2014 06:37] by Moderator Report message to a moderator |
] 
Blog
Search
Help
Members
Register
Login
Home
