Re: Which statement would be most efficient?

[the dragon <ceprn@HOTMAIL.COM>]

In Oracle at least, using rule based optimization, exists or not exists is
usually faster (see exception) that an IN for 2 reasons: 1) the exists
returns a true condition and stops processing as soon as it occurs where as
an IN builds the values by going through the entire table to collect all the
data 2) an IN statement is an explicit OR statement - such as IN (1, 2, 3)
is the same thing as = 1 OR = 2 OR = 3, and it will end up running the
gammut for each data particle tested.

exception - when the data row that returns a true is the last row in the
table, then there is not difference.


clark 'the dragon' willis
dragon enterprises, consulting services


PSA: Salary <> Slavery.  If you earn a salary, your employer is renting your
services for 40 hours a week, not purchasing your soul.  Your time is the
only real finite asset that you have, and once used it can never be
recovered, so don't waste it by giving it away.

"Time is the coin of your life. It is the only coin you
have, and only you can determine how it will be spent.
Be careful lest you let other people spend it for you."

Carl Sandburg
(1878 - 1967)


----Original Message Follows----

Exists tend to be more efficient than IN statements.  I would run a test
with an explain plan and see if you are using the best path and can you
optimize either by changing your SQL statement to match the indexes that
exists or build a new index to run with this statement.

-----Original Message-----
From: Rick_Creel@AONCONS.COM [mailto:Rick_Creel@AONCONS.COM]
Sent: Friday, April 26, 2002 8:40 AM
To: SQR-USERS@list.iex.net
Subject: Which statement would be most efficient?

I have an update statement that seems to be taking too much time.
The current statement reads:
UPDATE PS_PROJ_RESOURCE
SET PC_DISTRIB_STATUS = 'D'
WHERE PS_PROJ_RESOURCE.BUSINESS_UNIT     = $BU
AND   PS_PROJ_RESOURCE.PC_DISTRIB_STATUS = 'N'
AND EXISTS (SELECT 1 FROM PS_PC_DIST_LN PC_DIST_LN
             WHERE  PC_DIST_LN.BUSINESS_UNIT =
PS_PROJ_RESOURCE.BUSINESS_UNIT
             AND    PC_DIST_LN.PROJECT_ID    = PS_PROJ_RESOURCE.PROJECT_ID
             AND    PC_DIST_LN.ACTIVITY_ID   = PS_PROJ_RESOURCE.ACTIVITY_ID
             AND    PC_DIST_LN.RESOURCE_ID   = PS_PROJ_RESOURCE.RESOURCE_ID)
-----------------------------------------------------------
Here is what I propose:
UPDATE PS_PROJ_RESOURCE
SET PC_DISTRIB_STATUS = 'D'
WHERE PS_PROJ_RESOURCE.BUSINESS_UNIT     = $BU
AND   PS_PROJ_RESOURCE.PC_DISTRIB_STATUS = 'N'
AND   PS_PROJ_RESOURCE.RESOURCE_ID IN
      (SELECT PC_DIST_LN.RESOURCE_ID
       FROM PS_PC_DIST_LN PC_DIST_LN
       WHERE  PC_DIST_LN.BUSINESS_UNIT = PS_PROJ_RESOURCE.BUSINESS_UNIT
       AND    PC_DIST_LN.PROJECT_ID    = PS_PROJ_RESOURCE.PROJECT_ID
       AND    PC_DIST_LN.ACTIVITY_ID   = PS_PROJ_RESOURCE.ACTIVITY_ID
       AND    PC_DIST_LN.RESOURCE_ID   = PS_PROJ_RESOURCE.RESOURCE_ID)
-----------------------------------------------------------
The unique index on both tables contain   BUSINESS_UNIT, PROJECT_ID,
ACTIVITY_ID, RESOURCE_ID
as the first 4 fields.  After that, each table's unique index has other
(different) fields as part of the index.  So that
tells me, at best, I will get partial index usage.
In addition, PS_PROJ_RESOURCE has a non-unique index with BUSINESS_UNIT and
PC_DISTRIB_STATUS as the key items.
I am wondering if using 'IN' is more efficient than using 'EXISTS'??
Anyone have comments or suggestions on this?
Many thanks!

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.351 / Virus Database: 197 - Release Date: 04/19/2002





_________________________________________________________________
Chat with friends online, try MSN Messenger: http://messenger.msn.com