Re: EXISTS and OR statement

hi,
i posted the explain plan above. we are on oracle version 9.2.0.1.0.

thanks!
val

DA Morgan wrote:
> val wrote:
> > hi,
> > i wrote the sql statement with the EXISTS and OR statement but it's
> > seems too be running a long time and then runs out of temp space. the
> > reason i have OR is that i want to get A RECORD if any one of the three
> > conditions is met.
> >
> > 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?
> >
> > thanks in advance!
> > val
>
> No version
> No explain plan
> No help possible.
>
> I can't say that it is the issue but anytime you are using OR you
> need to very strategically place parentheses to make sure that the
> OR condition executes properly. You may be missing a few.
> --
> Daniel Morgan
> Puget Sound Oracle Users Group
Received on Thu Oct 05 2006 - 12:12:58 CDT