Greenplum數據庫開發基礎12014年10月目錄Greenplum概述和數據分布數據加載和外部表客戶端工具Greenplum數據庫基礎Greenplum與Oracle優化策略其他要點及示例MPP架構MPP(MassivelyParallelProcessing)

Shared-NothingArchitectureMPP無共享架構的優勢InterconnectLoading數據分布在所有的并行節點上每個節點只處理其中一部分數據最優化的I/O處理所有的節點同時進行并行處理節點之間完全無共享，無I/O沖突自動化的并行處理機制內部處理自動化并行，無需人工分區或優化加載與訪問方式與一般數據庫相同最易于擴展的架構BI和數據分析的最佳選擇增加節點實現線性擴展增加節點可線性增加存儲、查詢和加載性能Greenplum基本架構MPP(MassivelyParallelProcessing)

Shared-NothingArchitectureNetworkInterconnect............Master

SeversQueryplanning&dispatchSegment

SeversQueryprocessing&datastorageSQLMapReduceExternal

SourcesLoading,streaming,etc.Greenplum的并行處理特性并行處理由系統自動完成，無需人工干預所有數據均勻分布到所有節點，每個節點都計算自己的部分數據，所以并行處理無需人工干預，系統自動完成。

無需復雜的調優需求，只需要加載數據和查詢DBA工作量極少，無需復雜的調優工作和維護工作。

擴展性可線性擴展到10，000個節點每增加一個節點，查詢、加載性能都成線性增長

客戶端訪問及第三方工具支持完全支持數據庫技術接口標準，例如:SQL,ODBC,JDBC,OLEDB等。同時，廣泛地支持各個BI和ETL軟件工具。Greenplum基本體系架構客戶端接口和程序

psqlpgAdminIIIODBC/DatadirectJDBCPerlDBIPythonlibpqOLEDBMasterHost訪問系統的入口建立與客戶端的連接和管理SQL的解析并形成執行計劃執行計劃向Segment的分發收集Segment的執行結果協調工作處理過程Master不存儲業務數據，只存儲系統目錄表和元數據（數據字典）Segment每段（Segment）存放一部分用戶數據

一個系統可以有多段

用戶不能直接存取訪問

所有對段的訪問都經過Master用戶查詢SQL的執行Interconnect

Greenplum數據庫之間的連接層

進程間協調和管理

基于千兆以太網架構

屬于系統內部私網配置

支持兩種協議：TCPorUDPGreenplum高可用性體系架構Master/Standby鏡像保護Standby實時與Master節點的Catalog和事務日志保持同步Standby節點用于當PrimaryMaster節點損壞時提供Master服務數據冗余-Segment鏡像保護每個Segment的數據冗余存放在另一個Segment上，數據實時同步當PrimarySegment失敗時，MirrorSegment將自動提供服務PrimarySegment恢復正常后，使用gprecoverseg–F

同步數據。表分布的策略-并行計算的基礎

Hash分布CREATETABLE…DISTRIBUTEDBY(column[,…])同樣數值的內容被分配到同一個Segment上

循環分布

CREATETABLE…DISTRIBUTEDRANDOMLY具有同樣數值的行內容并不一定在同一個Segment上分布鍵(DistributionKeys)用于將數據平均分布到Segments之中的一個或者多個字段用表的主鍵作分布鍵可以使數據分布均勻建表時使用DISTRIBUTEDBY

子句定義表的分布鍵

CREATETABLEsales

(dtdate,prcfloat,qtyint,cust_idint,

prod_idint,vend_idint)DISTRIBUTEDBY(dt,cust_id,prod_id);如果表沒有主鍵，或者沒有合適的字段作為分布鍵，可以使用隨機分布鍵

（DISTRIBUTEDRANDOMLY）如果沒有明確定義分布鍵，系統會把第一個字段作為表的分布鍵分布存儲數據均勻分布-并行處理的關鍵43Oct2020051264Oct2020051145Oct2020054246Oct2020056477Oct2020053248Oct20200512OrderOrder#Order

DateCustomer

ID50Oct2020053456Oct2020052163Oct2020051544Oct2020051053Oct2020058255Oct20200555策略:

數據盡可能的均勻分布到每個節點查詢命令的執行SQL查詢處理機制并行查詢計劃SELECTcustomer,amountFROMsalesJOINcustomerUSING(cust_id)WHEREdate=04302008;壓縮存儲和行列存儲壓縮存儲支持ZLIB和QUICKLZ方式的壓縮,壓縮比可到10:1壓縮表只能是AppendOnly方式壓縮數據不一定會帶來性能的下降，壓縮表將消耗CPU資源，而減少I/O資源占用語法CREATETABLEfoo(aint,btext)

WITH(appendonly=true,compresstype=zlib,compresslevel=5);行或列存儲模式列模式目前只支持AppendOnly如果常用的查詢只取表中少量字段，則列模式效率更高，如查詢需要取表中的大量字段，行模式效率更高語法：

CREATETABLEsales2(LIKEsales)WITH(appendonly=true,orientation=column);鎖停止活動的SQL查詢查看要停止的SQL查詢的進程ID

執行select*frompg_stat_activity查看到當前數據庫連接的IP地址，用戶名，提交的查詢等。（另外也可以在master主機上查看進程，對每個客戶端連接，master都會創建一個進程。ps-ef|grep-ipostgres|grep-icon）##查詢表是否被鎖selectprocpid,t.*

frompg_stat_activityt

whereusename=‘lds_betl’

anddatname=‘ldsdb’

andwaiting=‘t’;停止SQL：

執行Select

pg_cancel_backend(procpid)

或者Selectpg_terminate_backend(procpid)或者在MASTER

OS：$killprocpid注：極端情況下，kill不能停止SQL時，采用kill

-11停止進程千萬不要使用kill

-9，該操作導致數據庫崩潰；

生產系統請不要采用kill操作。表分區的概念將一張大表邏輯性地分成多個部分，如按照分區條件進行查詢，將減少數據的掃描范圍，提高系統性能。提高對于特定類型數據的查詢速度和性能也可以更方便數據庫的維護和更新兩種類型:Range分區(日期范圍或數字范圍)/如日期、價格等List分區,例如地區、產品等Greenplum中的表分區在使用中具有總表的繼承性，并通過Check參數指定相應的子表分區的子表依然根據分布策略分布在各segment上分區是一種非常有用的優化措施，例如一年的交易按交易日期分區后，查詢一天的交易性能將提高365倍！！！DataDistribution&PartitioningSegment1ASegment1BSegment1CSegment1DSegment2ASegment2BSegment2CSegment2DSegment3ASegment3BSegment3CSegment3DJan2005Feb2005Mar2005Apr2005May2005Jun2005Jul2005Aug2005Sep2005Oct2005Nov2005Dec2005每個分區表的數據平均分布到各個節點表分區可減少數據的搜索范圍，提高查詢性能FullTableScanVS.PartitionPruningSegment1ASegment1BSegment1CSegment1DSegment2ASegment2BSegment2CSegment2DSegment3ASegment3BSegment3CSegment3DSegment1ASegment1BSegment1CSegment1DSegment2ASegment2BSegment2CSegment2DSegment3ASegment3BSegment3CSegment3DSegment1ASegment1BSegment1CSegment1DSegment2ASegment2BSegment2CSegment2DSegment3ASegment3BSegment3CSegment3DSELECTCOUNT(*)FROMordersWHEREorder_date>=‘Oct202005’ANDorder_date<‘Oct272005’VSHashDistributionHashDistribution+TablePartitioningSegment1ASegment1BSegment1CSegment1DSegment2ASegment2BSegment2CSegment2DSegment3ASegment3BSegment3CSegment3D表分區示意圖Range分區CREATETABLEorders(order_id BIGINT,order_date TIMESTAMP,order_mode VARCHAR(8),customer_id NUMERIC(6),order_status NUMERIC(2),order_total NUMERIC(8,2),sales_rep_id NUMERIC(6),promotion_id NUMERIC(6))DISTRIBUTEDBY(customer_id)PARTITIONBYRANGE(order_date)(START('2005-12-01')END('2007-12-01')EVERY(interval'1year'),START('2007-12-01')END('2008-12-01')EVERY(interval'1month'),START('2008-12-01')END('2008-12-08')EVERY(interval'1day'),START('2008-12-08')END('2008-12-09')EVERY(interval'1hour'));List分區CREATETABLErank(

idint,

rankint,

yearint,

genderchar(1),

countint)DISTRIBUTEDBY(id)PARTITIONBYLIST(gender)(PARTITIONgirlsVALUES('F'),PARTITIONboysVALUES('M'),DEFAULTPARTITIONother);Multi-level分區CREATETABLEsales(

trans_idint,

datedate,

amountdecimal(9,2),

regiontext)DISTRIBUTEDBY(trans_id)PARTITIONBYRANGE(date)SUBPARTITIONBYLIST(region)SUBPARTITIONTEMPLATE(SUBPARTITIONusaVALUES('usa'),SUBPARTITIONasiaVALUES('asia'),SUBPARTITIONeuropeVALUES('europe'),START(date'2008-01-01')INCLUSIVEEND(date'2009-01-01')EXCLUSIVEEVERY(INTERVAL'1month'),DEFAULTPARTITIONoutlying_dates);修改表分區ALTERTABLE… |ALTERPARTITION… |DROPPARTITION… |TRUNCATEPARTITION… |RENAMEPARTITION… |ADDPARTITION… |EXCHANGEPARTITION… |SPLITPARTITION… |SETSUBPARTITIONTEMPLATEEg:ALTERTABLEfooEXCHANGEPARTITIONFOR(RANK(1))WITHTABLEbar;Querythecatalog:SELECTpartitiontablename,partitionlevel,partitionrank,partitionrangestart,partitionrangeend,partitioneveryclauseFROMpg_partitions;目錄Greenplum概述和數據分布數據加載和外部表客戶端工具Greenplum數據庫基礎Greenplum與Oracle優化策略其他要點及示例外部表加載外部表的特征Read-only數據存放在數據庫外可執行SELECT,JOIN,SORT等命令，類似正規表的操作外部表的優點并行方式加載ETL的靈活性格式錯誤行的容錯處理支持多種數據源兩種方式ExternalTables:

基于文件WebTables:

基于URL或指令基于外部表的高速數據加載利用并行數據流引擎，Greenplum可以直接用SQL操作外部表數據加載完全并行Master主機Segment主機內部互聯網—千兆以太網交換機gpfdistgpfdistSegment主機Segment主機Segment主機外部表文件外部表文件ETL服務器內部網絡外部表加載的特征并行數據加載提供最好的性能能夠處理遠程存儲的文件采用HTTP協議每個gpfdist可達到200MB/s數據分布率gpfdist文件分發守護進程啟動:

gpfdist-d/var/load_files/expenses-p8080-l/home/gpadmin/log&外部表定義:CREATEEXTERNALTABLEext_expenses

(nametext,datedate,

amountfloat4,descriptiontext)LOCATION('gpfdist//etlhost:8081/*','gpfdist//etlhost:8082/*')FORMAT'TEXT'(DELIMITER'|')ENCODING’UTF-8’LOGERRORSINTOext_expenses_loaderrorsSEGMENTREJECTLIMIT10000ROWS;外部表加載異常處理加載正常數據并捕獲格式異常的數據，比如：缺少某些屬性的行屬性數據類型錯誤無效的字符集編碼不符合約束PRIMARYKEY,NOTNULL,CHECKorUNIQUEconstraints外部表錯誤處理可選子句：[LOGERRORSINTOerror_table]SEGMENTREJECTLIMITcount[ROWS|PERCENT](PERCENTbasedongp_reject_percent_thresholdparameter)例子CREATEEXTERNALTABLEext_customer(idint,nametext,sponsortext)LOCATION('gpfdist://filehost:8081/*.txt')FORMAT'TEXT'(DELIMITER'|'NULL'')LOGERRORSINTOerr_customerSEGMENTREJECTLIMIT5ROWS;COPYSQL

命令PostgreSQL命令支持數據加載和數據卸載加載大量數據的最佳方法串行加載所有行（非并行）從文件或者標準輸入讀取加載數據和外部表一樣支持錯誤處理EXAMPLECOPYmytableFROM'/data/myfile.csv'WITHCSVHEADER;（文件生成在Master）\COPYmytableFROM‘/data/myfile.csv’WITHCSVHEADER;（文件生成在本地）COPYcountryFROM'/data/gpdb/country_data'WITHDELIMITER'|'LOGERRORSINTOerr_countrySEGMENTREJECTLIMIT10ROWS;數據加載性能優化提示

刪除索引，加載完成后再重建

加載完成后執行ANALYZE

加載出錯、DELETE/UPDATE等操作之后執行VACUUM

不要使用ODBCINSERT加載大量數據目錄Greenplum概述和數據分布數據加載和外部表客戶端工具Greenplum數據庫基礎Greenplum與Oracle優化策略其他要點及示例客戶端工具pgAdmin3圖形化管理和SQL執行/分析/監控工具psql

行命令操作和管理工具pgAdmin3forGPDBpgAdmin3是一款重要的PostgreSQL圖形化管理和開發的開源管理工具pgAdmin3forGPDBpgAdmin3forGPDB監控活動session,同SQL：select*frompg_stat_activity;監控鎖，從pg_lock中獲取信息可以停止正在運行的SQLPSQL通過master建立連接連接選項databasename(-d|PGDATABASE)masterhostname(-h|PGHOST)masterport(-p|PGPORT)username(-U|PGUSER)ConnectToGPDBpsql-hdb_ip-pport-Udbusr-vON_ERROR_STOP=1-ddatabaseeg：psql-h1-p5432-Ugpadmin-vON_ERROR_STOP=1-dsordb常用PSQL命令\?(helponpsqlmeta-commands)\h(helponSQLcommandsyntax)\dt(showtables)\dtS(showsystemtables)\dgor\du(showroles)\l(showdatabases)\cdb_name(connecttothisdatabase)\q(quitpsql)\!(Enterintoshellmode)\df(showfunction)\dn(showschema)Setsearch_path=…\timing目錄Greenplum概述和數據分布數據加載和外部表客戶端工具Greenplum數據庫基礎Greenplum與Oracle優化策略其他要點及示例數據庫ToCreate:CREATEDATABASEorcreatedbToDrop:DROPDATABASEordropdbToEdit:ALTERDATABASE

ChangenameAssignnewownerSetconfigurationparametersPSQLTipsPSQL顯示所連接的數據庫

EXAMPLE:template1=#(superuser)

names=>(non-superuser)Toshowalistofalldatabases:

\lToconnecttoanotherdatabase:

\cdb_nameUsePGDATABASEenvironmentvariabletosetthedefaultdatabaseSchemaToCreate:CREATESCHEMAToDrop:DROPSCHEMAToEdit:ALTERSCHEMAChangenameAssignnewownerPSQLTipsToseethecurrentschema:

SELECTcurrent_schema();Toseealistofallschemasinthedatabase:

\dnToseetheschemasearchpath:

SHOWsearch_path;Tosetthesearchpathforadatabase:

ALTERDATABASESETsearch_pathTOmyschema,public,pg_catalog;表ToCreate:CREATETABLEAdditionalDISTRIBUTEDBYorDISTRIBUTEDRANDOMLYclauseSomesyntaxnotsupportedToEdit:ALTERTABLECannotalterdistributionkeycolumnsToDrop:DROPTABLEPSQLTipsTolisttablesinthedatabase:

\dtToseestructureofatable:

\d+table_nameTolistsystemcatalogtables:

\dtSTolistexternaltablesonly:

\dxToseethedistributionkeycolumnsofatable:

\dtable_name表和字段約束CHECKtableorcolumnconstraintsNOTNULLcolumnconstraintsUNIQUEcolumnconstraintsOneallowedpertableUniquecolumnsmustalsobeindistributionkeyNotallowediftablealsohasaprimarykeyPRIMARYKEYtableconstraintsUsedasdistributionkeybydefaultFOREIGNKEYconstraintsdefinitionsaresupportedbutnotenforcedForeignkeyrelationshipsareutilizedbythequeryplannertoimprovequeryplans.視圖

ViewSQLCommands:CREATEVIEWDROPVIEW

PSQLTips:Tolistallviewswhileinpsql:\dv

Toseeaviewdefinition:\d+view_name

EXAMPLE:

CREATEVIEWtopten

ASSELECTname,rank,gender,year

FROMnames,rank

WHERErank<’11’ANDnames.id=rank.id;

SELECT*FROMtoptenORDERBYyear,rank;索引在Greenplum數據庫中應謹慎創建索引索引不一定都能優化查詢應測試索引是否真正提升了性能刪除沒用的索引PRIMARYKEY索引會自動創建唯一性索引只能在分布鍵字段創建索引（續）索引類型:B-tree

Bitmap索引相關SQL命令：

CREATEINDEXALTERINDEXDROPINDEXREINDEX

PSQLTips:在PSQL顯示所有索引：

\di顯示索引定義:\d+index_name

大批量ETL加工最好不建索引，對性能提升作用不大B-TREE適用每次通過單一字段篩選查詢少量數據B-MAP適用每次通過多個字段篩選查詢大量數據其他數據庫對象FunctionsandoperatorsSequencesTriggersTablespaces數據類型常用數據類型CHAR,VARCHAR,TEXTSmallint,integer,bigintNumeric,real,doubleprecisionTimestamp,date,timeBooleanArray

類型。如

integer[]其它數據類型請參考常用系統表及視圖所有系統表在pg_catalogschema標準PostgreSQL系統表(pg_*)常用系統表：pg_stat_activitypg_tablespg_class

pg_attributepg_namespace在psql顯示所有系統表:\dtSPsql顯示所有系統視圖:\dvS其它catalog參考

函數日期函數Extract（day|month|year。。。Fromdate);Selectdate+‘1day’::interval,date+‘1month’::intervalSELECTdate_part('day',TIMESTAMP'2001-02-1620:38:40');Result:16SELECTdate_trunc('hour',TIMESTAMP'2001-02-1620:38:40');Result:2001-02-1620:00:00pg_sleep(seconds);系統日期變量Current_dateCurrent_timeCurrent_timestampNow()Timeofday()在

事務中發生變化，以上函數在事務中不變函數字符串處理函數Substr/length/lower/upper/trim/replace/positionrPad/lpadTo_char,||(字符串連接)substringlike，simillar

to(模式匹配)其它雜類Case。。。When/Coalescenullifgenerate_seriesIn/notin/exists/any/allBuilt-InFunctions(SELECT)FunctionDescriptionExampleCURRENT_DATEReturnsthecurrentsystemdate2006-11-06CURRENT_TIMEReturnsthecurrentsystemtime16:50:54CURRENT_TIMESTAMPReturnsthecurrentsystemdateandtime2008-01-0616:51:44.430000+00:00LOCALTIMEReturnsthecurrentsystemtimewithtimezoneadjustment19:50:54LOCALTIMESTAMPReturnsthecurrentsystemdateandtimewithtimezoneadjustment2008-01-0619:51:44.430000+00:00CURRENT_ROLE

ROLEReturnsthecurrentdatabaseuserjdoeMathematicalFunctionsFunctionReturnsDescriptionExampleResults+-*/sameAdd,Subtract,Multiply&Divide1+12%IntegerModulo10%20^SameExponentiation2^24|/NumericSquareRoot|/93||/NumericCubeRoot||/82!NumericFactorial!36&|#~NumericBitwiseAnd,Or,XOR,Not91&1511<<>>NumericBitwiseShiftleft,right1<<48>>2162MathematicalFunctions(Continued)FunctionReturnsDescriptionExampleResultsabssameAbsoluteValueabs(-998.2)998.2ceiling(numeric)NumericReturnssmallestintegernotlessthanargumentceiling(48.2)49floor(numeric)NumericReturnslargestintegernotgreaterthanargumentfloor(48.2)48pi()NumericTheπconstantpi()3.1419…random()NumericRandomvaluebetween0.0and1.0random().87663round()NumericRoundtonearestintegerround(22.7)23StringFunctionsFunctionReturnsDescriptionExampleResultsstring||stringTextStringconcatenation‘my’||‘my’‘mymy’char_length(string)Integernumberofcharsinstringchar_length(‘mymy’)4position(stringinstring)IntegerLocationofspecifiedsub-stringposition(‘my’in‘ohmy’)3lower(string)TextConvertstolowercaselower(‘MYMY’)‘mymy’upper(string)TextConvertstouppercaseupper(‘mymy’)‘MYMY’substring(stringfromnforn)TextDisplaysportionofstringsubstring(‘myohmy’from3for2)‘oh’trim(both,leading,trailingfromstring)TextRemoveleadingand/ortrailingcharacterstrim(‘mymy‘)‘mymy’StringFunctions(Continued)FunctionReturnsDescriptionExampleResultsinitcap(string)TextChangescaseinitcap(‘mymy’)‘MyMy’length(string)IntegerReturnsstringlengthlength(‘mymy’)4split_part(string,delimiter,occurrence)TextSeparatesdelimitedlistsplit_part(‘one|two|three’,’|’,2)‘two’DateFunctionsFunctionReturnsDescriptionExampleResultsage(timestamp,timestamp)TimestampDifferenceinyears,monthsanddaysage(‘2008-08-12’timestamp,current_timestamp)0years1month11daysextract(fieldfromtimestamp)IntegerReturnsyear,month,day,hour,minuteorsecondextract(dayfromcurrent_date)11now()TimestampReturnscurrentdate&timenow()2008-09-2211:00:01overlapsBooleanSimplifiescomparingdaterangesWHERE(‘2008-01-01’,’2008-02-11’)overlaps(‘2008-02-01’,’2008-09-11’)TRUE存儲過程Greenplum支持SQL/PYTHON/PERL/C語言構建函數，以下著重介紹SQL

存儲過程。一個存儲過程就是一個事務，包括對子過程的調用都在一個事務內存儲過程結構：CREATEFUNCTIONsomefunc()RETURNSintegerAS$$DECLAREquantityinteger:=30;BEGINRETURN;END;$$LANGUAGEplpgsql;賦值給一個變量或行/記錄賦值用下面方法：identifier:=expression例子：user_id:=20;執行一個沒有結果的查詢:PERFORMquery;一個例子：PERFORMcreate_mv('cs_session_page_requests_mv',my_query);存儲過程請參考：存儲過程動態SQLEXECUTEcommand-string[INTO[STRICT]target];SELECTINTOExample：SELECTIDINTOVAR_IDFROMTABLEA獲取結果狀態GETDIAGNOSTICSvariable=item[,...];一個例子： ·GETDIAGNOSTICSinteger_var=ROW_COUNT;SQL返回變量SQLERRM（SQL出錯信息），

SQLSTATE（SQL執行返回狀態編碼）控制結構IF...THEN...ELSEIF...THEN...ELSELOOP,EXIT,CONTINUE,WHILE,FOR從函數返回有兩個命令可以用來從函數中返回數據：RETURN和RETURNNEXT。Syntax：RETURNexpression;設置回調EXECSQLWHENEVERcondition

action;condition

可以是下列之一：SQLERROR，SQLWARNING，NOTFOUND存儲過程異常處理EXCEPTIONWHENunique_violationTHEN--donothingEND;忽略錯誤：EXCEPTIONWHENOTHERSTHEN

RAISENOTICE'anEXCEPTIONisabouttoberaised';

RAISEEXCEPTION'NUM:%,DETAILS:%',SQLSTATE,SQLERRM;END;錯誤和消息RAISElevel'format'[,expression[,...]];Level:Info:信息輸入Notice:信息提示Exception:產生一個例外，將退出存儲過程Example:RAISENOTICE'Callingcs_create_job(%)',v_job_id;OVER(PARTITIONBY…)ExampleSELECT*,row_number()OVER()FROMsaleORDERBYcn;SELECT*,row_number()OVER(PARTITIONBYcn)FROMsaleORDERBYcn;row_number|cn|vn|pn|dt|qty|prc++++++1|1|10|200|1401-03-01|1|02|1|30|300|1401-05-02|1|03|1|50|400|1401-06-01|1|04|1|30|500|1401-06-01|12|55|1|20|100|1401-05-01|1|01|2|50|400|1401-06-01|1|02|2|40|100|1401-01-01|1100|24001|3|40|200|1401-04-01|1|0(8rows)row_number|cn|vn|pn|dt|qty|prc++++++1|1|10|200|1401-03-01|1|02|1|30|300|1401-05-02|1|03|1|50|400|1401-06-01|1|04|1|30|500|1401-06-01|12|55|1|20|100|1401-05-01|1|06|2|50|400|1401-06-01|1|07|2|40|100|1401-01-01|1100|24008|3|40|200|1401-04-01|1|0(8rows)OVER(ORDERBY…)ExampleSELECTvn,sum(prc*qty)FROMsaleGROUPBYvnORDERBY2DESC;SELECTvn,sum(prc*qty),rank()OVER(ORDERBYsum(prc*qty)DESC)FROMsaleGROUPBYvnORDERBY2DESC;vn|sum|rank++40|2640002|130|180|250|0|320|0|310|0|3(5rows)vn|sum+40|264000230|18050|020|010|0(5rows)事務事務將多個語句捆綁為‘all-or-nothing’操作事務相關命令BEGINorSTARTTRANSACTIONENDorCOMMITROLLBACKSAVEPOINTandROLLBACKTOSAVEPOINTPsql中設置自動提交模式：\setautocommiton|off目錄Greenplum概述和數據分布數據加載和外部表客戶端工具Greenplum數據庫基礎Greenplum與Oracle優化策略其他要點及示例數據類型DataType

ORACLE

GreenplumNumericNUMBER(p,s)SMALLINT(2bytes)INTEGER(4bytes)BIGINT(8bytes)DECIMAL(p,s)(11+p/2bytes)NUMERIC(p,s)(11+p/2bytes)REAL(4bytes)DOUBLE(8bytes)CharacterCHARandNCHARCHARVARCHAR2andNVARCHAR2VARCHARDate&TimeDATE(includestimetosec)DATEorTIMESTAMPwithoutTimeZoneTIMESTAMPTIMESTAMPINTERVALINTERVAL/TIMEBinaryBFILE(>1GB)LargeObjects(upto2GB)RAWBFILE(<1GB)BYTEACLOBandNCLOBTEXTGreenplum與Oracle比較（1）DifferenceOracleGreenplumDUALSELECT1+1FROMDUALSELECT1+1;or

CREATEVIEWdualASSELECT'X'::VARCHAR(1)ASDUMMY;

SELECT1+1FROMdual;NEXTVALSELECTA_TABLE_SEQUENCE.NEXTVALFROMDUALSELECTNEXTVAL('A_TABLE_SEQUENCE')FROMDUALROWNUMSELECT*FROMAGE_TYPEWHEREROWNUM<=5SELECT*FROMAGE_TYPE

LIMIT5OFFSET0SELECT*FROMAGE_TYPEWHERECODEISNOTNULL

ANDROWNUM<=5

ORDERBYCODEDESCSELECT*FROMAGE_TYPE

WHERECODEISNOTNULL

ORDERBYCODEDESC

LIMIT5OFFSET0ASSELECTA.COL1A_COL1,

A.COL2A_COL2

FROMA_TABLEASELECTA.COL1ASA_COL1,

A.COL2ASA_COL2

FROMA_TABLEAGreenplum與Oracle比較（2）DifferenceOracleGreenplum(+)SELECT*FROMA_TABLEA,B_TABLEB

WHEREA.ID(+)=B.IDSELECT*FROMA_TABLEA

RIGHTOUTERJOINB_TABLEB

ONA.ID=B.IDSELECT*FROMA_TABLEA,B_TABLEB

WHEREA.ID(+)=B.ID

ANDA.COL1='COL1_VALUE'SELECT*FROMA_TABLEA

RIGHTOUTERJOINB_TABLEB

ONA.ID=B.IDANDA.COL1='COL1_VALUE'SELECT*FROMA_TABLEA,B_TABLEB,C_TABLEC,D_TABLED

WHEREA.ID=B.ID(+)AND

A.ID=C.ID(+)AND

A.COL1=D.COL1SELECT*FROM(A_TABLEA

LEFTOUTERJOINB_TABLEB

ONA.ID=B.ID)

LEFTOUTERJOINC_TABLEC

ONA.ID=C.ID,D_TABLED

WHEREA.COL1=D.COL1SELECT*FROMA_TABLEA

WHEREA.COL1(+)=0AND

A.COL2(+)='A_VALUE2'SELECT*FROMA_TABLEA

WHEREA.COL1=0AND

A.COL2='A_VALUE2'Greenplum與Oracle比較（3）DifferenceOracleGreenplumNVLSELECTNVL(SUM(VALUE11),0)FS_VALUE1,

NVL(SUM(VALUE21),0)FS_VALUE2

FROMFIELD_SUMSELECTCOALESCE(SUM(VALUE11),0)ASFS_VALUE1,

COALESCE(SUM(VALUE21),0)ASFS_VALUE2

FROMFIELD_SUMTO_NUMBERSELECTCOL1

FROMA_TABLE

ORDERBYTO_NUMBER(COL1)SELECTCOL1

FROMA_TABLE

ORDERBYTO_NUMBER(COL1,999999)

[note：'999999'islengthofCOL1]DECODESELECTDECODE(ENDFLAG,'1','A','B')ENDFLAG

FROMTESTSELECT(CASEENDFLAG

WHEN'1'THEN'A'ELSE'B'END)ASENDFLAG

FROMTEST||SELECTNULL||'-'||NULLASVALUES1

FROMDUALSELECTCOALESCE(NULL,'')||'-'||COALESCE(NULL,'')ASVALUES1

FROMDUALGreenplum與Oracle比較（4）DifferenceOracleGreenplumSYSDATEUPDATEA_TABLE

SETENTREDATE=SYSDATEUPDATEA_TABLE

SETENTREDATE=CURRENT_TIMESTAMP;or

UPDATEA_TABLE

SETENTREDATE=TO_TIMESTAMP(CURRENT_TIMESTAMP,'YYYY-MM-DDHH24:MI:SS')SELECTTO_DATE(SYSDATE,'YYYY-MM-DD')ASDAY

FROMDUALSELECTTO_DATE(CURRENT_DATE,'YYYY-MM-DD')ASDAY

FROMDUALaggregateSELECTROUND(AVG(SUM(BASICCNT1)))BASICCNT

FROMACCESS_INFO_SUM1_V

WHEREYEARCODEBETWEEN'200305'AND'200505'

GROUPBYSCCODESELECTROUND(AVG(AIV.BASICCNT))ASBASICCNT

FROM(SELECTSUM(BASICCNT1)ASBASICCNT

FROMACCESS_INFO_SUM1_V

WHEREYEARCODEBETWEEN'200305'AND'200505'

GROUPBYsccode

)AIVCEILSELECTCEIL(SYSDATE-TO_DATE('2005102714:56:10','YYYYMMDDHH24:MI:SS'))ASDAYS

FROMDUALSELECTEXTRACT(DAYFROM(TO_TIMESTAMP(CURRENT_TIMESTAMP,'YYYY-MM-DD-HH24-MI-SS')-TO_TIMESTAMP('2005-10-2714:56:10','YYYY-MM-DD-HH24-MI-SS')))+1ASDAYS

FROMDUALGreenplum與Oracle比較（5）DifferenceOracleGreenplum「"」SELECTLENGTH('')ASVALUE1FROMDUAL

[Result]VALUE1=NULLSELECTLENGTH('')ASVALUE1FROMDUAL

[Result]VALUE1=0SELECTTO_DATE('','YYYYMMDD')ASVALUE2

FROMDUAL

[Result]VALUE2=NULLSELECTTO_DATE('','YYYYMMDD')ASVALUE2

FROMDUAL

[Result]VALUE2=0001-01-01BCSELECTTO_NUMBER('',1)ASVALUE3FROMDUAL

[Result]VALUE3=NULLSELECTTO_NUMBER('',1)ASVALUE3FROMDUAL

[Result]cannotexecuteINSERTINTOTEST(VALUE4)VALUES('')

[Result]VALUE4=NULL

[note:VALUE4isnumerictype]INSERTINTOTEST(VALUE4)VALUES('')

[Result]VALUE4=0

[note:VALUE4isnumerictype]INSERTINTOTEST(VALUE5)VALUES('')

[Result]VALUE5=NULL

[note:VALUE5ischaractertype]INSERTINTOTEST(VALUE5)VALUES('')

[Result]VALUE5=''

[note:VALUE5ischaractertype,lengthequal0]INSERTINTOTEST(VALUE6)VALUES(TO_DATE('','YYYYMMDD'))

[Result]VALUE6=NULL

[note:VALUE6istimetype]INSERTINTOTEST(VALUE6)VALUES(TO_DATE('','YYYYMMDD'))

[Result]VALUE6=0001-01-01BC

[note:VALUE6istimetype]Greenplum與Oracle比較（6）DifferenceOracleGreenplumNULLIFnotsupportNULLIFSELECTNULLIF(VALUE1,VALUE2)ASCOL1FROMDUALCONCATCONCAT(CHAR,CHAR)CREATEFUNCTIONCONCAT(CHAR,CHAR)

RETURNSCHARAS'SELECT$1||$2'LANGUAGE'sql';ADD_MONTHSADD_MONTHS(date,int)CREATEFUNCTIONadd_months(date,int)

RETURNSdateAS

'SELECT($1+($2::text||''months'')::interval)::date;'

LANGUAGE'sql';LAST_DAYLAST_DAY(DATE)CREATEFUNCTIONLAST_DAY(DATE)

RETURNSDATEAS

'SELECTdate(substr(text($1+interval(''1month'')),1,7)||''-01'')-1'

LANGUAGE'sql';MONTH_BETWEENMONTH_BETWEEN(DATA,DATA)CREATEFUNCTIONMONTH_BETWEEN(DATA,DATA)

RETURNSNUMERICAS

'SELECTto_number((date($1)-date($2)),''999999999'')/31'

LANGUAGE'sql';BIN_TO_NUMSELECTBIN_TO_NUM(1,0,1,0)ASVALUE1FROMDUALSELECTCAST(B'1010'ASINTEGER)ASVALUE1BITANDBITAND(int,int)SELECTint&int目錄Greenplum概述和數據分布數據加載和外部表客戶端工具Greenplum數據庫基礎Greenplum與Oracle優化策略其他要點及示例查看執行計劃查看sql執行計劃的命令:EXPLAIN<query>EXPLAINANALYZE<query>查看執行計劃:一般從最后面往上查看以下命令可能會包含Gather,Redistribute,Broadcast等操作JoinsSortsAggregations每步操作給出如下度量：cost(unitsofdiskpagefetches)rows(rowsoutputbythisnode)width(bytecountofthewidestrowproducedbythisnode)執行計劃（EXPLAIN）EXPLAINSELECT*FROMnamesWHEREname='Joelle';

QUERYPLANGatherMotion2:1(slice1)(cost=0.00..20.88rows=1width=13)->SeqScanon'names'(cost=0.00..20.88rows=1width=13)Filter:name::text~~'Joelle'::textSQLqueryFilterconditionGathermotionCost,rows,andwidth執行計劃（EXPLAINANALYZE）EXPLAINANALYZESELECT*FROMnamesWHEREname='Joelle';QUERYPLANGatherMotion2:1(slice1)(cost=0.00..20.88rows=1width=13)recv:Total1rowswith0.305mstofirstrow,0.537mstoend.->SeqScanon'names'(cost=0.00..20.88rows=1width=13)

Total1rows(seg0)with0.255mstofirstrow,0.486mstoend.Filter:name::text~~'Joelle'::text22.548mselapsed1segment

returnedrowsActualtimeto

runthequery1rowreturned

tomaster優化策略（1）1、數據分布-選用合適字段作為DistributionKey(DK)，盡量做到平均分布Selectgp_segment_id,count(*)fromtablenamegroupby1;例1：優化策略（2）2、盡量選擇常用連接條件或Groupby列作為DistributionKey，最好只用一個字段作為DK，并且DK列的distinct值越多越好。例2：insertintotablec(auction_id,….)select*fromtablealeftjointalbebontablea.selid=tablec.id;優化前耗時120秒優化方式：將tablea的DistributionKey改為selid，tableb的改為id，tablec的DistributionKey改為auction_id優化后耗時88秒，提升了32秒

修改DK的語法：ALTERTABLEnameSETDISTRIBUTEDBY(column,[...])；ALTERTABLEnameSETWITH(REORGANIZE=true)；

3、采用Createtable代替Insertinto;盡量對中間過程使用臨時表；

數據庫內有很多表都是全量更新的，因此可以用Createtable來代替Insertinto，性能能夠大幅提高。例2中的SQL，改成Createtabletablecas(select*fromtablealeftjointablebontablea.selid=tablec.id)distributedby(auction_id)，優化后耗時為65秒，提升了13秒優化策略（3）4、定期收集統計信息和執行Vacuum定期收集統計信息,可以優化SQL執行路徑；sytax：analyzetable;數據加載后執行Vacuum，或者采用重創建表的方式來釋放垃圾數據，可以提高SQL效率；對系統的數據字典也需要定期做vacuum由于GPDB使用的是MVCC事務并發模型，被刪除或更新的數據行依然占據著物理磁盤空間，即便它們對于新的事務已經不可見。如果數據庫有大量的更新和刪除，會產生大量過期記錄。VACUMM命令還會收集表級別的統計信息，如行數和頁面數。ANALYZE命令收集查詢規劃器需要用到的列統計信息。VACUUM和ANALYZE操作可以在同一個命令中一起運行。例：=#VACUUMANALYZEmytable;

5、SQL合并在Greenplum中，建議將多個表的join和嵌套子查詢SQL適當合并為一個SQL實現，可以減少IO，達到提高性能的目的