need desperate help!

Database
Enthusiast

need desperate help!

I have this simple qry which runs for long .The product join takes a long time. Its just that the stats are not collected on the base table upon which the view is built.But I saw that it is being joined on the indices.What could be done here to reduce the skew and optimze ? Also why does the reccomended stats asks to collect stats on indices, since they shd be alrdy distributed properly.

SELECT clt_id
FROM orgn_d a , View1 b
WHERE clt_id IN ( '3725' , '50462' , '8588' , '1877' )
AND b.row = a.cnt

1) First, we lock a for access, we lock
PRSN_D_CST for access, and we lock
IND for access.
2) Next, we execute the following steps in parallel.
1) We do an all-AMPs RETRIEVE step from PRSN_D_CST
by way of an all-rows scan with a condition of (
"(PRSN_D_CST.DECEASE_FLG <> 'Y') AND (((( CASE
WHEN ((POSITION(('CNSLDTD') IN (TRIM(BOTH ' ' FROM
(PRSN_D_CST.FST_NAME ||'
')||PRSN_D_CST.LAST_NAME ))))> 0) THEN ('Y')
ELSE ('N') END ))<> 'Y') AND
(PRSN_D_CST.MKT_SEG_CD <> 't'))") into Spool 1
(all_amps) (compressed columns allowed), which is built
locally on the AMPs. The size of Spool 1 is estimated with
no confidence to be 6,302,784 rows. The estimated time for
this step is 3.91 seconds.
2) We do an all-AMPs RETRIEVE step from a by way of an
all-rows scan with a condition of ("(a.clt_id =
'3725') OR ((a.clt_id = '50462') OR
((a.clt_id = '8588') OR (a.clt_id =
'1877')))") into Spool 3 (all_amps) (compressed columns
allowed), which is duplicated on all AMPs. The size of Spool
3 is estimated with no confidence to be 150 rows. The
estimated time for this step is 0.01 seconds.
3) We do an all-AMPs JOIN step from Spool 3 (Last Use) by way of an
all-rows scan, which is joined to Spool 1 (Last Use) by way of an
all-rows scan. Spool 3 and Spool 1 are joined using a product
join, with a join condition of ("(ROW (DECIMAL(10,0)))=
cnt"). The result goes into Spool 4 (all_amps)
(compressed columns allowed), which is redistributed by hash code
to all AMPs. Then we do a SORT to order Spool 4 by row hash. The
size of Spool 4 is estimated with no confidence to be 2,511 rows.
The estimated time for this step is 0.12 seconds.
4) We do an all-AMPs JOIN step from Spool 4 (Last Use) by way of a
RowHash match scan, which is joined to IND by
way of a RowHash match scan with a condition of (
"IND.GUID = 'IND_DFT_GLBL_AUD'").
Spool 4 and IND are joined using a merge join,
with a join condition of ("ROW =
IND.TGT_LVL_ID"). The result goes into
Spool 2 (group_amps), which is built locally on the AMPs. The
size of Spool 2 is estimated with no confidence to be 9,714,375
rows. The estimated time for this step is 0.46 seconds.
5) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 2 are sent back to the user as the result of
statement 1. The total estimated time is 4.49 seconds.
BEGIN RECOMMENDED STATS ->
6) "COLLECT STATISTICS PRSN_D_CST COLUMN MKT_SEG_CD".
(HighConf)
7) "COLLECT STATISTICS PRSN_D_CST COLUMN DECEASE_FLG".
(HighConf)
8) "COLLECT STATISTICS IND COLUMN GUID".
(HighConf)
9) "COLLECT STATISTICS IND COLUMN (TGT_LVL_ID)".
(HighConf)
10) "COLLECT STATISTICS orgn_d COLUMN CLT_ID".
(HighConf)
11) "COLLECT STATISTICS orgn_d COLUMN (CNT)".
(HighConf)
<- END RECOMMENDED STATS

PRIMARY INDEX ORGN_D_PI ( cnt ,accnt ,pol_no );
cnt DECIMAL(10,0),

CREATE VIEW VIEW1 AS
SELECT CAST(ROW AS DECIMAL(10,0)) AS ROW, CNT,
CLUSTER, PRIM_ACCOUNT, PR_POSTN
FROM (
SELECT ROW, CNT, CLUSTER, PRIM_ACCOUNT,
PR_POSTN,(FST_NAME||' '||LAST_NAME) AS FULL_NAME,
(
CASE WHEN (POSITION('CNSLDTD' IN TRIM(BOTH ' ' FROM FULL_NAME))>0)
THEN 'Y'
ELSE 'N'
END ) AS SUPPRESS_NAME_CNSLDTD
FROM PRSN_D_CST
WHERE (MKT_SEG_CD NE 't')
AND (SUPPRESS_NAME_CNSLDTD NE 'Y')
AND (DECEASE_FLG NE 'Y')
) A
INNER JOIN
(
SELECT TGT_LVL_ID
FROM IND
WHERE GUID='IND_DFT_GLBL_AUD'
) B
ON ROW=TGT_LVL_ID;

Thx!!!!
2 REPLIES
Enthusiast

Re: need desperate help!

Have you collected the recommended statistics? Odds are that the data demographics are not being calculated properly which is leading to inefficient plans. There's also a lot of derived tables being created that don't need to be there. I don't see anything in your view definition that would require derived tables. These can often lead to unnecessary redistribution operations which introduces potential for skewing.

Without seeing the DDL of the underlying tables, it's hard to know what else to do.

Start by collecting statistics and then see what the explain plan looks like.
Enthusiast

Re: need desperate help!

I collctd stats on th underlying tabls of th view but it did not hlp. Would you pleas let me know which derivd tabls ar you talking about?