Skip to main content
Question

ENABLE_WITH_CLAUSE_MATERIALIZATION bugged?

  • December 10, 2024
  • 1 reply
  • 1 view

dimitri_p
Forum|alt.badge.img+2

It looks to me like ENABLE_WITH_CLAUSE_MATERIALIZATION does something wrong when a CTE is using row_number/limit. For example, try these on some big table:

with /*+ENABLE_WITH_CLAUSE_MATERIALIZATION*/
preproc as
(select * from some_really_big_table limit 1)
select count(1) from preproc

with  /*+ENABLE_WITH_CLAUSE_MATERIALIZATION*/
preproc as
(select * from (select *,row_number() over() as rn from some_really_big_table) a where rn<2)
select count(1) from preproc

without the hint both of these queries return in a second or so. But with the hint they seem to take forever as if Vertica tries to materialize the entire big table.

Tested on Vertica 12.0.3

1 reply

SruthiA
Forum|alt.badge.img+1
  • Participating Frequently
  • December 10, 2024

Please create a support case and share the reproducer so that we can review it further.