帮忙优化一下语句

Question

select P.ID, P.CONNECTNUM,(select COUNT(*) num
          from connection
         where (objectid = p.id AND objecttablename = 'PORT')
            OR PORTID = p.id) c
  from port p
       
 where p.eqid in (select eq.id
                    from equipment eq
                   where typecode = 3
                     and kindcode = 0)

Answer 1

select P.ID, P.CONNECTNUM,count(distinct b.objectid) num
from port p 
left join connect b
on (p.id=b.objectid and objecttablename = 'PORT') or b.PORTID = p.id
where p.eqid in (select eq.id from equipment eq where typecode = 3 and kindcode = 0)
group by P.ID, P.CONNECTNUM

Answer 2

select P.ID,
       P.CONNECTNUM,
       (select COUNT(*) num
          from connection
         where (objectid = p.id AND objecttablename = 'PORT')
            OR PORTID = p.id) c
  from port p
WHERE EXISTS(SELECT 1 FROM equipment EQ WHERE typecode = 3
                     and kindcode = 0 AND ID=P.EQID)                     

Answer 3

    select P.ID,
           P.CONNECTNUM,
           (
              select COUNT(*) num
              from connection
              where (objectid = p.id AND objecttablename = 'PORT') OR PORTID = p.id
            )c
    from port p    
    where exists (select 1
              from equipment eq
             where typecode = 3
               and kindcode = 0
               and eq.id = p.eqid)用exists比in要快些！

Answer 4

因为里面存在函数count(*)的统计,所以要用group by 也可以用下面语句:
select P.ID, P.CONNECTNUM,count(distinct b.objectid) num 
from port p 
left join connect b 
on (p.id=b.objectid and b.objecttablename = 'PORT') or b.PORTID = p.id 
where exists(select 1 from equipment eq where id=p.eqid and typecode = 3 and kindcode = 0) 
group by P.ID, P.CONNECTNUM

Answer 5

select P.ID, P.CONNECTNUM, count(distinct b.objectid) num
  from port p
  left join connect b on (p.id = b.objectid and objecttablename = 'PORT')
                      or b.PORTID = p.id
 where  exists (select 1
                    from equipment eq
                   where typecode = 3
                     and kindcode = 0 and p.eqid=eq.id
                  )
 group by P.ID, P.CONNECTNUM order by by P.ID, P.CONNECTNUM再仔细改了下，不知道是否符合你的业务逻辑。

Answer 6

select P.ID, P.CONNECTNUM,(select COUNT(*) num
          from connection
         where (objectid = p.id AND objecttablename = 'PORT')
            OR PORTID = p.id) c
  from port p
       
 where p.eqid in (select eq.id
                    from equipment eq
                   where typecode = 3
                     and kindcode = 0)
select P.ID, P.CONNECTNUM, count(distinct b.objectid) num
  from port p
  left join connection  b on (p.id = b.objectid and objecttablename = 'PORT')
                      or b.PORTID = p.id
 where  exists (select 1
                    from equipment eq
                   where typecode = 3
                     and kindcode = 0 and p.eqid=eq.id
                  )
 group by P.ID, P.CONNECTNUM order by P.ID, P.CONNECTNUM
上面两条语句，为什么在PLSQL Developer第一条查十几条的速度只要0.094s，第二条查十几条要272.86s.下面可能查全部的数据会快~
port 大概60w条数据
connection  大概40w条数据
equipment 大概4w条数据

Answer 7

exists对于大数据量会比in快。但是小数据量特别是十几条数据的话，不一定的。不过小数据量sql再怎么烂也不会影响效率啊！还是习惯用exists好些啊！

Answer 8

上网搜了下应该是“IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况”这样的原因。

Answer 9

其实你的这种情况，EXISTS和IN都是没必要的，完全可以通过表关联来实现;这样效率会更高；select P.ID,
       P.CONNECTNUM,
       (select COUNT(*) num
          from connection
         where (objectid = p.id AND objecttablename = 'PORT')
            OR PORTID = p.id) c
  from port p inner join equipment eq on p.eqid=eq.id
  where eq.typecode=3 and eq.kindcode=0上述语句中的子查询里的OR可以考虑用UNION换掉，不过前提是objectid，PORTID上都建立有索引，LZ更具实际情况定夺。

Answer 10

第一条查20w用了160.562s
第二条查20w用了60s

Answer 11

你在7楼提出的问题：第2条语句里用了DISTINCT和ORDERBY，group，对表做了至少3次排序