Home » RDBMS Server » Performance Tuning » query problem (merged 4)
query problem (merged 4) [message #393040] Fri, 20 March 2009 02:13 Go to next message
shoowaruno
Messages: 14
Registered: February 2009
Junior Member
Hi Guys,



TABLE_A with 39million rows
TABLE_B with 79 million rows
TABLE_C with 14 million rows

QUERY 1 : select balance from TABLE_B where id = (select id from TABLE_A where id = '1234567890' and bday = 092489);

QUERY 2 : select status from TABLE_C where id = (select id from TABLE_A where id = '1234567890' and bday = 092489);

Both queries have the right output, my problem is..

why query 1 gives the result faster than query 2? As you can see, query 2 (with TABLE_C in it) has less rows than TABLE_B. Query 1 shows the output in less 10 seconds.. Query 2 shows the output in 30 minutes.

THANKS!
Re: Table with million rows, query problem [message #393051 is a reply to message #393040] Fri, 20 March 2009 02:42 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Is there an index on ID in tableC?
Re: query problem (merged 4) [message #393054 is a reply to message #393040] Fri, 20 March 2009 03:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do not cross-post your question.

Regards
Michel
Re: Table with million rows, query problem [message #393370 is a reply to message #393051] Sun, 22 March 2009 04:27 Go to previous messageGo to next message
shoowaruno
Messages: 14
Registered: February 2009
Junior Member
I rechecked TABLE_C and found out it's a View. Not a table.
I know I cant create index on a view, right?


Thanks
Re: query problem (merged 4) [message #393400 is a reply to message #393040] Sun, 22 March 2009 09:48 Go to previous message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
>I know I cant create index on a view, right?
Right, but you create index on underlying table.
Previous Topic: No difference in plan and cost for IN and EXISTS
Next Topic: rownum slowing query
Goto Forum:
  


Current Time: Mon Dec 05 23:57:53 CST 2016

Total time taken to generate the page: 0.16895 seconds