Home » Infrastructure » Windows » query takes lot of time to execute
query takes lot of time to execute [message #187166] Fri, 11 August 2006 03:24 Go to next message
vikas_miraj2003
Messages: 13
Registered: July 2006
Location: INDIA
Junior Member
hello
sir,
i have following query executed in 9i it takes a lot of time
to execute

A) SELECT a.dpm_client_id,b.short_name
FROM client_signature a, client b
WHERE trim(b.dpm_client_id) <> trim(a.dpm_client_id) AND a.dpm_client_id is not null;

and the other hand following query executed quickly

B) SELECT trim(dpm_client_id) from client_signature
minus
SELECT trim(dpm_client_id) from client
near about 7000 records are in both the tables
why A) takes so much of time pl explain

thank's in advance
Re: query takes lot of time to execute [message #187170 is a reply to message #187166] Fri, 11 August 2006 03:41 Go to previous messageGo to next message
vikas_miraj2003
Messages: 13
Registered: July 2006
Location: INDIA
Junior Member
vikas_miraj2003 wrote on Fri, 11 August 2006 13:54

hello
sir,
i have following query executed in 9i it takes a lot of time
to execute

A) SELECT a.dpm_client_id,b.short_name
FROM client_signature a, client b
WHERE trim(b.dpm_client_id) <> trim(a.dpm_client_id) AND a.dpm_client_id is not null;

and the other hand following query executed quickly

B) SELECT trim(dpm_client_id) from client_signature
minus
SELECT trim(dpm_client_id) from client
near about 7000 records are in both the tables
why A) takes so much of time pl explain

thank's in advance

Re: query takes lot of time to execute [message #187259 is a reply to message #187170] Fri, 11 August 2006 09:16 Go to previous messageGo to next message
orausern
Messages: 826
Registered: December 2005
Senior Member
because the 2 queries are not technically equal..in first you are joining two tables and also checking for not null condition whereas you are doing just a select in the second query.

decide what meets ur requirement and then choose ur query. also to see what the db actually does, see the explain plan of both the queries and compare..
Re: query takes lot of time to execute [message #187899 is a reply to message #187259] Wed, 16 August 2006 05:21 Go to previous message
vikas_miraj2003
Messages: 13
Registered: July 2006
Location: INDIA
Junior Member
sir,
thank's a lot for your reply
now i will ask that the purpose of both the queries is
the same to find out odd record out comparing two tables
when i removed not null parameter through query A)
still it returns lot of row
on the other hand b) returns exact one row which is
odd one

pl help

with regards


vikas

Previous Topic: Need more information......
Next Topic: Creating an ODBC connection
Goto Forum:
  


Current Time: Thu Mar 28 09:23:27 CDT 2024