Home » SQL & PL/SQL » SQL & PL/SQL » update query
update query [message #343877] Thu, 28 August 2008 06:55 Go to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
When i execute the folowing query it displays the result vary fastly.

SELECT COUNT(1)
FROM test a
WHERE EXISTS
          (
		  SELECT b.address1
             FROM test b
            WHERE b.database_id = 438554
              AND b.email = a.email
              AND b.firstname = a.firstname)
AND a.database_id = 436334;


At the same time when i do update it takes so much time?

Please point out where i am wrong.

UPDATE test a
  SET a.address1 =
         (SELECT b.address1
            FROM test b
           WHERE b.database_id = 438554
             AND b.email = a.email
             AND b.firstname = a.firstname)  
WHERE a.database_id = 436334;

Re: update query [message #343890 is a reply to message #343877] Thu, 28 August 2008 07:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Counting is far much faster than writing.
Just make yourself the experience. Pick up all the person at your office: first count them all, then restart and write their address.

Regards
Michel
Re: update query [message #343913 is a reply to message #343877] Thu, 28 August 2008 07:46 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
May I know why count(1) instead of count(*) ? I am curious.

Regards

Raj
Re: update query [message #343917 is a reply to message #343913] Thu, 28 August 2008 07:57 Go to previous messageGo to next message
dwarak.k
Messages: 61
Registered: June 2008
Location: Hyderabad
Member
may be OP did not want to count the nulls in the column at position 1 . Not sure though Razz
Re: update query [message #343921 is a reply to message #343913] Thu, 28 August 2008 08:02 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

@Raj,

This (count (*) and count(1)) has been discussed in this forum in a very informative manner.
Just search it...

Regards,
Dipali.
Re: update query [message #343925 is a reply to message #343921] Thu, 28 August 2008 08:05 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

It's here:

http://www.orafaq.com/forum/m/322251/102855#msg_321254

Regards,
Dipali..

[Updated on: Thu, 28 August 2008 08:07]

Report message to a moderator

Re: update query [message #343936 is a reply to message #343925] Thu, 28 August 2008 08:36 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I know. That's the main reason I have asked OP why it's been used.

Regards

Raj
Re: update query [message #343941 is a reply to message #343936] Thu, 28 August 2008 08:38 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
There was a widely held, erroneous belief some releases back that COUNT(!) was quicker than COUNT(*)
Re: update query [message #343950 is a reply to message #343941] Thu, 28 August 2008 08:53 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
You too missed my point Smile

[Updated on: Thu, 28 August 2008 08:54]

Report message to a moderator

Re: update query [message #343960 is a reply to message #343877] Thu, 28 August 2008 09:05 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Even i tried update query
With rownum<100 Still it is taking time and still running.


SELECT ID
FROM test a
WHERE EXISTS
          (
		  SELECT b.address1
             FROM test b
            WHERE b.database_id = 438554
              AND b.email = a.email
              AND b.firstname = a.firstname)
AND a.database_id = 436334
AND ROWNUM<100;;


It is also faster.
Then why the update is not working?

explain plan is
Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop

SELECT STATEMENT Optimizer Mode=ALL_ROWS		1  	 	7263  	 	      	             	 
  HASH JOIN RIGHT SEMI		1  	75  	7263  	 	      	             	 
    TABLE ACCESS BY INDEX ROWID	TESTDB.		74 K	2 M	3451  	 	      	             	 
      INDEX RANGE SCAN	TESTDB.test_DATABASE_FK_I	107 K	 	357  	 	      	             	 
    TABLE ACCESS BY INDEX ROWID	TESTDB.test	74 K	2 M	3460  	 	      	             	 
      INDEX RANGE SCAN	TESTDB.test_DATABASE_FK_I	107 K	 	358  	 	      	             	 



Please let me know for any other informaiton is required.

[Updated on: Thu, 28 August 2008 09:08]

Report message to a moderator

Re: update query [message #343974 is a reply to message #343960] Thu, 28 August 2008 09:20 Go to previous message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Can you please provide the test case (DDL/INSERT script used for
the tables).It will really help to understand the structure data.


May be helpful for you:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6407993912330
http://www.orafaq.com/forum/t/122980/125380/




Regards,
Oli

[Updated on: Thu, 28 August 2008 09:32]

Report message to a moderator

Previous Topic: Simultaneous Access of Database
Next Topic: query to get nulll rows in between existing rows [merged]
Goto Forum:
  


Current Time: Sat Dec 10 22:33:56 CST 2016

Total time taken to generate the page: 0.07263 seconds