vertica-优化编码

朴灿烈づ我的快乐病毒、 2022-08-22 12:28 202阅读 0赞

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 ;