title: not in clause of same table takes time in oracle
body: <p>the query is like..</p>
  
  <pre><code>select deviceid,devicename,totalbytes,'Others' as protocolgroup from (
      select d.deviceid,d.name as devicename,sum(bytes) as totalbytes from tblmainallowedtraffic_5min  b,tbldevice d
      WHERE (  B.APPID IN ('C070100126-VW717U','C018600068-GEF3O6','C045701665-9B6S1A','C026100710-36NPQE','C016801597-V1Z8IM') ) 
      and proto_group not in (select proto_group from (select proto_group,sum(bytes) from
      TBLMAINALLOWEDTRAFFIC_5MIN B WHERE (  B.APPID IN ('C070100126-VW717U','C018600068-GEF3O6','C045701665-9B6S1A',
      'C026100710-36NPQE','C016801597-V1Z8IM') ) AND "5mintime" &gt;='04-APR-12 12.00.00.000000 AM' AND
      "5mintime" &lt;='04-APR-12 11.59.59.000000 PM' group by proto_group order by sum(bytes) desc ) WHERE ROWNUM &lt;=5 )
      AND B.APPID=D.APPID AND "5mintime" &gt;='04-APR-12 12.00.00.000000 AM' AND "5mintime" &lt;='04-APR-12 11.59.59.000000 PM' 
      GROUP BY D.APPID,D.DEVICEID,D.NAME)
      order by protocolgroup desc, totalbytes desc
  </code></pre>
  
  <p>in Explain plan it says that..</p>
  
  <p>3 - filter( NOT EXISTS (not feasible))</p>
  
  <p>If i change the above query with static inner query result,the output comes faster:</p>
  
  <pre><code> select deviceid,devicename,totalbytes,'Others' as protocolgroup from (
      select d.deviceid,d.name as devicename,sum(bytes) as totalbytes from tblmainallowedtraffic_5min  b,tbldevice d
      WHERE (  B.APPID IN ('C070100126-VW717U','C018600068-GEF3O6','C045701665-9B6S1A','C026100710-36NPQE','C016801597-V1Z8IM') ) 
      and proto_group not in ('POP3','HTTP Request Response','Web','GET HTTP DATA MORE THAN 976 KB','SMTP' )
      AND B.APPID=D.APPID AND "5mintime" &gt;='04-APR-12 12.00.00.000000 AM' AND "5mintime" &lt;='04-APR-12 11.59.59.000000 PM' 
      GROUP BY D.APPID,D.DEVICEID,D.NAME)
      order by protocolgroup desc, totalbytes desc
  </code></pre>
  
  <p>so not exists clause is aplied on same table creates the problem..<br>
  can you give some idea on this,,?                                                                              </p>