title: How to reduce the execution time of the following query in Oracle?
body: <p>I'd written the following SQL query in Oracle 11g.</p>
  
  <pre><code>SELECT p.matchcode      pmatchcode,
         p1.matchcode     p1matchcode,
         p.digits         digit,
         p1.effectivedate peff,
         p1.expirydate    pexp,
         p.expirydate     p1exp,
         p.tariff_id      tariff_id
    FROM tt_matchcodes_view p1
    JOIN tt_matchcodes_view p
      on p.tariff_id = p1.tariff_id
     AND p.type_id = p1.type_id
     AND p1.Digits = p.Digits
     AND p.matchcode &lt;&gt; p1.matchcode
     AND p1.EffectiveDate &lt; p.expirydate
     AND (p1.expirydate IS NULL OR p1.expirydate &gt; p.expirydate)
     AND substr(p.matchcode, 0, length(p1.matchcode)) = p1.matchcode;
  </code></pre>
  
  <p>The <code>tt_matchcodes_view</code> table has 71392 records. I've TWO indexes created on that table on the fields <code>matchcode and digits</code>. It is taking more than 10 minutes to execute. Is there anyway to reduce the execution time.</p>
  
  <p>Sample Table DATA:</p>
  
  <pre><code>MATCHCODE   DIGITS  DEST_ID MATCH   EFFECTIVEDATE   EXPIRYDATE  INHERITED   TARIFF_ID   TYPE_ID
  1787    1787    73999   1   01/03/2012      0   22  1
  1787201 1787    73999   0   01/03/2012      -1  22  1
  1787202 1787    73999   0   01/03/2012      -1  22  1
  1787203 1787    73999   0   01/03/2012      -1  22  1
  1787204 1787    73999   0   01/03/2012      -1  22  1
  1787205 1787    73999   0   01/03/2012      -1  22  1
  1787206 1787    73999   0   01/03/2012      -1  22  1
  1787207 1787    73999   0   01/03/2012      -1  22  1
  1787208 1787    73999   0   01/03/2012      -1  22  1
  1787212 1787    73999   0   01/03/2012      -1  22  1
  </code></pre>
  
  <p>Execution PLAN:</p>
  
  <pre><code>OPERATION   OPTIONS OBJECT_NAME OBJECT_INSTANCE OPTIMIZER   ID  PARENT_ID   DEPTH   POSITION    COST    CARDINALITY BYTES   CPU_COST    IO_COST
  SELECT STATEMENT                ALL_ROWS    0       0   703 703 3   501 83322403    698
  HASH JOIN                   1   0   1   1   703 3   501 83322403    698
  TABLE ACCESS    FULL    TT_MATCHCODES_VIEW  2       2   1   2   1   95  65498   5174342 22711001    94
  TABLE ACCESS    FULL    TT_MATCHCODES_VIEW  1       3   1   2   2   95  65498   5763824 22711001    94
  </code></pre>
  
  <p>Thx in advance.</p>