Home » RDBMS Server » Performance Tuning » Speed of query (10g)
Speed of query [message #378405] Tue, 30 December 2008 05:24 Go to next message
maheshmhs
Messages: 93
Registered: October 2008
Location: Nepal
Member
hi

i have two tables tbl_names and tbl_all. First table has 99 distinct ename and 24000 rows. Second table has 51500.
i wrote:
SELECT 
Count (DISTINCT Ename) 
FROM tbl_names A
WHERE EXISTS
(
SELECT * FROM  tbl_all B
WHERE Upper(b.empname) LIKE '%'||Upper(SubStr(A.Ename,1,3))||'%')


It took pretty long. So i inserted
 AND ROWNUM < 52000 

So it became:
SELECT 
Count (DISTINCT Ename) 
FROM tbl_names A
WHERE EXISTS
(
SELECT * FROM  tbl_all B
WHERE Upper(b.empname) LIKE '%'||Upper(SubStr(A.Ename,1,3))||'%') AND ROWNUM < 52000


Aren't these the same?
But the second one is too fast. If the result they give is same why so difference in speed.

thnx.
Re: Speed of query [message #378412 is a reply to message #378405] Tue, 30 December 2008 05:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64108
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read Performances Tuning sticky and - OraFAQ Performance Tuning then provide the requested and usual information.

Regards
Michel
Re: Speed of query [message #378518 is a reply to message #378412] Tue, 30 December 2008 18:54 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Post EXPLAIN PLAN for both queries

Ross Leishman
Re: Speed of query [message #380398 is a reply to message #378518] Sun, 11 January 2009 18:07 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
My first guess is that you have not collected statistics on the tables.

As was suggested, post the query plans for each query.

Good luck, Kevin
Previous Topic: Performance Prob with Group and Orader by clause
Next Topic: query taking lot of time
Goto Forum:
  


Current Time: Mon Dec 05 04:51:50 CST 2016

Total time taken to generate the page: 0.12305 seconds