Re: Which statement would be most efficient?

[George Jansen <GJANSEN@AFLCIO.ORG>]

The efficiency is going to depend on a number of things, including the
selectivity of the indexes and how the data is ordered physically. It
should be possible for you to see what access paths your database engine
is using--with Oracle you'd run EXPLAIN PLAN or set AUTOTRACE on.

>>> cconley@CDG-INC.COM 04/26/02 12:38PM >>>
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