title: Composite index on different tables ORACLE SQL
body: <p><strong>Hi guys, I need optimize this query:</strong></p>
  
  <pre><code>  SELECT * 
      FROM 
          (SELECT r.*, ROWNUM RNUM 
          FROM (
              SELECT t0.RISK , t3.AMOUNT, t3.DATE_TIME , t0.ID
              FROM 
                  REACTION.ALERT t0, REACTION.INVESTIGATION t1, 
                  REACTION.CLASSIFICATION_TYPE t2, REACTION.TRANS t3, 
                  REACTION.FRAUD_TYPE t4, REACTION.CARD t5 
              WHERE (
                      (NOT EXISTS (SELECT 1 FROM REACTION.INVESTIGATION WHERE REACTION.INVESTIGATION.ALERT_ID = t0.ID) OR 
                      t1.CLASSIFICATION_TYPE_ID IS NULL OR t2.CLASSIFICATION_TYPE = 2) AND 
                      t0.MODULE_TYPE = 0 AND
                      t0.PROCESSING_MODE_TYPE = 1 AND 
                      t0.ISS_INST IN (1201, 1101)
                  ) AND 
                  t0.ID = t1.ALERT_ID(+) AND 
                  t0.TRANSACTION_ID = t3.ID AND
                  t1.CLASSIFICATION_TYPE_ID = t2.ID(+) AND
                  t1.FRAUD_TYPE_ID = t4.ID(+) AND
                  t3.HPAN = t5.HPAN(+) 
                  ORDER BY t0.RISK DESC, t3.AMOUNT DESC, t3.DATE_TIME DESC, t0.ID DESC
              ) r 
          WHERE ROWNUM &lt;= 120)
      WHERE RNUM &gt; 100;   
  </code></pre>
  
  <p>But How I can use indexes on ALL order by colums (<em>t0.RISK DESC, t3.AMOUNT DESC, t3.DATE_TIME DESC, t0.ID DESC</em>)?
  I tryied to create 2 indexes:</p>
  
  <pre><code>create index risk_idx on  ALERT (risk,id);
  create index amount_date_idx on  TRANS (AMOUNT,DATE_TIME);
  </code></pre>
  
  <p>But I still have FULL SCAN on TRANS and ALERT tables, but if I change sorting to  <em>ORDER BY t0.RISK DESC, t0.ID DESC</em>: <em>risk_idx</em> index works and query execute faster.
  Also I tried to set index on each of that 4 columns individualy:</p>
  
  <pre><code>create index risk_idx on  ALERT  (risk,1);
      create index amount_idx on  TRANS  (amount,1);  
      create index date_time_idx on  TRANS  (DATE_TIME,1);  
  </code></pre>
  
  <p>But thath didnt help too(
  P.S. Columns <code>ALERT.RISK, TRANS.AMOUNT , TRANS.DATE_TIME</code> has <strong>NULLABLE = true</strong>;</p>