0

All tables used have millions of records. c.periodseq and c.VALIDTRANSACTION are not indexed and I cannot index it as its a prod table which cannot be modified. The '<' operator is making the query very slow. Please suggest an alternative.

  SELECT V.EVENTTYPEID, C.CREDITTYPEID
  FROM CREDIT C,
       VALIDTRANSACTION V
  WHERE V.VALIDTRANSACTIONSEQ = C.VALIDTRANSACTIONSEQ
    and c.periodseq <1055;

enter image description here

jarlh
  • 42,561
  • 8
  • 45
  • 63
Divya Sam
  • 131
  • 2
  • 10
  • 3
    I can not really see how you would be able to optimize this without creating an index. – GMB Jan 10 '20 at 13:25
  • 3
    Tip of today: Always use modern, explicit `JOIN` syntax. Easier to write (without errors), easier to read (and maintain), and easier to convert to outer join if needed. – jarlh Jan 10 '20 at 13:25
  • 1
    What does `EXPLAIN PLAN` tell you about that query? – Ken White Jan 10 '20 at 13:29
  • 2
    If your picture is showing the actual plan, then it *is* using an index. The CBO decided to use a fast full scan due to the expected cardinality of the row source. If this query is a production app's query and someone picks a recent reporting period, then they will essentially be extracting millions and millions of rows. There's not much you can do to optimize a ridiculous operation. – Jeff Holt Jan 10 '20 at 13:34

0 Answers0