Re: Insert-Select much slower than same select on its own

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 31 Aug 2006 10:33:42 -0700
Message-ID: <1157045624.199028@bubbleator.drizzle.com>

zzzzzz45_at_hotmail.com wrote:
> Got dbms_xplan working so here're the plans:
>
> "Bad " insert plan:
>
>
> -------------------------------------------------------------------------------------------------------------------------------
> | Id | Operation |
> 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 -
> 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"(+))
> 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)
> 96 - filter("QLI"."PELM_ID" IS NOT NULL AND
> "QLI"."COUNT_BASED_TYPE_ID" IS NOT NULL)
> 99 -
> access("CBPLGTM"."COUNT_BASED_PLG_ID"="CBPLG"."COUNT_BASED_PLG_ID" AND
>
> "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")
> 165 - access("TBL"."SERVICE_TYPE_ID"="ST"."SERVICE_TYPE_ID")
> 173 - filter("QLI"."ITEM_CATEGORY_ID"="RTICM"."ITEM_CATEGORY_ID" AND
> "QLI"."ITEM_CATEGORY_ID" IS NOT NULL)
> 185 - filter("QLI"."PELM_ID" IS NOT NULL AND
> "QLI"."COUNT_BASED_TYPE_ID" IS NOT NULL)
> 188 -
> access("CBPLGTM"."COUNT_BASED_PLG_ID"="CBPLG"."COUNT_BASED_PLG_ID" AND
>
> "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:
>
>
> -----------------------------------------------------------------------------------------------------------------------------
> | Id | Operation |
> 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)
> 82 - filter("QLI"."PELM_ID" IS NOT NULL AND
> "QLI"."COUNT_BASED_TYPE_ID" IS NOT NULL)
> 85 -
> access("CBPLGTM"."COUNT_BASED_PLG_ID"="CBPLG"."COUNT_BASED_PLG_ID" AND
>
> "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 -
> 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"(+))
> 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)
> 186 - filter("QLI"."PELM_ID" IS NOT NULL AND
> "QLI"."COUNT_BASED_TYPE_ID" IS NOT NULL)
> 189 -
> access("CBPLGTM"."COUNT_BASED_PLG_ID"="CBPLG"."COUNT_BASED_PLG_ID" AND
>
> "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

I started gagging at MERGE JOIN CARTESIAN more than one dozen times though the number of rows seems to be small and the number of FULL TABLE ACCESSES but it seems your problem is the INSERT not the SELECT if I recall correctly. Is that the case?

What is the timing with and without the insert?

What indexes exist where you are doing the insert?

How many rows are in each of the tables involved?

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Thu Aug 31 2006 - 12:33:42 CDT