| Speed of query [message #378405] |
Tue, 30 December 2008 05:24  |
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
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 #380398 is a reply to message #378518] |
Sun, 11 January 2009 18:07  |
 |
Kevin Meade
Messages: 2103
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
