Solved

faster sql

Posted on 2013-12-17
14
325 Views
Last Modified: 2014-01-02
Which one of these two sqls - SQL1 and SQL2 will be faster to execute and why -

SQL1:  
UPDATE mytable1  A SET A.XC  = ( SELECT B.PXC   FROM   mytable2 B WHERE A.PRCLID=B.PRCLID );
 
SQL2:
UPDATE mytable1  A SET A.XC  = ( SELECT B.PXC   FROM   mytable2 B WHERE A.PRCLID=B.PRCLID ) where exists( select c.prclid from mytable1 c, mytable2 d where c.prclid = d.prclid);

Or will it take same time to execute?

Or can you please give me tips how to make the first sql run faster?

thanks a lot
0
Comment
Question by:at999
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 2
  • +2
14 Comments
 
LVL 29

Assisted Solution

by:MikeOM_DBA
MikeOM_DBA earned 100 total points
ID: 39724381
The statements may not produce the same results, the second one appears to be the correct one and may be the faster.
UPDATE mytable1 a
   SET a.xc      =
         (SELECT b.pxc
            FROM mytable2 b
           WHERE a.prclid = b.prclid)
 WHERE EXISTS
         (SELECT '?'
            FROM mytable1 c, mytable2 d
           WHERE c.prclid = d.prclid);

Open in new window

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39724385
First, they are not equivalent in functionality.  So comparing execution time isn't particularly meaningful.

Second,  which will be faster depends on  how many rows this sub query returns and if the prclid columns are indexed.

(select c.prclid from mytable1 c, mytable2 d where c.prclid = d.prclid)

If that query returns one row, then SQL2 will probably be faster because it'll quickly find the one row and then update it.  Whereas SQL1 ALWAYS updates every row.  If it can't find a value it updates to NULL (even if already was NULL or had a value.)

If, on the other hand, the exists subquery returns every id, then it's a waste of time because SQL2 will do the same thing as SQL1 but with extra effort.
0
 
LVL 23

Expert Comment

by:David
ID: 39724536
There are many, many, many sources here on E-E, and of course on the Net, of tutorials and examples for statement tuning -- if you want to save your points.  Start with Oracle's EXPLAIN_PLAN to learn how to compare execution "cost".  See also the comment immediately above, that the tables' indexing and up-to-date statistics are used by the Optimizer to choose the fastest execution available to it.

The Search control in the top right corner is an undervalued friend.

dvz
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:at999
ID: 39724723
sdstuber,

d o i need this where clause( the one in bold)  too ?

UPDATE mytable1  A SET A.XC  = ( SELECT B.PXC   FROM   mytable2 B WHERE A.PRCLID=B.PRCLID ) where exists( select c.prclid from mytable1 c, mytable2 d where c.prclid = d.prclid
and c.prclid=a.prclid)
;

Or i dont need - c.prclid=a.prclid
this part is the most confusing
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39724896
and c.prclid=a.prclid


doesn't really add any functionality compared to SQL1

but it does make SQL2 behave differently.
0
 

Author Comment

by:at999
ID: 39724958
how sql2 behave differently ? will it not do what i am trying to do?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39724962
however,  if you're going to add

and c.prclid=a.prclid

to SQL2

then it makes more sense to remove the copy of mytable1 from the subquery


UPDATE mytable1  A SET A.XC  = ( SELECT B.PXC   FROM   mytable2 B WHERE A.PRCLID=B.PRCLID ) where exists( select null from  mytable2 d where a.prclid = d.prclid)
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 200 total points
ID: 39724972
how will sql2 behave differently?


UPDATE mytable1  A SET A.XC  = ( SELECT B.PXC   FROM   mytable2 B WHERE A.PRCLID=B.PRCLID ) where exists( select c.prclid from mytable1 c, mytable2 d where c.prclid = d.prclid
)

Open in new window


This says update mytable1  for EVERY row as long as ANY row in mytable1 has a corresponding row in mytable2


UPDATE mytable1  A SET A.XC  = ( SELECT B.PXC   FROM   mytable2 B WHERE A.PRCLID=B.PRCLID ) where exists( select c.prclid from mytable1 c, mytable2 d where c.prclid = d.prclid
and c.prclid=a.prclid)

Open in new window


This says update mytable1 for EACH row that matches itself (which, of course, they all do) and has a corresponding row in mytable2


UPDATE mytable1  A SET A.XC  = ( SELECT B.PXC   FROM   mytable2 B WHERE A.PRCLID=B.PRCLID ) where exists( select null from  mytable2 d where a.prclid = d.prclid) 

Open in new window


This says update mytable1 for EACH row that has a corresponding row in mytable2
0
 

Author Comment

by:at999
ID: 39725009
So is this better
UPDATE mytable1  A SET A.XC  = ( SELECT B.PXC   FROM   mytable2 B WHERE A.PRCLID=B.PRCLID ) where exists( select null from  mytable2 d where a.prclid = d.prclid)

than

UPDATE mytable1  A SET A.XC  = ( SELECT B.PXC   FROM   mytable2 B WHERE A.PRCLID=B.PRCLID ) where exists( select c.prclid from mytable1 c, mytable2 d where c.prclid = d.prclid
and c.prclid=a.prclid)
;


pl guide thanks
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 39725046
First one is better, second one has redundant join.
0
 

Author Comment

by:at999
ID: 39725058
thanks so much
0
 

Author Comment

by:at999
ID: 39725061
i will shortly close the question

many thanks to all of you
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39725259
yes,

UPDATE mytable1  A SET A.XC  = ( SELECT B.PXC   FROM   mytable2 B WHERE A.PRCLID=B.PRCLID ) where exists( select null from  mytable2 d where a.prclid = d.prclid)


is better

you only have to read mytable1 once

the other version requires you to read the table twice.
0
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 200 total points
ID: 39725291
One minor point to add: I always recommend writing joins with the "known" values on the right (and I believe that Oracle's query optimizer expects this, so you may save a bit of CPU time if you write your queries this way.

So, I would say that your statement should be:

UPDATE mytable1  A SET A.XC  = ( SELECT B.PXC   FROM   mytable2 B WHERE B.PRCLID=A.PRCLID ) where exists( select null from  mytable2 d where d.prclid = a.prclid);
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to take different types of Oracle backups using RMAN.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question