尝试优化 Oracle SQL 中的*随机*查询

Question

我需要优化Oracle SQL中的一个过程，主要使用索引。这是声明：

CREATE OR REPLACE PROCEDURE DEL_OBS(cuantos number) IS
begin
FOR I IN (SELECT * FROM (SELECT * FROM observations ORDER BY DBMS_RANDOM.VALUE)WHERE ROWNUM<=cuantos)
LOOP
DELETE FROM OBSERVATIONS WHERE nplate=i.nplate AND odatetime=i.odatetime; 
END LOOP; 
end del_obs;

我的计划是创建一个与rownum相关的索引，因为它似乎是用来执行删除的。但我不知道它是否值得。此过程的问题在于它的随机性会导致大量一致的获取。谁能帮我解决这个问题？？谢谢:)

注意：我无法更改代码，只能在事后进行改进

Answer 1

使用ROWID伪列筛选列：

CREATE OR REPLACE PROCEDURE DEL_OBS(
cuantos number
)
IS
BEGIN
DELETE FROM OBSERVATIONS
WHERE ROWID IN (
SELECT rid
FROM   (
SELECT ROWID AS rid
FROM   observations
ORDER BY DBMS_RANDOM.VALUE
)
WHERE ROWNUM < cuantos
);
END del_obs;

如果表上有索引，那么它可以使用索引快速完全扫描：

SQL 小提琴

Oracle 11g R2 架构设置：

CREATE TABLE table_name ( id ) AS
SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 50000;

查询 1：无索引：

DELETE FROM table_name
WHERE ROWID IN (
SELECT rid
FROM   (
SELECT ROWID AS rid
FROM   table_name
ORDER BY DBMS_RANDOM.VALUE
)
WHERE ROWNUM <= 10000
)

执行计划：

----------------------------------------------------------------------------------------
| Id  | Operation                      | Name       | Rows  | Bytes  | Cost | Time     |
----------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT               |            |     1 |     24 |  123 | 00:00:02 |
|   1 |   DELETE                       | TABLE_NAME |       |        |      |          |
|   2 |    NESTED LOOPS                |            |     1 |     24 |  123 | 00:00:02 |
|   3 |     VIEW                       | VW_NSO_1   | 10000 | 120000 |  121 | 00:00:02 |
|   4 |      SORT UNIQUE               |            |     1 | 120000 |      |          |
| * 5 |       COUNT STOPKEY            |            |       |        |      |          |
|   6 |        VIEW                    |            | 19974 | 239688 |  121 | 00:00:02 |
| * 7 |         SORT ORDER BY STOPKEY  |            | 19974 | 239688 |  121 | 00:00:02 |
|   8 |          TABLE ACCESS FULL     | TABLE_NAME | 19974 | 239688 |   25 | 00:00:01 |
|   9 |     TABLE ACCESS BY USER ROWID | TABLE_NAME |     1 |     12 |    1 | 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 5 - filter(ROWNUM<=10000)
* 7 - filter(ROWNUM<=10000)

查询 2添加索引：

ALTER TABLE table_name ADD CONSTRAINT tn__id__pk PRIMARY KEY ( id )

查询 3使用索引：

DELETE FROM table_name
WHERE ROWID IN (
SELECT rid
FROM   (
SELECT ROWID AS rid
FROM   table_name
ORDER BY DBMS_RANDOM.VALUE
)
WHERE ROWNUM <= 10000
)

执行计划：

---------------------------------------------------------------------------------------
| Id  | Operation                      | Name       | Rows | Bytes  | Cost | Time     |
---------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT               |            |    1 |     37 |   13 | 00:00:01 |
|   1 |   DELETE                       | TABLE_NAME |      |        |      |          |
|   2 |    NESTED LOOPS                |            |    1 |     37 |   13 | 00:00:01 |
|   3 |     VIEW                       | VW_NSO_1   | 9968 | 119616 |   11 | 00:00:01 |
|   4 |      SORT UNIQUE               |            |    1 | 119616 |      |          |
| * 5 |       COUNT STOPKEY            |            |      |        |      |          |
|   6 |        VIEW                    |            | 9968 | 119616 |   11 | 00:00:01 |
| * 7 |         SORT ORDER BY STOPKEY  |            | 9968 | 119616 |   11 | 00:00:01 |
|   8 |          INDEX FAST FULL SCAN  | TN__ID__PK | 9968 | 119616 |    9 | 00:00:01 |
|   9 |     TABLE ACCESS BY USER ROWID | TABLE_NAME |    1 |     25 |    1 | 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 5 - filter(ROWNUM<=10000)
* 7 - filter(ROWNUM<=10000)

如果无法使用ROWID在单个 SQL 语句中执行此操作，则可以重写现有过程以使用完全相同的查询，但使用FORALL语句：

CREATE OR REPLACE PROCEDURE DEL_OBS(cuantos number)
IS
TYPE obs_tab IS TABLE OF observations%ROWTYPE;
begin
SELECT *
BULK COLLECT INTO obs_tab
FROM   (
SELECT * FROM observations ORDER BY DBMS_RANDOM.VALUE
)
WHERE ROWNUM<=cuantos;
FORALL i IN 1 .. obs_tab.COUNT
DELETE FROM OBSERVATIONS
WHERE  nplate    = obs_tab(i).nplate
AND    odatetime = obs_tab(i).odatetime; 
END del_obs;

Answer 2

您最终需要的是OBSERVATIONS上的索引，以允许DELETE具有索引访问权限。

CREATE INDEX cuantos ON OBSERVATIONS(nplate, odatetime);

该过程的执行将导致OBSERVATIONS表的一个FULL TABLE SCAN，每个已删除的记录一个INDEX ACCESS。

对于有限数量的已删除记录，它的行为将类似于其他答案中提出的集合DELETE;对于大量已删除的记录，经过的时间将与删除次数成线性比例。

对于非三重数量的已删除记录，您必须假定索引未完全在缓冲池中，并且需要大量光盘访问。因此，您将以每秒大约 100 个已删除行结束。

换句话说，要删除 100K 行，大约需要 1/4 小时。

要删除1M 行，您需要 2 3/4 小时。

在此比例中删除任务的第一部分时，您会看到 - 表的FULL SCAN可以忽略不计，只需几分钟。在这种情况下，获得可接受的响应时间的唯一可能性是将逻辑切换到单个DELETE语句，如其他答案中提出的那样。

此行为也称为规则：">逐行慢慢">(即循环处理工作正常，但仅对有限数量的记录(。

Answer 3

您可以使用单个delete语句执行此操作：

delete from observations o
where (o.nplate, o.odatetime) in (select nplace, odatetime
from (select o2.nplate, o2.odatetime
from observations o2
order by DBMS_RANDOM.VALUE
) o2
where rownum <= v_cuantos
);

这通常比对要删除的每一行执行多个查询更快。

Answer 4

试试这个。 在MSSQL上进行测试希望，这样它也可以在Oracle上运行。 请备注状态。

CREATE OR REPLACE PROCEDURE DEL_OBS(cuantos number) IS
begin
DELETE OBSERVATIONS  FROM OBSERVATIONS 
join (select * from  OBSERVATIONS ORDER BY VALUE ) as i on
nplate=i.nplate AND 
odatetime=i.odatetime  AND 
i.ROWNUM<=cuantos;
End DEL_OBS;

Answer 5

既然你说nplate和odatetime是observations的主键，那么我猜问题就在这里：

SELECT * FROM (
SELECT * 
FROM observations 
ORDER BY DBMS_RANDOM.VALUE)
WHERE ROWNUM<=cuantos;

没有办法阻止它执行observations的完整扫描，如果这是一个大表，再加上大量的排序。

您需要更改运行的代码。到目前为止，更改代码的最简单方法是更改源代码并重新编译它。

但是，有一些方法可以在不更改源代码的情况下更改执行的代码。 这里有两个：

(1( 使用DBMS_FGAC添加一个策略来检测您是否在此过程，如果是，则向observations表添加一个谓词，如下所示：

AND rowid IN 
( SELECT obs_sample.rowid 
FROM observations sample (0.05) obs_sample)

(2( 使用DBMS_ADVANCED_REWRITE重写查询更改：

FROM observations

。。自。。

FROM observations SAMPLE (0.05)

在重写策略中使用查询的文本应防止它影响针对observations表的其他查询。

这些都不容易(一点也不容易(，但如果你真的卡住了，值得一试。