I am trying to run the following query
SELECT
cdc.cus_nbr,
cdc.indiv_fkey,
MAX(
CASE
WHEN UPPER(pay.pay_typ) IN
('B','G','I','L','R','X','Y') THEN 'Y'
WHEN pay.pay_typ IN
('0','1','2','3','4','5','6','7','8','9') THEN 'P'
ELSE 'N'
END
),
MAX(
CASE UPPER(pay.pay_typ)
WHEN 'E' THEN 'Y'
ELSE 'N'
END
)
FROM
cdm.cdm_ddw_customer cdc,
cdm.cdm_ddw_cust_paytype pay
WHERE
pay.cus_nbr = cdc.cus_nbr
AND cdc.indiv_fkey IS NOT NULL
AND cdc.lst_dte >= (select start_date from cdm_epiphany.inc_date) --
'2003-11-15'::date --
AND cdc.lst_dte < (select end_date from cdm_epiphany.inc_date)
--'2006-10-16'::date --
GROUP BY
cdc.cus_nbr,
cdc.indiv_fkey
Note: The table cdm_epiphany.inc_date has only one row.
When I do the explain plan, I get the following:
HashAggregate (cost=672585.68..679836.00 rows=90629 width=21)
InitPlan
-> Seq Scan on inc_date (cost=0.00..1.01 rows=1 width=4)
-> Seq Scan on inc_date (cost=0.00..1.01 rows=1 width=4)
-> Merge Join (cost=17667.93..671646.97 rows=93669 width=21)
Merge Cond: ("outer".cus_nbr = "inner".cus_nbr)
-> Index Scan using paytyp_cust_id on cdm_ddw_cust_paytype pay
(cost=0.00..593522.69 rows=23620542 width=13)
-> Sort (cost=17667.93..17894.51 rows=90629 width=16)
Sort Key: cdc.cus_nbr
-> Index Scan using cdm_ddwcust_lstdate_idx on
cdm_ddw_customer cdc (cost=0.00..10205.68 rows=90629 width=16)
Index Cond: ((lst_dte >= $0) AND (lst_dte < $1))
Filter: (indiv_fkey IS NOT NULL)
Now, if I change the query to :
GroupAggregate (cost=4952407.62..6300386.04 rows=14506983 width=21)
-> Sort (cost=4952407.62..4989891.57 rows=14993583 width=21)
Sort Key: cdc.cus_nbr, cdc.indiv_fkey
-> Merge Join (cost=0.00..2889809.31 rows=14993583 width=21)
Merge Cond: ("outer".cus_nbr = "inner".cus_nbr)
-> Index Scan using ddwcus_pk on cdm_ddw_customer cdc
(cost=0.00..2051240.77 rows=14506983 width=16)
Filter: ((indiv_fkey IS NOT NULL) AND (lst_dte >=
'2003-11-15'::date) AND (lst_dte < '2006-10-16'::date))
-> Index Scan using paytyp_cust_id on cdm_ddw_cust_paytype
pay (cost=0.00..593522.69 rows=23620542 width=13)
Notice, the row returned from the two queries are way off. I have analyzed,
reanalyzed the table, changed the cdc.cus_nbr, cdc.indiv_fkey and
cdc.lst_dte columns statistics value to 300 and reanalyzed but I cannot get
to give me the same exact or closer row count.
The query outcome is "Out of Memory".
I would appreciate if someone can provide some guidance.
Thanks
Abu
_________________________________________________________________
Get FREE company branded e-mail accounts and business Web site from
Microsoft Office Live
http://clk.atdmt.com/MRT/go/mcrssaub0050001411mrt/direct/01/
