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

Improve this question

1 Answer 1

Reset to default
1

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

Improve this answer
2
  • 2
    Maybe you should consider posting the answer here and then you can mark this question as answered.
    – Chris Kenst
    Jun 19, 2015 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, 2017 at 6:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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