Re: EXISTS and OR statement

hi,

the below is the explain plan. i have actually modified the sql statment as well. i removed the IN statement. the cost is huge 145254G.

SELECT count(distinct b.EML_ADDR) FROM dbscrub.BR_MASTER_SUPPRESSION a, eml_addr_t b, brnd_cust_eml_t d
WHERE exists(select cust_key from sls_txn_t 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 f where crd_typ_cd = 'BR' and TRUNC(f.ins_dt) >= TRUNC(SYSDATE) - 30 and d.cust_key = f.cust_key)
or exists ( select eml_addr_key from src_cust_eml_t g where EML_OPT_OUT_IND = 'N' and TRUNC(ins_dt) >= TRUNC(SYSDATE) - 30 and d.EML_ADDR_KEY = g.EML_ADDR_KEY)
and a.BR_EMAIL_ADDRESS = b.EML_ADDR
and b.EML_ADDR_KEY = d.EML_ADDR_KEY

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=CHOOSE 1 145254 G

  SORT GROUP BY 1 62
    CONCATENATION

      FILTER
        HASH JOIN		391277272 G	22593132124G	7003 G	:Q533969003	 P->S
QC (RANDOM)
          HASH JOIN		3614 G	168308G	109 M	:Q533969001	 P->P 	 HASH

            INDEX FAST FULL SCAN	DBSCRUB.X1	112 K	2 M	 	:Q533969000
P->P 	 BROADCAST
            TABLE ACCESS FULL	COREMAST.EML_ADDR_T	32 M	857 M	872
	:Q533969001	 PCWP
          PARTITION LIST ALL		  	 	 	:Q533969003	 PCWP 	            1	3
            TABLE ACCESS FULL	COREMAST.BRND_CUST_EML_T	108 K	1 M	710
	:Q533969002	 P->P 	 HASH       1	3
        TABLE ACCESS BY INDEX ROWID	COREMAST.SRC_CUST_EML_T	1  	16  	7

          INDEX RANGE SCAN	COREMAST.XPKSRC_CUST_EML_T	3  	 	4

      FILTER
        MERGE JOIN CARTESIAN		391277272 G	22593132124G	7003
G	:Q533970002	 P->S 	 QC (RANDOM)
          MERGE JOIN CARTESIAN		3614 G	168308G	109 M	:Q533970001	 P->P
	 BROADCAST
            TABLE ACCESS FULL	DBSCRUB.BR_MASTER_SUPPRESSION	112 K	2
M	20  	:Q533970000	 S->P 	 BROADCAST
            BUFFER SORT		32 M	857 M	109 M	:Q533970001	 PCWP

              TABLE ACCESS FULL	COREMAST.EML_ADDR_T	32 M	857 M	872
	:Q533970001	 PCWP
          BUFFER SORT		108 K	1 M	7003 G	:Q533970002	 PCWP

            PARTITION LIST ALL		  	 	 	:Q533970002	 PCWP
1	3
              TABLE ACCESS FULL	COREMAST.BRND_CUST_EML_T	108 K	1 M	710
	:Q533970002	 PCWP 	            1	3
        TABLE ACCESS BY INDEX ROWID	COREMAST.CUST_CR_CRD_ACCT_T	1  	17
	6
          INDEX RANGE SCAN	COREMAST.XIF4CUST_CR_CRD_ACCT_T	2  	 	3

        TABLE ACCESS BY INDEX ROWID	COREMAST.SRC_CUST_EML_T	1  	16  	7

          INDEX RANGE SCAN	COREMAST.XPKSRC_CUST_EML_T	3  	 	4

      FILTER
        MERGE JOIN CARTESIAN		391277272 G	22593132124G	7003
G	:Q533971002	 P->S 	 QC (RANDOM)
          MERGE JOIN CARTESIAN		3614 G	168308G	109 M	:Q533971001	 P->P
	 BROADCAST
            TABLE ACCESS FULL	DBSCRUB.BR_MASTER_SUPPRESSION	112 K	2
M	20  	:Q533971000	 S->P 	 BROADCAST
            BUFFER SORT		32 M	857 M	109 M	:Q533971001	 PCWP

              TABLE ACCESS FULL	COREMAST.EML_ADDR_T	32 M	857 M	872
	:Q533971001	 PCWP
          BUFFER SORT		108 K	1 M	7003 G	:Q533971002	 PCWP

            PARTITION LIST ALL		  	 	 	:Q533971002	 PCWP
1	3
              TABLE ACCESS FULL	COREMAST.BRND_CUST_EML_T	108 K	1 M	710
	:Q533971002	 PCWP 	            1	3
        PARTITION RANGE ALL		  	 	 	:Q533968000	 PCWP 	            1	25
          TABLE ACCESS BY LOCAL INDEX ROWID	COREMAST.SLS_TXN_T	1  	19
	4  	:Q533968000	 P->S 	 QC (RANDOM)1	25
            INDEX RANGE SCAN	COREMAST.XIF12SLS_TXN_T	8  	 	3
	:Q533968000	 PCWP 	            1	25
        TABLE ACCESS BY INDEX ROWID	COREMAST.SRC_CUST_EML_T	1  	16  	7

          INDEX RANGE SCAN	COREMAST.XPKSRC_CUST_EML_T	3  	 	4

        TABLE ACCESS BY INDEX ROWID	COREMAST.CUST_CR_CRD_ACCT_T	1  	17
	6
          INDEX RANGE SCAN	COREMAST.XIF4CUST_CR_CRD_ACCT_T	2  	 	3

Mark D Powell wrote:
> 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:52:59 CDT