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

1

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

  • 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. oracle-base.com/articles/misc/with-clause – Patrick Oct 24 '17 at 6:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.