| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Insert-Select much slower than same select on its own
Got dbms_xplan working so here're the plans:
"Bad " insert plan:
Name | Rows | Bytes | Cost | -------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT |
| 1 | 260 | 226 |
| 1 | SEQUENCE |
SVC_C_SS_QUOTE_LINE_ITEM_ID | | | |
| 2 | NESTED LOOPS |
| 1 | 260 | 226 |
| 3 | NESTED LOOPS |
| 1 | 240 | 225 | |* 4 | FILTER | | | | | |* 5 | HASH JOIN OUTER | | | | | |* 6 | HASH JOIN | | 1 | 182 | 155 |
| 7 | VIEW |
| 1 | 55 | 103 |
| 8 | SORT GROUP BY |
| 1 | 143 | 103 | |* 9 | HASH JOIN | | 1 | 143 | 101 | |* 10 | HASH JOIN | | 2 | 258 | 98 | |* 11 | TABLE ACCESS BY INDEX ROWID | PARTREGIONLISTPRICE | 2 | 96 | 1 |
| 12 | NESTED LOOPS |
| 2 | 252 | 95 |
| 13 | NESTED LOOPS |
| 1 | 78 | 94 |
| 14 | NESTED LOOPS |
| 1 | 75 | 93 | |* 15 | FILTER | | | | | |* 16 | HASH JOIN OUTER | | | | |
| 17 | NESTED LOOPS |
| 1 | 50 | 23 |
| 18 | TABLE ACCESS BY INDEX ROWID |
SVC_QUOTES | 1 | 6 | 1 | |* 19 | INDEX UNIQUE SCAN | PK_SVC_QUOTES | 1 | | 1 |
| 20 | VIEW |
| 1 | 44 | 22 |
| 21 | NESTED LOOPS |
| 1 | 102 | 22 |
| 22 | NESTED LOOPS |
| 1 | 95 | 21 |
| 23 | NESTED LOOPS |
| 1 | 88 | 20 |
| 24 | NESTED LOOPS |
| 1 | 84 | 19 |
| 25 | NESTED LOOPS |
| 1 | 71 | 18 |
| 26 | NESTED LOOPS |
| 2 | 128 | 17 |
| 27 | NESTED LOOPS OUTER |
| 2 | 112 | 16 | |* 28 | HASH JOIN | | 2 | 100 | 15 | |* 29 | HASH JOIN | | 60 | 1740 | 5 |
| 30 | VIEW |
SVC_QUOTE_PROD_LINE_CODES_ALL | 60 | 1260 | 2 |
| 31 | UNION-ALL |
| | | |
| 32 | NESTED LOOPS |
| 1503 | 24048 | 6 | |* 33 | HASH JOIN | | 1503 | 19539 | 5 | |* 34 | TABLE ACCESS FULL | SVC_QUOTE_PROD_LINE_CODES_SOM | 1503 | 10521 | 2 |
| 35 | TABLE ACCESS FULL |
SVC_PELM | 4122 | 24732 | 2 | |* 36 | INDEX UNIQUE SCAN | PK_SVC_QUOTE_EQUIP_LOC | 1 | 3 | |
| 37 | NESTED LOOPS |
| 1503 | 24048 | 6 | |* 38 | HASH JOIN | | 1503 | 19539 | 5 | |* 39 | TABLE ACCESS FULL | SVC_QUOTE_PROD_LINE_CODES_HOM | 1503 | 10521 | 2 |
| 40 | TABLE ACCESS FULL |
SVC_PELM | 4122 | 24732 | 2 | |* 41 | INDEX UNIQUE SCAN | PK_SVC_QUOTE_EQUIP_LOC | 1 | 3 | |
| 42 | TABLE ACCESS FULL |
SVC_PELM | 4122 | 32976 | 2 |
| 43 | VIEW |
| 54 | 1134 | 9 | |* 44 | HASH JOIN | | 54 | 2214 | 9 |
| 45 | MERGE JOIN CARTESIAN |
| 34 | 986 | 6 |
| 46 | MERGE JOIN CARTESIAN |
| 3 | 75 | 5 |
| 47 | MERGE JOIN CARTESIAN |
| 2 | 46 | 4 |
| 48 | NESTED LOOPS |
| 1 | 16 | 2 |
| 49 | TABLE ACCESS BY INDEX ROWID|
SVC_QUOTES | 1 | 6 | 1 | |* 50 | INDEX UNIQUE SCAN | PK_SVC_QUOTES | 1 | | 1 |
| 51 | TABLE ACCESS BY INDEX ROWID|
DISTRIBUTOR | 1 | 10 | 1 | |* 52 | INDEX RANGE SCAN | DISTRIBUTOR_IDX_017 | 1 | | 1 |
| 53 | BUFFER SORT |
| 2 | 14 | 3 | |* 54 | TABLE ACCESS FULL | SVC_QUOTE_EQUIP_LOC | 2 | 14 | 2 |
| 55 | BUFFER SORT |
| 2 | 4 | 3 |
| 56 | INDEX FULL SCAN |
PK_SVC_SERVICE_TYPES | 2 | 4 | 1 |
| 57 | BUFFER SORT |
| 11 | 44 | 5 |
| 58 | INDEX FULL SCAN |
PK_SVC_CEANTAR_GPPC_REGION_MAP | 11 | 44 | 1 |
| 59 | TABLE ACCESS FULL |
CUSTOMERMASTER | 1752 | 21024 | 2 | |* 60 | VIEW PUSHED PREDICATE | SVC_GEO_MAX_AVAILABILITY | 1 | 6 | 1 | |* 61 | HASH JOIN | | 3 | 168 | 11 | |* 62 | HASH JOIN | | 40 | 1040 | 6 |
| 63 | MERGE JOIN CARTESIAN |
| 35 | 350 | 3 |
| 64 | TABLE ACCESS BY INDEX ROWID |
SVC_SERVICE_TYPES | 1 | 4 | 1 | |* 65 | INDEX UNIQUE SCAN | PK_SVC_SERVICE_TYPES | 1 | | |
| 66 | TABLE ACCESS FULL |
SVC_ROOTS | 35 | 210 | 2 |
| 67 | TABLE ACCESS FULL |
SVC_CLARIFY_GEO_AVAIL_CACHE | 161 | 2576 | 2 |
| 68 | VIEW |
VW_NSO_1 | 32 | 960 | 4 |
| 69 | SORT GROUP BY |
| 32 | 320 | 4 |
| 70 | TABLE ACCESS FULL |
SVC_CLARIFY_GEO_AVAIL_CACHE | 161 | 1610 | 2 | |* 71 | TABLE ACCESS BY INDEX ROWID | SVC_QUOTE_LINE_ITEMS | 1 | 8 | 1 | |* 72 | INDEX RANGE SCAN | IX_SVC_QUOTE_LINE_ITEMS_PELM | 11 | | | |* 73 | TABLE ACCESS BY INDEX ROWID | SVC_ROOT_REGION_MAP | 1 | 7 | 1 | |* 74 | INDEX RANGE SCAN | IX_SVC_ROOT_REGION_MAP_ROOT | 2 | | | |* 75 | TABLE ACCESS BY INDEX ROWID | SVC_ROOTS | 1 | 13 | 1 | |* 76 | INDEX UNIQUE SCAN | PK_SVC_ROOTS | 1 | | | |* 77 | INDEX UNIQUE SCAN | PK_SVC_RT_TYPE_ITM_CAT_MAP | 1 | 4 | | |* 78 | TABLE ACCESS BY INDEX ROWID | SVC_ROOT_REGION_MAP | 1 | 7 | 1 | |* 79 | INDEX RANGE SCAN | IX_SVC_ROOT_REGION_MAP_SEQ | 5 | | 1 | |* 80 | TABLE ACCESS BY INDEX ROWID | SVC_ROOTS | 1 | 7 | 1 | |* 81 | INDEX UNIQUE SCAN | PK_SVC_ROOTS | 1 | | |
| 82 | VIEW |
SVC_CB_PECS_FOR_PLG | 1 | 12 | 68 |
| 83 | SORT UNIQUE |
| 1 | 54 | 68 | |* 84 | TABLE ACCESS BY INDEX ROWID | SVC_QUOTE_LINE_ITEMS | 2 | 8 | 1 |
| 85 | NESTED LOOPS |
| 1 | 54 | 66 |
| 86 | NESTED LOOPS |
| 1 | 50 | 65 |
| 87 | NESTED LOOPS |
| 1 | 46 | 64 |
| 88 | NESTED LOOPS |
| 1 | 38 | 63 |
| 89 | NESTED LOOPS |
| 1 | 27 | 62 |
| 90 | NESTED LOOPS |
| 1 | 24 | 61 |
| 91 | VIEW |
| 1 | 18 | 60 |
| 92 | SORT GROUP BY |
| 1 | 19 | 60 |
| 93 | NESTED LOOPS |
| 1 | 19 | 58 |
| 94 | NESTED LOOPS |
| 1 | 13 | 57 |
| 95 | MERGE JOIN CARTESIAN |
| 1 | 9 | 56 | |* 96 | TABLE ACCESS FULL | SVC_QUOTE_LINE_ITEMS | 1 | 7 | 55 |
| 97 | BUFFER SORT |
| 2 | 4 | 1 |
| 98 | INDEX FULL SCAN |
PK_SVC_COUNT_BASED_PLGS | 2 | 4 | 1 | |* 99 | INDEX UNIQUE SCAN | PK_SVC_CB_PLG_TYPES_MAP | 1 | 4 | |
| 100 | TABLE ACCESS BY INDEX ROWID |
SVC_PELM | 1 | 6 | 1 | |*101 | INDEX UNIQUE SCAN | PK_SVC_PELM | 1 | | |
| 102 | TABLE ACCESS BY INDEX ROWID |
SVC_QUOTE_EQUIP_LOC | 1 | 6 | 1 | |*103 | INDEX UNIQUE SCAN | PK_SVC_QUOTE_EQUIP_LOC | 1 | | | |*104 | INDEX UNIQUE SCAN | PK_SVC_QUOTES | 1 | 3 | |
| 105 | TABLE ACCESS BY INDEX ROWID |
SVC_COUNT_BASED_PLGS | 1 | 11 | 1 | |*106 | INDEX UNIQUE SCAN | PK_SVC_COUNT_BASED_PLGS | 1 | | | |*107 | TABLE ACCESS BY INDEX ROWID | SVC_PELM | 1 | 8 | 1 | |*108 | INDEX RANGE SCAN | IX_SVC_PELM_QEL_ID | 2 | | | |*109 | INDEX RANGE SCAN | PK_SVC_RT_TYPE_ITM_CAT_MAP | 2 | 8 | 1 | |*110 | INDEX RANGE SCAN | IX_SVC_QUOTE_LINE_ITEMS_PELM | 11 | | |
| 111 | TABLE ACCESS BY INDEX ROWID |
DISTRIBUTOR | 1 | 13 | 1 | |*112 | INDEX RANGE SCAN | DISTRIBUTOR_IDX_017 | 1 | | 1 | |*113 | INDEX UNIQUE SCAN | PK_COUNTRY | 1 | 3 | | |*114 | INDEX RANGE SCAN | PRLP_OCODE | 6 | | |
| 115 | TABLE ACCESS FULL |
MODELCURRENCY | 70 | 210 | 2 |
| 116 | TABLE ACCESS FULL |
CUSTOMERMASTER | 1752 | 24528 | 2 |
| 117 | VIEW |
| 17 | 2159 | 51 |
| 118 | NESTED LOOPS OUTER |
| 17 | 1802 | 51 | |*119 | HASH JOIN | | 17 | 1700 | 46 | |*120 | TABLE ACCESS BY INDEX ROWID | SVC_QUOTE_LINE_ITEMS | 1 | 12 | 1 |
| 121 | NESTED LOOPS |
| 6 | 474 | 29 | |*122 | HASH JOIN | | 31 | 2077 | 19 |
| 123 | TABLE ACCESS FULL |
SVC_ROOT_REGION_MAP | 104 | 728 | 2 | |*124 | HASH JOIN | | 1233 | 73980 | 16 | |*125 | HASH JOIN | | 1233 | 64116 | 13 |
| 126 | TABLE ACCESS FULL |
SVC_ROOT_REGION_MAP | 104 | 728 | 2 | |*127 | HASH JOIN | | 18406 | 808K| 10 |
| 128 | VIEW |
SVC_QUOTE_PROD_LINE_CODES_ALL | 60 | 1260 | 2 |
| 129 | UNION-ALL |
| | | |
| 130 | NESTED LOOPS |
| 1503 | 24048 | 6 | |*131 | HASH JOIN | | 1503 | 19539 | 5 | |*132 | TABLE ACCESS FULL | SVC_QUOTE_PROD_LINE_CODES_SOM | 1503 | 10521 | 2 |
| 133 | TABLE ACCESS FULL |
SVC_PELM | 4122 | 24732 | 2 | |*134 | INDEX UNIQUE SCAN | PK_SVC_QUOTE_EQUIP_LOC | 1 | 3 | |
| 135 | NESTED LOOPS |
| 1503 | 24048 | 6 | |*136 | HASH JOIN | | 1503 | 19539 | 5 | |*137 | TABLE ACCESS FULL | SVC_QUOTE_PROD_LINE_CODES_HOM | 1503 | 10521 | 2 |
| 138 | TABLE ACCESS FULL |
SVC_PELM | 4122 | 24732 | 2 | |*139 | INDEX UNIQUE SCAN | PK_SVC_QUOTE_EQUIP_LOC | 1 | 3 | | |*140 | HASH JOIN | | 613 | 14712 | 7 | |*141 | HASH JOIN | | 70 | 1190 | 4 |
| 142 | INDEX FULL SCAN |
PK_SVC_RT_TYPE_ITM_CAT_MAP | 4 | 16 | 1 |
| 143 | TABLE ACCESS FULL |
SVC_ROOTS | 35 | 455 | 2 |
| 144 | TABLE ACCESS FULL |
SVC_ROOTS | 35 | 245 | 2 |
| 145 | TABLE ACCESS FULL |
SVC_PELM | 4122 | 32976 | 2 | |*146 | INDEX RANGE SCAN | IX_SVC_QUOTE_LINE_ITEMS_PELM | 11 | | |
| 147 | VIEW |
| 100K| 2064K| 16 | |*148 | HASH JOIN | | 100K| 4030K| 16 |
| 149 | TABLE ACCESS FULL |
SVC_QUOTE_EQUIP_LOC | 2893 | 20251 | 2 | |*150 | HASH JOIN | | 65350 | 2169K| 12 |
| 151 | TABLE ACCESS FULL |
SVC_QUOTES | 1878 | 11268 | 2 | |*152 | HASH JOIN | | 102K| 2790K| 8 |
| 153 | INDEX FAST FULL SCAN |
DISTRIBUTOR_IDX_011 | 2912 | 29120 | 2 | |*154 | HASH JOIN | | 3504 | 63072 | 5 |
| 155 | MERGE JOIN CARTESIAN |
| 22 | 132 | 2 |
| 156 | INDEX FULL SCAN |
PK_SVC_SERVICE_TYPES | 2 | 4 | 1 |
| 157 | BUFFER SORT |
| 11 | 44 | 1 |
| 158 | INDEX FULL SCAN |
PK_SVC_CEANTAR_GPPC_REGION_MAP | 11 | 44 | 1 |
| 159 | TABLE ACCESS FULL |
CUSTOMERMASTER | 1752 | 21024 | 2 | |*160 | VIEW PUSHED PREDICATE | SVC_GEO_MAX_AVAILABILITY | 1 | 6 | 1 | |*161 | HASH JOIN SEMI | | 4 | 224 | 11 | |*162 | HASH JOIN | | 40 | 1040 | 6 |
| 163 | MERGE JOIN CARTESIAN |
| 35 | 350 | 3 |
| 164 | TABLE ACCESS BY INDEX ROWID |
SVC_SERVICE_TYPES | 1 | 4 | 1 | |*165 | INDEX UNIQUE SCAN | PK_SVC_SERVICE_TYPES | 1 | | |
| 166 | TABLE ACCESS FULL |
SVC_ROOTS | 35 | 210 | 2 |
| 167 | TABLE ACCESS FULL |
SVC_CLARIFY_GEO_AVAIL_CACHE | 161 | 2576 | 2 |
| 168 | VIEW |
VW_NSO_2 | 32 | 960 | 4 |
| 169 | SORT GROUP BY |
| 32 | 320 | 4 |
| 170 | TABLE ACCESS FULL |
SVC_CLARIFY_GEO_AVAIL_CACHE | 161 | 1610 | 2 |
| 171 | VIEW |
SVC_CB_PECS_FOR_PLG | 1 | 55 | 68 |
| 172 | SORT UNIQUE |
| 1 | 54 | 68 | |*173 | TABLE ACCESS BY INDEX ROWID | SVC_QUOTE_LINE_ITEMS | 2 | 8 | 1 |
| 174 | NESTED LOOPS |
| 1 | 54 | 66 |
| 175 | NESTED LOOPS |
| 1 | 50 | 65 |
| 176 | NESTED LOOPS |
| 1 | 46 | 64 |
| 177 | NESTED LOOPS |
| 1 | 38 | 63 |
| 178 | NESTED LOOPS |
| 1 | 27 | 62 |
| 179 | NESTED LOOPS |
| 1 | 24 | 61 |
| 180 | VIEW |
| 1 | 18 | 60 |
| 181 | SORT GROUP BY |
| 1 | 19 | 60 |
| 182 | NESTED LOOPS |
| 1 | 19 | 58 |
| 183 | NESTED LOOPS |
| 1 | 13 | 57 |
| 184 | MERGE JOIN CARTESIAN |
| 1 | 9 | 56 | |*185 | TABLE ACCESS FULL | SVC_QUOTE_LINE_ITEMS | 1 | 7 | 55 |
| 186 | BUFFER SORT |
| 2 | 4 | 1 |
| 187 | INDEX FULL SCAN |
PK_SVC_COUNT_BASED_PLGS | 2 | 4 | 1 | |*188 | INDEX UNIQUE SCAN | PK_SVC_CB_PLG_TYPES_MAP | 1 | 4 | |
| 189 | TABLE ACCESS BY INDEX ROWID |
SVC_PELM | 1 | 6 | 1 | |*190 | INDEX UNIQUE SCAN | PK_SVC_PELM | 1 | | |
| 191 | TABLE ACCESS BY INDEX ROWID |
SVC_QUOTE_EQUIP_LOC | 1 | 6 | 1 | |*192 | INDEX UNIQUE SCAN | PK_SVC_QUOTE_EQUIP_LOC | 1 | | | |*193 | INDEX UNIQUE SCAN | PK_SVC_QUOTES | 1 | 3 | |
| 194 | TABLE ACCESS BY INDEX ROWID |
SVC_COUNT_BASED_PLGS | 1 | 11 | 1 | |*195 | INDEX UNIQUE SCAN | PK_SVC_COUNT_BASED_PLGS | 1 | | | |*196 | TABLE ACCESS BY INDEX ROWID | SVC_PELM | 1 | 8 | 1 | |*197 | INDEX RANGE SCAN | IX_SVC_PELM_QEL_ID | 2 | | | |*198 | INDEX RANGE SCAN | PK_SVC_RT_TYPE_ITM_CAT_MAP | 2 | 8 | 1 | |*199 | INDEX RANGE SCAN | IX_SVC_QUOTE_LINE_ITEMS_PELM | 11 | | | |*200 | INDEX UNIQUE SCAN | PK_SVC_QUOTES | 1 | 3 | |
| 201 | TABLE ACCESS BY INDEX ROWID |
SVC_QUOTE_LINE_ITEMS | 1 | 20 | 1 | |*202 | INDEX UNIQUE SCAN | PK_SVC_QUOTE_LINE_ITEMS | 1 | | | -------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
4 -
filter("TBLMAXSEQ"."SERVICE_LINE_NUMBER"=DECODE("CBPECS"."SERVICE_LINE_NUMBER",NULL,"TBL"."SERVICE_LINE_NUMBER","CBPECS"
."SERVICE_LINE_NUMBER"))
5 -
access("TBL"."QUOTE_EQUIP_LOC_ID"="CBPECS"."QUOTE_EQUIP_LOC_ID"(+) AND
"TBL"."PRODUCT_LINE_GROUP_ID"="CBPECS"."PRODUCT_LINE_GROUP_ID"(+) AND
"TBL"."ROOT_TYPE_ID"="CBPECS"."ROOT_TYPE_ID"(+) AND
"TBL"."SERVICE_TYPE_ID"="CBPECS"."SERVICE_TYPE_ID"(+))
6 - access("TBL"."SERVICE_QUOTE_ID"="TBLMAXSEQ"."SERVICE_QUOTE_ID"
AND
"TBL"."SERVICE_TYPE_ID"="TBLMAXSEQ"."SERVICE_TYPE_ID" AND
"TBL"."ROOT_TYPE_ID"="TBLMAXSEQ"."ROOT_TYPE_ID" AND
"TBL"."SEQUENCE"="TBLMAXSEQ"."SEQUENCE")
9 - access(TRIM("CM"."COUNTRY")=TRIM("PRLP"."REGION_ID") AND
TRIM("D"."GPPRDISTRIBUTORID")=TRIM("CM"."CUST_CODE") AND
"CM"."CURRENCY"="MC"."CURRENCYID")
10 - access(TRIM("MC"."CURRENCYID")=TRIM("PRLP"."CURRENCY"))
11 - filter("PRLP"."EXPDATE">SYSDATE@!)
15 - filter(DECODE("CBPECS"."SERVICE_LINE_NUMBER",NULL,0,1)=0)
16 -
"TBL"."PRODUCT_LINE_GROUP_ID"="CBPECS"."PRODUCT_LINE_GROUP_ID"(+) AND "TBL"."ROOT_TYPE_ID"="CBPECS"."ROOT_TYPE_ID"(+) AND
"TBL"."SERVICE_TYPE_ID"="CBPECS"."SERVICE_TYPE_ID"(+))
19 - access("Q"."SERVICE_QUOTE_ID"=2165)
28 - access("TBL"."QUOTE_EQUIP_LOC_ID"="P"."QUOTE_EQUIP_LOC_ID")
29 - access("P"."PELM_ID"="HOMSOM"."PELM_ID")
33 - access("SOM"."PELM_ID"="P"."PELM_ID")
34 - filter("SOM"."SERVICE_REJECTED"=0)
36 - access("P"."QUOTE_EQUIP_LOC_ID"="QEL"."QUOTE_EQUIP_LOC_ID")
38 - access("HOM"."PELM_ID"="P"."PELM_ID")
39 - filter("HOM"."SERVICE_REJECTED"=0)
41 - access("P"."QUOTE_EQUIP_LOC_ID"="QEL"."QUOTE_EQUIP_LOC_ID")
44 - access(TRIM("D"."GPPRDISTRIBUTORID")=TRIM("C"."CUST_CODE") AND
"CRM"."REGION_ID"=TO_NUMBER("C"."COUNTRY"))
50 - access("Q"."SERVICE_QUOTE_ID"=2165)
52 - access("Q"."DISTRIBUTORID"="D"."DISTRIBUTORID")
54 - filter("QEL"."SERVICE_QUOTE_ID"=2165)
60 - filter("TBL"."CLARIFY_LOCATION_ID"="GEO"."CITY_ID"(+))
61 - access("GEO"."CITY_ID"="VW_NSO_1"."$nso_col_1" AND
"GEO"."SERVICE_TYPE"="VW_NSO_1"."$nso_col_2" AND
"GEO"."SUPPORT_PROGRAM_ORDER"="VW_NSO_1"."$nso_col_3")
62 - access("ST"."SERVICE_TYPE"=TO_NUMBER("GEO"."SERVICE_TYPE") AND
"GEO"."ROOT"="R"."ROOT")
65 - access("TBL"."SERVICE_TYPE_ID"="ST"."SERVICE_TYPE_ID")
71 - filter("QLI"."ITEM_CATEGORY_ID" IS NOT NULL AND
"QLI"."SERVICEABILITY_STATUS_ID"=0)
72 - access("P"."PELM_ID"="QLI"."PELM_ID")
filter("QLI"."PELM_ID" IS NOT NULL)
73 - filter("TBL"."CEANTAR_ID"="RRM"."CEANTAR_ID")
74 - access("HOMSOM"."ROOT_ID"="RRM"."ROOT_ID")
75 - filter("TBL"."SERVICE_TYPE_ID"="R"."SERVICE_TYPE_ID" AND
"HOMSOM"."ROOT_TYPE_ID"="R"."ROOT_TYPE_ID")
76 - access("RRM"."ROOT_ID"="R"."ROOT_ID")
77 - access("R"."ROOT_TYPE_ID"="RTICM"."ROOT_TYPE_ID" AND
"RTICM"."ITEM_CATEGORY_ID"="QLI"."ITEM_CATEGORY_ID")
78 - filter("RRM"."CEANTAR_ID"="RRMSEQ"."CEANTAR_ID")
79 - access("RRM"."SEQUENCE"<="RRMSEQ"."SEQUENCE")
80 - filter("R"."SERVICE_TYPE_ID"="RSEQ"."SERVICE_TYPE_ID" AND
"R"."ROOT_TYPE_ID"="RSEQ"."ROOT_TYPE_ID")
81 - access("RRMSEQ"."ROOT_ID"="RSEQ"."ROOT_ID")
84 - filter("QLI"."ITEM_CATEGORY_ID"="RTICM"."ITEM_CATEGORY_ID" AND
"QLI"."ITEM_CATEGORY_ID" IS NOT NULL)
"QLI"."COUNT_BASED_TYPE_ID"="CBPLGTM"."COUNT_BASED_TYPE_ID")
101 - access("QLI"."PELM_ID"="P"."PELM_ID")
103 -
access("QEL"."QUOTE_EQUIP_LOC_ID"="TBLHASCOUNTS"."QUOTE_EQUIP_LOC_ID")
104 - access("Q"."SERVICE_QUOTE_ID"="QEL"."SERVICE_QUOTE_ID")
106 -
access("CBPLG"."COUNT_BASED_PLG_ID"="TBLHASCOUNTS"."COUNT_BASED_PLG_ID")
107 -
filter("P"."PRODUCT_LINE_GROUP_ID"="CBPLG"."PRODUCT_LINE_GROUP_ID")
108 - access("QEL"."QUOTE_EQUIP_LOC_ID"="P"."QUOTE_EQUIP_LOC_ID")
109 - access("RTICM"."ROOT_TYPE_ID"="CBPLG"."ROOT_TYPE_ID")
110 - access("P"."PELM_ID"="QLI"."PELM_ID")
filter("QLI"."PELM_ID" IS NOT NULL)
112 - access("Q"."DISTRIBUTORID"="D"."DISTRIBUTORID")
113 - access("D"."COUNTRYID"="C"."COUNTRYID")
114 -
access("PRLP"."ORDERCODE"="TBL"."ROOT"||DECODE("CBPECS"."SERVICE_LINE_NUMBER",NULL,"TBL"."SERVICE_LINE_NUMBER","CBPECS".
"SERVICE_LINE_NUMBER"))
119 - access("TBL"."CEANTAR_ID"="RRM"."CEANTAR_ID" AND
"TBL"."QUOTE_EQUIP_LOC_ID"="P"."QUOTE_EQUIP_LOC_ID" AND
"TBL"."SERVICE_TYPE_ID"="R"."SERVICE_TYPE_ID")
120 - filter("RTICM"."ITEM_CATEGORY_ID"="QLI"."ITEM_CATEGORY_ID" AND
"QLI"."ITEM_CATEGORY_ID" IS NOT NULL AND
"QLI"."SERVICEABILITY_STATUS_ID"=0)
122 - access("RRM"."CEANTAR_ID"="RRMSEQ"."CEANTAR_ID" AND
"RRMSEQ"."ROOT_ID"="RSEQ"."ROOT_ID")
filter("RRM"."SEQUENCE"<="RRMSEQ"."SEQUENCE")
124 - access("P"."PELM_ID"="HOMSOM"."PELM_ID")
125 - access("HOMSOM"."ROOT_ID"="RRM"."ROOT_ID" AND
"RRM"."ROOT_ID"="R"."ROOT_ID")
127 - access("HOMSOM"."ROOT_TYPE_ID"="R"."ROOT_TYPE_ID")
131 - access("SOM"."PELM_ID"="P"."PELM_ID")
132 - filter("SOM"."SERVICE_REJECTED"=0)
134 - access("P"."QUOTE_EQUIP_LOC_ID"="QEL"."QUOTE_EQUIP_LOC_ID")
136 - access("HOM"."PELM_ID"="P"."PELM_ID")
137 - filter("HOM"."SERVICE_REJECTED"=0)
139 - access("P"."QUOTE_EQUIP_LOC_ID"="QEL"."QUOTE_EQUIP_LOC_ID")
140 - access("R"."SERVICE_TYPE_ID"="RSEQ"."SERVICE_TYPE_ID" AND
"R"."ROOT_TYPE_ID"="RSEQ"."ROOT_TYPE_ID")
141 - access("R"."ROOT_TYPE_ID"="RTICM"."ROOT_TYPE_ID")
146 - access("P"."PELM_ID"="QLI"."PELM_ID")
filter("QLI"."PELM_ID" IS NOT NULL)
148 - access("Q"."SERVICE_QUOTE_ID"="QEL"."SERVICE_QUOTE_ID")
150 - access("Q"."DISTRIBUTORID"="D"."DISTRIBUTORID")
152 - access(TRIM("D"."GPPRDISTRIBUTORID")=TRIM("C"."CUST_CODE"))
154 - access("CRM"."REGION_ID"=TO_NUMBER("C"."COUNTRY"))
160 - filter("TBL"."CLARIFY_LOCATION_ID"="GEO"."CITY_ID"(+))
161 - access("GEO"."CITY_ID"="VW_NSO_2"."$nso_col_1" AND
"GEO"."SERVICE_TYPE"="VW_NSO_2"."$nso_col_2" AND
"GEO"."SUPPORT_PROGRAM_ORDER"="VW_NSO_2"."$nso_col_3")
162 - access("ST"."SERVICE_TYPE"=TO_NUMBER("GEO"."SERVICE_TYPE") AND
"GEO"."ROOT"="R"."ROOT")
"QLI"."COUNT_BASED_TYPE_ID"="CBPLGTM"."COUNT_BASED_TYPE_ID")
190 - access("QLI"."PELM_ID"="P"."PELM_ID")
192 -
access("QEL"."QUOTE_EQUIP_LOC_ID"="TBLHASCOUNTS"."QUOTE_EQUIP_LOC_ID")
193 - access("Q"."SERVICE_QUOTE_ID"="QEL"."SERVICE_QUOTE_ID")
195 -
access("CBPLG"."COUNT_BASED_PLG_ID"="TBLHASCOUNTS"."COUNT_BASED_PLG_ID")
196 -
filter("P"."PRODUCT_LINE_GROUP_ID"="CBPLG"."PRODUCT_LINE_GROUP_ID")
197 - access("QEL"."QUOTE_EQUIP_LOC_ID"="P"."QUOTE_EQUIP_LOC_ID")
198 - access("RTICM"."ROOT_TYPE_ID"="CBPLG"."ROOT_TYPE_ID")
199 - access("P"."PELM_ID"="QLI"."PELM_ID")
filter("QLI"."PELM_ID" IS NOT NULL)
200 - access("Q"."SERVICE_QUOTE_ID"="TBL"."SERVICE_QUOTE_ID")
202 - access("TBL"."QUOTE_LINE_ITEM_ID"="QLI"."QUOTE_LINE_ITEM_ID")
Note: cpu costing is off
"Good" Select plan:
Name | Rows | Bytes | Cost | -----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | 264 | 197 |
| 1 | SEQUENCE |
SVC_C_SS_QUOTE_LINE_ITEM_ID | | | |
| 2 | NESTED LOOPS |
| 1 | 264 | 197 |
| 3 | NESTED LOOPS |
| 1 | 244 | 196 | |* 4 | HASH JOIN | | 1 | 241 | 195 | |* 5 | HASH JOIN OUTER | | 1 | 184 | 91 |
| 6 | VIEW |
| 1 | 129 | 22 |
| 7 | NESTED LOOPS |
| 1 | 106 | 22 |
| 8 | NESTED LOOPS |
| 1 | 99 | 21 |
| 9 | NESTED LOOPS |
| 1 | 92 | 20 |
| 10 | NESTED LOOPS |
| 1 | 88 | 19 |
| 11 | NESTED LOOPS |
| 1 | 75 | 18 |
| 12 | NESTED LOOPS |
| 2 | 136 | 17 |
| 13 | NESTED LOOPS OUTER |
| 2 | 112 | 16 | |* 14 | HASH JOIN | | 2 | 100 | 15 | |* 15 | HASH JOIN | | 60 | 1740 | 5 |
| 16 | VIEW |
SVC_QUOTE_PROD_LINE_CODES_ALL | 60 | 1260 | 2 |
| 17 | UNION-ALL |
| | | |
| 18 | NESTED LOOPS |
| 1503 | 24048 | 6 | |* 19 | HASH JOIN | | 1503 | 19539 | 5 | |* 20 | TABLE ACCESS FULL | SVC_QUOTE_PROD_LINE_CODES_SOM | 1503 | 10521 | 2 |
| 21 | TABLE ACCESS FULL |
SVC_PELM | 4122 | 24732 | 2 | |* 22 | INDEX UNIQUE SCAN | PK_SVC_QUOTE_EQUIP_LOC | 1 | 3 | |
| 23 | NESTED LOOPS |
| 1503 | 24048 | 6 | |* 24 | HASH JOIN | | 1503 | 19539 | 5 | |* 25 | TABLE ACCESS FULL | SVC_QUOTE_PROD_LINE_CODES_HOM | 1503 | 10521 | 2 |
| 26 | TABLE ACCESS FULL |
SVC_PELM | 4122 | 24732 | 2 | |* 27 | INDEX UNIQUE SCAN | PK_SVC_QUOTE_EQUIP_LOC | 1 | 3 | |
| 28 | TABLE ACCESS FULL |
SVC_PELM | 4122 | 32976 | 2 |
| 29 | VIEW |
| 54 | 1134 | 9 | |* 30 | HASH JOIN | | 54 | 2214 | 9 |
| 31 | MERGE JOIN CARTESIAN |
| 34 | 986 | 6 |
| 32 | MERGE JOIN CARTESIAN |
| 3 | 75 | 5 |
| 33 | MERGE JOIN CARTESIAN |
| 2 | 46 | 4 |
| 34 | NESTED LOOPS |
| 1 | 16 | 2 |
| 35 | TABLE ACCESS BY INDEX ROWID |
SVC_QUOTES | 1 | 6 | 1 | |* 36 | INDEX UNIQUE SCAN | PK_SVC_QUOTES | 1 | | 1 |
| 37 | TABLE ACCESS BY INDEX ROWID |
DISTRIBUTOR | 1 | 10 | 1 | |* 38 | INDEX RANGE SCAN | DISTRIBUTOR_IDX_017 | 1 | | 1 |
| 39 | BUFFER SORT |
| 2 | 14 | 3 | |* 40 | TABLE ACCESS FULL | SVC_QUOTE_EQUIP_LOC | 2 | 14 | 2 |
| 41 | BUFFER SORT |
| 2 | 4 | 3 |
| 42 | INDEX FULL SCAN |
PK_SVC_SERVICE_TYPES | 2 | 4 | 1 |
| 43 | BUFFER SORT |
| 11 | 44 | 5 |
| 44 | INDEX FULL SCAN |
PK_SVC_CEANTAR_GPPC_REGION_MAP | 11 | 44 | 1 |
| 45 | TABLE ACCESS FULL |
CUSTOMERMASTER | 1752 | 21024 | 2 | |* 46 | VIEW PUSHED PREDICATE | SVC_GEO_MAX_AVAILABILITY | 1 | 6 | 1 | |* 47 | HASH JOIN SEMI | | 4 | 224 | 11 | |* 48 | HASH JOIN | | 40 | 1040 | 6 |
| 49 | MERGE JOIN CARTESIAN |
| 35 | 350 | 3 |
| 50 | TABLE ACCESS BY INDEX ROWID |
SVC_SERVICE_TYPES | 1 | 4 | 1 | |* 51 | INDEX UNIQUE SCAN | PK_SVC_SERVICE_TYPES | 1 | | |
| 52 | TABLE ACCESS FULL |
SVC_ROOTS | 35 | 210 | 2 |
| 53 | TABLE ACCESS FULL |
SVC_CLARIFY_GEO_AVAIL_CACHE | 161 | 2576 | 2 |
| 54 | VIEW |
VW_NSO_2 | 32 | 960 | 4 |
| 55 | SORT GROUP BY |
| 32 | 320 | 4 |
| 56 | TABLE ACCESS FULL |
SVC_CLARIFY_GEO_AVAIL_CACHE | 161 | 1610 | 2 | |* 57 | TABLE ACCESS BY INDEX ROWID | SVC_QUOTE_LINE_ITEMS | 1 | 12 | 1 | |* 58 | INDEX RANGE SCAN | IX_SVC_QUOTE_LINE_ITEMS_PELM | 11 | | | |* 59 | TABLE ACCESS BY INDEX ROWID | SVC_ROOT_REGION_MAP | 1 | 7 | 1 | |* 60 | INDEX RANGE SCAN | IX_SVC_ROOT_REGION_MAP_ROOT | 2 | | | |* 61 | TABLE ACCESS BY INDEX ROWID | SVC_ROOTS | 1 | 13 | 1 | |* 62 | INDEX UNIQUE SCAN | PK_SVC_ROOTS | 1 | | | |* 63 | INDEX UNIQUE SCAN | PK_SVC_RT_TYPE_ITM_CAT_MAP | 1 | 4 | | |* 64 | TABLE ACCESS BY INDEX ROWID | SVC_ROOT_REGION_MAP | 1 | 7 | 1 | |* 65 | INDEX RANGE SCAN | IX_SVC_ROOT_REGION_MAP_SEQ | 5 | | 1 | |* 66 | TABLE ACCESS BY INDEX ROWID | SVC_ROOTS | 1 | 7 | 1 | |* 67 | INDEX UNIQUE SCAN | PK_SVC_ROOTS | 1 | | |
| 68 | VIEW |
SVC_CB_PECS_FOR_PLG | 1 | 55 | 68 |
| 69 | SORT UNIQUE |
| 1 | 54 | 68 | |* 70 | TABLE ACCESS BY INDEX ROWID | SVC_QUOTE_LINE_ITEMS | 2 | 8 | 1 |
| 71 | NESTED LOOPS |
| 1 | 54 | 66 |
| 72 | NESTED LOOPS |
| 1 | 50 | 65 |
| 73 | NESTED LOOPS |
| 1 | 46 | 64 |
| 74 | NESTED LOOPS |
| 1 | 38 | 63 |
| 75 | NESTED LOOPS |
| 1 | 27 | 62 |
| 76 | NESTED LOOPS |
| 1 | 24 | 61 |
| 77 | VIEW |
| 1 | 18 | 60 |
| 78 | SORT GROUP BY |
| 1 | 19 | 60 |
| 79 | NESTED LOOPS |
| 1 | 19 | 58 |
| 80 | NESTED LOOPS |
| 1 | 13 | 57 |
| 81 | MERGE JOIN CARTESIAN |
| 1 | 9 | 56 | |* 82 | TABLE ACCESS FULL | SVC_QUOTE_LINE_ITEMS | 1 | 7 | 55 |
| 83 | BUFFER SORT |
| 2 | 4 | 1 |
| 84 | INDEX FULL SCAN |
PK_SVC_COUNT_BASED_PLGS | 2 | 4 | 1 | |* 85 | INDEX UNIQUE SCAN | PK_SVC_CB_PLG_TYPES_MAP | 1 | 4 | |
| 86 | TABLE ACCESS BY INDEX ROWID |
SVC_PELM | 1 | 6 | 1 | |* 87 | INDEX UNIQUE SCAN | PK_SVC_PELM | 1 | | |
| 88 | TABLE ACCESS BY INDEX ROWID |
SVC_QUOTE_EQUIP_LOC | 1 | 6 | 1 | |* 89 | INDEX UNIQUE SCAN | PK_SVC_QUOTE_EQUIP_LOC | 1 | | | |* 90 | INDEX UNIQUE SCAN | PK_SVC_QUOTES | 1 | 3 | |
| 91 | TABLE ACCESS BY INDEX ROWID |
SVC_COUNT_BASED_PLGS | 1 | 11 | 1 | |* 92 | INDEX UNIQUE SCAN | PK_SVC_COUNT_BASED_PLGS | 1 | | | |* 93 | TABLE ACCESS BY INDEX ROWID | SVC_PELM | 1 | 8 | 1 | |* 94 | INDEX RANGE SCAN | IX_SVC_PELM_QEL_ID | 2 | | | |* 95 | INDEX RANGE SCAN | PK_SVC_RT_TYPE_ITM_CAT_MAP | 2 | 8 | 1 | |* 96 | INDEX RANGE SCAN | IX_SVC_QUOTE_LINE_ITEMS_PELM | 11 | | |
| 97 | VIEW |
| 1 | 57 | 103 |
| 98 | SORT GROUP BY |
| 1 | 145 | 103 | |* 99 | HASH JOIN | | 1 | 145 | 101 | |*100 | HASH JOIN | | 2 | 262 | 98 | |*101 | TABLE ACCESS BY INDEX ROWID | PARTREGIONLISTPRICE | 2 | 96 | 1 |
| 102 | NESTED LOOPS |
| 2 | 256 | 95 |
| 103 | NESTED LOOPS |
| 1 | 80 | 94 |
| 104 | NESTED LOOPS |
| 1 | 77 | 93 | |*105 | FILTER | | | | | |*106 | HASH JOIN OUTER | | | | |
| 107 | NESTED LOOPS |
| 1 | 52 | 23 |
| 108 | TABLE ACCESS BY INDEX ROWID |
SVC_QUOTES | 1 | 6 | 1 | |*109 | INDEX UNIQUE SCAN | PK_SVC_QUOTES | 1 | | 1 |
| 110 | VIEW |
| 1 | 46 | 22 |
| 111 | NESTED LOOPS |
| 1 | 102 | 22 |
| 112 | NESTED LOOPS |
| 1 | 95 | 21 |
| 113 | NESTED LOOPS |
| 1 | 88 | 20 |
| 114 | NESTED LOOPS |
| 1 | 84 | 19 |
| 115 | NESTED LOOPS |
| 1 | 71 | 18 |
| 116 | NESTED LOOPS |
| 2 | 128 | 17 |
| 117 | NESTED LOOPS OUTER |
| 2 | 112 | 16 | |*118 | HASH JOIN | | 2 | 100 | 15 | |*119 | HASH JOIN | | 60 | 1740 | 5 |
| 120 | VIEW |
SVC_QUOTE_PROD_LINE_CODES_ALL | 60 | 1260 | 2 |
| 121 | UNION-ALL |
| | | |
| 122 | NESTED LOOPS |
| 1503 | 24048 | 6 | |*123 | HASH JOIN | | 1503 | 19539 | 5 | |*124 | TABLE ACCESS FULL | SVC_QUOTE_PROD_LINE_CODES_SOM | 1503 | 10521 | 2 |
| 125 | TABLE ACCESS FULL |
SVC_PELM | 4122 | 24732 | 2 | |*126 | INDEX UNIQUE SCAN | PK_SVC_QUOTE_EQUIP_LOC | 1 | 3 | |
| 127 | NESTED LOOPS |
| 1503 | 24048 | 6 | |*128 | HASH JOIN | | 1503 | 19539 | 5 | |*129 | TABLE ACCESS FULL | SVC_QUOTE_PROD_LINE_CODES_HOM | 1503 | 10521 | 2 |
| 130 | TABLE ACCESS FULL |
SVC_PELM | 4122 | 24732 | 2 | |*131 | INDEX UNIQUE SCAN | PK_SVC_QUOTE_EQUIP_LOC | 1 | 3 | |
| 132 | TABLE ACCESS FULL |
SVC_PELM | 4122 | 32976 | 2 |
| 133 | VIEW |
| 54 | 1134 | 9 | |*134 | HASH JOIN | | 54 | 2214 | 9 |
| 135 | MERGE JOIN CARTESIAN |
| 34 | 986 | 6 |
| 136 | MERGE JOIN CARTESIAN |
| 3 | 75 | 5 |
| 137 | MERGE JOIN CARTESIAN |
| 2 | 46 | 4 |
| 138 | NESTED LOOPS |
| 1 | 16 | 2 |
| 139 | TABLE ACCESS BY INDEX ROWID|
SVC_QUOTES | 1 | 6 | 1 | |*140 | INDEX UNIQUE SCAN | PK_SVC_QUOTES | 1 | | 1 |
| 141 | TABLE ACCESS BY INDEX ROWID|
DISTRIBUTOR | 1 | 10 | 1 | |*142 | INDEX RANGE SCAN | DISTRIBUTOR_IDX_017 | 1 | | 1 |
| 143 | BUFFER SORT |
| 2 | 14 | 3 | |*144 | TABLE ACCESS FULL | SVC_QUOTE_EQUIP_LOC | 2 | 14 | 2 |
| 145 | BUFFER SORT |
| 2 | 4 | 3 |
| 146 | INDEX FULL SCAN |
PK_SVC_SERVICE_TYPES | 2 | 4 | 1 |
| 147 | BUFFER SORT |
| 11 | 44 | 5 |
| 148 | INDEX FULL SCAN |
PK_SVC_CEANTAR_GPPC_REGION_MAP | 11 | 44 | 1 |
| 149 | TABLE ACCESS FULL |
CUSTOMERMASTER | 1752 | 21024 | 2 | |*150 | VIEW PUSHED PREDICATE | SVC_GEO_MAX_AVAILABILITY | 1 | 6 | 1 | |*151 | HASH JOIN | | 3 | 168 | 11 | |*152 | HASH JOIN | | 40 | 1040 | 6 |
| 153 | MERGE JOIN CARTESIAN |
| 35 | 350 | 3 |
| 154 | TABLE ACCESS BY INDEX ROWID |
SVC_SERVICE_TYPES | 1 | 4 | 1 | |*155 | INDEX UNIQUE SCAN | PK_SVC_SERVICE_TYPES | 1 | | |
| 156 | TABLE ACCESS FULL |
SVC_ROOTS | 35 | 210 | 2 |
| 157 | TABLE ACCESS FULL |
SVC_CLARIFY_GEO_AVAIL_CACHE | 161 | 2576 | 2 |
| 158 | VIEW |
VW_NSO_1 | 32 | 960 | 4 |
| 159 | SORT GROUP BY |
| 32 | 320 | 4 |
| 160 | TABLE ACCESS FULL |
SVC_CLARIFY_GEO_AVAIL_CACHE | 161 | 1610 | 2 | |*161 | TABLE ACCESS BY INDEX ROWID | SVC_QUOTE_LINE_ITEMS | 1 | 8 | 1 | |*162 | INDEX RANGE SCAN | IX_SVC_QUOTE_LINE_ITEMS_PELM | 11 | | | |*163 | TABLE ACCESS BY INDEX ROWID | SVC_ROOT_REGION_MAP | 1 | 7 | 1 | |*164 | INDEX RANGE SCAN | IX_SVC_ROOT_REGION_MAP_ROOT | 2 | | | |*165 | TABLE ACCESS BY INDEX ROWID | SVC_ROOTS | 1 | 13 | 1 | |*166 | INDEX UNIQUE SCAN | PK_SVC_ROOTS | 1 | | | |*167 | INDEX UNIQUE SCAN | PK_SVC_RT_TYPE_ITM_CAT_MAP | 1 | 4 | | |*168 | TABLE ACCESS BY INDEX ROWID | SVC_ROOT_REGION_MAP | 1 | 7 | 1 | |*169 | INDEX RANGE SCAN | IX_SVC_ROOT_REGION_MAP_SEQ | 5 | | 1 | |*170 | TABLE ACCESS BY INDEX ROWID | SVC_ROOTS | 1 | 7 | 1 | |*171 | INDEX UNIQUE SCAN | PK_SVC_ROOTS | 1 | | |
| 172 | VIEW |
SVC_CB_PECS_FOR_PLG | 1 | 12 | 68 |
| 173 | SORT UNIQUE |
| 1 | 54 | 68 | |*174 | TABLE ACCESS BY INDEX ROWID | SVC_QUOTE_LINE_ITEMS | 2 | 8 | 1 |
| 175 | NESTED LOOPS |
| 1 | 54 | 66 |
| 176 | NESTED LOOPS |
| 1 | 50 | 65 |
| 177 | NESTED LOOPS |
| 1 | 46 | 64 |
| 178 | NESTED LOOPS |
| 1 | 38 | 63 |
| 179 | NESTED LOOPS |
| 1 | 27 | 62 |
| 180 | NESTED LOOPS |
| 1 | 24 | 61 |
| 181 | VIEW |
| 1 | 18 | 60 |
| 182 | SORT GROUP BY |
| 1 | 19 | 60 |
| 183 | NESTED LOOPS |
| 1 | 19 | 58 |
| 184 | NESTED LOOPS |
| 1 | 13 | 57 |
| 185 | MERGE JOIN CARTESIAN |
| 1 | 9 | 56 | |*186 | TABLE ACCESS FULL | SVC_QUOTE_LINE_ITEMS | 1 | 7 | 55 |
| 187 | BUFFER SORT |
| 2 | 4 | 1 |
| 188 | INDEX FULL SCAN |
PK_SVC_COUNT_BASED_PLGS | 2 | 4 | 1 | |*189 | INDEX UNIQUE SCAN | PK_SVC_CB_PLG_TYPES_MAP | 1 | 4 | |
| 190 | TABLE ACCESS BY INDEX ROWID |
SVC_PELM | 1 | 6 | 1 | |*191 | INDEX UNIQUE SCAN | PK_SVC_PELM | 1 | | |
| 192 | TABLE ACCESS BY INDEX ROWID |
SVC_QUOTE_EQUIP_LOC | 1 | 6 | 1 | |*193 | INDEX UNIQUE SCAN | PK_SVC_QUOTE_EQUIP_LOC | 1 | | | |*194 | INDEX UNIQUE SCAN | PK_SVC_QUOTES | 1 | 3 | |
| 195 | TABLE ACCESS BY INDEX ROWID |
SVC_COUNT_BASED_PLGS | 1 | 11 | 1 | |*196 | INDEX UNIQUE SCAN | PK_SVC_COUNT_BASED_PLGS | 1 | | | |*197 | TABLE ACCESS BY INDEX ROWID | SVC_PELM | 1 | 8 | 1 | |*198 | INDEX RANGE SCAN | IX_SVC_PELM_QEL_ID | 2 | | | |*199 | INDEX RANGE SCAN | PK_SVC_RT_TYPE_ITM_CAT_MAP | 2 | 8 | 1 | |*200 | INDEX RANGE SCAN | IX_SVC_QUOTE_LINE_ITEMS_PELM | 11 | | |
| 201 | TABLE ACCESS BY INDEX ROWID |
DISTRIBUTOR | 1 | 13 | 1 | |*202 | INDEX RANGE SCAN | DISTRIBUTOR_IDX_017 | 1 | | 1 | |*203 | INDEX UNIQUE SCAN | PK_COUNTRY | 1 | 3 | | |*204 | INDEX RANGE SCAN | PRLP_OCODE | 6 | | |
| 205 | TABLE ACCESS FULL |
MODELCURRENCY | 70 | 210 | 2 |
| 206 | TABLE ACCESS FULL |
CUSTOMERMASTER | 1752 | 24528 | 2 | |*207 | INDEX UNIQUE SCAN | PK_SVC_QUOTES | 1 | 3 | |
| 208 | TABLE ACCESS BY INDEX ROWID |
SVC_QUOTE_LINE_ITEMS | 1 | 20 | 1 | |*209 | INDEX UNIQUE SCAN | PK_SVC_QUOTE_LINE_ITEMS | 1 | | | -----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
4 - access("TBL"."SERVICE_QUOTE_ID"="TBLMAXSEQ"."SERVICE_QUOTE_ID"
AND
"TBL"."SERVICE_TYPE_ID"="TBLMAXSEQ"."SERVICE_TYPE_ID" AND
"TBL"."ROOT_TYPE_ID"="TBLMAXSEQ"."ROOT_TYPE_ID" AND
"TBLMAXSEQ"."SERVICE_LINE_NUMBER"=DECODE("CBPECS"."SERVICE_LINE_NUMBER",NULL,"TBL"."SERVICE_LINE_NUMBER","CBPECS"."SERVICE_LI
NE_NUMBER") AND "TBL"."SEQUENCE"="TBLMAXSEQ"."SEQUENCE")
5 -
access("TBL"."QUOTE_EQUIP_LOC_ID"="CBPECS"."QUOTE_EQUIP_LOC_ID"(+) AND
"TBL"."PRODUCT_LINE_GROUP_ID"="CBPECS"."PRODUCT_LINE_GROUP_ID"(+) AND "TBL"."ROOT_TYPE_ID"="CBPECS"."ROOT_TYPE_ID"(+) AND
"TBL"."SERVICE_TYPE_ID"="CBPECS"."SERVICE_TYPE_ID"(+))
14 - access("TBL"."QUOTE_EQUIP_LOC_ID"="P"."QUOTE_EQUIP_LOC_ID")
15 - access("P"."PELM_ID"="HOMSOM"."PELM_ID")
19 - access("SOM"."PELM_ID"="P"."PELM_ID")
20 - filter("SOM"."SERVICE_REJECTED"=0)
22 - access("P"."QUOTE_EQUIP_LOC_ID"="QEL"."QUOTE_EQUIP_LOC_ID")
24 - access("HOM"."PELM_ID"="P"."PELM_ID")
25 - filter("HOM"."SERVICE_REJECTED"=0)
27 - access("P"."QUOTE_EQUIP_LOC_ID"="QEL"."QUOTE_EQUIP_LOC_ID")
30 - access(TRIM("D"."GPPRDISTRIBUTORID")=TRIM("C"."CUST_CODE") AND
"CRM"."REGION_ID"=TO_NUMBER("C"."COUNTRY"))
36 - access("Q"."SERVICE_QUOTE_ID"=2165)
38 - access("Q"."DISTRIBUTORID"="D"."DISTRIBUTORID")
40 - filter("QEL"."SERVICE_QUOTE_ID"=2165)
46 - filter("TBL"."CLARIFY_LOCATION_ID"="GEO"."CITY_ID"(+))
47 - access("GEO"."CITY_ID"="VW_NSO_2"."$nso_col_1" AND
"GEO"."SERVICE_TYPE"="VW_NSO_2"."$nso_col_2" AND
"GEO"."SUPPORT_PROGRAM_ORDER"="VW_NSO_2"."$nso_col_3")
48 - access("ST"."SERVICE_TYPE"=TO_NUMBER("GEO"."SERVICE_TYPE") AND
"GEO"."ROOT"="R"."ROOT")
51 - access("TBL"."SERVICE_TYPE_ID"="ST"."SERVICE_TYPE_ID")
57 - filter("QLI"."ITEM_CATEGORY_ID" IS NOT NULL AND
"QLI"."SERVICEABILITY_STATUS_ID"=0)
58 - access("P"."PELM_ID"="QLI"."PELM_ID")
filter("QLI"."PELM_ID" IS NOT NULL)
59 - filter("TBL"."CEANTAR_ID"="RRM"."CEANTAR_ID")
60 - access("HOMSOM"."ROOT_ID"="RRM"."ROOT_ID")
61 - filter("TBL"."SERVICE_TYPE_ID"="R"."SERVICE_TYPE_ID" AND
"HOMSOM"."ROOT_TYPE_ID"="R"."ROOT_TYPE_ID")
62 - access("RRM"."ROOT_ID"="R"."ROOT_ID")
63 - access("R"."ROOT_TYPE_ID"="RTICM"."ROOT_TYPE_ID" AND
"RTICM"."ITEM_CATEGORY_ID"="QLI"."ITEM_CATEGORY_ID")
64 - filter("RRM"."CEANTAR_ID"="RRMSEQ"."CEANTAR_ID")
65 - access("RRM"."SEQUENCE"<="RRMSEQ"."SEQUENCE")
66 - filter("R"."SERVICE_TYPE_ID"="RSEQ"."SERVICE_TYPE_ID" AND
"R"."ROOT_TYPE_ID"="RSEQ"."ROOT_TYPE_ID")
67 - access("RRMSEQ"."ROOT_ID"="RSEQ"."ROOT_ID")
70 - filter("QLI"."ITEM_CATEGORY_ID"="RTICM"."ITEM_CATEGORY_ID" AND
"QLI"."ITEM_CATEGORY_ID" IS NOT NULL)
"QLI"."COUNT_BASED_TYPE_ID"="CBPLGTM"."COUNT_BASED_TYPE_ID")
87 - access("QLI"."PELM_ID"="P"."PELM_ID")
89 -
access("QEL"."QUOTE_EQUIP_LOC_ID"="TBLHASCOUNTS"."QUOTE_EQUIP_LOC_ID")
90 - access("Q"."SERVICE_QUOTE_ID"="QEL"."SERVICE_QUOTE_ID")
92 -
access("CBPLG"."COUNT_BASED_PLG_ID"="TBLHASCOUNTS"."COUNT_BASED_PLG_ID")
93 -
filter("P"."PRODUCT_LINE_GROUP_ID"="CBPLG"."PRODUCT_LINE_GROUP_ID")
94 - access("QEL"."QUOTE_EQUIP_LOC_ID"="P"."QUOTE_EQUIP_LOC_ID")
95 - access("RTICM"."ROOT_TYPE_ID"="CBPLG"."ROOT_TYPE_ID")
96 - access("P"."PELM_ID"="QLI"."PELM_ID")
filter("QLI"."PELM_ID" IS NOT NULL)
99 - access(TRIM("CM"."COUNTRY")=TRIM("PRLP"."REGION_ID") AND
TRIM("D"."GPPRDISTRIBUTORID")=TRIM("CM"."CUST_CODE") AND
"CM"."CURRENCY"="MC"."CURRENCYID")
100 - access(TRIM("MC"."CURRENCYID")=TRIM("PRLP"."CURRENCY"))
101 - filter("PRLP"."EXPDATE">SYSDATE@!)
105 - filter(DECODE("CBPECS"."SERVICE_LINE_NUMBER",NULL,0,1)=0)
106 -
"TBL"."PRODUCT_LINE_GROUP_ID"="CBPECS"."PRODUCT_LINE_GROUP_ID"(+) AND "TBL"."ROOT_TYPE_ID"="CBPECS"."ROOT_TYPE_ID"(+) AND
"TBL"."SERVICE_TYPE_ID"="CBPECS"."SERVICE_TYPE_ID"(+))
109 - access("Q"."SERVICE_QUOTE_ID"=2165)
118 - access("TBL"."QUOTE_EQUIP_LOC_ID"="P"."QUOTE_EQUIP_LOC_ID")
119 - access("P"."PELM_ID"="HOMSOM"."PELM_ID")
123 - access("SOM"."PELM_ID"="P"."PELM_ID")
124 - filter("SOM"."SERVICE_REJECTED"=0)
126 - access("P"."QUOTE_EQUIP_LOC_ID"="QEL"."QUOTE_EQUIP_LOC_ID")
128 - access("HOM"."PELM_ID"="P"."PELM_ID")
129 - filter("HOM"."SERVICE_REJECTED"=0)
131 - access("P"."QUOTE_EQUIP_LOC_ID"="QEL"."QUOTE_EQUIP_LOC_ID")
134 - access(TRIM("D"."GPPRDISTRIBUTORID")=TRIM("C"."CUST_CODE") AND
"CRM"."REGION_ID"=TO_NUMBER("C"."COUNTRY"))
140 - access("Q"."SERVICE_QUOTE_ID"=2165)
142 - access("Q"."DISTRIBUTORID"="D"."DISTRIBUTORID")
144 - filter("QEL"."SERVICE_QUOTE_ID"=2165)
150 - filter("TBL"."CLARIFY_LOCATION_ID"="GEO"."CITY_ID"(+))
151 - access("GEO"."CITY_ID"="VW_NSO_1"."$nso_col_1" AND
"GEO"."SERVICE_TYPE"="VW_NSO_1"."$nso_col_2" AND
"GEO"."SUPPORT_PROGRAM_ORDER"="VW_NSO_1"."$nso_col_3")
152 - access("ST"."SERVICE_TYPE"=TO_NUMBER("GEO"."SERVICE_TYPE") AND
"GEO"."ROOT"="R"."ROOT")
155 - access("TBL"."SERVICE_TYPE_ID"="ST"."SERVICE_TYPE_ID")
161 - filter("QLI"."ITEM_CATEGORY_ID" IS NOT NULL AND
"QLI"."SERVICEABILITY_STATUS_ID"=0)
162 - access("P"."PELM_ID"="QLI"."PELM_ID")
filter("QLI"."PELM_ID" IS NOT NULL)
163 - filter("TBL"."CEANTAR_ID"="RRM"."CEANTAR_ID")
164 - access("HOMSOM"."ROOT_ID"="RRM"."ROOT_ID")
165 - filter("TBL"."SERVICE_TYPE_ID"="R"."SERVICE_TYPE_ID" AND
"HOMSOM"."ROOT_TYPE_ID"="R"."ROOT_TYPE_ID")
166 - access("RRM"."ROOT_ID"="R"."ROOT_ID")
167 - access("R"."ROOT_TYPE_ID"="RTICM"."ROOT_TYPE_ID" AND
"RTICM"."ITEM_CATEGORY_ID"="QLI"."ITEM_CATEGORY_ID")
168 - filter("RRM"."CEANTAR_ID"="RRMSEQ"."CEANTAR_ID")
169 - access("RRM"."SEQUENCE"<="RRMSEQ"."SEQUENCE")
170 - filter("R"."SERVICE_TYPE_ID"="RSEQ"."SERVICE_TYPE_ID" AND
"R"."ROOT_TYPE_ID"="RSEQ"."ROOT_TYPE_ID")
171 - access("RRMSEQ"."ROOT_ID"="RSEQ"."ROOT_ID")
174 - filter("QLI"."ITEM_CATEGORY_ID"="RTICM"."ITEM_CATEGORY_ID" AND
"QLI"."ITEM_CATEGORY_ID" IS NOT NULL)
"QLI"."COUNT_BASED_TYPE_ID"="CBPLGTM"."COUNT_BASED_TYPE_ID")
191 - access("QLI"."PELM_ID"="P"."PELM_ID")
193 -
access("QEL"."QUOTE_EQUIP_LOC_ID"="TBLHASCOUNTS"."QUOTE_EQUIP_LOC_ID")
194 - access("Q"."SERVICE_QUOTE_ID"="QEL"."SERVICE_QUOTE_ID")
196 -
access("CBPLG"."COUNT_BASED_PLG_ID"="TBLHASCOUNTS"."COUNT_BASED_PLG_ID")
197 -
filter("P"."PRODUCT_LINE_GROUP_ID"="CBPLG"."PRODUCT_LINE_GROUP_ID")
198 - access("QEL"."QUOTE_EQUIP_LOC_ID"="P"."QUOTE_EQUIP_LOC_ID")
199 - access("RTICM"."ROOT_TYPE_ID"="CBPLG"."ROOT_TYPE_ID")
200 - access("P"."PELM_ID"="QLI"."PELM_ID")
filter("QLI"."PELM_ID" IS NOT NULL)
202 - access("Q"."DISTRIBUTORID"="D"."DISTRIBUTORID")
203 - access("D"."COUNTRYID"="C"."COUNTRYID")
204 -
access("PRLP"."ORDERCODE"="TBL"."ROOT"||DECODE("CBPECS"."SERVICE_LINE_NUMBER",NULL,"TBL"."SERVICE_LINE_NUMBER","CBPECS
"."SERVICE_LINE_NUMBER"))
207 - access("Q"."SERVICE_QUOTE_ID"="TBL"."SERVICE_QUOTE_ID")
209 - access("TBL"."QUOTE_LINE_ITEM_ID"="QLI"."QUOTE_LINE_ITEM_ID")
Note: cpu costing is off Received on Thu Aug 31 2006 - 09:46:53 CDT
 |
 |