title: Performance issue view with DENSE_RANK() in oracle
body: <p>We have a requirement to get the records from the below tables with mentioned matching condition &amp; limit the count of UNIQUE COL3 to 20K.</p>
  
  <p>We have achieved this by  using <code>DENSE_RANK()</code> logic but we observed extremely slowness in performance when we have implemented in live system.</p>
  
  <p>For processing 20K UNIQUE COL3 &amp; 60K total records, it is taking 14-15 hours to complete (The process is running over a daily JOB &amp; select FROM view &amp; performing DML operation to few tables based on View records).</p>
  
  <pre><code>SELECT COUNT (distinct COL3) CNT1,COUNT(1) CNT2 FROM VW_ENTP;
  --20,000                                                  60,000
  </code></pre>
  
  <p><strong>Without</strong> the <code>DENSE_RAN()</code> logic it is finishing very fast but with <code>DENSE_RAN()</code> it extremely slow.</p>
  
  <p>Request your help to share any recommendation to IMPROVE QUERY PERFORMANCE or any alternative approach. Thanks in advance !</p>
  
  <h3>Sample View Code</h3>
  
  <pre><code>CREATE OR REPLACE VIEW VW_ENTP(COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10) AS
  SELECT
  COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10
   FROM
  (
  SELECT
             NP.NFCD COL1,
             D.CLO2 COL2,
             NP.COL3 COL3,
             E.COL4 COL4,
             EDT.COL5,
             EDT.COL6,
             NP. COL7,
             NP. COL8,
             EDT.COL9 COL9,
             NP.COL10,
             DENSE_RANK() OVER (ORDER BY NP.COL3) RANK
    FROM ENP NP,EDTS EDT,EM E,EDT D,BDT BD
  WHERE EDT.COL2=D.COL2
    AND E.COL3=NP.COL3
    AND E.COL7=NP.COL7
    AND E.COL8=NP.COL8
    AND E.APP=NP.APP
    AND NP.STATUS='P'
    AND NP.NFCD=D.COL1
    AND E.SUSP !='YES'
    AND NP.APP='EDOC'
    AND TRUNC(NP.RC_DATE)&lt;=TRUNC(BD.LAST_RUN_DATE)
  )
    WHERE RANK&lt;=20000;
  </code></pre>
  
  <p>Note : The above used columns are indexed.</p>