Qry runs slow

Database

Qry runs slow

Hi!

I have been trying to run this query but it runs for 2 hours but still does not complete. Collect stats also does not help much.
I could not understand this part- and (coalesce(null, NULL ) is null) ??
Also, anyting that can help speed it up? Any help is appreciated.

select
PERSON.X_PIN,A.INTGRN_ID,A.X_CON_ID,A.ACTUAL_START_DT,A.CRTR_LOGIN,A.X_FUNCTION_ROLE,B.INTRACTN_CTGRY1,B.INTRACTN_TYPE1,
B.INTRACTN_CTGRY2,B.INTRACTN_TYPE2,A.COMMENTS_LONG,B.X_CHANNEL,STATUS.VAL,A.ACTUAL_END_DT,A.ACCNT,ORG.X_ALIAS_NAME
from
actvty_f a,
lov_d stat,
act_prdct_d b,
prsn_d_cust prsn,
org_dx org
where
a.stat = stat.rowid
and a.rowid = b.actvty
and a.cntct = prsn.rowid
and a.accnt = org.rowid
and a.CRTD_DT between 20110601 and 20110601
and a.DATASRC_NUM_ID=1
and (coalesce(null, NULL ) is null) OR A.CRTR_LOGIN IS NULL
and a.X_REC_TYPE='Intractn'
and a.INTGRN_ID='1A-B'

1) First, we lock a distinct ."pseudo table" for read on a
RowHash to prevent global deadlock for .prsn.
2) Next, we lock a distinct ."pseudo table" for read on a
RowHash to prevent global deadlock for .b.
3) We lock a distinct ."pseudo table" for read on a RowHash to
prevent global deadlock for .org.
4) We lock a distinct ."pseudo table" for read on a RowHash to
prevent global deadlock for .stat.
5) We lock a distinct ."pseudo table" for read on a RowHash to
prevent global deadlock for .a.
6) We lock .prsn for read, we lock .b for read, we lock
.org for read, we lock .stat for read, and we lock
.a for read.
7) We execute the following steps in parallel.
1) We do an all-AMPs RETRIEVE step from .a by way of an
all-rows scan with a condition of ("((.a.CRTR_LOGIN
IS NULL) OR (( CASE WHEN (NOT (NULL IS NULL )) THEN (NULL)
ELSE (NULL) END )IS NULL )) AND ((((.a.CRTD_DT >=
20110601) AND ((.a.CRTD_DT <= 20110601) AND
(.a.DATASRC_NUM_ID = 1 ))) OR (.a.CRTR_LOGIN
IS NULL )) AND (((.a.X_REC_TYPE = 'Intractn') OR
(( CASE WHEN (NOT (NULL IS NULL )) THEN (NULL) ELSE (NULL)
END )IS NULL )) AND ((((.a.CRTD_DT >= 20110601)
AND ((.a.CRTD_DT <= 20110601) AND
(.a.DATASRC_NUM_ID = 1 ))) OR (.a.X_REC_TYPE
= 'Intractn')) AND (((.a.INTGRN_ID = '1A-B')
OR (( CASE WHEN (NOT (NULL IS NULL )) THEN (NULL) ELSE (NULL)
END )IS NULL )) AND (((.a.CRTD_DT >= 20110601)
AND ((.a.CRTD_DT <= 20110601) AND
(.a.DATASRC_NUM_ID = 1 ))) OR (.a.INTGRN_ID
= '1A-B'))))))") into Spool 2 (all_amps) (compressed
columns allowed), which is built locally on the AMPs. The
input table will not be cached in memory, but it is eligible
for synchronized scanning. The size of Spool 2 is estimated
with low confidence to be 68,433 rows. The estimated time
for this step is 1 minute and 35 seconds.
2) We do an all-AMPs RETRIEVE step from .stat by way of
an all-rows scan with no residual conditions into Spool 3
(all_amps) (compressed columns allowed), which is duplicated
on all AMPs. The size of Spool 3 is estimated with high
confidence to be 15,002,550 rows. The estimated time for
this step is 0.35 seconds.
3) We do an all-AMPs RETRIEVE step from .prsn by way of
an all-rows scan with no residual conditions into Spool 4
(all_amps) (compressed columns allowed), which is duplicated
on all AMPs. The result spool file will not be cached in
memory. The size of Spool 4 is estimated with high
confidence to be 1,298,469,900 rows. The estimated time for
this step is 42.98 seconds.
8) We execute the following steps in parallel.
1) We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of
an all-rows scan, which is joined to Spool 3 (Last Use) by
way of an all-rows scan. Spool 2 and Spool 3 are joined
using a prdct join, with a join condition of (
"((CRTR_LOGIN IS NULL) OR (STATUS = ROW )) AND
(((X_REC_TYPE = 'Intractn') OR (STATUS = ROW ))
AND ((INTGRN_ID = '1A-B') OR (STATUS = ROW
)))"). The result goes into Spool 5 (all_amps) (compressed
columns allowed), which is built locally on the AMPs. The
result spool file will not be cached in memory. The size of
Spool 5 is estimated with no confidence to be 26,164,149 rows.
The estimated time for this step is 45.29 seconds.
2) We do an all-AMPs RETRIEVE step from .b by way of an
all-rows scan with no residual conditions into Spool 6
(all_amps) (compressed columns allowed), which is duplicated
e
but it is eligible for synchronized scanning. The result
spool file will not be cached in memory. The size of Spool 6
is estimated with high confidence to be 5,878,040,400 rows.
The estimated time for this step is 7 minutes and 30 seconds.
9) We execute the following steps in parallel.
1) We do an all-AMPs JOIN step from Spool 4 (Last Use) by way of
an all-rows scan, which is joined to Spool 5 (Last Use) by
way of an all-rows scan. Spool 4 and Spool 5 are joined
using a prdct join, with a join condition of (
"((CRTR_LOGIN IS NULL) OR (CONTACT = ROW )) AND
(((X_REC_TYPE = 'Intractn') OR (CONTACT = ROW
)) AND ((INTGRN_ID = '1A-B') OR (CONTACT =
ROW )))"). The result goes into Spool 7 (all_amps)
(compressed columns allowed), which is built locally on the
AMPs. The result spool file will not be cached in memory.
The size of Spool 7 is estimated with no confidence to be
76,979,826,422 rows. The estimated time for this step is 363
hours and 19 minutes.
2) We do an all-AMPs RETRIEVE step from .org by way of an
all-rows scan with no residual conditions into Spool 8
(all_amps) (compressed columns allowed), which is duplicated
on all AMPs. The size of Spool 8 is estimated with high
confidence to be 69,131,550 rows. The estimated time for
this step is 2.94 seconds.
10) We do an all-AMPs JOIN step from Spool 6 (Last Use) by way of an
all-rows scan, which is joined to Spool 7 (Last Use) by way of an
all-rows scan. Spool 6 and Spool 7 are joined using a prdct
join, with a join condition of ("((INTGRN_ID = '1A-B') OR
(ROW = ACTIVITY )) AND (((X_REC_TYPE = 'Intractn')
OR (ROW = ACTIVITY )) AND ((CRTR_LOGIN IS NULL) OR
(ROW = ACTIVITY )))"). The result goes into Spool 9
(all_amps) (compressed columns allowed), which is built locally on
the AMPs. The result spool file will not be cached in memory.
The size of Spool 9 is estimated with no confidence to be
481,889,630,748,264 rows. The estimated time for this step is
4,822,255 hours and 51 minutes.
11) We do an all-AMPs JOIN step from Spool 8 (Last Use) by way of an
all-rows scan, which is joined to Spool 9 (Last Use) by way of an
all-rows scan. Spool 8 and Spool 9 are joined using a prdct
join, with a join condition of ("((INTGRN_ID = '1A-B') OR
(ACCNT = ROW )) AND (((X_REC_TYPE = 'Intractn') OR
(ACCNT = ROW )) AND ((CRTR_LOGIN IS NULL) OR (ACCNT
= ROW )))"). The result goes into Spool 1 (group_amps), which
is built locally on the AMPs. The result spool file will not be
cached in memory. The size of Spool 1 is estimated with no
confidence to be *** rows. The estimated time for this step is
377,613,597 hours and 27 minutes.
12) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 1 are sent back to the user as the result of
statement 1. The total estimated time is 382,436,216 hours and 46
minutes.
BEGIN RECOMMENDED STATS ->
13) "COLLECT STATISTICS .actvty_f COLUMN CRTR_LOGIN".
(HighConf)
<- END RECOMMENDED STATS

1 REPLY
N/A

Re: Qry runs slow

2 hours?
You just have to wait another 382,436,214 hours :-)

Just remove this part, its only confusing the optimizer:
"(coalesce(null, NULL ) is null) OR "

And whenever you mix AND and OR you should add parenthesis to you and help the reader to understand the logic.
There's a precedence of evaluation for logical operators:
1. NOT
2. AND
3. OR

This was your original condition, which is probably wrong:
(
a.stat = stat.rowid
and a.rowid = b.actvty
and a.cntct = prsn.rowid
and a.accnt = org.rowid
and a.CRTD_DT between 20110601 and 20110601
and a.DATASRC_NUM_ID=1
and (coalesce(null, NULL ) is null)
)
OR
(
A.CRTR_LOGIN IS NULL
and a.X_REC_TYPE='Intractn'
and a.INTGRN_ID='1A-B'
)

Dieter