Friday, December 23, 2011

Performance Tunings tips : Inlist vs Between

There are lot of questions running around guys who do tuning . Sometimes they do suggest use of temporary tables instead of using large in list values, as the optimizer would go for value in XXX or Value in XXY or Value in XXZ to generate a explain plain.

What if the column compared against large in-list values was part of any index say PI,SI,JI.... ?  Sometimes it so happens that even after using a temp table with list of values you would still get same performance issue, why so? Did you ever consider to use  "between" clause to check if the query performed better ??  Did you...

I would say give it a try to see if this would be much better option compared to standard "temp table " against the in list

Say for example:

SELECT customer_number, customer_name
FROM customer
WHERE customer_number in (1000, 1001, 1002, 1003, 1004);
is much less efficient than:

SELECT customer_number, customer_name
FROM customer
WHERE customer_number BETWEEN 1000 and 1004

We are assuming that an index on customer_number, the Query Optimizer can locate a range of numbers much faster (using BETWEEN) than it can find a series of numbers using the IN clause.

Check for explain to compare difference,  if still the same...  refresh/collect stats on index column it would help.   If still you would find some kind of issue try to find out skewness of column  using following query and try to rectify the issue.

Sel hashamp(hashbucket(hashrow(customer_number))), count(*) from Customer  group by 1;

Over all i would say trying the query with between would be of great help.  But query tuning is such a complex thing you will never get what you want unless you understand the data ;-)


  1. Thanks for the post

  2. Hi,

    Yes Vinay...I agree with the same time...if you make customer_number as ppi in that case...performance will boost for the between query as well.

    Pinal Patel

  3. I had more complex SQL in which IN clause is used for the dates and that dates were coming from sys_calendar.calendar. It was giving me NO more spool space error but when I used BETWEEN clause instead of IN as date was consecutive, this error removed and got the result. But it is still a mistery for me, how No more spool space error was highlighted.