optimizer using wrong index

Hi, I have seen if index is on two columns of a table one used in where clause and other used in order by clause some time its noticed that optimizer going to use index of that column which used in order by clause . Is there any way to priorities where clause column index ? Thanks, Khurram Shahzad

You can use an optimizer directive to suggest an index for the optimizer to use. The optimizer is free to ignore your directive if it finds that it doesn't make sense, but will usually follow your recommendation. Note that IDS normally finds that sorting is so fast that it will most often choose the index needed for filtering or joining, in this case the index you think it should be using. If the optimizer is selecting the index that matches the ORDER BY conditions most likely this is the fastest way to perform the query. That said, it can't hurt to at least use a directive to test the alternative yourself and time both. BTW, if you have OPT_GOAL set to FIRST_ROWS the optimizer will tend to avoid sorting so that it can return the first few rows as quickly as possible even if that makes the complete query run a bit slower. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Jul 9, 2010 at 7:55 AM, KHURRAM SHAHZAD <kshahzad02@i2cinc.com>wrote: > Hi, > > I have seen if index is on two columns of a table one used in where clause > and > other used in order by clause some time its noticed that optimizer going to > use index of that column which used in order by clause . > > Is there any way to priorities where clause column index ? > > Thanks, > Khurram Shahzad > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --000e0cd6ef2e8945a6048af39102