Re: Inmemory table on Exadata and indexes

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

--
http://www.freelists.org/webpage/oracle-l