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 <alexander.schroeder@stripped>:
>
> 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@stripped]
> Sent: Mittwoch, 27. Juni 2007 20:30
> To: maxdb@stripped
> 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@stripped
> 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: danielti@stripped
Skype: danielti2005
ICQ : 316727989
Jabber: danielti
Linux User: 387864
AOL User: Danielti2005
Sola Scriptura, Sola Gratia, Sola Fide, Solus Christus, Soli Deo Gloria
