Home » SQL & PL/SQL » SQL & PL/SQL » My Simple query takes 30 min to run (10)
My Simple query takes 30 min to run [message #424272] Thu, 01 October 2009 04:10 Go to next message
dheeraj007
Messages: 1
Registered: October 2009
Location: Japan
Junior Member
Hi

I have a table with three fields.

id,attr_name,attr_value.

i have an index on id and attr_name.
there are 500 million rows in the table.

where i run the follow query ti takes 30min. how can i speed it up

select * from table where attr_name ='ad_date' and attr_value ='29/09/09'

this query in my database should return no records..but it does a table access scan...which takes about 30min to return no rows.


Thanks
Re: My Simple query takes 30 min to run [message #424279 is a reply to message #424272] Thu, 01 October 2009 04:20 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
I would suggest an index on attr_name and attr_value
Re: My Simple query takes 30 min to run [message #424280 is a reply to message #424272] Thu, 01 October 2009 04:22 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm guessing that your index is in (id,attr_name), in which case it is of almost no use for any query that isn't searching by id.

Lacking an index to search the table by, it will do a full table scan - in this case, it's reading 500,000,000 rows in 30 minutes, which means it's reading about 278,000 rows a second.
That's pretty good performance in my book.

You should be able to improve the performance of this query by adding an index on (attr_name,attr_value), or (if you have a large number of values of attr_name) then you might get away with an index just on attr_name
Re: My Simple query takes 30 min to run [message #424290 is a reply to message #424272] Thu, 01 October 2009 05:11 Go to previous message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think you should read http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:10678084117056 and related links.

Regards
Michel
Previous Topic: Right Text Alignment
Next Topic: long query runing
Goto Forum:
  


Current Time: Sun Sep 25 00:59:59 CDT 2016

Total time taken to generate the page: 0.27559 seconds