1

issue summary

  • I need to use my client table to lookup name, address, etc
  • my client table has 2 million rows, but only 167,000 unique clients, so I need to find the most current "max" entry for each
  • most current entry is easy to find using a group by with max that gives me the "PK" for the rows I want ("PK" is max date & ID)
  • I concatenate date || ID and use an IN to make sure I get the correct row from my subquery
  • this runs slow since I have 167,000 values for my IN statement

issue screen print enter image description here

oracle version session Metadata (getDatabaseProductVersion): Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

Dmitry Cheremushkin
  • 1,653
  • 10
  • 31
Paul Ehrsam
  • 81
  • 1
  • 6

1 Answers1

1

issue solution - exact same results, but much faster execution enter image description here

Dmitry Cheremushkin
  • 1,653
  • 10
  • 31
Paul Ehrsam
  • 81
  • 1
  • 6
  • 2
    Maybe you should consider posting the answer here and then you can mark this question as answered. – Chris Kenst Jun 19 '15 at 16:35
  • Instead of creating a table called temp (as a tester your often don't have the authorization for) try the with clause. With this you create a temporary in-memory database. You don't need table-creation authorization for that.

    https://oracle-base.com/articles/misc/with-clause

     – Patrick Oct 24 '17 at 06:31