vertica-not fit in memory

来源：互联网发布：python list fuzhi 编辑：程序博客网时间：2024/06/06 03:15

ERROR: Join [(web_test.Web_Dim_Domain x web.JSLogRecord) using Web_Dim_Domain_b0 and JSLogRecord_DBD_5_seg_c_stg_web2_b1 (PATH ID: 8)] inner partition did not fit in memory; value [cp.eastmoney.com]

解决方法

explain这个sql

 Access Path: +-JOIN MERGEJOIN(inputs presorted) [Cost: 815, Rows: 10K (NO STATISTICS)] (PATH ID: 1) | Join Cond: (dim.y = ext.z) | Materialize at Output: fact.x | Execute on: All Nodes | +-- Outer -> JOIN MERGEJOIN(inputs presorted) [Cost: 408, Rows: 10K (NO STATISTICS)] (PATH ID: 2) | | Join Cond: (fact.x = dim.y) | | Execute on: All Nodes | | +-- Outer -> STORAGE ACCESS for fact [Cost: 202, Rows: 10K (NO STATISTICS)] (PATH ID: 3) | | | Projection: public.fact_super | | | Materialize: fact.x | | | Execute on: All Nodes | | +-- Inner -> STORAGE ACCESS for dim [Cost: 202, Rows: 10K (NO STATISTICS)] (PATH ID: 4) | | | Projection: public.dim_super | | | Materialize: dim.y | | | Execute on: All Nodes | +-- Inner -> STORAGE ACCESS for ext [Cost: 202, Rows: 10K (NO STATISTICS)] (PATH ID: 5) | | Projection: public.ext_super | | Materialize: ext.z | | Execute on: All Nodes

注意这里NO STATISTICS是没用收集统计信息

没用收集统计信息导致sql占用内存超过既定内存，从而导致上述报错

select ANALYZE_STATISTICS('web.jslogrecord')

select ANALYZE_STATISTICS('web_test.Dim_Ip_Borui')

select ANALYZE_STATISTICS('web_test.web_dim_domain')

select ANALYZE_STATISTICS('common.Cmm_Dim_Geography')

select ANALYZE_STATISTICS('web.Web_Dim_Time')

只需收集全部统计信息即可解决

0 0