Re: EXISTS and OR statement

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 5 Oct 2006 11:50:08 -0700
Message-ID: <1160074208.200437.94250@m7g2000cwm.googlegroups.com>

Brian Peasland wrote:
> > SELECT count(distinct b.EML_ADDR) FROM table 1a, table 2 b, table 3 d
> > WHERE exists(select cust_key from table 4 e where e.SLS_CHNL_TYP_CD =
> > 'O' and brnd_key = 1 and TRUNC(e.ins_dt) >= TRUNC(SYSDATE) - 30 and
> > e.cust_key =d.cust_key)
> > or exists (select cust_key from cust_cr_crd_acct_t e where crd_typ_cd =
> > 'BR' and TRUNC(e.ins_dt) >= TRUNC(SYSDATE) - 30 and d.cust_key =
> > e.cust_key)
> > or d.EML_ADDR_KEY in ( select eml_addr_key from src_cust_eml_t where
> > EML_OPT_OUT_IND = 'N' and TRUNC(ins_dt) >= TRUNC(SYSDATE) - 30)
> > or exists ( select eml_addr_key from src_cust_eml_t f where
> > EML_OPT_OUT_IND = 'N' and TRUNC(ins_dt) >= TRUNC(SYSDATE) - 30 and
> > d.EML_ADDR_KEY = f.EML_ADDR_KEY)
> > and a.BR_EMAIL_ADDRESS = b.EML_ADDR
> > and b.EML_ADDR_KEY = d.EML_ADDR_KEY
> >
> > is this correct or is there a better way of writing it?
>
> Personally, I use joins instead of EXISTS whenever possible. Have you
> considered rewriting this as a series of joins:
>
> SELECT count(distinct b.EML_ADDR)
> FROM table 1a, table 2 b, table 3 d, table 4 e, src_cust_eml_t f
> WHERE a.BR_EMAIL_ADDRESS = b.EML_ADDR
> and b.EML_ADDR_KEY = d.EML_ADDR_KEY
> and e.cust_key =d.cust_key
> and TRUNC(e.ins_dt) >= TRUNC(SYSDATE) - 30
> and f.EML_OPT_OUT_IND = 'N' and TRUNC(f.ins_dt) >= TRUNC(SYSDATE) - 30
> and ( (e.SLS_CHNL_TYP_CD='O' and brnd_key) = 1 OR (crd_typ_cd='BR')
> OR d.EML_ADDR_KEY=f.eml_addr_key
> OR (d.EML_ADDR_KEY=f.eml_addr_key and d.EML_ADDR_KEY=f.EML_ADDR_KEY))
>
>
> Something like that....
>
> I'm not sure I followed the logic of your query to a tee. That is
> because some of it does not make any sense to me. In your query, you
> have the following:
>
> > or d.EML_ADDR_KEY in ( select eml_addr_key from src_cust_eml_t where
> > EML_OPT_OUT_IND = 'N' and TRUNC(ins_dt) >= TRUNC(SYSDATE) - 30)
> > or exists ( select eml_addr_key from src_cust_eml_t f where
> > EML_OPT_OUT_IND = 'N' and TRUNC(ins_dt) >= TRUNC(SYSDATE) - 30 and
> > d.EML_ADDR_KEY = f.EML_ADDR_KEY)
>
> The part that consfuese me iw that you have EML_OPT_OUT_IND='N' and
> TRUNC on the dates in both. Yet one has d.EML_ADDR_KEY = f.EML_ADDR_KEY
> and the other uses IN. Aren't those the same things?
>
> Cheers,
> Brian

This is more or less what Brian is likely suggesting:

SELECT
  COUNT(DISTINCT B.EML_ADDR)
FROM
  DBSCRUB.BR_MASTER_SUPPRESSION A,
  EML_ADDR_T B,
  BRND_CUST_EML_T D,
  (SELECT
    CUST_KEY,
    COUNT(*)
  FROM
    (SELECT
      CUST_KEY
    FROM
      SLS_TXN_T E
    WHERE

      E.SLS_CHNL_TYP_CD = 'O'
      AND BRND_KEY = 1
      AND E.INS_DT >= TRUNC(SYSDATE) - 30

    UNION ALL
    SELECT
      CUST_KEY
    FROM
      CUST_CR_CRD_ACCT_T F
    WHERE

      CRD_TYP_CD = 'BR'
      AND F.INS_DT >= TRUNC(SYSDATE) - 30

    UNION ALL
    SELECT
      EML_ADDR_KEY CUST_KEY
    FROM
      SRC_CUST_EML_T G
    WHERE

      EML_OPT_OUT_IND = 'N'
      AND INS_DT >= TRUNC(SYSDATE) - 30)

  GROUP BY
    CUST_KEY) CK
WHERE
  A.BR_EMAIL_ADDRESS = B.EML_ADDR
  AND B.EML_ADDR_KEY = D.EML_ADDR_KEY
  AND (
    D.CUST_KEY=CK.CUST_KEY
    OR D.EML_ADDR_KEY=CK.CUST_KEY); Note that I removed the TRUNC() around the INS_DT columns, since this is unnecessary with you already performing a TRUNC(SYSDATE), and will potentially allow an index to be used on the INS_DT column if one exists. What I have done here is to roll up the subqueries into an inline view that only needs to be evaluated once, rather than having three subqueries that need to be evaluated once per row. Note that this logic is not tested.

It would be interesting to see how the explain plan for my query compares with that of the original query.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Thu Oct 05 2006 - 13:50:08 CDT