RE: Space for results table exhausted

From: Schroeder, Alexander <alexander.schroeder(at)sap.com>
Date: Thu Jun 28 2007 - 05:31:14 EDT

Hello Daniel,

maybe you can post the EXPLAIN output (plus possibly the definition of the tables/views that participate in this query), so that anyone can help you. From just the query there's little to analyse.

Regards
Alexander Schröder
SAP AG Sitz der Gesellschaft/Registered Office: Walldorf, Germany Vorstand/SAP Executive Board: Henning Kagermann (Sprecher/CEO), Shai Agassi, Léo Apotheker, Werner Brandt, Claus Heinrich, Gerhard Oswald, Peter Zencke Vorsitzender des Aufsichtsrats/Chairperson of the SAP Supervisory Board: Hasso Plattner Registergericht/Commercial Register Mannheim No HRB 350269

Diese E-Mail kann Betriebs- oder Geschäftsgeheimnisse oder sonstige vertrauliche Informationen enthalten. Sollten Sie diese E-Mail irrtümlich erhalten haben, ist Ihnen eine Kenntnisnahme des Inhalts, eine Vervielfältigung oder Weitergabe der E-Mail ausdrücklich untersagt. Bitte benachrichtigen Sie uns und vernichten Sie die empfangene E-Mail. Vielen Dank.

This e-mail may contain trade secrets or privileged, undisclosed, or otherwise confidential information. If you have received this e-mail in error, you are hereby notified that any review, copying, or distribution of it is strictly prohibited. Please inform us immediately and destroy the original transmittal. Thank you for your cooperation.

-----Original Message-----
From: Daniel Castro [mailto:danielti@gmail.com] Sent: Mittwoch, 27. Juni 2007 20:30
To: maxdb@lists.mysql.com
Subject: Space for results table exhausted

Hi,

I have the folowing query:

  SELECT cnv.cnv_reg_ans , gih.gih_dias , gih.gih_senha , pac.pac_nome ,

gih.gih_numero , gih.gih_atendente , gih.gih_qtde , pac.pac_mcnv ,
pac.pac_dt_valid , pac.pac_cartao_sus , psv.psv_nome , psv.psv_uf ,
psv.psv_crm , psv.psv_cpf , emp_a.emp_cgc , gih.gih_dthr_ini ,
cfg.cfg_ind_homecare , cap.cap_pac_reg , cap.cap_num , cap.cap_cid_cod ,
cap.cap_tiss_tipo_doenca , cap.cap_tiss_tempo_doenca ,
cap.cap_tiss_escala_doenca , cap.cap_tiss_tipo_acidente ,
cap.cap_int_carater , cap.cap_int_clinc_cir , cap.cap_int_hosp ,
gih.gih_dthr_fim , gih.gih_dthr_lib , gih.gih_obs , gih.gih_qtde_solic ,

( SELECT pln.pln_cod_conv FROM pln WHERE pln.pln_cnv_cod = cap.cap_cnv_codAND
pln.pln_cod = pac.pac_pln_cod AND pln.pln_cnv_cod = pac.pac_cnv ) as plano,
( SELECT pln.pln_nome FROM pln WHERE pln.pln_cnv_cod = cap.cap_cnv_cod AND
pln.pln_cod = pac.pac_pln_cod AND pln.pln_cnv_cod = pac.pac_cnv ) as plano_nome, psv.psv_conselho , ( SELECT MIN ( d.esp_cbo_s ) FROM esp d, esm m WHERE m.esm_esp = d.esp_cod AND m.esm_default = 'S' AND m.esm_med = gih.gih_psv_solic ) as esp_medico_solic,

emp_a.emp_nome_fantasia , emp_a.emp_cnes , emp_b.emp_cgc ,
emp_b.emp_nome_fantasia , emp_b.emp_cnes ,

( SELECT o.emp_tiss_cod_oper FROM emp o WHERE o.emp_cod = cnv.cnv_emp_cod )
as emp_cod_oper,
( SELECT cid.cid_nome FROM cid WHERE cid.cid_cod = cap.cap_cid_cod ) as
cid_nome, cap.cap_obs , cnv.cnv_nome ,
( SELECT cle.cle_cod_tiss FROM cle WHERE cle_cod = gih.gih_cle_cod ) as
padrao_autorizado, cap.cap_tiss_indic_clinica ,
( SELECT min ( pdg.pdg_cid ) FROM pdg WHERE pdg.pdg_pac = gih.gih_pac_regAND
pdg.pdg_hsp = gih.gih_hsp_num AND pdg.pdg_hsp > 0 AND pdg.pdg_principal <> 'S' ) as cid_cod_2,
( SELECT max ( pdg.pdg_cid ) FROM pdg WHERE pdg.pdg_pac = gih.gih_pac_regAND
pdg.pdg_hsp = gih.gih_hsp_num AND pdg.pdg_hsp > 0 AND pdg.pdg_principal <> 'S' ) as cid_cod_3,
( SELECT min ( pdg.pdg_cid ) FROM pdg WHERE pdg.pdg_pac = gih.gih_pac_regAND
pdg.pdg_hsp = gih.gih_hsp_num AND pdg.pdg_hsp > 0 AND pdg.pdg_principal <> 'S' and pdg_cid not in ( SELECT min ( b.pdg_cid ) FROM pdg b WHERE b.pdg_pac= gih.gih_pac_reg AND b.pdg_hsp = gih.gih_hsp_num AND b.pdg_hsp > 0 AND b.pdg_principal <> 'S' ) ) as cid_cod_4
FROM pac , gih , cnv , emp emp_a , psv , cfg , cap , gcc , str , emp emp_b WHERE ( cap.cap_psv_assist = psv.psv_cod )

and ( cap.cap_cnv_cod = cnv.cnv_cod )
and ( cap.cap_pac_reg = pac.pac_reg )
and ( gih.gih_pac_reg = cap.cap_pac_reg )
and ( gih.gih_cap_num = cap.cap_num )
and ( emp_a.emp_cod = cap.cap_emp_cod_hosp )
and ( gcc.gcc_cod = cap.cap_gcc_cod )
and ( str.str_cod = gcc.gcc_str_cod )
and ( str.str_emp_cod = emp_b.emp_cod )
and ( ( cap.cap_pac_reg = 57849 )
and ( cap.cap_num = 1 )
and ( gih.gih_hsp_num_aut is null )
and ( gih.gih_num_prorrog is null )

No matter how hard I try I get the 'space for results table exhausted' error.
I have a lot of free space (2Gb) in the dabase. I tried to create a lot of indexes, but there is no big table in this query. The largest table has 80.000 rows.
I read in this article : http://lists.mysql.com/maxdb/12612 that the outer join above can be the problem.

can you help me?

Best regards,

-- 
Daniel Castro - דניאל
MSN: danielti@gmail.com
Skype: danielti2005
ICQ : 316727989
Jabber: danielti
Linux User: 387864
AOL User: Danielti2005
Sola Scriptura, Sola Gratia, Sola Fide, Solus Christus, Soli Deo Gloria

Received on Thu Jun 28 05:31:25 2007