When you say the query is called 10k times in 10 mins, is the 10k different
business process calls calling it or are you doing silly row-by-row calls
of the query multiple times in one business process?
Think about how many partitions your table has, how many different
partitions will need to be hit for your 100 values? Are you really just
asking every execution of the SQL to read the entire table partition by
partition, sometimes multiple times?
If this has been written as a slow-by-slow process then that’s an easy fix.
Include the complete list of values required in the in list or do it as a
join from wherever these values are being generated. Doing one full scan of
the entire table and then applying the filter (either as a filter or as a
hash join type condition) will probably be considerably faster that
repeatedly scanning the same partitions.
If this is legitimately calling the statement this many times then what’s
really wrong with having the supporting indexes? How selective is your
filter column within the partition? Is it worth increasing the number of
hash partitions?
I strongly suggest you copy and paste the full execution plans (including
the predicates section) rather than a brief comment about them.
Andrew
