• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 330
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Featured Post

Technology Partners: 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!

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