ORACLE数据库SQL优化 not in 与not exits

2017-10-18 来源: GalenGao 发布在  http://www.cnblogs.com/galengao/p/7685345.html

各个表的数据量:

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 where ((fd_file_id is not null or fd_file_id <> '') and fd_file_id not in (select fd_id from sys_att_file)) or fd_attmain_id not in (select fd_id from sys_att_main union all select fd_id from sys_att_rtf_data); rows deleted. Execution Plan ---------------------------------------------------------- Plan hash value: -------------------------------------------------------------------------------- --------------- | Id | Operation | Name | Rows | Bytes | Cost (%C PU)| Time | -------------------------------------------------------------------------------- --------------- | | DELETE STATEMENT | | | 9879K| 125K ()| :: | | | DELETE | SYS_FILE_CONVERT_QUEUE | | | | | |* | FILTER | | | | | | | | TABLE ACCESS FULL | SYS_FILE_CONVERT_QUEUE | | 9879K| ()| :: | | | UNION-ALL | | | | | | |* | INDEX UNIQUE SCAN| SYS_C0015191 | | | ()| :: | |* | INDEX UNIQUE SCAN| SYS_C0015192 | | | ()| :: | |* | INDEX FULL SCAN | SYS_C0014984 | | | ()| :: | -------------------------------------------------------------------------------- --------------- Predicate Information (identified by operation id): --------------------------------------------------- - 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 FROM "SYS_ATT_FILE" "SYS_ATT_FILE" WHERE LNNVL("FD_ID"<> :B3))) - access("FD_ID"=:B1) - access("FD_ID"=:B1) - filter(LNNVL("FD_ID"<>:B1)) Statistics ---------------------------------------------------------- recursive calls db block gets consistent gets physical reads redo size bytes sent via SQL*Net to client bytes received via SQL*Net from client SQL*Net roundtrips to/from client sorts (memory) sorts (disk) rows processed
优化后,执行时间2秒不到
SQL> delete from sys_file_convert_queue a where not exists (select fd_id from sys_att_file b where a.fd_file_id = b.fd_id) or not exists ( from (select fd_id from sys_att_main union all select fd_id from sys_att_rtf_data) c where a.fd_attmain_id = c.fd_id) ; rows deleted. Execution Plan ---------------------------------------------------------- Plan hash value: -------------------------------------------------------------------------------- ---------------- | Id | Operation | Name | Rows | Bytes | Cost (% CPU)| Time | -------------------------------------------------------------------------------- ---------------- | | DELETE STATEMENT | | | 9879K| ()| :: | | | DELETE | SYS_FILE_CONVERT_QUEUE | | | | | |* | FILTER | | | | | | | | TABLE ACCESS FULL | SYS_FILE_CONVERT_QUEUE | | 9879K| ()| :: | |* | INDEX UNIQUE SCAN | SYS_C0014984 | | | ()| :: | | | VIEW | | | | ()| :: | | | UNION-ALL | | | | | | |* | INDEX UNIQUE SCAN| SYS_C0015191 | | | ()| :: | |* | INDEX UNIQUE SCAN| SYS_C0015192 | | | ()| :: | -------------------------------------------------------------------------------- ---------------- Predicate Information (identified by operation id): --------------------------------------------------- - filter( NOT EXISTS (SELECT FROM "SYS_ATT_FILE" "B" WHERE "B"."FD_ID"=:B ) OR NOT EXISTS (SELECT 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")) - access("B"."FD_ID"=:B1) - access("FD_ID"=:B1) - access("FD_ID"=:B1) Statistics ---------------------------------------------------------- recursive calls db block gets consistent gets physical reads redo size bytes sent via SQL*Net to client bytes received via SQL*Net from client SQL*Net roundtrips to/from client sorts (memory) sorts (disk) rows processed

相关文章