SELECT and INSERT...SELECT use two different plans
- From: "McPeak, Matt" <vxsmimmcp@xxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 7 May 2014 17:35:15 +0000
Without getting too much into the specific situation (which I'll include in the
post below, for reference), is there any logical reason why Oracle would take a
SELECT statement having plan "X" with a cost of, say, 300, and replace it with
plan "Y", with a cost of, say, 16000 when I stick an INSERT INTO... in front of
it?
The SELECT statement refers to views (e.g., BOM_BILL_OF_MATERIALS) that use
non-deterministic PL/SQL functions - the problem seems to be around that area.
I'm not looking for anyone to dig into the specific query (but, hey, if you
want to do my work for me, great!). I'm more looking for general/theoretical
reasons why something like this would happen. It seems to me that the SELECT
should use the same plan, regardless of whether the results are then being
inserted into a table.
Here is the exact SQL:
INSERT INTO verp_otm_staged_von_accys (von,
accessory_code,
labor_hours,
heavy_flag)
SELECT DISTINCT v.von,
msi.segment1 accessory_code,
NVL (bsor.usage_rate_or_amount, 0) labor_hours,
CASE WHEN ',' || msid.heavy_ports || ',' LIKE '%' || v.port
|| '%' THEN 'Y' ELSE 'N' END heavy_flag
FROM verp_otm_staged_vons v
INNER JOIN (SELECT oel.header_id, oel.inventory_item_id,
oel.item_type_code
FROM oe_order_lines oel
WHERE oel.item_type_code IN ('OPTION', 'CLASS')
UNION ALL
SELECT vosal.header_id, vosal.inventory_item_id,
vosal.item_type_code
FROM verp_order_std_accy_lines vosal
WHERE vosal.item_type_code IN ('OPTION', 'CLASS')) oel
ON oel.header_id = v.header_id
LEFT JOIN bom_bill_of_materials bbom
ON bbom.organization_id = 92
AND bbom.assembly_item_id = oel.inventory_item_id
AND oel.item_type_code = 'CLASS'
LEFT JOIN bom_inventory_components bic
ON bic.bill_sequence_id = bbom.bill_sequence_id
AND NVL (bic.disable_date, SYSDATE + 1) > SYSDATE
INNER JOIN mtl_system_items msi
ON msi.organization_id = 92
AND msi.inventory_item_id = NVL (bic.component_item_id,
oel.inventory_item_id)
INNER JOIN mtl_system_items_b_dfv msid
ON msid.row_id = msi.ROWID
INNER JOIN mtl_parameters mp
ON mp.organization_code = v.port
LEFT JOIN bom_standard_operations bso
ON bso.operation_code = msi.segment1
AND bso.organization_id = mp.organization_id
LEFT JOIN bom_std_op_resources bsor
ON bsor.standard_operation_id = bso.standard_operation_id
LEFT JOIN bom_resources br
ON br.resource_id = bsor.resource_id
AND br.resource_code IN ('Labor', 'Std Labor')
WHERE v.header_id = :b1
Without the INSERT INTO... , the plan is:
Plan
SELECT STATEMENT ALL_ROWSCost: 304 Bytes: 3,762 Cardinality: 19 Partition
#: 0
36 HASH UNIQUE Cost: 304 Bytes: 3,762 Cardinality: 19 Partition #: 0
35 NESTED LOOPS OUTER Cost: 303 Bytes: 3,762 Cardinality: 19 Partition
#: 0
32 NESTED LOOPS OUTER Cost: 284 Bytes: 3,496 Cardinality: 19
Partition #: 0
29 NESTED LOOPS OUTER Cost: 247 Bytes: 3,268 Cardinality: 19
Partition #: 0
26 VIEW SYS. Cost: 227 Bytes: 3,021 Cardinality: 19 Partition #: 0
25 NESTED LOOPS Cost: 0 Bytes: 0 Cardinality: 0 Partition #: 0
23 NESTED LOOPS Cost: 227 Bytes: 3,667 Cardinality: 19
Partition #: 0
21 NESTED LOOPS OUTER Cost: 53 Bytes: 10,962 Cardinality: 87
Partition #: 0
18 NESTED LOOPS OUTER Cost: 29 Bytes: 492 Cardinality: 6
Partition #: 0
13 MERGE JOIN CARTESIAN Cost: 5 Bytes: 402 Cardinality:
6 Partition #: 0
4 NESTED LOOPS Cost: 1 Bytes: 37 Cardinality: 1
Partition #: 0
2 TABLE ACCESS BY INDEX ROWID
VERPCUST.VERP_OTM_STAGED_VONS Cost: 0 Bytes: 29 Cardinality: 1 Partition #: 0
1 INDEX UNIQUE SCAN VERPCUST.VERP_OTM_STAGED_VONS_U2
[Analyzed]Cost: 0 Bytes: 0 Cardinality: 1 Partition #: 0
3 INDEX FULL SCAN VERPCUST.VERP_VPS_SUPPLY_MP_N1
[Analyzed]Cost: 1 Bytes: 8 Cardinality: 1 Partition #: 0
12 BUFFER SORT Cost: 4 Bytes: 180 Cardinality: 6
Partition #: 0
11 VIEW APPS. Cost: 4 Bytes: 180 Cardinality: 6
Partition #: 0
10 UNION-ALL Cost: 0 Bytes: 0 Cardinality: 0
Partition #: 0
7 FILTER Cost: 0 Bytes: 0 Cardinality: 0
Partition #: 0
6 TABLE ACCESS BY INDEX ROWID
ONT.OE_ORDER_LINES_ALL [Analyzed]Cost: 7 Bytes: 160 Cardinality: 8 Partition
#: 0
5 INDEX RANGE SCAN
VERPCUST.VERP_OE_ORDER_LINES_N1 [Analyzed]Cost: 4 Bytes: 0 Cardinality: 7
Partition #: 0
9 TABLE ACCESS BY INDEX ROWID
VERPCUST.VERP_ORDER_STD_ACCY_LINES [Analyzed]Cost: 4 Bytes: 95 Cardinality: 5
Partition #: 0
8 INDEX RANGE SCAN
VERPCUST.VERP_ORDER_STD_ACCY_LINES_N1 [Analyzed]Cost: 3 Bytes: 0 Cardinality:
5 Partition #: 0
17 VIEW PUSHED PREDICATE APPS.BOM_BILL_OF_MATERIALS Cost: 4
Bytes: 15 Cardinality: 1 Partition #: 0
16 FILTER Cost: 0 Bytes: 0 Cardinality: 0 Partition
#: 0
15 TABLE ACCESS BY INDEX ROWID BOM.BOM_STRUCTURES_B
[Analyzed]Cost: 4 Bytes: 36 Cardinality: 1 Partition #: 0
14 INDEX RANGE SCAN BOM.BOM_STRUCTURES_B_N2
[Analyzed]Cost: 3 Bytes: 0 Cardinality: 1 Partition #: 0
20 TABLE ACCESS BY INDEX ROWID BOM.BOM_COMPONENTS_B
[Analyzed]Cost: 4 Bytes: 616 Cardinality: 14 Partition #: 0
19 INDEX RANGE SCAN BOM.BOM_COMPONENTS_B_N2 [Analyzed]Cost:
2 Bytes: 0 Cardinality: 15 Partition #: 0
22 INDEX UNIQUE SCAN INV.MTL_SYSTEM_ITEMS_B_U1 [Analyzed]Cost:
1 Bytes: 0 Cardinality: 1 Partition #: 0
24 TABLE ACCESS BY INDEX ROWID INV.MTL_SYSTEM_ITEMS_B
[Analyzed]Cost: 2 Bytes: 67 Cardinality: 1 Partition #: 0
28 TABLE ACCESS BY INDEX ROWID BOM.BOM_STANDARD_OPERATIONS
[Analyzed]Cost: 2 Bytes: 13 Cardinality: 1 Partition #: 0
27 INDEX RANGE SCAN BOM.BOM_STANDARD_OPERATIONS_U1 [Analyzed]Cost:
1 Bytes: 0 Cardinality: 1 Partition #: 0
31 TABLE ACCESS BY INDEX ROWID BOM.BOM_STD_OP_RESOURCES [Analyzed]Cost:
2 Bytes: 12 Cardinality: 1 Partition #: 0
30 INDEX RANGE SCAN BOM.BOM_STD_OP_RESOURCES_U1 [Analyzed]Cost: 1
Bytes: 0 Cardinality: 1 Partition #: 0
34 TABLE ACCESS BY INDEX ROWID BOM.BOM_RESOURCES [Analyzed]Cost: 1
Bytes: 14 Cardinality: 1 Partition #: 0
33 INDEX UNIQUE SCAN BOM.BOM_RESOURCES_U2 [Analyzed]Cost: 0 Bytes: 0
Cardinality: 1 Partition #: 0
With the INSERT INTO, the plan is:
Plan
INSERT STATEMENT ALL_ROWSCost: 15,986 Bytes: 4,009 Cardinality: 19
Partition #: 0
35 LOAD TABLE CONVENTIONAL VERPCUST.VERP_OTM_STAGED_VON_ACCYS Cost: 0 Bytes:
0 Cardinality: 0 Partition #: 0
34 HASH UNIQUE Cost: 15,986 Bytes: 4,009 Cardinality: 19 Partition #: 0
33 NESTED LOOPS OUTER Cost: 15,985 Bytes: 4,009 Cardinality: 19
Partition #: 0
30 NESTED LOOPS OUTER Cost: 15,966 Bytes: 3,743 Cardinality: 19
Partition #: 0
27 NESTED LOOPS OUTER Cost: 15,929 Bytes: 3,515 Cardinality: 19
Partition #: 0
24 VIEW SYS. Cost: 15,909 Bytes: 3,268 Cardinality: 19 Partition
#: 0
23 NESTED LOOPS Cost: 0 Bytes: 0 Cardinality: 0 Partition #: 0
21 NESTED LOOPS Cost: 15,909 Bytes: 4,370 Cardinality: 19
Partition #: 0
19 NESTED LOOPS OUTER Cost: 15,735 Bytes: 14,181
Cardinality: 87 Partition #: 0
16 HASH JOIN OUTER Cost: 15,712 Bytes: 714 Cardinality:
6 Partition #: 0
13 MERGE JOIN CARTESIAN Cost: 5 Bytes: 480
Cardinality: 6 Partition #: 0
4 NESTED LOOPS Cost: 1 Bytes: 37 Cardinality: 1
Partition #: 0
2 TABLE ACCESS BY INDEX ROWID
VERPCUST.VERP_OTM_STAGED_VONS Cost: 0 Bytes: 29 Cardinality: 1 Partition #: 0
1 INDEX UNIQUE SCAN
VERPCUST.VERP_OTM_STAGED_VONS_U2 [Analyzed]Cost: 0 Bytes: 0 Cardinality: 1
Partition #: 0
3 INDEX FULL SCAN VERPCUST.VERP_VPS_SUPPLY_MP_N1
[Analyzed]Cost: 1 Bytes: 8 Cardinality: 1 Partition #: 0
12 BUFFER SORT Cost: 4 Bytes: 258 Cardinality: 6
Partition #: 0
11 VIEW APPS. Cost: 4 Bytes: 258 Cardinality: 6
Partition #: 0
10 UNION-ALL Cost: 0 Bytes: 0 Cardinality: 0
Partition #: 0
7 FILTER Cost: 0 Bytes: 0 Cardinality: 0
Partition #: 0
6 TABLE ACCESS BY INDEX ROWID
ONT.OE_ORDER_LINES_ALL [Analyzed]Cost: 7 Bytes: 160 Cardinality: 8 Partition
#: 0
5 INDEX RANGE SCAN
VERPCUST.VERP_OE_ORDER_LINES_N1 [Analyzed]Cost: 4 Bytes: 0 Cardinality: 7
Partition #: 0
9 TABLE ACCESS BY INDEX ROWID
VERPCUST.VERP_ORDER_STD_ACCY_LINES [Analyzed]Cost: 4 Bytes: 95 Cardinality: 5
Partition #: 0
8 INDEX RANGE SCAN
VERPCUST.VERP_ORDER_STD_ACCY_LINES_N1 [Analyzed]Cost: 3 Bytes: 0 Cardinality:
5 Partition #: 0
15 VIEW APPS.BOM_BILL_OF_MATERIALS Cost: 15,707 Bytes:
166,296 Cardinality: 4,264 Partition #: 0
14 TABLE ACCESS FULL BOM.BOM_STRUCTURES_B
[Analyzed]Cost: 15,707 Bytes: 153,504 Cardinality: 4,264 Partition #: 0
18 TABLE ACCESS BY INDEX ROWID BOM.BOM_COMPONENTS_B
[Analyzed]Cost: 4 Bytes: 616 Cardinality: 14 Partition #: 0
17 INDEX RANGE SCAN BOM.BOM_COMPONENTS_B_N2
[Analyzed]Cost: 2 Bytes: 0 Cardinality: 15 Partition #: 0
20 INDEX UNIQUE SCAN INV.MTL_SYSTEM_ITEMS_B_U1
[Analyzed]Cost: 1 Bytes: 0 Cardinality: 1 Partition #: 0
22 TABLE ACCESS BY INDEX ROWID INV.MTL_SYSTEM_ITEMS_B
[Analyzed]Cost: 2 Bytes: 67 Cardinality: 1 Partition #: 0
26 TABLE ACCESS BY INDEX ROWID BOM.BOM_STANDARD_OPERATIONS
[Analyzed]Cost: 2 Bytes: 13 Cardinality: 1 Partition #: 0
25 INDEX RANGE SCAN BOM.BOM_STANDARD_OPERATIONS_U1
[Analyzed]Cost: 1 Bytes: 0 Cardinality: 1 Partition #: 0
29 TABLE ACCESS BY INDEX ROWID BOM.BOM_STD_OP_RESOURCES
[Analyzed]Cost: 2 Bytes: 12 Cardinality: 1 Partition #: 0
28 INDEX RANGE SCAN BOM.BOM_STD_OP_RESOURCES_U1 [Analyzed]Cost: 1
Bytes: 0 Cardinality: 1 Partition #: 0
32 TABLE ACCESS BY INDEX ROWID BOM.BOM_RESOURCES [Analyzed]Cost: 1
Bytes: 14 Cardinality: 1 Partition #: 0
31 INDEX UNIQUE SCAN BOM.BOM_RESOURCES_U2 [Analyzed]Cost: 0 Bytes: 0
Cardinality: 1 Partition #: 0
Other related posts: