First我将替换这个复杂的子查询:
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 = '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_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
还有第二种情况:
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 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.
其次,您的查询不是确定性的!
它强烈依赖于物理表结构,并且不使用强加正确的顺序ORDER BY
clause.
记住->维基百科 - 排序依据 https://en.wikipedia.org/wiki/Order_by
ORDER BY是对结果集中的行进行排序的唯一方法。没有
该子句中,关系数据库系统可以返回任意行中的行
命令。如果需要订购,则必须提供 ORDER BY
应用程序发送的 SELECT 语句。
考虑这个测试用例:
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(*)
----------
100000
DELETE FROM TEMP_TABLE_NAME
WHERE 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 /*+ NO_INDEX(T2) */ 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 .... (+)
syntax.
SELECT * FROM (
SELECT /*+ NO_INDEX(T2) */ 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
部分是成本最高的。
另一种尝试是使用预先生成的数字序列高达 1 mln(某种物化视图)的表来代替CONNECT BY
每次在内存中动态生成数字的子查询:
create table seq(
seq_number int primary key
)
ORGANIZATION INDEX ;
INSERT INTO seq
SELECT level FROM dual
CONNECT 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 %)。