Sorry for the mistake.
But here is the explain:
DBA PAC EQUAL CONDITION FOR KEY COLUMN 4910
PAC_REG (USED KEY COLUMN)
DBA GIH GIH_PAC_REG JOIN VIA KEY RANGE 286
DBA CNV TABLE SCAN 14
EMP_A TABLE SCAN 45
DBA PSV TABLE SCAN 1020
DBA CFG TABLE SCAN 1
DBA CAP EQUAL CONDITION FOR KEY COLUMN 1
CAP_PAC_REG (USED KEY COLUMN)
CAP_NUM (USED KEY COLUMN)
DBA GCC GCC_COD JOIN VIA KEY COLUMN 1
DBA STR STR_COD JOIN VIA KEY COLUMN 2
EMP_B EMP_COD JOIN VIA KEY COLUMN 45
INTERNAL TEMPORARY RESULT EQUAL CONDITION FOR KEY COLUMN
500
EMP_COD (USED KEY COLUMN)
INTERNAL TEMPORARY RESULT TABLE SCAN 500
DBA PLN EQUAL CONDITION FOR KEY COLUMN 45
PLN_CNV_COD (USED KEY COLUMN)
PLN_COD (USED KEY COLUMN)
DBA PLN EQUAL CONDITION FOR KEY COLUMN 45
PLN_CNV_COD (USED KEY COLUMN)
PLN_COD (USED KEY COLUMN)
INTERNAL TEMPORARY RESULT TABLE SCAN 500
INTERNAL TEMPORARY RESULT TABLE SCAN 500
O EQUAL CONDITION FOR KEY COLUMN 1
EMP_COD (USED KEY COLUMN)
DBA CID NO STRATEGY NOW (ONLY AT EXECUTION TIME)
DBA CLE EQUAL CONDITION FOR KEY COLUMN 1
CLE_COD (USED KEY COLUMN)
DBA PDG NO STRATEGY NOW (ONLY AT EXECUTION TIME)
DBA PDG NO STRATEGY NOW (ONLY AT EXECUTION TIME)
DBA PDG NO STRATEGY NOW (ONLY AT EXECUTION TIME)
INTERNAL TEMPORARY RESULT EQUAL CONDITION FOR KEY COLUMN
500
PDG_PAC (USED KEY COLUMN)
INTERNAL TEMPORARY RESULT TABLE SCAN 500
INTERNAL TEMPORARY RESULT TABLE SCAN 500
B NO STRATEGY NOW (ONLY AT EXECUTION TIME)
DBA RESULT IS COPIED , COSTVALUE IS
2007/6/28, Schroeder, Alexander <[EMAIL PROTECTED]>:
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:[EMAIL PROTECTED]
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: [EMAIL PROTECTED]
Skype: danielti2005
ICQ : 316727989
Jabber: danielti
Linux User: 387864
AOL User: Danielti2005
Sola Scriptura, Sola Gratia, Sola Fide, Solus Christus, Soli Deo Gloria
--
Daniel Castro - דניאל
MSN: [EMAIL PROTECTED]
Skype: danielti2005
ICQ : 316727989
Jabber: danielti
Linux User: 387864
AOL User: Danielti2005
Sola Scriptura, Sola Gratia, Sola Fide, Solus Christus, Soli Deo Gloria
