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

各个表的数据量：

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

 

转载于:https://www.cnblogs.com/galengao/p/7685345.html