Re: EXISTS and OR statement

On Oct 5, 10:04 am, "val" <minnie_ngu..._at_yahoo.com> 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

Without an explain plan being posted you are asking everyone to guess at the problem. Please run an explain plan on the query.

If you are running out of temp while executing the query it is likely that the query plan is using either a sort/merge join or a hash join where the join table is too large to fit into memory. You should consider if the temp tablespace is really too small while you consider how the query should be solved.

You may want to consider if there is anyway to combine the two subquries that return the cust_key into only one query then you could do an IN clause on it or perhaps join the result (inline view) to the other 3 tables.

HTH -- Mark D Powell -- Received on Thu Oct 05 2006 - 10:31:07 CDT