Hi all!

I´m facing a curious performance drop in a query using CTE's. Don't know if its related to CTE or to the join clause, so i will try to give more details (examples below):

I have a block which uses CTE´s inside it.

I started declaring the alias CTE_1, which queries a real table, grouping its records (it reads 6738 records, and returns 285 records grouped)

Then i declare a second alias CTE_2, which just reads CTE_1 and transform its rows in columns (1 column for each month). This part returns about 40 rows.

Up to this point, the query runs in about 2 seconds (see EXAMPLE 1 below)

Next step i insert a third alias, CTE_3, which just reads CTE_2, grouping all its records in a single row. After inserting CTE_3 and joining it in the final SELECT, the performance drops drastically (the block takes about 1 minute to execute - see EXAMPLE 2 below).

Is this performance drop expected in this case?

Regards,

Fabiano


EXAMPLE 1

execute block (
p_data date = :p_data)
returns (
vendedor_id integer,
vendedor_nome varchar(100),
vendas_mes_0 numeric(11,2),
vendas_mes_1 numeric(11,2),
variacao_mes_1 numeric(11,2),
vendas_mes_2 numeric(11,2),
variacao_mes_2 numeric(11,2),
vendas_mes_3 numeric(11,2),
variacao_mes_3 numeric(11,2),
vendas_mes_4 numeric(11,2),
variacao_mes_4 numeric(11,2),
vendas_mes_5 numeric(11,2),
variacao_mes_5 numeric(11,2),
vendas_mes_6 numeric(11,2),
variacao_mes_6 numeric(11,2),
vendas_mes_7 numeric(11,2),
variacao_mes_7 numeric(11,2),
vendas_mes_8 numeric(11,2),
variacao_mes_8 numeric(11,2),
vendas_mes_9 numeric(11,2),
variacao_mes_9 numeric(11,2),
vendas_mes_10 numeric(11,2),
variacao_mes_10 numeric(11,2),
vendas_mes_11 numeric(11,2),
variacao_mes_11 numeric(11,2),
vendas_mes_12 numeric(11,2),
variacao_mes_12 numeric(11,2))
as
declare variable v_data_inicial date;
declare variable v_data_final date;
declare variable v_mes_delta integer;
begin
v_data_final = dateadd(1 month to p_data - extract(day from p_data) + 1) - 1;
v_data_inicial = dateadd(-1 month to dateadd(-1 year to v_data_final) + 1);
v_mes_delta = extract(year from v_data_inicial) * 12 + extract(month from v_data_inicial);

for with
/*
* Resumo de vendas por vendedor e mes
*/
cte_1 (
vendedor_id,
vendas,
mes_relativo) as
(
select
a1.conta_id_vendedor,
sum(a2.valor_contabil * a1.es * -1),
extract(year from a1.data_fiscal) * 12 + extract(month from a1.data_fiscal) - :v_mes_delta
from
v3$notas_fiscais a1
inner join v3$notas_fiscais_itens a2 on a2.nota_fiscal_id = a1.nota_fiscal_id
inner join opr2 a3 on a3.numopr2 = a2.operacao_fiscal_id
where
(a1.data_fiscal between :v_data_inicial and :v_data_final)
and (a1.cancelada = 'N')
and (a3.faturamento = 'S')
and ((a3.tipo = 'S' and a3.devolucao = 'N') or (a3.tipo = 'E' and a3.devolucao = 'S'))
group by
a1.conta_id_vendedor,
extract(year from a1.data_fiscal),
extract(month from a1.data_fiscal)),
/*
* Vendas agrupadas em colunas
*/
cte_2 (
vendedor_id,
vendas_mes_0,
vendas_mes_1,
vendas_mes_2,
vendas_mes_3,
vendas_mes_4,
vendas_mes_5,
vendas_mes_6,
vendas_mes_7,
vendas_mes_8,
vendas_mes_9,
vendas_mes_10,
vendas_mes_11,
vendas_mes_12) as
( select
b1.vendedor_id,
coalesce(sum(case when b1.mes_relativo = 0 then b1.vendas else 0 end), 0),
coalesce(sum(case when b1.mes_relativo = 1 then b1.vendas else 0 end), 0),
coalesce(sum(case when b1.mes_relativo = 2 then b1.vendas else 0 end), 0),
coalesce(sum(case when b1.mes_relativo = 3 then b1.vendas else 0 end), 0),
coalesce(sum(case when b1.mes_relativo = 4 then b1.vendas else 0 end), 0),
coalesce(sum(case when b1.mes_relativo = 5 then b1.vendas else 0 end), 0),
coalesce(sum(case when b1.mes_relativo = 6 then b1.vendas else 0 end), 0),
coalesce(sum(case when b1.mes_relativo = 7 then b1.vendas else 0 end), 0),
coalesce(sum(case when b1.mes_relativo = 8 then b1.vendas else 0 end), 0),
coalesce(sum(case when b1.mes_relativo = 9 then b1.vendas else 0 end), 0),
coalesce(sum(case when b1.mes_relativo = 10 then b1.vendas else 0 end), 0),
coalesce(sum(case when b1.mes_relativo = 11 then b1.vendas else 0 end), 0),
coalesce(sum(case when b1.mes_relativo = 12 then b1.vendas else 0 end), 0)
from
cte_1 b1
group by
b1.vendedor_id)
select
a.numcad1,
coalesce(a.apelido, a.nome) as nome,
b.vendas_mes_0,
b.vendas_mes_1,
(b.vendas_mes_1 / nullif(b.vendas_mes_0, 0) - 1) * 100,
b.vendas_mes_2,
(b.vendas_mes_2 / nullif(b.vendas_mes_1, 0) - 1) * 100,
b.vendas_mes_3,
(b.vendas_mes_3 / nullif(b.vendas_mes_2, 0) - 1) * 100,
b.vendas_mes_4,
(b.vendas_mes_4 / nullif(b.vendas_mes_3, 0) - 1) * 100,
b.vendas_mes_5,
(b.vendas_mes_5 / nullif(b.vendas_mes_4, 0) - 1) * 100,
b.vendas_mes_6,
(b.vendas_mes_6 / nullif(b.vendas_mes_5, 0) - 1) * 100,
b.vendas_mes_7,
(b.vendas_mes_7 / nullif(b.vendas_mes_6, 0) - 1) * 100,
b.vendas_mes_8,
(b.vendas_mes_8 / nullif(b.vendas_mes_7, 0) - 1) * 100,
b.vendas_mes_9,
(b.vendas_mes_9 / nullif(b.vendas_mes_8, 0) - 1) * 100,
b.vendas_mes_10,
(b.vendas_mes_10 / nullif(b.vendas_mes_9, 0) - 1) * 100,
b.vendas_mes_11,
(b.vendas_mes_11 / nullif(b.vendas_mes_10, 0) - 1) * 100,
b.vendas_mes_12,
(b.vendas_mes_12 / nullif(b.vendas_mes_11, 0) - 1) * 100
from
cad1 a
left join cte_2 b on b.vendedor_id = a.numcad1
where
a.representante = 'S'
and a.ativo = 'S'
and a.bloqueado = 'N'
order by
coalesce(a.apelido, a.nome)
into
:vendedor_id,
:vendedor_nome,
:vendas_mes_0,
:vendas_mes_1,
:variacao_mes_1,
:vendas_mes_2,
:variacao_mes_2,
:vendas_mes_3,
:variacao_mes_3,
:vendas_mes_4,
:variacao_mes_4,
:vendas_mes_5,
:variacao_mes_5,
:vendas_mes_6,
:variacao_mes_6,
:vendas_mes_7,
:variacao_mes_7,
:vendas_mes_8,
:variacao_mes_8,
:vendas_mes_9,
:variacao_mes_9,
:vendas_mes_10,
:variacao_mes_10,
:vendas_mes_11,
:variacao_mes_11,
:vendas_mes_12,
:variacao_mes_12
do
suspend;
end

Plan
PLAN SORT (JOIN (A INDEX (XIE4CAD1)SORT (JOIN (JOIN (B B1 A1 INDEX (R_931), B B1 A2 INDEX (R_906)), B B1 A3 INDEX (XPKOPR2)))))



EXAMPLE 2

execute block (
p_data date = :p_data)
returns (
vendedor_id integer,
vendedor_nome varchar(100),
vendas_mes_0 numeric(11,2),
vendas_mes_1 numeric(11,2),
variacao_mes_1 numeric(11,2),
vendas_mes_2 numeric(11,2),
variacao_mes_2 numeric(11,2),
vendas_mes_3 numeric(11,2),
variacao_mes_3 numeric(11,2),
vendas_mes_4 numeric(11,2),
variacao_mes_4 numeric(11,2),
vendas_mes_5 numeric(11,2),
variacao_mes_5 numeric(11,2),
vendas_mes_6 numeric(11,2),
variacao_mes_6 numeric(11,2),
vendas_mes_7 numeric(11,2),
variacao_mes_7 numeric(11,2),
vendas_mes_8 numeric(11,2),
variacao_mes_8 numeric(11,2),
vendas_mes_9 numeric(11,2),
variacao_mes_9 numeric(11,2),
vendas_mes_10 numeric(11,2),
variacao_mes_10 numeric(11,2),
vendas_mes_11 numeric(11,2),
variacao_mes_11 numeric(11,2),
vendas_mes_12 numeric(11,2),
variacao_mes_12 numeric(11,2))
as
declare variable v_data_inicial date;
declare variable v_data_final date;
declare variable v_mes_delta integer;
begin
v_data_final = dateadd(1 month to p_data - extract(day from p_data) + 1) - 1;
v_data_inicial = dateadd(-1 month to dateadd(-1 year to v_data_final) + 1);
v_mes_delta = extract(year from v_data_inicial) * 12 + extract(month from v_data_inicial);

for with
/*
* Resumo de vendas por vendedor e mes
*/
cte_1 (
vendedor_id,
vendas,
mes_relativo) as
(
select
a1.conta_id_vendedor,
sum(a2.valor_contabil * a1.es * -1),
extract(year from a1.data_fiscal) * 12 + extract(month from a1.data_fiscal) - :v_mes_delta
from
v3$notas_fiscais a1
inner join v3$notas_fiscais_itens a2 on a2.nota_fiscal_id = a1.nota_fiscal_id
inner join opr2 a3 on a3.numopr2 = a2.operacao_fiscal_id
where
(a1.data_fiscal between :v_data_inicial and :v_data_final)
and (a1.es = -1)
and (a1.cancelada = 'N')
and (a3.faturamento = 'S')
and ((a3.tipo = 'S' and a3.devolucao = 'N') or (a3.tipo = 'E' and a3.devolucao = 'S'))
group by
a1.conta_id_vendedor,
extract(year from a1.data_fiscal),
extract(month from a1.data_fiscal)),
/*
* Vendas agrupadas em colunas
*/
cte_2 (
vendedor_id,
vendas_mes_0,
vendas_mes_1,
vendas_mes_2,
vendas_mes_3,
vendas_mes_4,
vendas_mes_5,
vendas_mes_6,
vendas_mes_7,
vendas_mes_8,
vendas_mes_9,
vendas_mes_10,
vendas_mes_11,
vendas_mes_12) as
( select
b1.vendedor_id,
coalesce(sum(case when b1.mes_relativo = 0 then b1.vendas else 0 end), 0),
coalesce(sum(case when b1.mes_relativo = 1 then b1.vendas else 0 end), 0),
coalesce(sum(case when b1.mes_relativo = 2 then b1.vendas else 0 end), 0),
coalesce(sum(case when b1.mes_relativo = 3 then b1.vendas else 0 end), 0),
coalesce(sum(case when b1.mes_relativo = 4 then b1.vendas else 0 end), 0),
coalesce(sum(case when b1.mes_relativo = 5 then b1.vendas else 0 end), 0),
coalesce(sum(case when b1.mes_relativo = 6 then b1.vendas else 0 end), 0),
coalesce(sum(case when b1.mes_relativo = 7 then b1.vendas else 0 end), 0),
coalesce(sum(case when b1.mes_relativo = 8 then b1.vendas else 0 end), 0),
coalesce(sum(case when b1.mes_relativo = 9 then b1.vendas else 0 end), 0),
coalesce(sum(case when b1.mes_relativo = 10 then b1.vendas else 0 end), 0),
coalesce(sum(case when b1.mes_relativo = 11 then b1.vendas else 0 end), 0),
coalesce(sum(case when b1.mes_relativo = 12 then b1.vendas else 0 end), 0)
from
cte_1 b1
group by
b1.vendedor_id),
cte_3 (
vendas_mes_0,
vendas_mes_1,
vendas_mes_2,
vendas_mes_3,
vendas_mes_4,
vendas_mes_5,
vendas_mes_6,
vendas_mes_7,
vendas_mes_8,
vendas_mes_9,
vendas_mes_10,
vendas_mes_11,
vendas_mes_12) as
( select
coalesce(sum(case when b1.mes_relativo = 0 then b1.vendas else 0 end), 0),
coalesce(sum(case when b1.mes_relativo = 1 then b1.vendas else 0 end), 0),
coalesce(sum(case when b1.mes_relativo = 2 then b1.vendas else 0 end), 0),
coalesce(sum(case when b1.mes_relativo = 3 then b1.vendas else 0 end), 0),
coalesce(sum(case when b1.mes_relativo = 4 then b1.vendas else 0 end), 0),
coalesce(sum(case when b1.mes_relativo = 5 then b1.vendas else 0 end), 0),
coalesce(sum(case when b1.mes_relativo = 6 then b1.vendas else 0 end), 0),
coalesce(sum(case when b1.mes_relativo = 7 then b1.vendas else 0 end), 0),
coalesce(sum(case when b1.mes_relativo = 8 then b1.vendas else 0 end), 0),
coalesce(sum(case when b1.mes_relativo = 9 then b1.vendas else 0 end), 0),
coalesce(sum(case when b1.mes_relativo = 10 then b1.vendas else 0 end), 0),
coalesce(sum(case when b1.mes_relativo = 11 then b1.vendas else 0 end), 0),
coalesce(sum(case when b1.mes_relativo = 12 then b1.vendas else 0 end), 0)
from
cte_1 b1)
select
a.numcad1,
coalesce(a.apelido, a.nome) as nome,
b.vendas_mes_0,
b.vendas_mes_1,
(b.vendas_mes_1 / nullif(b.vendas_mes_0, 0) - 1) * 100,
b.vendas_mes_2,
(b.vendas_mes_2 / nullif(b.vendas_mes_1, 0) - 1) * 100,
b.vendas_mes_3,
(b.vendas_mes_3 / nullif(b.vendas_mes_2, 0) - 1) * 100,
b.vendas_mes_4,
(b.vendas_mes_4 / nullif(b.vendas_mes_3, 0) - 1) * 100,
b.vendas_mes_5,
(b.vendas_mes_5 / nullif(b.vendas_mes_4, 0) - 1) * 100,
b.vendas_mes_6,
(b.vendas_mes_6 / nullif(b.vendas_mes_5, 0) - 1) * 100,
b.vendas_mes_7,
(b.vendas_mes_7 / nullif(b.vendas_mes_6, 0) - 1) * 100,
b.vendas_mes_8,
(b.vendas_mes_8 / nullif(b.vendas_mes_7, 0) - 1) * 100,
b.vendas_mes_9,
(b.vendas_mes_9 / nullif(b.vendas_mes_8, 0) - 1) * 100,
b.vendas_mes_10,
(b.vendas_mes_10 / nullif(b.vendas_mes_9, 0) - 1) * 100,
b.vendas_mes_11,
(b.vendas_mes_11 / nullif(b.vendas_mes_10, 0) - 1) * 100,
b.vendas_mes_12,
(b.vendas_mes_12 / nullif(b.vendas_mes_11, 0) - 1) * 100
from
cad1 a
left join cte_2 b on b.vendedor_id = a.numcad1
inner join cte_3 c on 1 = 1
where
(a.representante = 'S')
and (a.ativo = 'S')
and (a.bloqueado = 'N')
order by
coalesce(a.apelido, a.nome)
into
:vendedor_id,
:vendedor_nome,
:vendas_mes_0,
:vendas_mes_1,
:variacao_mes_1,
:vendas_mes_2,
:variacao_mes_2,
:vendas_mes_3,
:variacao_mes_3,
:vendas_mes_4,
:variacao_mes_4,
:vendas_mes_5,
:variacao_mes_5,
:vendas_mes_6,
:variacao_mes_6,
:vendas_mes_7,
:variacao_mes_7,
:vendas_mes_8,
:variacao_mes_8,
:vendas_mes_9,
:variacao_mes_9,
:vendas_mes_10,
:variacao_mes_10,
:vendas_mes_11,
:variacao_mes_11,
:vendas_mes_12,
:variacao_mes_12
do
suspend;
end

Plan
PLAN SORT (JOIN (JOIN (A INDEX (XIE4CAD1)SORT (JOIN (JOIN (B B1 A1 INDEX (R_931), B B1 A2 INDEX (R_906)), B B1 A3 INDEX (XPKOPR2)))SORT (JOIN (C B1 A3 NATURAL, C B1 A2 INDEX (R_919), C B1 A1 INDEX (XPKV3$NOTAS_FISCAIS))))))