Home » RDBMS Server » Performance Tuning » Performance Not Improved with Hint
Performance Not Improved with Hint [message #273289] Tue, 09 October 2007 19:56 Go to next message
mchittib
Messages: 87
Registered: September 2005
Member
Hi,
Please help me with the below query. Its not using the index even when i use the hints.
  select 
      a.col1,b.col2
  from
      orders a,
      (select col1,col2 
       from lines
       where col1='Primary'
       group by col1,col2 ) b
  where 
       a.col1=b.col1;

There is a index on a.col1,b.col1,a.col2
I have used the hints to
  select /*+ index(a a_indx_1) index(b b_indx_1) */
      a.col1,b.col2
  from
      orders a,
      (select col1,col2 
       from lines
       where col1='Primary'
       group by col1,col2 ) b
  where 
       a.col1=b.col1;

Even I tried this
  select /*+ index(a a_indx_1) index(b b_indx_1) index(b b_indx_2) */
      a.col1,b.col2
  from
      orders a,
      (select col1,col2 
       from lines
       where col1='Primary'
       group by col1,col2 ) b
  where 
       a.col1=b.col1;


Somehow the index is not used on table lines when I used a group by. Lines table is fully scanned.

I searched in google but didnt know how to solve this.

Any help would be great.

Thanks.
Re: Performance Not Improved with Hint [message #273291 is a reply to message #273289] Tue, 09 October 2007 21:11 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/84315/74940/

Are statistics current on both table(s) & index(es)?

>Somehow the index is not used on table lines when I used a group by.
What is the purpose of the GROUP BY clause?
Does the result set change when it is not included?

[Updated on: Tue, 09 October 2007 21:14] by Moderator

Report message to a moderator

Re: Performance Not Improved with Hint [message #273293 is a reply to message #273291] Tue, 09 October 2007 21:53 Go to previous messageGo to next message
mchittib
Messages: 87
Registered: September 2005
Member
Infact there are some group/aggregate functions in the main select which i didnt mention.
I need to filter out the duplicates from lines.

Thanks.
Re: Performance Not Improved with Hint [message #273294 is a reply to message #273289] Tue, 09 October 2007 21:55 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Since I can't assist with code I can not see, You're On Your Own (YOYO)!
Re: Performance Not Improved with Hint [message #273298 is a reply to message #273294] Tue, 09 October 2007 22:20 Go to previous message
mchittib
Messages: 87
Registered: September 2005
Member
Thanks Dude
Previous Topic: Non Standard Block size
Next Topic: Rule for optimizing the query (merged)
Goto Forum:
  


Current Time: Sun Dec 11 00:23:40 CST 2016

Total time taken to generate the page: 0.14132 seconds