467,858 Members | 1,607 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,858 developers. It's quick & easy.

Using a lot of Tempspace

This sql statement dies after using more than 40GB Tempspace:

INSERT INTO dcp.cmp_mo_p_bab_kart ( currency, year,
figure, period, month_date, company, center, account,
value )
SELECT
CASE
WHEN src.currency IS null
THEN dest.currency
ELSE src.currency END AS currency ,
CASE
WHEN src.year IS null
THEN dest.year
ELSE src.year END AS year ,
CASE
WHEN src.figure IS null
THEN dest.figure
ELSE src.figure END AS figure ,
CASE
WHEN src.period IS null
THEN dest.period
ELSE src.period END AS period ,
CASE
WHEN src.month_date IS null
THEN dest.month_date
ELSE src.month_date END AS month_date ,
CASE
WHEN src.company IS null
THEN dest.company
ELSE src.company END AS company ,
CASE
WHEN src.center IS null
THEN dest.center
ELSE src.center END AS center ,
CASE
WHEN src.account IS null
THEN dest.account
ELSE src.account END AS account , COALESCE(src.value,0)
AS value
FROM dcp.t_mo_p_bab_kart src FULL OUTER JOIN (
SELECT DISTINCT d.currency AS currency , d.year AS
year , d.figure AS figure , d.period AS period ,
d.month_date AS month_date , d.company AS company
, d.center AS center , d.account AS account , d.value
AS value
FROM dcp.mo_p_bab_kart d , dcp.t_mo_p_bab_kart s
WHERE d.company = s.company and d.year = s.year and
d.period = s.period and d.month_date = s.month_date
and d.figure = s.figure and d.currency = s.currency
) dest ON src.company = dest.company AND src.center
= dest.center AND src.year = dest.year AND src.period
= dest.period AND src.month_date = dest.month_date
AND src.figure = dest.figure AND src.currency =
dest.currency AND src.account = dest.account

Table dcp.mo_p_bab_kart has 2129489 cards , dcp.t_mo_p_bab_kart 189424
cards,
Runstats ok.

Perhaps is it better to use UNION ALL or work with temporary table?

WHY?

--
Mit freundlichen Gruessen / Best regards
_______________________________
Joachim Mueller
Nov 12 '05 #1
  • viewed: 1714
Share:
2 Replies
Just a guess: Would it be semantically OK to place the DISTINCT in the
top-SELECT?
You should take a look at the plan and check for sorts and temps.

Cheers
Serge
Nov 12 '05 #2
Serge,

thank you for the hint.

Regards,
Joachim
"Serge Rielau" <sr*****@ca.eyebeem.com> schrieb im Newsbeitrag
news:bo**********@hanover.torolab.ibm.com...
Just a guess: Would it be semantically OK to place the DISTINCT in the
top-SELECT?
You should take a look at the plan and check for sorts and temps.

Cheers
Serge

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Mehul Patel | last post: by
2 posts views Thread by rawCoder | last post: by
5 posts views Thread by Enos Meroka | last post: by
8 posts views Thread by acb | last post: by
reply views Thread by apple | last post: by
reply views Thread by Eugene Anthony | last post: by
1 post views Thread by alexhguerra | last post: by
8 posts views Thread by =?Utf-8?B?Q2hyaXMgSGFsY3Jvdw==?= | last post: by
reply views Thread by jack112 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.