Simple SQL – Finding the Next Operation

3 11 2011

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?


Actions

Information

One response

4 11 2011
Log Buffer #244, A Carnival of the Vanities for DBAs | The Pythian Blog

[...] Charles Hooper answers – how would you write a SQL statement that indicates the next operation in a manufacturing process. [...]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 148 other followers

%d bloggers like this: