首先， 我将替换此复杂的子查询：
Select Rownum seq_number From Dual Connect By Rownum <= (Select LPAd(9,(UTC.DATA_PRECISION - UTC.DATA_SCALE),9)From User_Tab_Columns UTCwhere UTC.Table_Name = 'Table_Name' And UTC.Column_Name = 'seq_number')
与此：
Select Rownum As seq_number From Dual Connect By Rownum <= (Select max( seq_number ) + 10 From TEMP_TABLE_NAME )
甚至是一个简单的常量：
Select Rownum As seq_number From Dual Connect By Rownum <= 1000000
坦白地说，您的子查询不适用于非常基本的情况：
create table 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_NAMEFROM User_Tab_Columns UTCWHERe UTC.Table_Name = 'TEMP_TABLE_NAME' AND UTC.Column_Name = 'SEQ_NUMBER';X DATA_PRECISION DATA_SCALE COLUMN_NAME-------- -------------- ---------- ----------- (null) (null) (null) SEQ_NUMBER
第二种情况：
create table TEMP_TABLE_NAME( seq_number NUMBER(15,0));
在这种情况下，子查询尝试生成999999999999999行，这很快导致内存不足错误
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 operation30009. 0000 - "Not enough memory for %s operation"*Cause: The memory size was not sufficient to process all the levels of thehierarchy specified by the query.*Action: In WORKAREA_SIZE_POLICY=AUTO mode, set PGA_AGGREGATE_TARGET toa reasonably larger value.Or, in WORKAREA_SIZE_POLICY=MANUAL mode, set SORT_AREA_SIZE to areasonably larger value.
其次，您的查询不是确定性的！
它在很大程度上取决于物理表结构，并且没有强加正确的use
ORDER BY子句顺序。
记住->维基百科-ORDER BY
ORDER BY 是对结果集中的行进行排序的唯一方法。 没有此子句，关系数据库系统可以按任何顺序返回行。
如果需要排序，则必须在应用程序发送的SELECt语句中提供ORDER BY。
考虑以下测试案例：
create table TEMP_TABLE_NAME as SELECT * FROM ( select rownum as seq_number , t.* from ALL_OBJECTS t cross join ( select * from dual connect by level <= 10) where rownum <= 100000)ORDER BY DBMS_RANDOM.Value;create unique index TEMP_TABLE_NAME_IDX on TEMP_TABLE_NAME(seq_Number);select count(*) from TEMP_TABLE_NAME; COUNT(*)---------- 100000DELETe FROM TEMP_TABLE_NAMEWHERe seq_number between 10000 and 10002 OR seq_number between 20000 and 20002 OR seq_number between 30000 and 30002 OR seq_number between 40000 and 40002 OR seq_number between 50000 and 50002 OR seq_number between 60000 and 60002 ;
如果索引存在，则结果为OK：
SELECt T1.* FROM ( SELECt ROWNUM seq_number FROM DUAL ConNECT BY ROWNUM <= 1000000) T1, TEMP_TABLE_NAME T2 WHERe T1.seq_number = T2.seq_number(+) AND T2.ROWID IS NULL AND ROWNUM <= 10;SEQ_NUMBER---------- 10000 10001 10002 20000 20001 20002 30000 30001 30002 40000
但是，当某天某人删除索引，或者由于某些原因优化器决定不使用该索引时，会发生什么呢？
根据定义： 如果没有ORDER BY，则关系数据库系统可以按任何顺序返回行。 我使用提示来模拟这些情况：
SELECt T1.* FROM ( SELECt ROWNUM seq_number FROM DUAL ConNECT BY ROWNUM <= 1000000) T1, TEMP_TABLE_NAME T2 WHERe T1.seq_number = T2.seq_number(+) AND T2.ROWID IS NULL AND ROWNUM <= 10;SEQ_NUMBER---------- 213856 910281 668862 412743 295487 214762 788486 346216 777734 806457
下面的查询使用
ORDER BY子句强制执行正确的顺序，并给出再生成结果，而不管是否存在正确的索引。
我正在使用推荐的ANSI SQL LEFT JOIN子句，而不是过时的
WHERe .... (+)语法。
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 ORDER BY T1.seq_number)WHERe ROWNUM <= 10
性能
检查 性能 的最简单方法是进行测试-运行查询10-100次并测量时间：
SET TIMING ON;DECLARE x NUMBER;BEGIN FOR i IN 1..10 LOOP SELECt 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 ORDER BY T1.seq_number ) WHERe ROWNUM <= 10 ); END LOOP;END;/PL/SQL procedure successfully completed.Elapsed: 00:00:11.750
10次-11.75秒，因此一个查询需要1,2秒。
下一个限制
ConNECT BY使用子查询的版本：
SET TIMING ON;DECLARE x NUMBER;BEGIN FOR i IN 1..10 LOOP SELECt sum( seq_number ) INTO x FROM (SELECt * FROM ( SELECt T1.* FROM ( SELECt ROWNUM seq_number FROM DUAL ConNECT BY ROWNUM <= (Select max( seq_number ) + 10 From TEMP_TABLE_NAME ) ) T1 LEFT JOIN TEMP_TABLE_NAME T2 ON T1.seq_number = T2.seq_number WHERe T2.ROWID IS NULL ORDER BY T1.seq_number ) WHERe ROWNUM <= 10 ); END LOOP;END;/PL/SQL procedure successfully completed.Elapsed: 00:00:00.986
好多了-仅100毫秒。
由此得出结论，该
ConNECT BY零件是最昂贵的。
另一种尝试是使用一个表，该表具有最多100万个预生成的数字序列（具体化视图的种类），而不是
ConNECT BY每次在内存中动态生成数字的子查询：
create table seq( seq_number int primary key)ORGANIZATION INDEX ;INSERT INTO seq SELECt level FROM dualConNECT BY LEVEL <= 1000000;SET TIMING ON;DECLARE x NUMBER;BEGIN FOR i IN 1..10 LOOP SELECt sum( seq_number ) INTO x FROM (SELECt * FROM ( SELECt T1.* FROM seq T1 LEFT JOIN TEMP_TABLE_NAME T2 ON T1.seq_number = T2.seq_number WHERe T2.ROWID IS NULL ORDER BY T1.seq_number ) WHERe ROWNUM <= 10 ); END LOOP;END;/PL/SQL procedure successfully completed.Elapsed: 00:00:00.398
这是最快的-仅40毫秒
，第一个1200毫秒，最后一个40毫秒-快30倍（3000％）。
