WITH /*+ MONITOR */ bb
     AS (  SELECT node_id
             FROM krm.r_prodtree_badd
            WHERE reptree_id = 1 AND node_id BETWEEN 5000 AND 7999
         ORDER BY node_id),
     bb1 AS (SELECT ROWNUM + 4999 rn, bb.* FROM bb),
     tt_1
     AS (  SELECT bb1.*
             FROM bb1
            WHERE rn <> node_id
         ORDER BY rn),
     tt_2 AS (SELECT ROWNUM rr, tt_1.* FROM tt_1),
     ss
     AS (SELECT rn newnode, (SELECT MAX (node_id) mx FROM bb) mx, 7999 cap
           FROM tt_2
          WHERE rr = 1)
SELECT CASE
          WHEN newnode IS NOT NULL THEN newnode
          WHEN mx = cap THEN 9999
          WHEN mx < cap THEN mx + 1
          WHEN newnode IS NULL THEN 5000
       END
          newnode
  FROM ss;

Plan hash value: 3898179686
 
----------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                        | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                             |        |       |     7 (100)|          |       |       |          |
|   1 |  SORT AGGREGATE                          |                             |      1 |    13 |            |          |       |       |          |
|   2 |   VIEW                                   |                             |     33 |   429 |     2   (0)| 00:00:01 |       |       |          |
|   3 |    TABLE ACCESS FULL                     | SYS_TEMP_0FD9D7E3A_76EC0EEE |     33 |   132 |     2   (0)| 00:00:01 |       |       |          |
|   4 |  TEMP TABLE TRANSFORMATION               |                             |        |       |            |          |       |       |          |
|   5 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D7E3A_76EC0EEE |        |       |            |          |  1024 |  1024 |          |
|*  6 |    INDEX RANGE SCAN                      | R_PRODTREE_UIDX1_BADD       |     33 |   231 |     2   (0)| 00:00:01 |       |       |          |
|*  7 |   VIEW                                   |                             |     33 |   858 |     3  (34)| 00:00:01 |       |       |          |
|   8 |    COUNT                                 |                             |        |       |            |          |       |       |          |
|   9 |     VIEW                                 |                             |     33 |   429 |     3  (34)| 00:00:01 |       |       |          |
|  10 |      SORT ORDER BY                       |                             |     33 |   858 |     3  (34)| 00:00:01 |  2048 |  2048 | 2048  (0)|
|* 11 |       VIEW                               |                             |     33 |   858 |     2   (0)| 00:00:01 |       |       |          |
|  12 |        COUNT                             |                             |        |       |            |          |       |       |          |
|  13 |         VIEW                             |                             |     33 |   429 |     2   (0)| 00:00:01 |       |       |          |
|  14 |          TABLE ACCESS FULL               | SYS_TEMP_0FD9D7E3A_76EC0EEE |     33 |   132 |     2   (0)| 00:00:01 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------
 

WITH /*+ MONITOR_000  */ bb
     AS (  SELECT node_id
             FROM krm.r_prodtree_badd
            WHERE reptree_id = 1 AND node_id BETWEEN 5000 AND 7999
    --     ORDER BY node_id
         ),
     bb1 AS (SELECT ROWNUM + 4999 rn, bb.* FROM bb),
     tt_1
     AS (  SELECT bb1.*
             FROM bb1
            WHERE rn <> node_id
  --       ORDER BY rn
         ),
     tt_2 AS (SELECT ROWNUM rr, tt_1.* FROM tt_1),
     ss
     AS (SELECT rn newnode, (SELECT MAX (node_id) mx FROM bb) mx, 7999 cap
           FROM tt_2
          WHERE rr = 1)
SELECT CASE
          WHEN newnode IS NOT NULL THEN newnode
          WHEN mx = cap THEN 9999
          WHEN mx < cap THEN mx + 1
          WHEN newnode IS NULL THEN 5000
       END
          newnode
  FROM ss;

CREATE UNIQUE INDEX KRM.R_PRODTREE_UIDX1_BADD ON KRM.R_PRODTREE_BADD
(REPTREE_ID, NODE_ID)
LOGGING
TABLESPACE KRMT
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

WITH bb
     AS (  SELECT ((ROW_NUMBER() OVER (ORDER BY node_id)) + 4999) rn, node_id
             FROM krm.r_prodtree_badd
            WHERE reptree_id = 1 AND node_id BETWEEN 5000 AND 7999
    --     ORDER BY node_id
         ),
     bb1 AS (SELECT ROWNUM + 4999 rn1, bb.* FROM bb),
     tt_1
     AS (  SELECT bb1.*
             FROM bb1
            WHERE rn <> node_id
         ORDER BY rn1
         ),
     tt_2 AS (SELECT ROWNUM rr, tt_1.* FROM tt_1),
     ss
     AS (SELECT rn newnode, (SELECT MAX (node_id) mx FROM bb) mx, 7999 cap
           FROM tt_2
          WHERE rr = 1)
SELECT CASE
          WHEN newnode IS NOT NULL THEN newnode
          WHEN mx = cap THEN 9999
          WHEN mx < cap THEN mx + 1
          WHEN newnode IS NULL THEN 5000
       END
          newnode
  FROM ss;