Hi, Vlad,

> 1 - It appears that your use of "inner join cte_3 c on 1 = 1" is the source of your slowdown, it results in the "explosion" of cte_3 for each row in the cad1 table.

Is there something i can do to force CTE_3 to explode just once? I had
previously tried to return an "ID" column in CTE_3, with value "1",
and used "inner join CTE_3 C on C.ID = 1", without improving
performance. Also tried "cross join CTE_3 c", without success.

> 2 - CTE_3 does not "reads CTE_2", as you outline, but rather it is reading CTE_1 (see your example 2)

Sorry, Vlad, i was doing some tests searching what was slowing it
down, and changed it to read CTE_1 instead of CTE_2, and posted this
changed version. The original declaration of CTE_3 is below (altough
it doesn´t change the performance at all):

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
sum(b1.vendas_mes_0),
sum(b1.vendas_mes_1),
sum(b1.vendas_mes_2),
sum(b1.vendas_mes_3),
sum(b1.vendas_mes_4),
sum(b1.vendas_mes_5),
sum(b1.vendas_mes_6),
sum(b1.vendas_mes_7),
sum(b1.vendas_mes_8),
sum(b1.vendas_mes_9),
sum(b1.vendas_mes_10),
sum(b1.vendas_mes_11),
sum(b1.vendas_mes_12)
from
cte_2 b1)

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 B1 A3 NATURAL, C B1 B1 A2 INDEX (R_919), C B1 B1 A1 INDEX
(XPKV3$NOTAS_FISCAIS))))))

> 3 - No elements/columns of CTE_3 is referenced anywhere in the final query...  so why it is there?

I simplified the query before posting it here, just to make it easier
for debug/understand.
CTE_3 returns total company sales of each month, and i use it to
calculate and return the sales participation of each salesperson in
the total sales.
I get CTE_2.VENDAS_MES_1, which is the sales in month 1 for a
salesperson, and compare it to CTE_3.VENDAS_MES_1, which is the total
sales in month 1, and so on. The final select would be like this:

b.vendas_mes_1,
(b.vendas_mes_1 / nullif(B.vendas_mes_0, 0) - 1) * 100, --
compares the sales to previous month
(b.vendas_mes_1 / nullif(C.vendas_mes_1, 0) - 1) * 100, --
compares the sales to total month sales
<same for other months>...

> 4 - Why do you have both CTE_2 and CTE_3 joined in the final query?

Same reason above.

Regards,

Fabiano