Help to understand this sqexplain (now with plain)
Leandro Kohler (12.10.FC7WE) had a query on a huge, complex view that ran for hours. Eric Vercelletto asked about statistics; Art Kagel explained the view must be materialized in a temp table; Paul Watson advised splitting complex queries into temp tables. Garcia Benjamin asked for table counts and schemas, saw a sequential scan where an index existed, and advised replacing the view with an SPL. Unresolved.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hello, here I am again, this time with sqexplain in the body message. I have this query that is taking many hours to run. I made a setexplain of it but I can't understand where the problem is. I believe I should create some table indices but I don't know which tables ------------------------------ QUERY ------------------------------ SELECT eis_v_cap_despesas.cod_empresa cod_empresa, SUM(eis_v_cap_despesas.val_tot_nf) val_tot_nf,eis_v_cap_despesas.den_nivel_2 den_nivel_2,eis_v_cap_despesas.den_nivel_4 den_nivel_4,eis_v_cap_despesas.raz_social raz_social,eis_v_cap_despesas.ano_mes ano_mes, SUM(eis_v_cap_despesas.valor) valor from eis_v_cap_despesas eis_v_cap_despesas WHERE 1=1 AND ((eis_v_cap_despesas.dat_pgto >= '07/01/2022' and eis_v_cap_despesas.dat_pgto <= '07/31/2022' ) and eis_v_cap_despesas.num_conta_contabil = '8.1000.04.09') --,'8.1100.04.10','10000409','11000410' )) GROUP BY eis_v_cap_despesas.cod_empresa,eis_v_cap_despesas.den_nivel_2,eis_v_cap_despesas.den_nivel_4,eis_v_cap_despesas.raz_social,eis_v_cap_despesas.ano_mes ------------------------------ SQEXPLAIN.OUT ------------------------------ QUERY: (OPTIMIZATION TIMESTAMP: 10-27-2021 17:59:58) ------ create view "informix".eis_v_cap_despesas (origem,cod_empresa,num_ap,num_parcela,dat_emis_ap,dat_vencto_s_desc,ano_mes_vencto,dat_pgto,ano_mes,cod_fornecedor,raz_social,raz_social_reduz,num_duplicata,cod_tip_despesa,nom_tip_despesa,num_conta_contabil,num_ad,num_nf,dat_emis_nf,dat_rec_nf,observ,val_tot_nf,valor,num_conta,den_conta,nivel_1,nivel_2,nivel_3,nivel_4,nivel_5,den_nivel_1,den_nivel_2,den_nivel_3,den_nivel_4,den_nivel_5,ies_pagto) as select 'SUP' ,x0.cod_empresa ,x0.num_ap ,x0.num_parcela ,x0.dat_emis ,x0.dat_vencto_s_desc ,TO_CHAR (x0.dat_vencto_s_desc ,'%Y-%m' ),x0.dat_pgto ,TO_CHAR (x0.dat_pgto ,'%Y-%m' ),x0.cod_fornecedor ,x8.raz_social ,x8.raz_social_reduz ,x0.num_duplicata ,x3.cod_tip_despesa ,x4.nom_tip_despesa ,x7.num_conta_deb_desp ,x3.num_ad ,x3.num_nf ,x3.dat_emis_nf ,x3.dat_rec_nf ,x3.observ ,x3.val_tot_nf ,(((CASE WHEN (x2.num_adf IS NOT NULL ) THEN (((x0.val_nom_ap + eis_f_apvalores(x0.cod_empresa ,x0.num_ap ,x0.dat_proposta )) * (x3.val_tot_nf + eis_f_advalores(x0.cod_empresa ,x3.num_ad )) ) / (select (x35.val_tot_nf + eis_f_advalores(x35.cod_empresa ,x35.num_ad )) from "informix".ad_mestre x35 where ((x35.cod_empresa = x0.cod_empresa ) AND (x35.num_ad = x2.num_adf ) ) ) ) WHEN (x0.val_nom_ap <= (x3.val_tot_nf + eis_f_advalores(x0.cod_empresa ,x3.num_ad )) ) THEN (x0.val_nom_ap + eis_f_apvalores(x0.cod_empresa ,x0.num_ap ,x0.dat_proposta )) WHEN (x0.val_nom_ap > (x3.val_tot_nf + eis_f_advalores(x0.cod_empresa ,x3.num_ad )) ) THEN (NVL ((x0.val_nom_ap + eis_f_apvalores(x0.cod_empresa ,x0.num_ap ,x0.dat_proposta )) ,(x3.val_tot_nf + eis_f_advalores(x0.cod_empresa ,x3.num_ad )) )* ((x3.val_tot_nf + eis_f_advalores(x0.cod_empresa ,x3.num_ad )) / x0.val_nom_ap ) ) WHEN (x3.val_tot_nf != eis_f_get_total_ad_ap(x0.cod_empresa ,x0.num_ap ,x3.cod_tip_despesa )) THEN (NVL (x0.val_nom_ap ,(x3.val_tot_nf + eis_f_advalores(x3.cod_empresa ,x3.num_ad )) )* ((x3.val_tot_nf + eis_f_advalores(x0.cod_empresa ,x3.num_ad )) / eis_f_get_total_ad_ap(x3.cod_empresa ,x0.num_ap ,x3.cod_tip_despesa )) ) END * (x6.val_contabil_item / x3.val_tot_nf ) ) * x7.pct_particip_comp ) / 100. ) ,x9.num_conta ,x9.den_conta ,eis_f_get_conta_nivel(x9.num_conta ,1 ),eis_f_get_conta_nivel(x9.num_conta ,2 ),eis_f_get_conta_nivel(x9.num_conta ,3 ),eis_f_get_conta_nivel(x9.num_conta ,4 ),eis_f_get_conta_nivel(x9.num_conta ,5 ),eis_f_get_den_conta_nivel(x9.num_conta ,1 ,x9.cod_empresa ),eis_f_get_den_conta_nivel(x9.num_conta ,2 ,x9.cod_empresa ),eis_f_get_den_conta_nivel(x9.num_conta ,3 ,x9.cod_empresa ),eis_f_get_den_conta_nivel(x9.num_conta ,4 ,x9.cod_empresa ),eis_f_get_den_conta_nivel(x9.num_conta ,5 ,x9.cod_empresa ),CASE WHEN (x0.dat_pgto IS NULL ) THEN 'N' ELSE 'S' END from ((((((((("informix".ap x0 join "informix".ad_ap x1 on ((x1.cod_empresa = x0.cod_empresa ) AND (x1.num_ap = x0.num_ap ) ) )left join "informix".adn_adf x2 on ((x1.cod_empresa = x2.cod_empresa ) AND (x1.num_ad = x2.num_adf ) ) )join "informix".ad_mestre x3 on ((x0.cod_empresa = x3.cod_empresa ) AND (NVL (x2.num_adn ,x1.num_ad )= x3.num_ad ) ) )join "informix".tipo_despesa x4 on ((x4.cod_empresa = x3.cod_empresa ) AND (x4.cod_tip_despesa = x3.cod_tip_despesa ) ) )join "informix".nf_sup x5 on (((((x5.cod_empresa = x3.cod_empresa ) AND (x5.num_nf = to_number2(x3.num_nf )) ) AND (x5.ser_nf = x3.ser_nf ) ) AND (x5.ssr_nf = x3.ssr_nf ) ) AND (x5.cod_fornecedor = x0.cod_fornecedor ) ) )join "informix".aviso_rec x6 on ((x5.cod_empresa = x6.cod_empresa ) AND (x5.num_aviso_rec = x6.num_aviso_rec ) ) )join "informix".dest_aviso_rec x7 on (((x7.cod_empresa = x6.cod_empresa ) AND (x7.num_aviso_rec = x6.num_aviso_rec ) ) AND (x7.num_seq = x6.num_seq ) ) )join "informix".fornecedor x8 on (x8.cod_fornecedor = x0.cod_fornecedor ) )left join "informix".plano_contas x9 on ((x9.cod_empresa = CASE WHEN (x9.ies_tip_conta = 9 ) THEN x0.cod_empresa ELSE '99' END ) AND ((x9.num_conta = x7.num_conta_deb_desp ) OR (x9.num_conta_reduz = x7.num_conta_deb_desp ) ) ) )where (x0.ies_versao_atual = 'S' ) union all select 'AD' ,x10.cod_empresa ,x10.num_ap ,x10.num_parcela ,x10.dat_emis ,x10.dat_vencto_s_desc ,TO_CHAR (x10.dat_vencto_s_desc ,'%Y-%m' ),x10.dat_pgto ,TO_CHAR (x10.dat_pgto ,'%Y-%m' ),x10.cod_fornecedor ,x16.raz_social ,x16.raz_social_reduz ,x10.num_duplicata ,x13.cod_tip_despesa ,x14.nom_tip_despesa ,x15.num_conta_cont ,x13.num_ad ,x13.num_nf ,x13.dat_emis_nf ,x13.dat_rec_nf ,x13.observ ,x13.val_tot_nf ,((CASE WHEN (x12.num_adf IS NOT NULL ) THEN (((x10.val_nom_ap + eis_f_apvalores(x10.cod_empresa ,x10.num_ap ,x10.dat_proposta )) * (x13.val_tot_nf + eis_f_advalores(x10.cod_empresa ,x13.num_ad )) ) / (select (x36.val_tot_nf + eis_f_advalores(x36.cod_empresa ,x36.num_ad )) from "informix".ad_mestre x36 where ((x36.cod_empresa = x10.cod_empresa ) AND (x36.num_ad = x12.num_adf ) ) ) ) WHEN (x10.val_nom_ap <= (x13.val_tot_nf + eis_f_advalores(x10.cod_empresa ,x13.num_ad )) ) THEN (x10.val_nom_ap + eis_f_apvalores(x10.cod_empresa ,x10.num_ap ,x10.dat_proposta )) WHEN (x10.val_nom_ap > (x13.val_tot_nf + eis_f_advalores(x10.cod_empresa ,x13.num_ad )) ) THEN (NVL ((x10.val_nom_ap + eis_f_apvalores(x10.cod_empresa ,x10.num_ap ,x10.dat_proposta )) ,(x13.val_tot_nf + eis_f_advalores(x10.cod_empresa ,x13.num_ad )) )* ((x13.val_tot_nf + eis_f_advalores(x10.cod_empresa ,x13.num_ad )) / x10.val_nom_ap ) ) WHEN (x13.val_tot_nf != eis_f_get_total_ad_ap(x10.cod_empresa ,x10.num_ap ,x13.cod_tip_despesa )) THEN (NVL (x10.val_nom_ap ,(x13.val_tot_nf + eis_f_advalores(x13.cod_empresa ,x13.num_ad )) )* ((x13.val_tot_nf + eis_f_advalores(x10.cod_empresa ,x13.num_ad )) / eis_f_get_total_ad_ap(x13.cod_empresa ,x10.num_ap ,x13.cod_tip_despesa )) ) END * x15.val_lanc ) / x13.val_tot_nf ) ,x17.num_conta ,x17.den_conta ,eis_f_get_conta_nivel(x17.num_conta ,1 ),eis_f_get_conta_nivel(x17.num_conta ,2 ),eis_f_get_conta_nivel(x17.num_conta ,3 ),eis_f_get_conta_nivel(x17.num_conta ,4 ),eis_f_get_conta_nivel(x17.num_conta ,5 ),eis_f_get_den_conta_nivel(x17.num_conta ,1 ,x17.cod_empresa ),eis_f_get_den_conta_nivel(x17.num_conta ,2 ,x17.cod_empresa ),eis_f_get_den_conta_nivel(x17.num_conta ,3 ,x17.cod_empresa ),eis_f_get_den_conta_nivel(x17.num_conta ,4 ,x17.cod_empresa ),eis_f_get_den_conta_nivel(x17.num_conta ,5 ,x17.cod_empresa ),CASE WHEN (x10.dat_pgto IS NULL ) THEN 'N' ELSE 'S' END from ((((((("informix".ap x10 join "informix".ad_ap x11 on ((x11.cod_empresa = x10.cod_empresa ) AND (x11.num_ap = x10.num_ap ) ) )left join "informix".adn_adf x12 on ((x11.cod_empresa = x12.cod_empresa ) AND (x11.num_ad = x12.num_adf ) ) )join "informix".ad_mestre x13 on ((x10.cod_empresa = x13.cod_empresa ) AND (NVL (x12.num_adn ,x11.num_ad )= x13.num_ad ) ) )join "informix".tipo_despesa x14 on ((x14.cod_empresa = x13.cod_empresa ) AND (x14.cod_tip_despesa = x13.cod_tip_despesa ) ) )join "informix".lanc_cont_cap x15 on (((((x15.cod_empresa = x13.cod_empresa ) AND (x15.num_ad_ap = x13.num_ad ) ) AND (x15.ies_ad_ap = '1' ) ) AND (x15.ies_tipo_lanc = 'D' ) ) AND (x15.ies_desp_val = 'D' ) ) )join "informix".fornecedor x16 on (x16.cod_fornecedor = x10.cod_fornecedor ) )left join "informix".plano_contas x17 on ((x17.cod_empresa = CASE WHEN (x17.ies_tip_conta = 9 ) THEN x10.cod_empresa ELSE '99' END ) AND ((x17.num_conta = x15.num_conta_cont ) OR (x17.num_conta_reduz = x15.num_conta_cont ) ) ) )where (x10.ies_versao_atual = 'S' ) union all select 'AP' ,x18.cod_empresa ,x18.num_ap ,x18.num_parcela ,x18.dat_emis ,x18.dat_vencto_s_desc ,TO_CHAR (x18.dat_vencto_s_desc ,'%Y-%m' ),x18.dat_pgto ,TO_CHAR (x18.dat_pgto ,'%Y-%m' ),x18.cod_fornecedor ,x24.raz_social ,x24.raz_social_reduz ,x18.num_duplicata ,x21.cod_tip_despesa ,x22.nom_tip_despesa ,x23.num_conta_cont ,x21.num_ad ,x21.num_nf ,x21.dat_emis_nf ,x21.dat_rec_nf ,x21.observ ,x21.val_tot_nf ,(((CASE WHEN (x20.num_adf IS NOT NULL ) THEN (((x18.val_nom_ap + eis_f_apvalores(x18.cod_empresa ,x18.num_ap ,x18.dat_proposta )) * (x21.val_tot_nf + eis_f_advalores(x18.cod_empresa ,x21.num_ad )) ) / (select (x37.val_tot_nf + eis_f_advalores(x37.cod_empresa ,x37.num_ad )) from "informix".ad_mestre x37 where ((x37.cod_empresa = x18.cod_empresa ) AND (x37.num_ad = x20.num_adf ) ) ) ) WHEN (x18.val_nom_ap <= (x21.val_tot_nf + eis_f_advalores(x18.cod_empresa ,x21.num_ad )) ) THEN (x18.val_nom_ap + eis_f_apvalores(x18.cod_empresa ,x18.num_ap ,x18.dat_proposta )) WHEN (x18.val_nom_ap > (x21.val_tot_nf + eis_f_advalores(x18.cod_empresa ,x21.num_ad )) ) THEN (NVL ((x18.val_nom_ap + eis_f_apvalores(x18.cod_empresa ,x18.num_ap ,x18.dat_proposta )) ,(x21.val_tot_nf + eis_f_advalores(x18.cod_empresa ,x21.num_ad )) )* ((x21.val_tot_nf + eis_f_advalores(x18.cod_empresa ,x21.num_ad )) / x18.val_nom_ap ) ) WHEN (x21.val_tot_nf != eis_f_get_total_ad_ap(x18.cod_empresa ,x18.num_ap ,x21.cod_tip_despesa )) THEN (NVL ((x18.val_nom_ap + eis_f_apvalores(x18.cod_empresa ,x18.num_ap ,x18.dat_proposta )) ,(x21.val_tot_nf + eis_f_advalores(x21.cod_empresa ,x21.num_ad )) )* ((x21.val_tot_nf + eis_f_advalores(x18.cod_empresa ,x21.num_ad )) / eis_f_get_total_ad_ap(x21.cod_empresa ,x18.num_ap ,x21.cod_tip_despesa )) ) END * x23.val_lanc ) / x21.val_tot_nf ) * ((x21.val_tot_nf + eis_f_advalores(x21.cod_empresa ,x21.num_ad )) / x18.val_nom_ap ) ) ,x25.num_conta ,x25.den_conta ,eis_f_get_conta_nivel(x25.num_conta ,1 ),eis_f_get_conta_nivel(x25.num_conta ,2 ),eis_f_get_conta_nivel(x25.num_conta ,3 ),eis_f_get_conta_nivel(x25.num_conta ,4 ),eis_f_get_conta_nivel(x25.num_conta ,5 ),eis_f_get_den_conta_nivel(x25.num_conta ,1 ,x25.cod_empresa ),eis_f_get_den_conta_nivel(x25.num_conta ,2 ,x25.cod_empresa ),eis_f_get_den_conta_nivel(x25.num_conta ,3 ,x25.cod_empresa ),eis_f_get_den_conta_nivel(x25.num_conta ,4 ,x25.cod_empresa ),eis_f_get_den_conta_nivel(x25.num_conta ,5 ,x25.cod_empresa ),CASE WHEN (x18.dat_pgto IS NULL ) THEN 'N' ELSE 'S' END from ((((((("informix".ap x18 join "informix".ad_ap x19 on ((x19.cod_empresa = x18.cod_empresa ) AND (x19.num_ap = x18.num_ap ) ) )left join "informix".adn_adf x20 on ((x19.cod_empresa = x20.cod_empresa ) AND (x19.num_ad = x20.num_adf ) ) )join "informix".ad_mestre x21 on ((x18.cod_empresa = x21.cod_empresa ) AND (NVL (x20.num_adn ,x19.num_ad )= x21.num_ad ) ) )join "informix".tipo_despesa x22 on ((x22.cod_empresa = x21.cod_empresa ) AND (x22.cod_tip_despesa = x21.cod_tip_despesa ) ) )join "informix".lanc_cont_cap x23 on (((((x23.cod_empresa = x21.cod_empresa ) AND (x23.num_ad_ap = x18.num_ap ) ) AND (x23.ies_ad_ap = '2' ) ) AND (x23.ies_tipo_lanc = 'D' ) ) AND (x23.ies_desp_val = 'D' ) ) )join "informix".fornecedor x24 on (x24.cod_fornecedor = x18.cod_fornecedor ) )left join "informix".plano_contas x25 on ((x25.cod_empresa = CASE WHEN (x25.ies_tip_conta = 9 ) THEN x18.cod_empresa ELSE '99' END ) AND ((x25.num_conta = x23.num_conta_cont ) OR (x25.num_conta_reduz = x23.num_conta_cont ) ) ) )where ((((x18.ies_versao_atual = 'S' ) AND ((select count(*) from "informix".lanc_cont_cap x38 where ((((x38.cod_empresa = x21.cod_empresa ) AND (x38.num_ad_ap = x21.num_ad ) ) AND (x38.ies_ad_ap = '1' ) ) AND (x38.ies_tipo_lanc = 'D' ) ) ) = 0 ) ) AND ((select count(1 ) from "informix".dest_frete_sup x39 where (((((x39.cod_empresa = x21.cod_empresa ) AND (x39.num_conhec = to_number2(x21.num_nf )) ) AND (x39.ser_conhec = x21.ser_nf ) ) AND (x39.ssr_conhec = x21.ssr_nf ) ) AND (x39.cod_transpor = x21.cod_fornecedor ) ) ) = 0 ) ) AND ((select count(*) from (("informix".nf_sup x40 join "informix".aviso_rec x41 on ((x40.cod_empresa = x41.cod_empresa ) AND (x40.num_aviso_rec = x41.num_aviso_rec ) ) )join "informix".dest_aviso_rec x42 on (((x42.cod_empresa = x41.cod_empresa ) AND (x42.num_aviso_rec = x41.num_aviso_rec ) ) AND (x42.num_seq = x41.num_seq ) ) )where (((((x40.cod_empresa = x21.cod_empresa ) AND (x40.num_nf = to_number2(x21.num_nf )) ) AND (x40.ser_nf = x21.ser_nf ) ) AND (x40.ssr_nf = x21.ssr_nf ) ) AND (x40.cod_fornecedor = x18.cod_fornecedor ) ) ) = 0 ) ) union all select 'FRT' ,x26.cod_empresa ,x26.num_ap ,x26.num_parcela ,x26.dat_emis ,x26.dat_vencto_s_desc ,TO_CHAR (x26.dat_vencto_s_desc ,'%Y-%m' ),x26.dat_pgto ,TO_CHAR (x26.dat_pgto ,'%Y-%m' ),x26.cod_fornecedor ,x33.raz_social ,x33.raz_social_reduz ,x26.num_duplicata ,x29.cod_tip_despesa ,x30.nom_tip_despesa ,x32.num_conta_deb_desp ,x29.num_ad ,x29.num_nf ,x29.dat_emis_nf ,x29.dat_rec_nf ,x29.observ ,x29.val_tot_nf ,(((CASE WHEN (x28.num_adf IS NOT NULL ) THEN (((x26.val_nom_ap + eis_f_apvalores(x26.cod_empresa ,x26.num_ap ,x26.dat_proposta )) * (x29.val_tot_nf + eis_f_advalores(x26.cod_empresa ,x29.num_ad )) ) / (select (x43.val_tot_nf + eis_f_advalores(x43.cod_empresa ,x43.num_ad )) from "informix".ad_mestre x43 where ((x43.cod_empresa = x26.cod_empresa ) AND (x43.num_ad = x28.num_adf ) ) ) ) WHEN (x26.val_nom_ap <= (x29.val_tot_nf + eis_f_advalores(x26.cod_empresa ,x29.num_ad )) ) THEN (x26.val_nom_ap + eis_f_apvalores(x26.cod_empresa ,x26.num_ap ,x26.dat_proposta )) WHEN (x26.val_nom_ap > (x29.val_tot_nf + eis_f_advalores(x26.cod_empresa ,x29.num_ad )) ) THEN (NVL ((x26.val_nom_ap + eis_f_apvalores(x26.cod_empresa ,x26.num_ap ,x26.dat_proposta )) ,(x29.val_tot_nf + eis_f_advalores(x26.cod_empresa ,x29.num_ad )) )* ((x29.val_tot_nf + eis_f_advalores(x26.cod_empresa ,x29.num_ad )) / x26.val_nom_ap ) ) WHEN (x29.val_tot_nf != eis_f_get_total_ad_ap(x26.cod_empresa ,x26.num_ap ,x29.cod_tip_despesa )) THEN (NVL (x26.val_nom_ap ,(x29.val_tot_nf + eis_f_advalores(x29.cod_empresa ,x29.num_ad )) )* ((x29.val_tot_nf + eis_f_advalores(x26.cod_empresa ,x29.num_ad )) / eis_f_get_total_ad_ap(x29.cod_empresa ,x26.num_ap ,x29.cod_tip_despesa )) ) END * (x31.val_frete / x29.val_tot_nf ) ) * x32.pct_particip_comp ) / 100. ) ,x34.num_conta ,x34.den_conta ,eis_f_get_conta_nivel(x34.num_conta ,1 ),eis_f_get_conta_nivel(x34.num_conta ,2 ),eis_f_get_conta_nivel(x34.num_conta ,3 ),eis_f_get_conta_nivel(x34.num_conta ,4 ),eis_f_get_conta_nivel(x34.num_conta ,5 ),eis_f_get_den_conta_nivel(x34.num_conta ,1 ,x34.cod_empresa ),eis_f_get_den_conta_nivel(x34.num_conta ,2 ,x34.cod_empresa ),eis_f_get_den_conta_nivel(x34.num_conta ,3 ,x34.cod_empresa ),eis_f_get_den_conta_nivel(x34.num_conta ,4 ,x34.cod_empresa ),eis_f_get_den_conta_nivel(x34.num_conta ,5 ,x34.cod_empresa ),CASE WHEN (x26.dat_pgto IS NULL ) THEN 'N' ELSE 'S' END from (((((((("informix".ap x26 join "informix".ad_ap x27 on ((x27.cod_empresa = x26.cod_empresa ) AND (x27.num_ap = x26.num_ap ) ) )left join "informix".adn_adf x28 on ((x27.cod_empresa = x28.cod_empresa ) AND (x27.num_ad = x28.num_adf ) ) )join "informix".ad_mestre x29 on ((x26.cod_empresa = x29.cod_empresa ) AND (NVL (x28.num_adn ,x27.num_ad )= x29.num_ad ) ) )join "informix".tipo_despesa x30 on ((x30.cod_empresa = x29.cod_empresa ) AND (x30.cod_tip_despesa = x29.cod_tip_despesa ) ) )join "informix".frete_sup x31 on (((((x31.cod_empresa = x29.cod_empresa ) AND (x31.num_conhec = to_number2(x29.num_nf )) ) AND (x31.ser_conhec = x29.ser_nf ) ) AND (x31.ssr_conhec = x29.ssr_nf ) ) AND (x31.cod_transpor = x29.cod_fornecedor ) ) )join "informix".dest_frete_sup x32 on (((((x31.cod_empresa = x29.cod_empresa ) AND (x32.num_conhec = to_number2(x29.num_nf )) ) AND (x32.ser_conhec = x29.ser_nf ) ) AND (x32.ssr_conhec = x29.ssr_nf ) ) AND (x32.cod_transpor = x29.cod_fornecedor ) ) )join "informix".fornecedor x33 on (x33.cod_fornecedor = x26.cod_fornecedor ) )left join "informix".plano_contas x34 on ((x34.cod_empresa = CASE WHEN (x34.ies_tip_conta = 9 ) THEN x26.cod_empresa ELSE '99' END ) AND ((x34.num_conta = x32.num_conta_deb_desp ) OR (x34.num_conta_reduz = x32.num_conta_deb_desp ) ) ) )where (x26.ies_versao_atual = 'S' ); Estimated Cost: 116410329734316032 Estimated # of Rows Returned: 137974188605440 1) informix.fornecedor: SEQUENTIAL SCAN 2) informix.nf_sup: INDEX PATH (1) Index Name: informix.ix_nf_sup_g2 Index Keys: cod_empresa cod_fornecedor ies_incl_cap nom_resp_aceite_er (Serial, fragments: ALL) Index Self Join Keys (cod_empresa ) Lower Index Filter: informix.nf_sup.cod_empresa = informix.nf_sup.cod_empresa AND informix.nf_sup.cod_fornecedor = informix.fornecedor.cod_fornecedor 3) informix.tipo_despesa: INDEX PATH (1) Index Name: informix. 10573_78351 Index Keys: cod_empresa cod_tip_despesa (Serial, fragments: ALL) Lower Index Filter: informix.tipo_despesa.cod_empresa = informix.nf_sup.cod_empresa 4) informix.ap: INDEX PATH (1) Index Name: informix.ix_ap_7 Index Keys: cod_empresa ies_versao_atual cod_fornecedor dat_vencto_s_desc (Serial, fragments: ALL) Lower Index Filter: ((informix.fornecedor.cod_fornecedor = informix.ap.cod_fornecedor AND informix.ap.cod_empresa = informix.tipo_despesa.cod_empresa ) AND informix.ap.ies_versao_atual = 'S' ) 5) informix.ad_ap: INDEX PATH (1) Index Name: informix.ix_ad_ap_2 Index Keys: cod_empresa num_ap (Serial, fragments: ALL) Lower Index Filter: (informix.ad_ap.cod_empresa = informix.tipo_despesa.cod_empresa AND informix.ad_ap.num_ap = informix.ap.num_ap ) ON-Filters:(informix.ad_ap.cod_empresa = informix.ap.cod_empresa AND informix.ad_ap.num_ap = informix.ap.num_ap ) NESTED LOOP JOIN 6) informix.adn_adf: INDEX PATH (1) Index Name: informix.ix_adn_adf_2 Index Keys: cod_empresa num_adf (Serial, fragments: ALL) Lower Index Filter: (informix.ad_ap.cod_empresa = informix.adn_adf.cod_empresa AND informix.ad_ap.num_ad = informix.adn_adf.num_adf ) ON-Filters:(informix.ad_ap.cod_empresa = informix.adn_adf.cod_empresa AND informix.ad_ap.num_ad = informix.adn_adf.num_adf ) NESTED LOOP JOIN(LEFT OUTER JOIN) 7) informix.ad_mestre: INDEX PATH (1) Index Name: informix.ix_ad_m_2 Index Keys: cod_empresa cod_tip_despesa num_ad (Serial, fragments: ALL) Lower Index Filter: (informix.tipo_despesa.cod_empresa = informix.ad_mestre.cod_empresa AND informix.tipo_despesa.cod_tip_despesa = informix.ad_mestre.cod_tip_despesa ) ON-Filters:(informix.ap.cod_empresa = informix.ad_mestre.cod_empresa AND informix.ad_mestre.num_ad = NVL (informix.adn_adf.num_adn , informix.ad_ap.num_ad ) ) NESTED LOOP JOIN ON-Filters:(informix.tipo_despesa.cod_empresa = informix.ad_mestre.cod_empresa AND informix.tipo_despesa.cod_tip_despesa = informix.ad_mestre.cod_tip_despesa ) Other Join Filters: informix.ad_ap.cod_empresa = informix.tipo_despesa.cod_empresa NESTED LOOP JOIN ON-Filters:((((informix.nf_sup.cod_empresa = informix.ad_mestre.cod_empresa AND informix.nf_sup.num_nf = informix.to_number2(informix.ad_mestre.num_nf )) AND informix.nf_sup.ser_nf = informix.ad_mestre.ser_nf ) AND informix.nf_sup.ssr_nf = informix.ad_mestre.ssr_nf ) AND informix.nf_sup.cod_fornecedor = informix.ap.cod_fornecedor ) NESTED LOOP JOIN 8) informix.aviso_rec: INDEX PATH (1) Index Name: informix.ix_aviso_rec_5 Index Keys: cod_empresa num_aviso_rec num_seq ies_receb_fiscal (Serial, fragments: ALL) Lower Index Filter: (informix.nf_sup.cod_empresa = informix.aviso_rec.cod_empresa AND informix.nf_sup.num_aviso_rec = informix.aviso_rec.num_aviso_rec ) ON-Filters:(informix.nf_sup.cod_empresa = informix.aviso_rec.cod_empresa AND informix.nf_sup.num_aviso_rec = informix.aviso_rec.num_aviso_rec ) NESTED LOOP JOIN 9) informix.dest_aviso_rec: INDEX PATH (1) Index Name: informix.ix_dest_aviso_rec2 Index Keys: cod_empresa num_aviso_rec num_seq cod_secao_receb num_conta_deb_desp sequencia cod_area_negocio cod_lin_negocio (Serial, fragments: ALL) Lower Index Filter: ((informix.dest_aviso_rec.num_seq = informix.aviso_rec.num_seq AND informix.nf_sup.num_aviso_rec = informix.dest_aviso_rec.num_aviso_rec ) AND informix.tipo_despesa.cod_empresa = informix.dest_aviso_rec.cod_empresa ) ON-Filters:((informix.dest_aviso_rec.cod_empresa = informix.aviso_rec.cod_empresa AND informix.dest_aviso_rec.num_aviso_rec = informix.aviso_rec.num_aviso_rec ) AND informix.dest_aviso_rec.num_seq = informix.aviso_rec.num_seq ) NESTED LOOP JOIN ON-Filters:informix.fornecedor.cod_fornecedor = informix.ap.cod_fornecedor NESTED LOOP JOIN 10) informix.plano_contas: SEQUENTIAL SCAN ON-Filters:(informix.plano_contas.cod_empresa = CASE WHEN informix.plano_contas.ies_tip_conta = 9 THEN informix.ap.cod_empresa ELSE '99' ENDAND (informix.plano_contas.num_conta = informix.dest_aviso_rec.num_conta_deb_desp OR informix.plano_contas.num_conta_reduz = informix.dest_aviso_rec.num_conta_deb_desp ) ) NESTED LOOP JOIN(LEFT OUTER JOIN) Subquery: --------- Estimated Cost: 1 Estimated # of Rows Returned: 1 1) root.x35: INDEX PATH (1) Index Name: informix. 169_478 Index Keys: cod_empresa num_ad (Serial, fragments: ALL) Lower Index Filter: (root.x35.num_ad = informix.adn_adf.num_adf AND root.x35.cod_empresa = informix.ap.cod_empresa ) UDRs in query: --------------
Leandro, First question: is this the first time this query is launched / has something changed in the query or IFMX version change ? Then At first glance, here are some ideas to check: Wow ! that 's a piece of query, of course it can take time because it looks very complex to optimize Are your statistics UP TO DATE on the involved tables Views are not 'materialized', this means that every time you query in a view, you will execute the queries that make the view. Generally, using such complex views allow the developer to write less code, but the query will probably take longer to execute Lots of 'CASE' will make it more complex, as this generates 'virtual' data (that does not exist and can be included in an index...) Using filter with stored procedures (which I think I saw) will probably prevent from using an index on that clause OUTER keyword generally makes it longer: it triggers a full scan you could go more into details by using sqltrace and check in detail how many buffer reads/disk reads on each table for that query All of this may take time to complete...
Leandro: OK, I can tell you what it's doing, not sure if I can tell you how to fix it though. Here goes: Your really simple query on its own has only two rather small problems: The '(1 = 1) AND ' construct at the beginning of the WHERE clause is completely no-op and completely not needed. I am aware that some folk teach that this should be the start of every WHERE clause, and I know that it makes auto-generating queries a bit simple (it saves a single 'if' statement), but I strongly disagree. Fortunately it is also completely harmless, just one of my pet peeves. The SELECT is querying a VIEW and that view is so massive and complex that it has to be instantiated in a temp table before it can be queried. That it is querying a VIEW is a tiny problem. That the VIEW is massively complicated is a HUGE problem. Read on. Then there are the BIG problems: There are four queries in the VIEW joined by UNION ALL. Of these: One joins 11 tables ( 4,435,200 possible join plans to be evaluated) and includes a correlated subquery. Two of them join 8 tables each ( 40,320 possible join plans to be evaluated for each SELECT). One of these contains a correlated subquery while the other contains four correlated subqueries. The last SELECT joins 10 tables (403,200 possible join plans to be evaluated) and includes a correlated subquery. Each of these calls many many functions in the projection clause. Several of the join conditions are not supported by the first column in an index on the target table. Each correlated subquery has to be executed for every row examined. So, bottom line, it's understandable that the query runs for a very long time! Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------
Generally, if you have more than 6 tables then a temp table as intermediate temp normally helps ....my experience Cheers Paul Paul Watson Oninit LLC +1-913-387-7529 www.oninit.com Oninit® is a registered trademark of Oninit LLC
I would go further ... doing it one SQL is really cool and neat and clever but multiple statements on complex queries can be significantly quicker Cheers Paul Paul Watson Oninit LLC +1-913-387-7529 www.oninit.com Oninit® is a registered trademark of Oninit LLC
Hi,
could you provide his :
select count(*) count_adn_adf FROM adn_adf ;
select count(*) count_ad_ap FROM ad_ap ;
select count(*) count_ad_mestre FROM ad_mestre ;
select count(*) count_ap FROM ap ;
select count(*) count_aviso_rec FROM aviso_rec ;
select count(*) count_dest_aviso_rec FROM dest_aviso_rec ;
select count(*) count_dest_frete_sup FROM dest_frete_sup ;
select count(*) count_fornecedor FROM fornecedor ;
select count(*) count_frete_sup FROM frete_sup ;
select count(*) count_lanc_cont_cap FROM lanc_cont_cap ;
select count(*) count_nf_sup FROM nf_sup ;
select count(*) count_plano_contas FROM plano_contas ;
select count(*) count_tipo_despesa FROM tipo_despesa ;dbschema -d ? -t adn_adf adn_adf.sql
dbschema -d ? -t ad_ap ad_ap.sql
dbschema -d ? -t ad_mestre ad_mestre.sql
dbschema -d ? -t ap ap.sql
dbschema -d ? -t aviso_rec aviso_rec.sql
dbschema -d ? -t dest_aviso_rec dest_aviso_rec.sql
dbschema -d ? -t dest_frete_sup dest_frete_sup.sql
dbschema -d ? -t fornecedor fornecedor.sql
dbschema -d ? -t frete_sup frete_sup.sql
dbschema -d ? -t lanc_cont_cap lanc_cont_cap.sql
dbschema -d ? -t nf_sup nf_sup.sql
dbschema -d ? -t plano_contas plano_contas.sql
dbschema -d ? -t tipo_despesa tipo_despesa.sqlregards
------------------------------
Garcia Benjamin
------------------------------
and aso this spl ...
dbschema -d ? -f eis_f_advalores eis_f_advalores.spl
dbschema -d ? -f eis_f_get_conta_nivel eis_f_get_conta_nivel.spl
dbschema -d ? -f eis_f_get_den_conta_nivel eis_f_get_den_conta_nivel.spl
dbschema -d ? -f eis_f_get_total_ad_ap eis_f_get_total_ad_ap.spl
dbschema -d ? -f to_number2 to_number2.spl
------------------------------Garcia Benjamin
------------------------------
Helo Art! The SELECT is querying a VIEW and that view is so massive and complex that it has to be instantiated in a temp table before it can be queried. That it is querying a VIEW is a tiny problem. That the VIEW is massively complicated is a HUGE problem. Read on. Are there options to optimize this temporary table that the view was instantiated? This is a view that was developed by our external BI consultant ------------------------------ Leandro Kohler ------------------------------
Hi Garcia!
Thanks for your answer, here is go.
Follow below sqls. And follow attached the files.
------------------------------
COUNTS
------------------------------
select count(*) count_adn_adf FROM adn_adf ; -- 4888
select count(*) count_ad_ap FROM ad_ap ; -- 212342
select count(*) count_ad_mestre FROM ad_mestre ; -- 215319
select count(*) count_ap FROM ap ; -- 292434
select count(*) count_aviso_rec FROM aviso_rec ; -- 210882
select count(*) count_dest_aviso_rec FROM dest_aviso_rec ; -- 222718
select count(*) count_dest_frete_sup FROM dest_frete_sup ; -- 70795
select count(*) count_fornecedor FROM fornecedor ; -- 12184
select count(*) count_frete_sup FROM frete_sup ; -- 79161
select count(*) count_lanc_cont_cap FROM lanc_cont_cap ; -- 380493
select count(*) count_nf_sup FROM nf_sup ; -- 127229
select count(*) count_plano_contas FROM plano_contas ; -- 20242
select count(*) count_tipo_despesa FROM tipo_despesa ; -- 5644
------------------------------DBSCHEMAS
------------------------------
{ TABLE "informix".lanc_cont_cap row size = 110 number of columns = 16 index size = 64 }
create table "informix".lanc_cont_cap
(
cod_empresa char(2) not null ,
num_ad_ap decimal(6,0) not null ,
ies_ad_ap char(1) not null ,
num_seq decimal(3,0) not null ,
cod_tip_desp_val decimal(4,0),
ies_desp_val char(1),
ies_man_aut char(1) not null ,
ies_tipo_lanc char(1) not null ,
num_conta_cont char(23) not null ,
val_lanc decimal(15,2) not null ,
tex_hist_lanc char(50),
ies_cnd_pgto char(1) not null ,
num_lote_lanc decimal(3,0) not null ,
ies_liberad_contab char(1) not null ,
num_lote_transf decimal(3,0) not null ,
dat_lanc date not null ,
primary key (cod_empresa,num_ad_ap,ies_ad_ap,num_seq) constraint "informix".pk_lanc_cont_cap_1
);
revoke all on "informix".lanc_cont_cap from "public" as "informix";create index "informix".ix_lanc_cont_cap_2 on "informix".lanc_cont_cap
(cod_empresa,dat_lanc) using btree ;
create index "informix".ix_lanc_cont_cap_3 on "informix".lanc_cont_cap
(cod_empresa,num_lote_lanc) using btree ;
create index "informix".ix_num_conta_cont on "informix".lanc_cont_cap
(num_conta_cont) using btree ;
=========================================================
{ TABLE "informix".frete_sup row size = 213 number of columns = 42 index size = 162 }
create table "informix".frete_sup
(
cod_empresa char(2) not null ,
cod_empresa_estab char(2),
cod_transpor char(19) not null ,
num_conhec decimal(20,0) not null ,
ser_conhec char(3) not null ,
ssr_conhec decimal(2,0) not null ,
dat_emis_conhec date not null ,
dat_entrada_conhec date not null ,
cnd_pgto_frt decimal(3,0) not null ,
cod_regist_entrada decimal(2,0) not null ,
cod_fiscal_frete char(7) not null ,
gru_ctr_desp_frete decimal(4,0) not null ,
ies_incid_icms_fre char(1) not null ,
val_frete decimal(17,2) not null ,
val_base_c_frete_c decimal(17,2) not null ,
val_tot_icms_frt_d decimal(17,2) not null ,
val_tot_icms_frt_c decimal(17,2) not null ,
val_icms_diferen decimal(17,2) not null ,
pct_icms_frete_c decimal(7,4) not null ,
pct_red_bc_frete_c decimal(7,4) not null ,
pct_diferen_fret_c decimal(7,4) not null ,
val_adiant decimal(17,2) not null ,
ies_conhec_erro char(1) not null ,
nom_resp_aceite_er char(8),
ies_incl_cap char(1) not null ,
ies_incl_contab char(1) not null ,
cod_praca decimal(9,0),
ies_frt_contratado char(1),
tip_carga char(1),
cod_item_tab_frete char(15),
pes_vol_frete decimal(13,5),
val_frete_c decimal(17,2),
ies_diverg_listada char(1) not null ,
ies_diverg_val_frt char(1) not null ,
ies_liberacao_frt char(1) not null ,
ies_diverg_relac char(1) not null ,
ies_diverg_cnd_pgt char(1) not null ,
cnd_pgto_tab decimal(3,0),
tip_frete char(1) not null ,
cod_tip_despesa decimal(4,0),
ies_subst_tribut char(1) not null ,
val_base_c_frete_d decimal(17,2),
primary key (cod_empresa,cod_transpor,num_conhec,ser_conhec,ssr_conhec) constraint
"informix".pk_fretesup
);
revoke all on "informix".frete_sup from "public" as "informix";create index "informix".ix3_fretesup on "informix".frete_sup (cod_empresa,
ies_incl_contab,ies_conhec_erro,nom_resp_aceite_er) using
btree ;
create index "informix".ix4_fretesup on "informix".frete_sup (cod_empresa,
dat_entrada_conhec,ies_conhec_erro,nom_resp_aceite_er) using
btree ;
create index "informix".ix6_frete_sup on "informix".frete_sup
(cod_empresa,num_conhec,cod_transpor) using btree ;
create index "informix".ix_frete_sup_1 on "informix".frete_sup
(cod_empresa,dat_entrada_conhec) using btree ;
create index "informix".ix_frete_sup_g on "informix".frete_sup
(cod_empresa,cod_transpor,ies_incl_cap,nom_resp_aceite_er)
using btree ;
=====================================================
{ TABLE "informix".fornecedor row size = 386 number of columns = 38 index size = 81 }
create table "informix".fornecedor
(
num_cgc_cpf char(19) not null ,
cod_fornecedor char(15) not null ,
raz_social char(50) not null ,
raz_social_reduz char(10) not null ,
ies_tip_fornec char(1) not null ,
ies_fornec_ativo char(1) not null ,
ies_contrib_ipi char(1) not null ,
ies_fis_juridica char(1) not null ,
ins_estadual char(16),
dat_cadast date not null ,
dat_atualiz date not null ,
dat_validade date not null ,
dat_movto_ult date,
end_fornec char(36) not null ,
den_bairro char(20) not null ,
cod_cep char(9) not null ,
cod_cidade char(5) not null ,
cod_uni_feder char(2) not null ,
cod_pais char(3) not null ,
ies_zona_franca char(1) not null ,
num_telefone char(15) not null ,
num_fax char(15),
num_telex char(15),
nom_contato char(20),
nom_guerra char(15),
cod_cidade_pgto char(5),
camara_comp decimal(3,0),
cod_banco decimal(4,0),
num_agencia char(6),
num_conta_banco char(15),
tmp_transpor decimal(3,0) not null ,
tex_observ char(50),
num_lote_transf decimal(3,0) not null ,
pct_aceite_div decimal(6,3) not null ,
ies_tip_entrega char(1) not null ,
ies_dep_cred char(1) not null ,
ult_num_coleta decimal(6,0) not null ,
ies_gera_ap char(1),
primary key (cod_fornecedor) constraint "informix".pk_fornec_cod
);
revoke all on "informix".fornecedor from "public" as "informix";create index "informix".ix1_fornecedor on "informix".fornecedor
(ins_estadual) using btree ;
create index "informix".ix_fornecedor on "informix".fornecedor
(num_cgc_cpf,ins_estadual) using btree ;
======================================================
{ TABLE "informix".dest_frete_sup row size = 68 number of columns = 9 index size = 179 }
create table "informix".dest_frete_sup
(
cod_empresa char(2) not null ,
num_conhec decimal(20,0) not null ,
ser_conhec char(3) not null ,
ssr_conhec decimal(2,0) not null ,
cod_transpor char(19) not null ,
cod_area_negocio decimal(2,0) not null ,
cod_lin_negocio decimal(2,0) not null ,
pct_particip_comp decimal(5,2) not null ,
num_conta_deb_desp char(23) not null ,
primary key (cod_empresa,num_conhec,ser_conhec,ssr_conhec,cod_transpor,cod_area_negocio,cod_lin_negocio,num_conta_deb_desp)
constraint "informix".pk_dest_frete_sup
);
revoke all on "informix".dest_frete_sup from "public" as "informix";create index "informix".ak_dest_frete_sup_01 on "informix".dest_frete_sup
(cod_empresa,num_conhec,ser_conhec,ssr_conhec,cod_transpor)
using btree ;
create index "informix".id_dest_frete_sup on "informix".dest_frete_sup
(num_conhec,ser_conhec,ssr_conhec,cod_transpor) using btree
;
create index "informix".ix_dest_frete_sup_numconta on "informix"
.dest_frete_sup (num_conta_deb_desp) using btree ;
============================================================
{ TABLE "informix".nf_sup row size = 278 number of columns = 43 index size = 350 }
create table "informix".nf_sup
(
cod_empresa char(2) not null ,
cod_empresa_estab char(2),
num_nf decimal(20,0) not null ,
ser_nf char(3) not null ,
ssr_nf decimal(2,0) not null ,
ies_especie_nf char(3) not null ,
cod_fornecedor char(15) not null ,
num_conhec decimal(20,0) not null ,
ser_conhec char(3) not null ,
ssr_conhec decimal(2,0) not null ,
cod_transpor char(19) not null ,
num_aviso_rec decimal(6,0) not null ,
dat_emis_nf date not null ,
dat_entrada_nf date not null ,
cod_regist_entrada decimal(2,0) not null ,
val_tot_nf_c decimal(17,2) not null ,
val_tot_nf_d decimal(17,2) not null ,
val_tot_icms_nf_d decimal(17,2) not null ,
val_tot_icms_nf_c decimal(17,2) not null ,
val_tot_desc decimal(17,2) not null ,
val_tot_acresc decimal(17,2) not null ,
val_ipi_nf decimal(17,2) not null ,
val_ipi_calc decimal(17,2) not null ,
val_despesa_aces decimal(17,2) not null ,
val_adiant decimal(17,2) not null ,
ies_tip_frete char(1) not null ,
cnd_pgto_nf decimal(3,0) not null ,
cod_mod_embar decimal(2,0) not null ,
ies_nf_com_erro char(1) not null ,
nom_resp_aceite_er char(8),
ies_incl_cap char(1) not null ,
ies_incl_contab char(1) not null ,
cod_operacao char(7) not null ,
ies_calc_subst char(1),
val_bc_subst_d decimal(17,2) not null ,
val_icms_subst_d decimal(17,2) not null ,
val_bc_subst_c decimal(17,2) not null ,
val_icms_subst_c decimal(17,2) not null ,
cod_imp_renda char(4),
val_imp_renda decimal(17,2) not null ,
ies_situa_import char(1) not null ,
val_bc_imp_renda decimal(17,2) not null ,
ies_nf_aguard_nfe char(1),
primary key (cod_empresa,num_nf,ser_nf,ssr_nf,ies_especie_nf,cod_fornecedor)
constraint "informix".pk_nfsup
);
revoke all on "informix".nf_sup from "public" as "informix";create index "informix".ix11_nf_sup on "informix".nf_sup (num_nf,
ser_nf,ssr_nf,ies_especie_nf,cod_fornecedor) using btree
;
create index "informix".ix12_nf_sup on "informix".nf_sup (cod_empresa,
num_nf,cod_fornecedor) using btree ;
create unique index "informix".ix_nf_sup_1 on "informix".nf_sup
(cod_empresa,num_aviso_rec) using btree ;
create index "informix".ix_nf_sup_10 on "informix".nf_sup (cod_empresa,
dat_entrada_nf,ies_nf_com_erro,nom_resp_aceite_er) using
btree ;
create index "informix".ix_nf_sup_2 on "informix".nf_sup (cod_empresa,
cnd_pgto_nf,dat_entrada_nf) using btree ;
create index "informix".ix_nf_sup_3 on "informix".nf_sup (cod_empresa,
num_aviso_rec,cod_fornecedor,dat_entrada_nf) using btree
;
create index "informix".ix_nf_sup_4 on "informix".nf_sup (cod_empresa,
num_conhec) using btree ;
create index "informix".ix_nf_sup_5 on "informix".nf_sup (cod_empresa,
num_conhec,ser_conhec,ssr_conhec,cod_transpor) using btree
;
create index "informix".ix_nf_sup_6 on "informix".nf_sup (cod_empresa,
num_conhec,ies_incl_cap) using btree ;
create index "informix".ix_nf_sup_9 on "informix".nf_sup (cod_empresa,
ies_incl_contab,ies_nf_com_erro,nom_resp_aceite_er) using
btree ;
create unique index "informix".ix_nf_sup_g on "informix".nf_sup
(cod_empresa,num_aviso_rec,cod_fornecedor) using btree ;
create index "informix".ix_nf_sup_g1 on "informix".nf_sup (cod_empresa,
cod_mod_embar) using btree ;
create index "informix".ix_nf_sup_g2 on "informix".nf_sup (cod_empresa,
cod_fornecedor,ies_incl_cap,nom_resp_aceite_er) using btree
;
==============================================
{ TABLE "informix".plano_contas row size = 91 number of columns = 9 index size = 213 }
create table "informix".plano_contas
(
cod_empresa char(2) not null ,
num_conta char(23) not null ,
num_conta_reduz char(10) not null ,
den_conta char(50) not null ,
ies_tip_conta decimal(2,0) not null ,
ies_saldo_conta char(1) not null ,
ies_sit_conta char(1) not null ,
ies_titulo char(1) not null ,
ies_conta_estoque char(1) not null ,
primary key (cod_empresa,num_conta,num_conta_reduz) constraint "informix".pk_plano_ct_1
);
revoke all on "informix".plano_contas from "public" as "informix";create index "informix".id_plano_contas on "informix".plano_contas
(cod_empresa,ies_tip_conta) using btree ;
create index "informix".id_plano_contas_2 on "informix".plano_contas
(cod_empresa,ies_tip_conta,num_conta,num_conta_reduz) using
btree ;
create index "informix".ix_plano_ct_2 on "informix".plano_contas
(cod_empresa,num_conta_reduz,ies_sit_conta) using btree ;
create index "informix".ix_plano_ct_3 on "informix".plano_contas
(cod_empresa,num_conta,ies_sit_conta) using btree ;
create index "informix".ix_plano_ct_4 on "informix".plano_contas
(cod_empresa,num_conta,ies_tip_conta,ies_sit_conta) using
btree ;
create index "informix".ix_plano_ct_5 on "informix".plano_contas
(cod_empresa,ies_tip_conta,ies_sit_conta) using btree ;
create index "informix".ix_plano_ct_6 on "informix".plano_contas
(cod_empresa,num_conta) using btree ;
=============================================
{ TABLE "informix".ad_mestre row size = 157 number of columns = 30 index size = 152 }
create table "informix".ad_mestre
(
cod_empresa char(2) not null ,
num_ad decimal(6,0) not null ,
cod_tip_despesa decimal(4,0) not null ,
ser_nf char(3),
ssr_nf decimal(2,0),
num_nf char(20) not null ,
dat_emis_nf date,
dat_rec_nf date,
cod_empresa_estab char(2),
mes_ano_compet decimal(4,0),
num_ord_forn decimal(6,0),
cnd_pgto decimal(3,0),
dat_venc date,
cod_fornecedor char(15) not null ,
cod_portador decimal(3,0),
val_tot_nf decimal(15,2) not null ,
val_saldo_ad decimal(15,2) not null ,
cod_moeda decimal(2,0) not null ,
set_aplicacao decimal(4,0),
cod_lote_pgto decimal(4,0) not null ,
observ char(40),
cod_tip_ad decimal(2,0) not null ,
ies_ap_autom char(1) not null ,
ies_sup_cap char(1) not null ,
ies_fatura char(1) not null ,
ies_ad_cont char(1) not null ,
num_lote_transf decimal(3,0) not null ,
ies_dep_cred char(1) not null ,
num_lote_pat decimal(3,0),
cod_empresa_orig char(2) not null ,
primary key (cod_empresa,num_ad) constraint "informix".pk_ad_mestre
);
revoke all on "informix".ad_mestre from "public" as "informix";create unique index "informix".ix_ad_m_2 on "informix".ad_mestre
(cod_empresa,cod_tip_despesa,num_ad) using btree ;
create index "informix".ix_ad_m_3 on "informix".ad_mestre (cod_empresa,
cod_fornecedor) using btree ;
create unique index "informix".ix_ad_m_4 on "informix".ad_mestre
(cod_empresa,num_nf,cod_fornecedor,ser_nf,ssr_nf) using btree
;
create index "informix".ix_ad_m_5 on "informix".ad_mestre (cod_empresa_orig,
num_ad) using btree ;
create index "informix".ix_ad_m_6 on "informix".ad_mestre (cod_empresa_orig,
num_nf,ser_nf,ssr_nf,cod_fornecedor) using btree ;
===============================================
{ TABLE "informix".ad_ap row size = 13 number of columns = 4 index size = 37 }
create table "informix".ad_ap
(
cod_empresa char(2) not null ,
num_ad decimal(6,0) not null ,
num_ap decimal(6,0) not null ,
num_lote_transf decimal(3,0) not null
);
revoke all on "informix".ad_ap from "public" as "informix";create index "informix".ix_ad_ap_1 on "informix".ad_ap (cod_empresa,
num_ad,num_ap) using btree ;
create index "informix".ix_ad_ap_2 on "informix".ad_ap (cod_empresa,
num_ap) using btree ;
create index "informix".ix_ad_ap_3 on "informix".ad_ap (cod_empresa,
num_ad) using btree ;
=================================================
{ TABLE "informix".ap row size = 252 number of columns = 45 index size = 178 }
create table "informix".ap
(
cod_empresa char(2) not null ,
num_ap decimal(6,0) not null ,
num_versao decimal(2,0) not null ,
ies_versao_atual char(1) not null ,
num_parcela decimal(3,0) not null ,
cod_portador decimal(3,0),
cod_bco_pagador decimal(3,0),
num_conta_banc char(15),
cod_fornecedor char(15) not null ,
cod_banco_for decimal(4,0),
num_agencia_for char(6),
num_conta_bco_for char(15),
num_nf char(20) not null ,
num_duplicata char(10),
num_bl_awb char(30),
compl_docum char(10),
val_nom_ap decimal(15,2) not null ,
val_ap_dat_pgto decimal(15,2) not null ,
cod_moeda decimal(2,0) not null ,
val_jur_dia decimal(15,2) not null ,
taxa_juros decimal(12,8),
cod_formula decimal(2,0),
dat_emis date not null ,
dat_vencto_s_desc date not null ,
dat_vencto_c_desc date,
val_desc decimal(15,2),
dat_pgto date,
dat_proposta date,
cod_lote_pgto decimal(4,0) not null ,
num_docum_pgto decimal(8,0),
ies_lib_pgto_cap char(1) not null ,
ies_lib_pgto_sup char(1) not null ,
ies_baixada char(1) not null ,
ies_docum_pgto char(1),
ies_ap_impressa char(1) not null ,
ies_ap_contab char(1) not null ,
num_lote_transf decimal(3,0) not null ,
ies_dep_cred char(1) not null ,
data_receb date,
num_lote_rem_escr integer not null ,
num_lote_ret_escr integer not null ,
dat_rem date,
dat_ret date,
status_rem smallint not null ,
ies_form_pgto_escr char(3),
primary key (cod_empresa,num_ap,num_versao) constraint "informix".pk_ap_1
);
revoke all on "informix".ap from "public" as "informix";create index "informix".ak_ap_001 on "informix".ap (ies_versao_atual,
dat_pgto) using btree ;
create index "informix".ix7_ap on "informix".ap (cod_empresa,num_bl_awb,
num_versao) using btree ;
create index "informix".ix_ap_2 on "informix".ap (cod_empresa,
num_ap,ies_versao_atual) using btree ;
create index "informix".ix_ap_3 on "informix".ap (cod_empresa,
ies_versao_atual,dat_proposta,cod_lote_pgto) using btree
;
create index "informix".ix_ap_4 on "informix".ap (cod_empresa,
num_docum_pgto,ies_docum_pgto,ies_versao_atual) using btree
;
create index "informix".ix_ap_5 on "informix".ap (cod_empresa,
ies_versao_atual,dat_vencto_s_desc) using btree ;
create index "informix".ix_ap_6 on "informix".ap (cod_empresa,
num_nf) using btree ;
create index "informix".ix_ap_7 on "informix".ap (cod_empresa,
ies_versao_atual,cod_fornecedor,dat_vencto_s_desc) using
btree ;
create index "informix".ix_ap_dat_pgto on "informix".ap (dat_pgto)
using btree ;
==============================================
{ TABLE "informix".adn_adf row size = 13 number of columns = 4 index size = 26 }
create table "informix".adn_adf
(
cod_empresa char(2) not null ,
num_adn decimal(6,0) not null ,
num_adf decimal(6,0) not null ,
num_lote_transf decimal(3,0) not null ,
primary key (cod_empresa,num_adn,num_adf) constraint "informix".pk_adn_adf_1
);
revoke all on "informix".adn_adf from "public" as "informix";create index "informix".ix_adn_adf_2 on "informix".adn_adf (cod_empresa,
num_adf) using btree ;
==============================================
{ TABLE "informix".aviso_rec row size = 620 number of columns = 89 index size = 195 }
create table "informix".aviso_rec
(
cod_empresa char(2) not null ,
cod_empresa_estab char(2),
num_aviso_rec decimal(6,0) not null ,
num_seq decimal(3,0) not null ,
dat_inclusao_seq date,
ies_situa_ar char(1) not null ,
ies_incl_almox char(1) not null ,
ies_receb_fiscal char(1) not null ,
ies_liberacao_ar char(1) not null ,
ies_liberacao_cont char(1) not null ,
ies_liberacao_insp char(1) not null ,
ies_diverg_listada char(1) not null ,
ies_item_estoq char(1) not null ,
ies_controle_lote char(1) not null ,
cod_comprador decimal(3,0) not null ,
num_pedido decimal(6,0),
num_oc decimal(9,0),
cod_item char(15) not null ,
den_item char(50) not null ,
cod_cla_fisc char(10) not null ,
cod_unid_med_nf char(3) not null ,
pre_unit_nf decimal(17,6) not null ,
val_despesa_aces_i decimal(17,2) not
It's not the temp table. It is the view itself also the underlying tables need better index support for this view. Also maybe the data distributions are out of date or not detailed enough. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------
It's not the temp table. It is the view itself also the underlying tables need better index support for this view. Also maybe the data distributions are out of date or not detailed enough. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------
|
view attached
Garcia,
Below is the sqls. And the files are attached I forgot to attach before.
------------------------------
COUNTS
------------------------------
select count(*) count_adn_adf FROM adn_adf ; -- 4888
select count(*) count_ad_ap FROM ad_ap ; -- 212342
select count(*) count_ad_mestre FROM ad_mestre ; -- 215319
select count(*) count_ap FROM ap ; -- 292434
select count(*) count_aviso_rec FROM aviso_rec ; -- 210882
select count(*) count_dest_aviso_rec FROM dest_aviso_rec ; -- 222718
select count(*) count_dest_frete_sup FROM dest_frete_sup ; -- 70795
select count(*) count_fornecedor FROM fornecedor ; -- 12184
select count(*) count_frete_sup FROM frete_sup ; -- 79161
select count(*) count_lanc_cont_cap FROM lanc_cont_cap ; -- 380493
select count(*) count_nf_sup FROM nf_sup ; -- 127229
select count(*) count_plano_contas FROM plano_contas ; -- 20242
select count(*) count_tipo_despesa FROM tipo_despesa ; -- 5644
------------------------------Leandro Kohler
------------------------------
hi, forgot to ask wich Informix version ? regards ------------------------------ Garcia Benjamin ------------------------------
Right, About update statistics I use your script utils2_ak (dostats) every day. So, about data distributions, how can I improve that? ------------------------------ Leandro Kohler ------------------------------
Hei Garcia, Version 12.10.FC7WE ------------------------------ Leandro Kohler ------------------------------
Hello, This is not the most beautiful SQL but it should work. Does this view exist since 2008 like the spl? Do you have this problem after upgrading from informix? Have you ever tried to drop/create the view? What is the value of your OPTCOMPIND and IFX_FOLDVIEW? I did some tests with a 12.10FC15 version and the SQL plan seems ok (not perfect but ok ...) - obviously I don't have any datas so it distorts the result but your explain seems weird to me because a it does a sequential scan on the des_aviso_rec table and there is an index it should use (ix_num_conta_deb_desp) I strongly advise you not to use this view in an OLTP system, it is really not well done and moreover the maintenance must be impossible ... You should use a better structured SPL with a SELECT * from TABLE(spl_function(parameters)) call with a row_type in return. Sincerely Translated with www.DeepL.com/Translator (free version) ------------------------------ Garcia Benjamin ------------------------------
can you send the explain plan of this sql ? (this is the first select of the view ...) select 'SUP' ,x0.cod_empresa ,x0.num_ap ,x0.num_parcela ,x0.dat_emis ,x0.dat_vencto_s_desc ,TO_CHAR (x0.dat_vencto_s_desc,'%Y-%m') ,x0.dat_pgto ,TO_CHAR (x0.dat_pgto,'%Y-%m') ,x0.cod_fornecedor ,x8.raz_social ,x8.raz_social_reduz ,x0.num_duplicata ,x3.cod_tip_despesa ,x4.nom_tip_despesa ,x7.num_conta_deb_desp ,x3.num_ad ,x3.num_nf ,x3.dat_emis_nf ,x3.dat_rec_nf ,x3.observ ,x3.val_tot_nf ,(((CASE WHEN (x2.num_adf IS NOT NULL) THEN (((x0.val_nom_ap + eis_f_apvalores(x0.cod_empresa, x0.num_ap, x0.dat_proposta)) * (x3.val_tot_nf + eis_f_advalores(x0.cod_empresa, x3.num_ad))) / (select (x35.val_tot_nf + eis_f_advalores(x35.cod_empresa, x35.num_ad)) from "informix".ad_mestre x35 where ((x35.cod_empresa = x0.cod_empresa) AND (x35.num_ad = x2.num_adf)))) WHEN (x0.val_nom_ap <= (x3.val_tot_nf + eis_f_advalores(x0.cod_empresa, x3.num_ad))) THEN (x0.val_nom_ap + eis_f_apvalores(x0.cod_empresa, x0.num_ap, x0.dat_proposta)) WHEN (x0.val_nom_ap > (x3.val_tot_nf + eis_f_advalores(x0.cod_empresa, x3.num_ad))) THEN (NVL ((x0.val_nom_ap + eis_f_apvalores(x0.cod_empresa, x0.num_ap, x0.dat_proposta)),(x3.val_tot_nf + eis_f_advalores(x0.cod_empresa, x3.num_ad)))*((x3.val_tot_nf + eis_f_advalores(x0.cod_empresa, x3.num_ad)) / x0.val_nom_ap)) WHEN (x3.val_tot_nf != eis_f_get_total_ad_ap(x0.cod_empresa, x0.num_ap, x3.cod_tip_despesa)) THEN (NVL (x0.val_nom_ap,(x3.val_tot_nf + eis_f_advalores(x3.cod_empresa, x3.num_ad)))* ((x3.val_tot_nf + eis_f_advalores(x0.cod_empresa, x3.num_ad)) / eis_f_get_total_ad_ap(x3.cod_empresa, x0.num_ap, x3.cod_tip_despesa))) END * (x6.val_contabil_item / x3.val_tot_nf)) * x7.pct_particip_comp) / 100.) ,x9.num_conta ,x9.den_conta ,eis_f_get_conta_nivel(x9.num_conta,1) ,eis_f_get_conta_nivel(x9.num_conta,2) ,eis_f_get_conta_nivel(x9.num_conta,3) ,eis_f_get_conta_nivel(x9.num_conta,4) ,eis_f_get_conta_nivel(x9.num_conta,5) ,eis_f_get_den_conta_nivel(x9.num_conta,1,x9.cod_empresa) ,eis_f_get_den_conta_nivel(x9.num_conta,2,x9.cod_empresa) ,eis_f_get_den_conta_nivel(x9.num_conta,3,x9.cod_empresa) ,eis_f_get_den_conta_nivel(x9.num_conta,4,x9.cod_empresa) ,eis_f_get_den_conta_nivel(x9.num_conta,5,x9.cod_empresa) ,CASE WHEN (x0.dat_pgto IS NULL) THEN 'N' ELSE 'S' END from ( ( ( ( ( ( ( (("informix".ap x0 join "informix".ad_ap x1 on ((x1.cod_empresa = x0.cod_empresa) AND (x1.num_ap = x0.num_ap))) left join "informix".adn_adf x2 on ((x1.cod_empresa = x2.cod_empresa) AND (x1.num_ad = x2.num_adf))) join "informix".ad_mestre x3 on ((x0.cod_empresa = x3.cod_empresa) AND (NVL (x2.num_adn, x1.num_ad)= x3.num_ad))) join "informix".tipo_despesa x4 on ((x4.cod_empresa = x3.cod_empresa) AND (x4.cod_tip_despesa = x3.cod_tip_despesa))) join "informix".nf_sup x5 on (((((x5.cod_empresa = x3.cod_empresa) AND (x5.num_nf = to_number2(x3.num_nf))) AND (x5.ser_nf = x3.ser_nf)) AND (x5.ssr_nf = x3.ssr_nf)) AND (x5.cod_fornecedor = x0.cod_fornecedor)) ) join "informix".aviso_rec x6 on ((x5.cod_empresa = x6.cod_empresa) AND (x5.num_aviso_rec = x6.num_aviso_rec))) join "informix".dest_aviso_rec x7 on (((x7.cod_empresa = x6.cod_empresa) AND (x7.num_aviso_rec = x6.num_aviso_rec)) AND (x7.num_seq = x6.num_seq))) join "informix".fornecedor x8 on (x8.cod_fornecedor = x0.cod_fornecedor)) left join "informix".plano_contas x9 on ((x9.cod_empresa = CASE WHEN (x9.ies_tip_conta = 9) THEN x0.cod_empresa ELSE '99' END) AND ((x9.num_conta = x7.num_conta_deb_desp) OR (x9.num_conta_reduz = x7.num_conta_deb_desp)))) where (x0.ies_versao_atual = 'S') and ((dat_pgto >= '03/01/2022' and dat_pgto <= '03/30/2022' ) and num_conta_deb_desp IN( '8.1000.04.09','8.1100.04.10','10000409','11000410' )) ------------------------------ Garcia Benjamin ------------------------------
This my explain plan if you want to compare ... QUERY: (OPTIMIZATION TIMESTAMP: 07-24-2022 14:38:07) ------ select 'SUP' ,x0.cod_empresa ,x0.num_ap ,x0.num_parcela ,x0.dat_emis ,x0.dat_vencto_s_desc ,TO_CHAR (x0.dat_vencto_s_desc,'%Y-%m') ,x0.dat_pgto ,TO_CHAR (x0.dat_pgto,'%Y-%m') ,x0.cod_fornecedor ,x8.raz_social ,x8.raz_social_reduz ,x0.num_duplicata ,x3.cod_tip_despesa ,x4.nom_tip_despesa ,x7.num_conta_deb_desp ,x3.num_ad ,x3.num_nf ,x3.dat_emis_nf ,x3.dat_rec_nf ,x3.observ ,x3.val_tot_nf ,(((CASE WHEN (x2.num_adf IS NOT NULL) THEN (((x0.val_nom_ap + eis_f_apvalores(x0.cod_empresa, x0.num_ap, x0.dat_proposta)) * (x3.val_tot_nf + eis_f_advalores(x0.cod_empresa, x3.num_ad))) / (select (x35.val_tot_nf + eis_f_advalores(x35.cod_empresa, x35.num_ad)) from "informix".ad_mestre x35 where ((x35.cod_empresa = x0.cod_empresa) AND (x35.num_ad = x2.num_adf)))) WHEN (x0.val_nom_ap <= (x3.val_tot_nf + eis_f_advalores(x0.cod_empresa, x3.num_ad))) THEN (x0.val_nom_ap + eis_f_apvalores(x0.cod_empresa, x0.num_ap, x0.dat_proposta)) WHEN (x0.val_nom_ap > (x3.val_tot_nf + eis_f_advalores(x0.cod_empresa, x3.num_ad))) THEN (NVL ((x0.val_nom_ap + eis_f_apvalores(x0.cod_empresa, x0.num_ap, x0.dat_proposta)),(x3.val_tot_nf + eis_f_advalores(x0.cod_empresa, x3.num_ad)))*((x3.val_tot_nf + eis_f_advalores(x0.cod_empresa, x3.num_ad)) / x0.val_nom_ap)) WHEN (x3.val_tot_nf != eis_f_get_total_ad_ap(x0.cod_empresa, x0.num_ap, x3.cod_tip_despesa)) THEN (NVL (x0.val_nom_ap,(x3.val_tot_nf + eis_f_advalores(x3.cod_empresa, x3.num_ad)))* ((x3.val_tot_nf + eis_f_advalores(x0.cod_empresa, x3.num_ad)) / eis_f_get_total_ad_ap(x3.cod_empresa, x0.num_ap, x3.cod_tip_despesa))) END * (x6.val_contabil_item / x3.val_tot_nf)) * x7.pct_particip_comp) / 100.) ,x9.num_conta ,x9.den_conta ,eis_f_get_conta_nivel(x9.num_conta,1) ,eis_f_get_conta_nivel(x9.num_conta,2) ,eis_f_get_conta_nivel(x9.num_conta,3) ,eis_f_get_conta_nivel(x9.num_conta,4) ,eis_f_get_conta_nivel(x9.num_conta,5) ,eis_f_get_den_conta_nivel(x9.num_conta,1,x9.cod_empresa) ,eis_f_get_den_conta_nivel(x9.num_conta,2,x9.cod_empresa) ,eis_f_get_den_conta_nivel(x9.num_conta,3,x9.cod_empresa) ,eis_f_get_den_conta_nivel(x9.num_conta,4,x9.cod_empresa) ,eis_f_get_den_conta_nivel(x9.num_conta,5,x9.cod_empresa) ,CASE WHEN (x0.dat_pgto IS NULL) THEN 'N' ELSE 'S' END from ( ( ( ( ( ( ( (("informix".ap x0 join "informix".ad_ap x1 on ((x1.cod_empresa = x0.cod_empresa) AND (x1.num_ap = x0.num_ap))) left join "informix".adn_adf x2 on ((x1.cod_empresa = x2.cod_empresa) AND (x1.num_ad = x2.num_adf))) join "informix".ad_mestre x3 on ((x0.cod_empresa = x3.cod_empresa) AND (NVL (x2.num_adn, x1.num_ad)= x3.num_ad))) join "informix".tipo_despesa x4 on ((x4.cod_empresa = x3.cod_empresa) AND (x4.cod_tip_despesa = x3.cod_tip_despesa))) join "informix".nf_sup x5 on (((((x5.cod_empresa = x3.cod_empresa) AND (x5.num_nf = to_number2(x3.num_nf))) AND (x5.ser_nf = x3.ser_nf)) AND (x5.ssr_nf = x3.ssr_nf)) AND (x5.cod_fornecedor = x0.cod_fornecedor)) ) join "informix".aviso_rec x6 on ((x5.cod_empresa = x6.cod_empresa) AND (x5.num_aviso_rec = x6.num_aviso_rec))) join "informix".dest_aviso_rec x7 on (((x7.cod_empresa = x6.cod_empresa) AND (x7.num_aviso_rec = x6.num_aviso_rec)) AND (x7.num_seq = x6.num_seq))) join "informix".fornecedor x8 on (x8.cod_fornecedor = x0.cod_fornecedor)) left join "informix".plano_contas x9 on ((x9.cod_empresa = CASE WHEN (x9.ies_tip_conta = 9) THEN x0.cod_empresa ELSE '99' END) AND ((x9.num_conta = x7.num_conta_deb_desp) OR (x9.num_conta_reduz = x7.num_conta_deb_desp)))) where (x0.ies_versao_atual = 'S') and ((dat_pgto >= '01/06/2022' and dat_pgto <= '30/06/2022' ) and num_conta_deb_desp IN( '8.1000.04.09','8.1100.04.10','10000409','11000410' )) Estimated Cost: 5 Estimated # of Rows Returned: 1 1) informix.x7: INDEX PATH (1) Index Name: informix.ix_num_conta_deb_desp Index Keys: num_conta_deb_desp (Serial, fragments: ALL) Lower Index Filter: informix.x7.num_conta_deb_desp = '8.1000.04.09' (2) Index Name: informix.ix_num_conta_deb_desp Index Keys: num_conta_deb_desp (Serial, fragments: ALL) Lower Index Filter: informix.x7.num_conta_deb_desp = '8.1100.04.10' (3) Index Name: informix.ix_num_conta_deb_desp Index Keys: num_conta_deb_desp (Serial, fragments: ALL) Lower Index Filter: informix.x7.num_conta_deb_desp = '10000409' (4) Index Name: informix.ix_num_conta_deb_desp Index Keys: num_conta_deb_desp (Serial, fragments: ALL) Lower Index Filter: informix.x7.num_conta_deb_desp = '11000410' 2) informix.x0: INDEX PATH Filters: (informix.x0.dat_pgto <= 30.06.2022 AND informix.x0.dat_pgto >= 01.06.2022 ) (1) Index Name: informix.ix_ap_5 Index Keys: cod_empresa ies_versao_atual dat_vencto_s_desc (Serial, fragments: ALL) Lower Index Filter: (informix.x0.cod_empresa = informix.x7.cod_empresa AND informix.x0.ies_versao_atual = 'S' ) 3) informix.x1: INDEX PATH (1) Index Name: informix.ix_ad_ap_1 Index Keys: cod_empresa num_ad num_ap (Key-Only) (Serial, fragments: ALL) Lower Index Filter: informix.x1.cod_empresa = informix.x7.cod_empresa ON-Filters:(informix.x1.cod_empresa = informix.x0.cod_empresa AND informix.x1.num_ap = informix.x0.num_ap ) NESTED LOOP JOIN 4) informix.x2: INDEX PATH (1) Index Name: informix. 109_179 Index Keys: cod_empresa num_adn num_adf (Key-Only) (Serial, fragments: ALL) Lower Index Filter: informix.x1.cod_empresa = informix.x2.cod_empresa ON-Filters:(informix.x1.cod_empresa = informix.x2.cod_empresa AND informix.x1.num_ad = informix.x2.num_adf ) NESTED LOOP JOIN(LEFT OUTER JOIN) 5) informix.x3: INDEX PATH (1) Index Name: informix. 106_132 Index Keys: cod_empresa num_ad (Serial, fragments: ALL) Lower Index Filter: informix.x0.cod_empresa = informix.x3.cod_empresa ON-Filters:(informix.x0.cod_empresa = informix.x3.cod_empresa AND informix.x3.num_ad = NVL (informix.x2.num_adn , informix.x1.num_ad ) ) NESTED LOOP JOIN 6) informix.x4: INDEX PATH (1) Index Name: informix. 112_277 Index Keys: cod_empresa cod_tip_despesa (Serial, fragments: ALL) Lower Index Filter: informix.x0.cod_empresa = informix.x4.cod_empresa ON-Filters:(informix.x4.cod_empresa = informix.x3.cod_empresa AND informix.x4.cod_tip_despesa = informix.x3.cod_tip_despesa ) NESTED LOOP JOIN 7) informix.x5: INDEX PATH (1) Index Name: informix.ix_nf_sup_g2 Index Keys: cod_empresa cod_fornecedor ies_incl_cap nom_resp_aceite_er (Serial, fragments: ALL) Lower Index Filter: (informix.x5.cod_fornecedor = informix.x0.cod_fornecedor AND informix.x0.cod_empresa = informix.x5.cod_empresa ) ON-Filters:((((informix.x5.cod_empresa = informix.x3.cod_empresa AND informix.x5.num_nf = informix.to_number2(informix.x3.num_nf )) AND informix.x5.ser_nf = informix.x3.ser_nf ) AND informix.x5.ssr_nf = informix.x3.ssr_nf ) AND informix.x5.cod_fornecedor = informix.x0.cod_fornecedor ) NESTED LOOP JOIN 8) informix.x6: INDEX PATH (1) Index Name: informix.ak_aviso_rec_01 Index Keys: cod_empresa cod_item (Serial, fragments: ALL) Lower Index Filter: informix.x0.cod_empresa = informix.x6.cod_empresa ON-Filters:(informix.x5.cod_empresa = informix.x6.cod_empresa AND informix.x5.num_aviso_rec = informix.x6.num_aviso_rec ) NESTED LOOP JOIN ON-Filters:((informix.x7.cod_empresa = informix.x6.cod_empresa AND informix.x7.num_aviso_rec = informix.x6.num_aviso_rec ) AND informix.x7.num_seq = informix.x6.num_seq ) Other Join Filters: (informix.x5.num_aviso_rec = informix.x7.num_aviso_rec AND informix.x5.cod_empresa = informix.x7.cod_empresa ) NESTED LOOP JOIN 9) informix.x8: INDEX PATH (1) Index Name: informix. 102_47 Index Keys: cod_fornecedor (Serial, fragments: ALL) Lower Index Filter: informix.x8.cod_fornecedor = informix.x0.cod_fornecedor ON-Filters:informix.x8.cod_fornecedor = informix.x0.cod_fornecedor NESTED LOOP JOIN 10) informix.x9: SEQUENTIAL SCAN ON-Filters:(informix.x9.cod_empresa = CASE WHEN informix.x9.ies_tip_conta = 9 THEN informix.x0.cod_empresa ELSE '99' ENDAND (informix.x9.num_conta = informix.x7.num_conta_deb_desp OR informix.x9.num_conta_reduz = informix.x7.num_conta_deb_desp ) ) NESTED LOOP JOIN(LEFT OUTER JOIN) Subquery: --------- Estimated Cost: 1 Estimated # of Rows Returned: 1 1) informix.x35: INDEX PATH (1) Index Name: informix. 106_132 Index Keys: cod_empresa num_ad (Serial, fragments: ALL) Lower Index Filter: (informix.x35.cod_empresa = informix.x0.cod_empresa AND informix.x35.num_ad = informix.x2.num_adf ) UDRs in query: -------------- UDR id : 536 UDR name: eis_f_get_den_conta_nivel UDR id : 536 UDR name: eis_f_get_den_conta_nivel UDR id : 536 UDR name: eis_f_get_den_conta_nivel UDR id : 536 UDR name: eis_f_get_den_conta_nivel UDR id : 536 UDR name: eis_f_get_den_conta_nivel UDR id : 534 UDR name: eis_f_get_conta_nivel UDR id : 534 UDR name: eis_f_get_conta_nivel UDR id : 534 UDR name: eis_f_get_conta_nivel UDR id : 534 UDR name: eis_f_get_conta_nivel UDR id : 534 UDR name: eis_f_get_conta_nivel UDR id : 535 UDR name: eis_f_get_total_ad_ap UDR id : 533 UDR name: eis_f_advalores UDR id : 533 UDR name: eis_f_advalores UDR id : 535 UDR name: eis_f_get_total_ad_ap UDR id : 533 UDR name: eis_f_advalores UDR id : 533 UDR name: eis_f_advalores UDR id : 537 UDR name: eis_f_apvalores UDR id : 533 UDR name: eis_f_advalores UDR id : 537 UDR name: eis_f_apvalores UDR id : 533 UDR name: eis_f_advalores UDR id : 533 UDR name: eis_f_advalores UDR id : 533 UDR name: eis_f_advalores UDR id : 537 UDR name: eis_f_apvalores UDR id : 532 UDR name: to_number2 UDRs in query: -------------- UDR id : 536 UDR name: eis_f_get_den_conta_nivel UDR id : 536 UDR name: eis_f_get_den_conta_nivel UDR id : 536 UDR name: eis_f_get_den_conta_nivel UDR id : 536 UDR name: eis_f_get_den_conta_nivel UDR id : 536 UDR name: eis_f_get_den_conta_nivel UDR id : 534 UDR name: eis_f_get_conta_nivel UDR id : 534 UDR name: eis_f_get_conta_nivel UDR id : 534 UDR name: eis_f_get_conta_nivel UDR id : 534 UDR name: eis_f_get_conta_nivel UDR id : 534 UDR name: eis_f_get_conta_nivel UDR id : 535 UDR name: eis_f_get_total_ad_ap UDR id : 533 UDR name: eis_f_advalores UDR id : 533 UDR name: eis_f_advalores UDR id : 535 UDR name: eis_f_get_total_ad_ap UDR id : 533 UDR name: eis_f_advalores UDR id : 533 UDR name: eis_f_advalores UDR id : 537 UDR name: eis_f_apvalores UDR id : 533 UDR name: eis_f_advalores UDR id : 537 UDR name: eis_f_apvalores UDR id : 533 UDR name: eis_f_advalores UDR id : 533 UDR name: eis_f_advalores UDR id : 533 UDR name: eis_f_advalores UDR id : 537 UDR name: eis_f_apvalores UDR id : 532 UDR name: to_number2 Query statistics: ----------------- Table map : ------------------------------ Internal name Table name ------------------------------ t1 x7 t2 x0 t3 x1 t4 x2 t5 x3 t6 x4 t7 x5 t8 x6 t9 x8 t10 x9 type table rows_prod est_rows rows_scan time est_cost ------------------------------ scan t1 0 1 0 00:00.00 2 type table rows_prod est_rows rows_scan time est_cost ------------------------------ scan t2 0 1 0 00:00.00 0 type table rows_prod est_rows rows_scan time est_cost ------------------------------ scan t3 0 1 0 00:00.00 0 type rows_prod est_rows time est_cost ------------------------------ nljoin 0 1 00:00.00 0 type table rows_prod est_rows rows_scan time est_cost ------------------------------ scan t4 0 1 0 00:00.00 0 type rows_prod est_rows time est_cost ------------------------------ nljoin 0 1 00:00.00 1 type table rows_prod est_rows rows_scan time est_cost ------------------------------ scan t5 0 1 0 00:00.00 0 type rows_prod est_rows time est_cost ------------------------------ nljoin 0 1 00:00.00 1 type table rows_prod est_rows rows_scan time est_cost ------------------------------ scan t6 0 1 0 00:00.00 0 type rows_prod est_rows time est_cost ------------------------------ nljoin 0 1 00:00.00 1 type table rows_prod est_rows rows_scan time est_cost ------------------------------ scan t7 0 1 0 00:00.00 0 type rows_prod est_rows time est_cost ------------------------------ nljoin 0 1 00:00.00 1 type table rows_prod est_rows rows_scan time est_cost ------------------------------ scan t8 0 1 0 00:00.00 0 type rows_prod est_rows time est_cost ------------------------------ nljoin 0 1 00:00.00 1 type rows_prod est_rows time est_cost ------------------------------ nljoin 0 1 00:00.00 4 type table rows_prod est_rows rows_scan time est_cost ------------------------------ scan t9 0 1 0 00:00.00 0 type rows_prod est_rows time est_cost ------------------------------ nljoin 0 1 00:00.00 4 type table rows_prod est_rows rows_scan time est_cost ------------------------------ scan t10 0 1 0 00:00.00 2 type rows_prod est_rows time est_cost ------------------------------ nljoin 0 1 00:00.00 6 ------------------------------ Garcia Benjamin