vertica-优化编码

vertica有各种各样优秀的编码方式，由于起初表里面没有数据，导致编码不尽合理，可以使用dbd进行编码重新调整

三步
1，运行DB Designer进行增量的设计
2，增量设计之后，运行ANALYZE_STATISTICS收集统计信息
3，对增量设计的SQL运行EXPLAIN以验证是否使用了新的Projection
REFRESH
语法
REFRESH ( '[[db-name.]schema.]table_name [ , ... ]' )
例如
select refresh('tmp.JSLogRecord ');
MAKE_AHM_NOW
只有dbadmin有权限，保证数据一致，在wos中没有其他操作
语法
MAKE_AHM_NOW ( [ true ] )
例如
select make_ahm_now();
优化前
explain select count(*) from tmp.web_Dim_Page ;
------------------------------
QUERY PLAN DESCRIPTION:
------------------------------


explain select * from tmp.web_Dim_Page 


Access Path:
+-STORAGE ACCESS for web_Dim_Page  [Cost: 376, Rows: 8K] (PATH ID: 1)
|  Projection: tmp.web_Dim_Page _b0
|  Materialize: web_Dim_Page .Eid, web_Dim_Page .pageid, web_Dim_Page .pageinfo, web_Dim_Page .SourceDomain, web_Dim_Page .DomainNo, web_Dim_Page .NewDomain, web_Dim_Page .SiteType, web_Dim_Page .DOmaininfo, web_Dim_Page .IsMb, web_Dim_Page .Product, web_Dim_Page .SourceType, web_Dim_Page .CompanyName, web_Dim_Page .HomePageID, web_Dim_Page .ContentType, web_Dim_Page .SourceTime, web_Dim_Page .EUTIME
|  Execute on: All Nodes
具体实战
vsql下跑
select DESIGNER_DESIGN_PROJECTION_ENCODINGS ('web.web_Dim_Page ','encodings.sql','false');
 DESIGNER_DESIGN_PROJECTION_ENCODINGS
--------------------------------------
(1 row)
explain select * from tmp.web_Dim_Page ;
CREATE PROJECTION web_Dim_Page _DBD_31_seg_EncodingDesign /*+createtype(D)*/
(
 Eid ENCODING COMMONDELTA_COMP,
 pageid ENCODING DELTARANGE_COMP,
 pageinfo,
 SourceDomain,
 DomainNo ENCODING COMMONDELTA_COMP,
 NewDomain,
 SiteType,
 DOmaininfo,
 IsMb,
 Product,
 SourceType,
 CompanyName,
 HomePageID,
 ContentType,
 SourceTime ENCODING COMMONDELTA_COMP,
 EUTIME ENCODING BLOCKDICT_COMP
)
AS
 SELECT web_Dim_Page .Eid,
        web_Dim_Page .pageid,
        web_Dim_Page .pageinfo,
        web_Dim_Page .SourceDomain,
        web_Dim_Page .DomainNo,
        web_Dim_Page .NewDomain,
        web_Dim_Page .SiteType,
        web_Dim_Page .DOmaininfo,
        web_Dim_Page .IsMb,
        web_Dim_Page .Product,
        web_Dim_Page .SourceType,
        web_Dim_Page .CompanyName,
        web_Dim_Page .HomePageID,
        web_Dim_Page .ContentType,
        web_Dim_Page .SourceTime,
        web_Dim_Page .EUTIME
 FROM tmp.web_Dim_Page 
 ORDER BY web_Dim_Page .Eid,
          web_Dim_Page .pageid,
          web_Dim_Page .pageinfo,
          web_Dim_Page .SourceDomain,
          web_Dim_Page .DomainNo,
          web_Dim_Page .NewDomain,
          web_Dim_Page .SiteType,
          web_Dim_Page .DOmaininfo,
          web_Dim_Page .IsMb,
          web_Dim_Page .Product,
          web_Dim_Page .SourceType,
          web_Dim_Page .CompanyName,
          web_Dim_Page .HomePageID,
          web_Dim_Page .ContentType,
          web_Dim_Page .SourceTime,
          web_Dim_Page .EUTIME
SEGMENTED BY hash(web_Dim_Page .Eid, web_Dim_Page .pageid, web_Dim_Page .DomainNo, web_Dim_Page .SourceTime, web_Dim_Page .EUTIME, web_Dim_Page .SiteType, web_Dim_Page .DOmaininfo, web_Dim_Page .IsMb, web_Dim_Page .ContentType, web_Dim_Page .NewDomain, web_Dim_Page .Product, web_Dim_Page .SourceType, web_Dim_Page .CompanyName, web_Dim_Page .HomePageID, web_Dim_Page .pageinfo, web_Dim_Page .SourceDomain) ALL NODES KSAFE 1;






select refresh('tmp.web_Dim_Page ');


select make_ahm_now();


DROP PROJECTION tmp.web_Dim_Page  CASCADE;


ALTER PROJECTION tmp.web_Dim_Page _DBD_31_seg_EncodingDesign RENAME TO web_Dim_Page ;
select ANALYZE_STATISTICS('tmp.web_Dim_Page ')
explain select * from tmp.web_Dim_Page ;