November 3, 2011
An interesting request came in from an ERP mailing list – how would you write a SQL statement that indicates the next operation in a manufacturing process. Sounds like an easy requirement. Let’s take a look at a graphical view of one example (the graphical view is created using a program that I wrote probably 7 years ago):
The cyan colored rectangles in the above picture are the various operations (found in the OPERATION table) in the manufacturing process. The light green colored rectangles are the material requirements (found in the REQUIREMENT table) that are used by the operation that is immediately above the sequence of light green rectangles. The white rectangles are the sub-assembly headers for the manufacturing process. The white rectangle at the far left is considered the main sub-assembly 0 (WORKORDER_SUB_ID = ‘0’), the middle white rectangle in this case is sub-assembly 1 (WORKORDER_SUB_ID = ‘1’), and the right-most white rectangle in this case is sub-assembly 200 (WORKORDER_SUB_ID = ‘200’). All sub-assemblies except the main sub-assembly 0 are tied to another “parent” operation that consumes the sub-assembly just as if it were another material requirement; therefore a dummy row is added to the material requirements table (REQUIREMENT) with the SUBORD_WO_SUB_ID column set to the sub-assembly number of the connected operation.
In the above picture, on the main sub-assembly 0, operation 888 (at resource ID INSPECT) is the first operation, and operation 999 (at resource ID SHIP) is the second operation. On sub-assembly 1, operation 10 (at resource ID 92) is the first operation, operation 20 (at resource ID 62) is the second operation, and operation 541 (at resource ID KRW) is the third operation. On sub-assembly 200, operation 10 (at resource ID WSD70-TACK) is the first operation, operation 20 (at resource ID WELD-CBA) is the second operation, operation 40 (at resource ID BLAST) is the third operation, and operation 50 (at resource ID PAINT) is the fourth operation.
Since I am working with Oracle Database, I can construct a SQL statement using the LEAD analytic function to find the next operation number and the next resource ID:
SELECT * FROM (SELECT O.WORKORDER_BASE_ID, O.WORKORDER_LOT_ID, O.WORKORDER_SPLIT_ID, O.WORKORDER_SUB_ID, O.SEQUENCE_NO, O.RESOURCE_ID, LEAD(O.SEQUENCE_NO,1) OVER (PARTITION BY O.WORKORDER_BASE_ID, O.WORKORDER_LOT_ID, O.WORKORDER_SPLIT_ID, O.WORKORDER_SUB_ID ORDER BY O.SEQUENCE_NO) AS NEXT_SEQUENCE_NO, LEAD(O.RESOURCE_ID,1) OVER (PARTITION BY O.WORKORDER_BASE_ID, O.WORKORDER_LOT_ID, O.WORKORDER_SPLIT_ID, O.WORKORDER_SUB_ID ORDER BY O.SEQUENCE_NO) AS NEXT_RESOURCE_ID FROM OPERATION O WHERE O.WORKORDER_TYPE='W' AND O.WORKORDER_BASE_ID='13000' AND O.WORKORDER_LOT_ID='50' AND O.WORKORDER_SPLIT_ID='0') O ORDER BY O.WORKORDER_BASE_ID, O.WORKORDER_LOT_ID, O.WORKORDER_SPLIT_ID, O.WORKORDER_SUB_ID, O.SEQUENCE_NO; WORKORDER_BASE_ID WOR WOR WOR SEQUENCE_NO RESOURCE_ID NEXT_SEQUENCE_NO NEXT_RESOURCE_I -------------------- --- --- --- ----------- --------------- ---------------- --------------- 13000 50 0 0 888 INSPECT 999 SHIP 13000 50 0 0 999 SHIP 13000 50 0 1 10 92 20 62 13000 50 0 1 20 62 541 KRW 13000 50 0 1 541 KRW 13000 50 0 200 10 WSD70-TACK FIX 20 WELD-CBA 13000 50 0 200 20 WELD-CBA 40 BLAST 13000 50 0 200 40 BLAST 50 PAINT 13000 50 0 200 50 PAINT
Looks like a pretty easy solution… unless we recognize that after the last operation on sub-assembly 200 the next operation is really the parent operation of that sub-assembly (operation 10 on sub-assembly 1). Likewise, after the last operation on sub-assembly 1, the next operation is really the parent operation of that sub-assembly (operation 888 on sub-assembly 0). There is no next operation after operation 999 on the main sub-assembly 0. How can we fix this problem with the NULL next operations in the previous output? We just need an outer join to the dummy row in the REQUIREMENT table to pick up the parent’s sub-assembly number, and then join that dummy row back to a second reference of the OPERATION table. The NVL2 and COALESCE functions are used to handle the cases where the original next operation and next resource ID would have been output as NULL values:
SELECT O.WORKORDER_BASE_ID, O.WORKORDER_LOT_ID, O.WORKORDER_SPLIT_ID, O.WORKORDER_SUB_ID, O.SEQUENCE_NO, O.RESOURCE_ID, NVL2(O.NEXT_SEQUENCE_NO,O.WORKORDER_SUB_ID,O2.WORKORDER_SUB_ID) NEXT_SUB_ID, COALESCE(O.NEXT_SEQUENCE_NO,O2.SEQUENCE_NO) NEXT_SEQUENCE_NO, COALESCE(O.NEXT_RESOURCE_ID,O2.RESOURCE_ID) NEXT_RESOURCE_ID FROM (SELECT O.WORKORDER_TYPE, O.WORKORDER_BASE_ID, O.WORKORDER_LOT_ID, O.WORKORDER_SPLIT_ID, O.WORKORDER_SUB_ID, O.SEQUENCE_NO, O.RESOURCE_ID, LEAD(O.SEQUENCE_NO,1) OVER (PARTITION BY O.WORKORDER_BASE_ID, O.WORKORDER_LOT_ID, O.WORKORDER_SPLIT_ID, O.WORKORDER_SUB_ID ORDER BY O.SEQUENCE_NO) AS NEXT_SEQUENCE_NO, LEAD(O.RESOURCE_ID,1) OVER (PARTITION BY O.WORKORDER_BASE_ID, O.WORKORDER_LOT_ID, O.WORKORDER_SPLIT_ID, O.WORKORDER_SUB_ID ORDER BY O.SEQUENCE_NO) AS NEXT_RESOURCE_ID FROM OPERATION O WHERE O.WORKORDER_TYPE='W' AND O.WORKORDER_BASE_ID='13000' AND O.WORKORDER_LOT_ID='50' AND O.WORKORDER_SPLIT_ID='0') O, REQUIREMENT R, OPERATION O2 WHERE O.WORKORDER_TYPE=R.WORKORDER_TYPE(+) AND O.WORKORDER_BASE_ID=R.WORKORDER_BASE_ID(+) AND O.WORKORDER_LOT_ID=R.WORKORDER_LOT_ID(+) AND O.WORKORDER_SPLIT_ID=R.WORKORDER_SPLIT_ID(+) AND O.WORKORDER_SUB_ID=R.SUBORD_WO_SUB_ID(+) AND R.WORKORDER_TYPE=O2.WORKORDER_TYPE(+) AND R.WORKORDER_BASE_ID=O2.WORKORDER_BASE_ID(+) AND R.WORKORDER_LOT_ID=O2.WORKORDER_LOT_ID(+) AND R.WORKORDER_SPLIT_ID=O2.WORKORDER_SPLIT_ID(+) AND R.WORKORDER_SUB_ID=O2.WORKORDER_SUB_ID(+) AND R.OPERATION_SEQ_NO=O2.SEQUENCE_NO(+) ORDER BY O.WORKORDER_BASE_ID, O.WORKORDER_LOT_ID, O.WORKORDER_SPLIT_ID, O.WORKORDER_SUB_ID, O.SEQUENCE_NO; WORKORDER_BASE_ID WOR WOR WOR SEQUENCE_NO RESOURCE_ID NEX NEXT_SEQUENCE_NO NEXT_RESOURCE_I -------------------- --- --- --- ----------- --------------- --- ---------------- --------------- 13000 50 0 0 888 INSPECT 0 999 SHIP 13000 50 0 0 999 SHIP 13000 50 0 1 10 92 1 20 62 13000 50 0 1 20 62 1 541 KRW 13000 50 0 1 541 KRW 0 888 INSPECT 13000 50 0 200 10 WSD70-TACK FIX 200 20 WELD-CBA 13000 50 0 200 20 WELD-CBA 200 40 BLAST 13000 50 0 200 40 BLAST 200 50 PAINT 13000 50 0 200 50 PAINT 1 10 92
In case you are wondering, the execution plan for the above SQL statement looks like this:
Plan hash value: 1263351280
-----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 9 |00:00:00.01 | 43 | | | |
| 1 | SORT ORDER BY | | 1 | 9 | 9 |00:00:00.01 | 43 | 2048 | 2048 | 2048 (0)|
| 2 | NESTED LOOPS OUTER | | 1 | 9 | 9 |00:00:00.01 | 43 | | | |
| 3 | NESTED LOOPS OUTER | | 1 | 9 | 9 |00:00:00.01 | 23 | | | |
| 4 | VIEW | | 1 | 9 | 9 |00:00:00.01 | 9 | | | |
| 5 | WINDOW BUFFER | | 1 | 9 | 9 |00:00:00.01 | 9 | 2048 | 2048 | 2048 (0)|
| 6 | TABLE ACCESS BY INDEX ROWID| OPERATION | 1 | 9 | 9 |00:00:00.01 | 9 | | | |
|* 7 | INDEX RANGE SCAN | SYS_C0021734 | 1 | 9 | 9 |00:00:00.01 | 3 | | | |
| 8 | TABLE ACCESS BY INDEX ROWID | REQUIREMENT | 9 | 1 | 7 |00:00:00.01 | 14 | | | |
|* 9 | INDEX RANGE SCAN | X_REQUIREMENT_5 | 9 | 1 | 7 |00:00:00.01 | 13 | | | |
| 10 | TABLE ACCESS BY INDEX ROWID | OPERATION | 9 | 1 | 7 |00:00:00.01 | 20 | | | |
|* 11 | INDEX UNIQUE SCAN | SYS_C0021734 | 9 | 1 | 7 |00:00:00.01 | 13 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("O"."WORKORDER_TYPE"='W' AND "O"."WORKORDER_BASE_ID"='13000' AND "O"."WORKORDER_LOT_ID"='50' AND
"O"."WORKORDER_SPLIT_ID"='0')
9 - access("O"."WORKORDER_TYPE"="R"."WORKORDER_TYPE" AND "O"."WORKORDER_BASE_ID"="R"."WORKORDER_BASE_ID" AND
"O"."WORKORDER_LOT_ID"="R"."WORKORDER_LOT_ID" AND "O"."WORKORDER_SPLIT_ID"="R"."WORKORDER_SPLIT_ID" AND
"O"."WORKORDER_SUB_ID"="R"."SUBORD_WO_SUB_ID")
filter("R"."SUBORD_WO_SUB_ID" IS NOT NULL)
11 - access("R"."WORKORDER_TYPE"="O2"."WORKORDER_TYPE" AND "R"."WORKORDER_BASE_ID"="O2"."WORKORDER_BASE_ID" AND
"R"."WORKORDER_LOT_ID"="O2"."WORKORDER_LOT_ID" AND "R"."WORKORDER_SPLIT_ID"="O2"."WORKORDER_SPLIT_ID" AND
"R"."WORKORDER_SUB_ID"="O2"."WORKORDER_SUB_ID" AND "R"."OPERATION_SEQ_NO"="O2"."SEQUENCE_NO")
Note
-----
- cardinality feedback used for this statement
The above takes less that 0.01 seconds to execute, in part because I was careful to preserve the leading columns in the primary key indexes’ columns when building the inline view.
But, what if the OP (as he mentioned) was running with an older version of SQL Server that did not support the LEAD analytic function? Is the OP out of luck (it could be argued that is a trick question)?
Let’s start again, this time we will self-join the OPERATION table rather than using an analytic function to determine the next operation, and just for fun we will use ANSI join syntax:
SELECT O.WORKORDER_BASE_ID, O.WORKORDER_LOT_ID, O.WORKORDER_SPLIT_ID, O.WORKORDER_SUB_ID, O.SEQUENCE_NO, MIN(O2.SEQUENCE_NO) AS NEXT_SEQUENCE_NO FROM OPERATION O LEFT OUTER JOIN OPERATION O2 ON O.WORKORDER_TYPE=O2.WORKORDER_TYPE AND O.WORKORDER_BASE_ID=O2.WORKORDER_BASE_ID AND O.WORKORDER_LOT_ID=O2.WORKORDER_LOT_ID AND O.WORKORDER_SPLIT_ID=O2.WORKORDER_SPLIT_ID AND O.WORKORDER_SUB_ID=O2.WORKORDER_SUB_ID AND O.SEQUENCE_NO < O2.SEQUENCE_NO WHERE O.WORKORDER_TYPE='W' AND O.WORKORDER_BASE_ID='13000' AND O.WORKORDER_LOT_ID='50' AND O.WORKORDER_SPLIT_ID='0' GROUP BY O.WORKORDER_BASE_ID, O.WORKORDER_LOT_ID, O.WORKORDER_SPLIT_ID, O.WORKORDER_SUB_ID, O.SEQUENCE_NO ORDER BY O.WORKORDER_BASE_ID, O.WORKORDER_LOT_ID, O.WORKORDER_SPLIT_ID, O.WORKORDER_SUB_ID, O.SEQUENCE_NO; WORKORDER_BASE_ID WOR WOR WOR SEQUENCE_NO NEXT_SEQUENCE_NO -------------------- --- --- --- ----------- ---------------- 13000 50 0 0 888 999 13000 50 0 0 999 13000 50 0 1 10 20 13000 50 0 1 20 541 13000 50 0 1 541 13000 50 0 200 10 20 13000 50 0 200 20 40 13000 50 0 200 40 50 13000 50 0 200 50
Now, we will slide the above SQL statement into an inline view and use the COALESCE function to return the first non-NULL value in a list of columns, and the Oracle only NVL2 function to return one of two column values depending on whether or not a third column contains a NULL value:
SELECT O.WORKORDER_TYPE, O.WORKORDER_BASE_ID, O.WORKORDER_LOT_ID, O.WORKORDER_SPLIT_ID, O.WORKORDER_SUB_ID, O.SEQUENCE_NO, O.RESOURCE_ID, NVL2(O.NEXT_SEQUENCE_NO,O.WORKORDER_SUB_ID,R.WORKORDER_SUB_ID) AS NEXT_SUB_ID, COALESCE(O.NEXT_SEQUENCE_NO,R.OPERATION_SEQ_NO) AS NEXT_SEQUENCE_NO FROM (SELECT O.WORKORDER_TYPE, O.WORKORDER_BASE_ID, O.WORKORDER_LOT_ID, O.WORKORDER_SPLIT_ID, O.WORKORDER_SUB_ID, O.SEQUENCE_NO, O.RESOURCE_ID, MIN(O2.SEQUENCE_NO) AS NEXT_SEQUENCE_NO FROM OPERATION O LEFT OUTER JOIN OPERATION O2 ON O.WORKORDER_TYPE=O2.WORKORDER_TYPE AND O.WORKORDER_BASE_ID=O2.WORKORDER_BASE_ID AND O.WORKORDER_LOT_ID=O2.WORKORDER_LOT_ID AND O.WORKORDER_SPLIT_ID=O2.WORKORDER_SPLIT_ID AND O.WORKORDER_SUB_ID=O2.WORKORDER_SUB_ID AND O.SEQUENCE_NO < O2.SEQUENCE_NO WHERE O.WORKORDER_TYPE='W' AND O.WORKORDER_BASE_ID='13000' AND O.WORKORDER_LOT_ID='50' AND O.WORKORDER_SPLIT_ID='0' GROUP BY O.WORKORDER_TYPE, O.WORKORDER_BASE_ID, O.WORKORDER_LOT_ID, O.WORKORDER_SPLIT_ID, O.WORKORDER_SUB_ID, O.SEQUENCE_NO, O.RESOURCE_ID) O LEFT OUTER JOIN REQUIREMENT R ON O.WORKORDER_TYPE=R.WORKORDER_TYPE AND O.WORKORDER_BASE_ID=R.WORKORDER_BASE_ID AND O.WORKORDER_LOT_ID=R.WORKORDER_LOT_ID AND O.WORKORDER_SPLIT_ID=R.WORKORDER_SPLIT_ID AND O.WORKORDER_SUB_ID=R.SUBORD_WO_SUB_ID ORDER BY O.WORKORDER_BASE_ID, O.WORKORDER_LOT_ID, O.WORKORDER_SPLIT_ID, O.WORKORDER_SUB_ID, O.SEQUENCE_NO; W WORKORDER_BASE_ID WOR WOR WOR SEQUENCE_NO RESOURCE_ID NEX NEXT_SEQUENCE_NO - -------------------- --- --- --- ----------- --------------- --- ---------------- W 13000 50 0 0 888 INSPECT 0 999 W 13000 50 0 0 999 SHIP W 13000 50 0 1 10 92 1 20 W 13000 50 0 1 20 62 1 541 W 13000 50 0 1 541 KRW 0 888 W 13000 50 0 200 10 WSD70-TACK FIX 200 20 W 13000 50 0 200 20 WELD-CBA 200 40 W 13000 50 0 200 40 BLAST 200 50 W 13000 50 0 200 50 PAINT 1 10
Almost there – we still need the resource ID of the next operation. Sliding the above into another level of inline view, we end up with the following:
SELECT O.WORKORDER_BASE_ID, O.WORKORDER_LOT_ID, O.WORKORDER_SPLIT_ID, O.WORKORDER_SUB_ID, O.RESOURCE_ID, O.NEXT_SUB_ID, O.NEXT_SEQUENCE_NO, O2.RESOURCE_ID NEXT_RESOURCE_ID FROM (SELECT O.WORKORDER_TYPE, O.WORKORDER_BASE_ID, O.WORKORDER_LOT_ID, O.WORKORDER_SPLIT_ID, O.WORKORDER_SUB_ID, O.SEQUENCE_NO, O.RESOURCE_ID, NVL2(O.NEXT_SEQUENCE_NO,O.WORKORDER_SUB_ID,R.WORKORDER_SUB_ID) AS NEXT_SUB_ID, COALESCE(O.NEXT_SEQUENCE_NO,R.OPERATION_SEQ_NO) AS NEXT_SEQUENCE_NO FROM (SELECT O.WORKORDER_TYPE, O.WORKORDER_BASE_ID, O.WORKORDER_LOT_ID, O.WORKORDER_SPLIT_ID, O.WORKORDER_SUB_ID, O.SEQUENCE_NO, O.RESOURCE_ID, MIN(O2.SEQUENCE_NO) AS NEXT_SEQUENCE_NO FROM OPERATION O LEFT OUTER JOIN OPERATION O2 ON O.WORKORDER_TYPE=O2.WORKORDER_TYPE AND O.WORKORDER_BASE_ID=O2.WORKORDER_BASE_ID AND O.WORKORDER_LOT_ID=O2.WORKORDER_LOT_ID AND O.WORKORDER_SPLIT_ID=O2.WORKORDER_SPLIT_ID AND O.WORKORDER_SUB_ID=O2.WORKORDER_SUB_ID AND O.SEQUENCE_NO < O2.SEQUENCE_NO WHERE O.WORKORDER_TYPE='W' AND O.WORKORDER_BASE_ID='13000' AND O.WORKORDER_LOT_ID='50' AND O.WORKORDER_SPLIT_ID='0' GROUP BY O.WORKORDER_TYPE, O.WORKORDER_BASE_ID, O.WORKORDER_LOT_ID, O.WORKORDER_SPLIT_ID, O.WORKORDER_SUB_ID, O.SEQUENCE_NO, O.RESOURCE_ID) O LEFT OUTER JOIN REQUIREMENT R ON O.WORKORDER_TYPE=R.WORKORDER_TYPE AND O.WORKORDER_BASE_ID=R.WORKORDER_BASE_ID AND O.WORKORDER_LOT_ID=R.WORKORDER_LOT_ID AND O.WORKORDER_SPLIT_ID=R.WORKORDER_SPLIT_ID AND O.WORKORDER_SUB_ID=R.SUBORD_WO_SUB_ID) O LEFT OUTER JOIN OPERATION O2 ON O.WORKORDER_TYPE=O2.WORKORDER_TYPE AND O.WORKORDER_BASE_ID=O2.WORKORDER_BASE_ID AND O.WORKORDER_LOT_ID=O2.WORKORDER_LOT_ID AND O.WORKORDER_SPLIT_ID=O2.WORKORDER_SPLIT_ID AND O.NEXT_SUB_ID=O2.WORKORDER_SUB_ID AND O.NEXT_SEQUENCE_NO=O2.SEQUENCE_NO ORDER BY O.WORKORDER_BASE_ID, O.WORKORDER_LOT_ID, O.WORKORDER_SPLIT_ID, O.WORKORDER_SUB_ID, O.SEQUENCE_NO; WORKORDER_BASE_ID WOR WOR WOR RESOURCE_ID NEX NEXT_SEQUENCE_NO NEXT_RESOURCE_I -------------------- --- --- --- --------------- --- ---------------- --------------- 13000 50 0 0 INSPECT 0 999 SHIP 13000 50 0 0 SHIP 13000 50 0 1 92 1 20 62 13000 50 0 1 62 1 541 KRW 13000 50 0 1 KRW 0 888 INSPECT 13000 50 0 200 WSD70-TACK FIX 200 20 WELD-CBA 13000 50 0 200 WELD-CBA 200 40 BLAST 13000 50 0 200 BLAST 200 50 PAINT 13000 50 0 200 PAINT 1 10 92
The above SQL statement has the following execution plan:
Plan hash value: 3374377097
---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 9 |00:00:00.01 | 41 | | | |
| 1 | SORT ORDER BY | | 1 | 9 | 9 |00:00:00.01 | 41 | 2048 | 2048 | 2048 (0)|
| 2 | NESTED LOOPS OUTER | | 1 | 9 | 9 |00:00:00.01 | 41 | | | |
| 3 | VIEW | | 1 | 9 | 9 |00:00:00.01 | 19 | | | |
| 4 | HASH GROUP BY | | 1 | 9 | 9 |00:00:00.01 | 19 | 781K| 781K| 1103K (0)|
|* 5 | HASH JOIN OUTER | | 1 | 9 | 13 |00:00:00.01 | 19 | 851K| 851K| 653K (0)|
|* 6 | HASH JOIN OUTER | | 1 | 9 | 9 |00:00:00.01 | 16 | 927K| 927K| 651K (0)|
| 7 | TABLE ACCESS BY INDEX ROWID| OPERATION | 1 | 9 | 9 |00:00:00.01 | 9 | | | |
|* 8 | INDEX RANGE SCAN | SYS_C0021734 | 1 | 9 | 9 |00:00:00.01 | 3 | | | |
|* 9 | TABLE ACCESS BY INDEX ROWID| REQUIREMENT | 1 | 1 | 2 |00:00:00.01 | 7 | | | |
|* 10 | INDEX RANGE SCAN | SYS_C0021842 | 1 | 1 | 18 |00:00:00.01 | 3 | | | |
|* 11 | INDEX RANGE SCAN | SYS_C0021734 | 1 | 1 | 9 |00:00:00.01 | 3 | | | |
| 12 | TABLE ACCESS BY INDEX ROWID | OPERATION | 9 | 1 | 8 |00:00:00.01 | 22 | | | |
|* 13 | INDEX UNIQUE SCAN | SYS_C0021734 | 9 | 1 | 8 |00:00:00.01 | 14 | | | |
---------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("O"."WORKORDER_SUB_ID"="O2"."WORKORDER_SUB_ID" AND "O"."WORKORDER_SPLIT_ID"="O2"."WORKORDER_SPLIT_ID" AND
"O"."WORKORDER_LOT_ID"="O2"."WORKORDER_LOT_ID" AND "O"."WORKORDER_BASE_ID"="O2"."WORKORDER_BASE_ID" AND
"O"."WORKORDER_TYPE"="O2"."WORKORDER_TYPE")
filter("O"."SEQUENCE_NO"<"O2"."SEQUENCE_NO")
6 - access("O"."WORKORDER_SUB_ID"="R"."SUBORD_WO_SUB_ID" AND "O"."WORKORDER_SPLIT_ID"="R"."WORKORDER_SPLIT_ID" AND
"O"."WORKORDER_LOT_ID"="R"."WORKORDER_LOT_ID" AND "O"."WORKORDER_BASE_ID"="R"."WORKORDER_BASE_ID" AND
"O"."WORKORDER_TYPE"="R"."WORKORDER_TYPE")
8 - access("O"."WORKORDER_TYPE"='W' AND "O"."WORKORDER_BASE_ID"='13000' AND "O"."WORKORDER_LOT_ID"='50' AND
"O"."WORKORDER_SPLIT_ID"='0')
9 - filter("R"."SUBORD_WO_SUB_ID" IS NOT NULL)
10 - access("R"."WORKORDER_TYPE"='W' AND "R"."WORKORDER_BASE_ID"='13000' AND "R"."WORKORDER_LOT_ID"='50' AND
"R"."WORKORDER_SPLIT_ID"='0')
11 - access("O2"."WORKORDER_TYPE"='W' AND "O2"."WORKORDER_BASE_ID"='13000' AND "O2"."WORKORDER_LOT_ID"='50' AND
"O2"."WORKORDER_SPLIT_ID"='0')
13 - access("O"."WORKORDER_TYPE"="O2"."WORKORDER_TYPE" AND "O"."WORKORDER_BASE_ID"="O2"."WORKORDER_BASE_ID" AND
"O"."WORKORDER_LOT_ID"="O2"."WORKORDER_LOT_ID" AND "O"."WORKORDER_SPLIT_ID"="O2"."WORKORDER_SPLIT_ID" AND
"O"."NEXT_SUB_ID"="O2"."WORKORDER_SUB_ID" AND "O"."NEXT_SEQUENCE_NO"="O2"."SEQUENCE_NO")
Note
-----
- cardinality feedback used for this statement
—
So, now that we have two different SQL statements that both solve the problem that was presented by the OP, which version of the SQL statement is the most efficient, and which would you use in production?
Charles Hooper's Oracle Notes 
Recent Comments