Getting First 10 Unused Manual_sequence Numbers

I would like to find the first 10 unused manual sequence numbers from a range. Please find my query below: select X1.* From (Select Rownum seq_number From Dual Connect By Rownu

Solution 1:

First I would replace this sophisticated subquery:

Select Rownum seq_number From Dual ConnectBy Rownum <= 
         (Select LPAD(9,(UTC.DATA_PRECISION - UTC.DATA_SCALE),9) 
          From User_Tab_Columns UTC 
          where UTC.Table_Name ='Table_Name'And UTC.Column_Name ='seq_number')

with this one:

Select Rownum As seq_number From Dual 
ConnectBy Rownum <= (Selectmax( seq_number ) +10From TEMP_TABLE_NAME ) 

or even with a simple constant:

Select Rownum As seq_number From Dual Connect By Rownum <= 1000000

Your subquery frankly does not work for a very basic case:

createtable TEMP_TABLE_NAME(
  seq_number NUMBER
);

SELECT LPAD (9,(UTC.DATA_PRECISION - UTC.DATA_SCALE),9) as x , 
       UTC.DATA_PRECISION, UTC.DATA_SCALE, UTC.COLUMN_NAME
FROM User_Tab_Columns UTC
WHERE     UTC.Table_Name ='TEMP_TABLE_NAME'AND UTC.Column_Name ='SEQ_NUMBER'
;

X        DATA_PRECISION DATA_SCALE COLUMN_NAME
-------- -------------- ---------- -----------
  (null)         (null)     (null) SEQ_NUMBER

---

And a second case:

createtable TEMP_TABLE_NAME(
  seq_number NUMBER(15,0)
);

in this case the subquery tries to generate 999999999999999 rows, which quickly leads to out of memory error

SELECT count(*) FROM (
 SELECT ROWNUM seq_number
              FROM DUAL
        CONNECT BY ROWNUM <=
                      (SELECT LPAD (9,(UTC.DATA_PRECISION - UTC.DATA_SCALE),9)
                         FROM User_Tab_Columns UTC
                        WHERE     UTC.Table_Name = 'TEMP_TABLE_NAME'AND UTC.Column_Name = 'SEQ_NUMBER')
);

ORA-30009: Not enough memory for CONNECT BY operation
30009. 0000 -  "Not enough memory for %s operation"
*Cause:    The memory size was not sufficient to process all the levels of the
           hierarchy specified by the query.
*Action:   In WORKAREA_SIZE_POLICY=AUTO mode, set PGA_AGGREGATE_TARGET to
           a reasonably larger value.
           Or, in WORKAREA_SIZE_POLICY=MANUAL mode, set SORT_AREA_SIZE to a
           reasonably larger value.

---

Secondly your query is not deterministic !!! It strongly depends on a physical table structure and does not impose the correct order using ORDER BY clause. Remember ->Wikipedia - ORDER BY

ORDER BY is the only way to sort the rows in the result set. Without this clause, the relational database system may return the rows in any order. If an ordering is required, the ORDER BY must be provided in the SELECT statement sent by the application.

Consider this test case:

createtable TEMP_TABLE_NAME 
asSELECT*FROM (
    select rownum as seq_number , t.*from ALL_OBJECTS t
    crossjoin ( select*from dual connectby level <=10)
    where rownum <=100000
)
ORDERBY DBMS_RANDOM.Value;
createunique index TEMP_TABLE_NAME_IDX on TEMP_TABLE_NAME(seq_Number);

selectcount(*) from TEMP_TABLE_NAME;
  COUNT(*)
----------100000DELETEFROM TEMP_TABLE_NAME
WHERE seq_number between10000and10002OR seq_number between20000and20002OR seq_number between30000and30002OR seq_number between40000and40002OR seq_number between50000and50002OR seq_number between60000and60002
  ;

If the index exists, then the result is OK:

SELECT T1.*FROM (    SELECT ROWNUM seq_number
              FROM DUAL
        CONNECTBY ROWNUM <=1000000
) T1,
       TEMP_TABLE_NAME T2
 WHERE     T1.seq_number = T2.seq_number(+)
       AND T2.ROWID ISNULLAND ROWNUM <=10
;

SEQ_NUMBER
----------10000100011000220000200012000230000300013000240000

But what happens when some day someone deletes the index, or the optimizer for some reasons decides to not use that index ? According to the definition: Without ORDER BY, the relational database system may return the rows in any order. I simulate these cases using a hint:

SELECT/*+ NO_INDEX(T2) */ T1.*FROM (    SELECT ROWNUM seq_number
              FROM DUAL
        CONNECTBY ROWNUM <=1000000
) T1,
       TEMP_TABLE_NAME T2
 WHERE     T1.seq_number = T2.seq_number(+)
       AND T2.ROWID ISNULLAND ROWNUM <=10
;

SEQ_NUMBER
----------213856910281668862412743295487214762788486346216777734806457

The below query enforces a proper order using ORDER BY clause and gives reproductibe results regardless of the proper index exists or not. I am using the recommended ANSI SQL LEFT JOIN clause instead obsolete WHERE .... (+) syntax.

SELECT*FROM (
    SELECT/*+ NO_INDEX(T2) */ T1.*FROM (    SELECT ROWNUM seq_number
                  FROM DUAL
            CONNECTBY ROWNUM <=1000000
    ) T1 
    LEFTJOIN TEMP_TABLE_NAME T2
    ON T1.seq_number = T2.seq_number
    WHERE T2.ROWID ISNULLORDERBY T1.seq_number
)
WHERE ROWNUM <=10

---

Performance The easiest way to check the performance is to do a test - run the query 10- 100 times and measure the time:

SET TIMING ON;
DECLARE
   x NUMBER;
BEGIN
   FOR i IN1..10LOOPSELECT sum( seq_number ) INTO x
      FROM (
           SELECT  * FROM (
            SELECT T1.*
              FROM (    SELECT ROWNUM seq_number
                          FROM DUAL
                    CONNECT BY ROWNUM <= 1000000
            ) T1 
            LEFT JOIN TEMP_TABLE_NAME T2
            ON T1.seq_number = T2.seq_number
            WHERE T2.ROWID IS NULL
            ORDERBY T1.seq_number
            )
            WHERE ROWNUM <= 10
        );
    ENDLOOP;
END;
/

PL/SQL procedure successfully completed.

Elapsed:00:00:11.750

10 times - 11.75 sec, so one query takes 1,2 sec.

---

And a next version where a limit in CONNECT BY uses a subquery:

SET TIMING ON;
DECLARE
   x NUMBER;
BEGINFOR i IN1..10 LOOP
      SELECTsum( seq_number ) INTO x
      FROM (
           SELECT*FROM (
            SELECT T1.*FROM (    SELECT ROWNUM seq_number
                          FROM DUAL
                    CONNECTBY ROWNUM <= (Selectmax( seq_number ) +10From TEMP_TABLE_NAME ) 
            ) T1 
            LEFTJOIN TEMP_TABLE_NAME T2
            ON T1.seq_number = T2.seq_number
            WHERE T2.ROWID ISNULLORDERBY T1.seq_number
            )
            WHERE ROWNUM <=10
        );
    END LOOP;
END;
/
PL/SQLprocedure successfully completed.

Elapsed: 00:00:00.986

Much better - only 100 miliseconds. This lead to the conclusion, that the CONNECT BY part is the most costly.

---

Another attempt that uses a table with pre-generated sequence of numbers up to 1 mln (kind of materialized view) instead of the CONNECT BY subquery that generates numbers each time on the fly in the memory:

createtable seq(
   seq_number intprimary key
)
ORGANIZATION INDEX ;

INSERTINTO seq 
SELECT level FROM dual
CONNECTBY LEVEL <=1000000;

SET TIMING ON;
DECLARE
   x NUMBER;
BEGINFOR i IN1..10 LOOP
      SELECTsum( seq_number ) INTO x
      FROM (
           SELECT*FROM (
            SELECT T1.*FROM seq T1 
            LEFTJOIN TEMP_TABLE_NAME T2
            ON T1.seq_number = T2.seq_number
            WHERE T2.ROWID ISNULLORDERBY T1.seq_number
            )
            WHERE ROWNUM <=10
        );
    END LOOP;
END;
/

PL/SQLprocedure successfully completed.

Elapsed: 00:00:00.398

This one is the fastest - only 40 ms The first one 1200 ms, the last one 40ms - 30 times faster (3000 %).