各个表的数据量：
sys_file_convert_queue 65989
sys_att_file 73061
sys_att_main 84405
sys_att_rtf_data 1507
优化前，执行时间大概2分多钟
SQL> set autotrace traceonly SQL> delete from sys_file_convert_queue 2 where ((fd_file_id is not null or fd_file_id <> '') and 3 fd_file_id not in (select fd_id from sys_att_file)) 4 or fd_attmain_id not in 5 (select fd_id 6 from sys_att_main 7 union all 8 select fd_id from sys_att_rtf_data); 0 rows deleted. Execution Plan ---------------------------------------------------------- Plan hash value: 3784236352 -------------------------------------------------------------------------------- --------------- | Id | Operation | Name | Rows | Bytes | Cost (%C PU)| Time | -------------------------------------------------------------------------------- --------------- | 0 | DELETE STATEMENT | | 64436 | 9879K| 125K (1)| 00:25:11 | | 1 | DELETE | SYS_FILE_CONVERT_QUEUE | | | | | |* 2 | FILTER | | | | | | | 3 | TABLE ACCESS FULL | SYS_FILE_CONVERT_QUEUE | 64437 | 9879K| 869 (1)| 00:00:11 | | 4 | UNION-ALL | | | | | | |* 5 | INDEX UNIQUE SCAN| SYS_C0015191 | 1 | 33 | 1 (0)| 00:00:01 | |* 6 | INDEX UNIQUE SCAN| SYS_C0015192 | 1 | 33 | 1 (0)| 00:00:01 | |* 7 | INDEX FULL SCAN | SYS_C0014984 | 1 | 33 | 405 (1)| 00:00:05 | -------------------------------------------------------------------------------- --------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter( NOT EXISTS ( (SELECT "FD_ID" FROM "SYS_ATT_MAIN" "SYS_ATT_MAIN" W HERE "FD_ID"=:B1) UNION ALL (SELECT "FD_ID" FROM "SYS_ATT_RTF_DATA" "S YS_ATT_RTF_DATA" WHERE "FD_ID"=:B2)) OR ("FD_FILE_ID" IS NOT NULL OR "FD_FILE_ID"<> '') AND NOT EXISTS (SELECT 0 FROM "SYS_ATT_FILE" "SYS_ATT_FILE" WHERE LNNVL("FD_ID"<> :B3))) 5 - access("FD_ID"=:B1) 6 - access("FD_ID"=:B1) 7 - filter(LNNVL("FD_ID"<>:B1)) Statistics ---------------------------------------------------------- 94 recursive calls 0 db block gets 14521723 consistent gets 960 physical reads 116 redo size 832 bytes sent via SQL*Net to client 1052 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 22 sorts (memory) 0 sorts (disk) 0 rows processed
优化后，执行时间2秒不到
SQL> delete from sys_file_convert_queue a 2 where not exists 3 (select fd_id from sys_att_file b where a.fd_file_id = b.fd_id) 4 or not exists (select 1 5 from (select fd_id 6 from sys_att_main 7 union all 8 select fd_id from sys_att_rtf_data) c 9 where a.fd_attmain_id = c.fd_id) 10 ; 0 rows deleted. Execution Plan ---------------------------------------------------------- Plan hash value: 831590816 -------------------------------------------------------------------------------- ---------------- | Id | Operation | Name | Rows | Bytes | Cost (% CPU)| Time | -------------------------------------------------------------------------------- ---------------- | 0 | DELETE STATEMENT | | 64436 | 9879K| 63392 (1)| 00:12:41 | | 1 | DELETE | SYS_FILE_CONVERT_QUEUE | | | | | |* 2 | FILTER | | | | | | | 3 | TABLE ACCESS FULL | SYS_FILE_CONVERT_QUEUE | 64437 | 9879K| 868 (1)| 00:00:11 | |* 4 | INDEX UNIQUE SCAN | SYS_C0014984 | 1 | 33 | 1 (0)| 00:00:01 | | 5 | VIEW | | 2 | 148 | 2 (0)| 00:00:01 | | 6 | UNION-ALL | | | | | | |* 7 | INDEX UNIQUE SCAN| SYS_C0015191 | 1 | 33 | 1 (0)| 00:00:01 | |* 8 | INDEX UNIQUE SCAN| SYS_C0015192 | 1 | 33 | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------- ---------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter( NOT EXISTS (SELECT 0 FROM "SYS_ATT_FILE" "B" WHERE "B"."FD_ID"=:B 1) OR NOT EXISTS (SELECT 0 FROM ( (SELECT "FD_ID" "FD_ID" FROM "SYS_ATT _MAIN" "SYS_ATT_MAIN" WHERE "FD_ID"=:B2) UNION ALL (SELECT "FD_ID" "FD_ID" FROM "SYS_AT T_RTF_DATA" "SYS_ATT_RTF_DATA" WHERE "FD_ID"=:B3)) "C")) 4 - access("B"."FD_ID"=:B1) 7 - access("FD_ID"=:B1) 8 - access("FD_ID"=:B1) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 243938 consistent gets 0 physical reads 0 redo size 837 bytes sent via SQL*Net to client 1099 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 0 rows processed