RE: SELECT and INSERT...SELECT use two different plans
From: Iotzov, Iordan <IIotzov_at_newsamerica.com>
Date: Wed, 7 May 2014 13:47:30 -0400
Message-ID: <9287D00721407A4CBDC9925C789DBB6702FE197775_at_nam-wil-exc-l03.newsamerica.com>
When Oracle runs INSERT as SELECT, it has to have all records from the select clause in order to succeed. When Oracle runs a SELECT, it has to be able to deliver, at least initially, only the beginning records from the query. In theory, setting OPTIMIZER_MODE = ALL_ROWS should result in identical execution plans for INSERT as SELECT and SELECT, but it does not...
This message and its attachments may contain legally privileged or confidential information. It is intended solely for the named addressee. If you are not the addressee indicated in this message (or responsible for delivery of the message to the addressee), you may not copy or deliver this message or its attachments to anyone. Rather, you should permanently delete this message and its attachments and kindly notify the sender by reply e-mail. Any content of this message and its attachments that does not relate to the official business of News America Incorporated or its subsidiaries must be taken not to have been sent or endorsed by any of them. No warranty is made that the e-mail or attachment(s) are free from computer virus or other defect.
Date: Wed, 7 May 2014 13:47:30 -0400
Message-ID: <9287D00721407A4CBDC9925C789DBB6702FE197775_at_nam-wil-exc-l03.newsamerica.com>
When Oracle runs INSERT as SELECT, it has to have all records from the select clause in order to succeed. When Oracle runs a SELECT, it has to be able to deliver, at least initially, only the beginning records from the query. In theory, setting OPTIMIZER_MODE = ALL_ROWS should result in identical execution plans for INSERT as SELECT and SELECT, but it does not...
Iordan Iotzov | Senior Database Administrator, Information Services | News America Marketing 20 Westport Road, 1st floor, Wilton CT 06897 | P 203.563.6472 | C 203.423.9269 iiotzov_at_newsamerica.com<mailto:iiotzov_at_newsamerica.com>| newsamerica.com<http://www.newsamerica.com/> | smartsource.com<http://www.smartsource.com/>
[cid:image001.gif_at_01CF69FA.E3B40500]
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of McPeak, Matt
Sent: Wednesday, May 07, 2014 1:35 PM
To: oracle-l_at_freelists.org
Subject: SELECT and INSERT...SELECT use two different plans
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
________________________________
This message and its attachments may contain legally privileged or confidential information. It is intended solely for the named addressee. If you are not the addressee indicated in this message (or responsible for delivery of the message to the addressee), you may not copy or deliver this message or its attachments to anyone. Rather, you should permanently delete this message and its attachments and kindly notify the sender by reply e-mail. Any content of this message and its attachments that does not relate to the official business of News America Incorporated or its subsidiaries must be taken not to have been sent or endorsed by any of them. No warranty is made that the e-mail or attachment(s) are free from computer virus or other defect.
-- http://www.freelists.org/webpage/oracle-lReceived on Wed May 07 2014 - 19:47:30 CEST