Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 332
  • Last Modified:

faster sql

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
at999
Asked:
at999
  • 5
  • 5
  • 2
  • +2
3 Solutions
 
MikeOM_DBACommented:
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
 
sdstuberCommented:
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
 
DavidSenior Oracle Database AdministratorCommented:
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
at999Author Commented:
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
 
sdstuberCommented:
and c.prclid=a.prclid


doesn't really add any functionality compared to SQL1

but it does make SQL2 behave differently.
0
 
at999Author Commented:
how sql2 behave differently ? will it not do what i am trying to do?
0
 
sdstuberCommented:
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
 
sdstuberCommented:
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
 
at999Author Commented:
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
 
MikeOM_DBACommented:
First one is better, second one has redundant join.
0
 
at999Author Commented:
thanks so much
0
 
at999Author Commented:
i will shortly close the question

many thanks to all of you
0
 
sdstuberCommented:
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
 
Mark GeerlingsDatabase AdministratorCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 5
  • 5
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now