Not in的效率之差

SQL> set timing on
SQL> select count(*) from device_side_port dsp
  2  where dsp.device_id = 501
  3  and dsp.port_id not in
  4  (
  5  select c.side_id from CONNECTOR c
  6  where c.side_id in
  7  (select s.side_id from side s
  8   where s.node_id_b = 501)
  9  )
 10  ;

  COUNT(*)
----------
     16327

已用时间:  00: 01: 33.09
SQL> select count(*) from device_side_port dsp
  2  where dsp.device_id = 501
  3  and dsp.port_id not in
  4  (
  5  select c.sideb_port_id from CONNECTOR c
  6  where c.side_id in
  7  (select s.side_id from side s
  8   where s.node_id_b = 501)
  9  )
 10  ;

  COUNT(*)
----------
      2239

已用时间:  00: 04: 07.71



SQL> select count(*) from
  2  (select * from device_side_port dsp where dsp.device_id = 501) d
  3  left join
  4  (
  5  select c.sideb_port_id from CONNECTOR c
  6  where c.side_id in
  7  (select s.side_id from side s
  8   where s.node_id_b = 501)
  9  ) spi
 10   on d.port_id = spi.sideb_port_id
 11  where spi.sideb_port_id is null;

  COUNT(*)
----------
      2239

已用时间:  00: 00: 00.65


SQL> select count(*) from
  2  (select * from device_side_port dsp where dsp.device_id = 501) d
  3  where not exists
  4  (
  5  select 1 from
  6  (select c.sideb_port_id from CONNECTOR c
  7                          where c.side_id in (select s.side_id from side s
  8                                              where s.node_id_b = 501)
  9  ) spi
 10  where d.port_id = spi.sideb_port_id
 11  );

  COUNT(*)
----------
      2239

已用时间:  00: 00: 00.76