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: 65143
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: 3727
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: 2102
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: Fri Aug 18 13:31:16 CDT 2017

Total time taken to generate the page: 0.08298 seconds