Greenplum-数据库开发基础ppt课件(PPT 103页)

1、Greenplum 数据库开发基础2014年10月第1页，共103页。目录Greenplum概述和数据分布数据加载和外部表客户端工具Greenplum数据库基础Greenplum与Oracle优化策略其他要点及示例第2页，共103页。MPP架构MPP (Massively Parallel Processing) Shared-Nothing Architecture第3页，共103页。MPP无共享架构的优势InterconnectLoading数据分布在所有的并行节点上每个节点只处理其中一部分数据最优化的I/O处理所有的节点同时进行并行处理节点之间完全无共享，无I/O冲突自动化的并行处理机制

2、内部处理自动化并行，无需人工分区或优化加载与访问方式与一般数据库相同最易于扩展的架构BI和数据分析的最佳选择增加节点实现线性扩展增加节点可线性增加存储、查询和加载性能第4页，共103页。Greenplum基本架构MPP (Massively Parallel Processing) Shared-Nothing ArchitectureNetworkInterconnect.MasterSeversQuery planning & dispatch SegmentSeversQuery processing & data storageSQLMapReduceExternalSourcesLo

3、ading, streaming, etc.第5页，共103页。Greenplum的并行处理特性并行处理由系统自动完成，无需人工干预所有数据均匀分布到所有节点，每个节点都计算自己的部分数据，所以并行处理无需人工干预，系统自动完成。无需复杂的调优需求，只需要加载数据和查询DBA工作量极少，无需复杂的调优工作和维护工作。扩展性可线性扩展到10，000个节点每增加一个节点，查询、加载性能都成线性增长客户端访问及第三方工具支持完全支持数据库技术接口标准，例如: SQL, ODBC, JDBC, OLEDB等。同时，广泛地支持各个BI和ETL软件工具。第6页，共103页。Greenplum 基本体系架构

4、第7页，共103页。客户端接口和程序 psql pgAdmin III ODBC/Datadirect JDBC Perl DBI Python libpqOLE DB第8页，共103页。Master Host 访问系统的入口建立与客户端的连接和管理SQL的解析并形成执行计划执行计划向Segment的分发收集Segment的执行结果协调工作处理过程Master不存储业务数据，只存储系统目录表和元数据（数据字典）第9页，共103页。Segment 每段（Segment）存放一部分用户数据 一个系统可以有多段 用户不能直接存取访问 所有对段的访问都经过Master用户查询SQL的执行第10页，共1

5、03页。Interconnect Greenplum数据库之间的连接层 进程间协调和管理 基于千兆以太网架构 属于系统内部私网配置 支持两种协议：TCP or UDP第11页，共103页。Greenplum 高可用性体系架构第12页，共103页。Master/Standby 镜像保护Standby 实时与 Master 节点的 Catalog 和事务日志保持同步Standby 节点用于当 Primary Master 节点损坏时提供 Master 服务第13页，共103页。数据冗余-Segment 镜像保护每个Segment的数据冗余存放在另一个Segment上，数据实时同步当Primary

6、Segment失败时，Mirror Segment将自动提供服务Primary Segment恢复正常后，使用gprecoverseg F 同步数据。第14页，共103页。表分布的策略-并行计算的基础 Hash分布 CREATE TABLE DISTRIBUTED BY (column ,) 同样数值的内容被分配到同一个Segment上 循环分布 CREATE TABLE DISTRIBUTED RANDOMLY 具有同样数值的行内容并不一定在同一个Segment上第15页，共103页。分布键(Distribution Keys)用于将数据平均分布到Segments之中的一个或者多个字段用表的

7、主键作分布键可以使数据分布均匀建表时使用 DISTRIBUTED BY 子句定义表的分布键CREATE TABLE sales (dt date, prc float, qty int, cust_id int, prod_id int, vend_id int)DISTRIBUTED BY (dt, cust_id, prod_id);如果表没有主键，或者没有合适的字段作为分布键，可以使用随机分布键 （DISTRIBUTED RANDOMLY）如果没有明确定义分布键，系统会把第一个字段作为表的分布键第16页，共103页。分布存储第17页，共103页。数据均匀分布- 并行处理的关键43Oct

8、20 20051264Oct 20 20051145Oct 20 20054246Oct 20 20056477Oct 20 20053248Oct 20 200512OrderOrder #Order DateCustomer ID50Oct 20 20053456Oct 20 20052163Oct 20 20051544Oct 20 20051053Oct 20 20058255Oct 20 200555策略: 数据尽可能的均匀分布到每个节点第18页，共103页。查询命令的执行第19页，共103页。SQL查询处理机制第20页，共103页。并行查询计划SELECT customer, am

9、ount FROM sales JOIN customer USING (cust_id) WHERE date=04302008;第21页，共103页。压缩存储和行列存储压缩存储支持ZLIB和QUICKLZ方式的压缩,压缩比可到10:1压缩表只能是Append Only方式压缩数据不一定会带来性能的下降，压缩表将消耗CPU资源，而减少I/O资源占用语法 CREATE TABLE foo (a int, b text) WITH (appendonly=true, compresstype=zlib, compresslevel=5); 行或列存储模式列模式目前只支持Append Only 如

10、果常用的查询只取表中少量字段，则列模式效率更高，如查询需要取表中的大量字段，行模式效率更高语法： CREATE TABLE sales2 (LIKE sales) WITH (appendonly=true, orientation=column);第22页，共103页。锁第23页，共103页。停止活动的SQL查询查看要停止的SQL查询的进程ID 执行 select * from pg_stat_activity 查看到当前数据库连接的IP 地址，用户名，提交的查询等。（另外也可以在master 主机上查看进程，对每个客户端连接，master 都会创建一个进程。ps -ef |grep -i

11、postgres |grep -i con）#查询表是否被锁select procpid,t.* from pg_stat_activity t where usename = lds_betl and datname = ldsdb and waiting = t;停止SQL： 执行 Select pg_cancel_backend(procpid) 或者 Select pg_terminate_backend(procpid) 或者 在MASTER OS：$ kill procpid注：极端情况下， kill 不能停止SQL时，采用kill -11停止进程 千万不要使用kill -9，该操

12、作导致数据库崩溃； 生产系统请不要采用kill操作。第24页，共103页。表分区的概念将一张大表逻辑性地分成多个部分，如按照分区条件进行查询，将减少数据的扫描范围，提高系统性能。提高对于特定类型数据的查询速度和性能也可以更方便数据库的维护和更新两种类型:Range分区 (日期范围或数字范围)/如日期、价格等List 分区,例如地区、产品等Greenplum中的表分区在使用中具有总表的继承性，并通过Check参数指定相应的子表分区的子表依然根据分布策略分布在各segment上分区是一种非常有用的优化措施，例如一年的交易按交易日期分区后，查询一天的交易性能将提高365倍！第25页，共103页。Da

13、ta Distribution & PartitioningSegment 1ASegment 1BSegment 1CSegment 1DSegment 2ASegment 2BSegment 2CSegment 2DSegment 3ASegment 3BSegment 3CSegment 3DJan 2005Feb 2005Mar 2005Apr 2005May 2005Jun 2005Jul 2005Aug 2005Sep 2005Oct 2005Nov 2005Dec 2005每个分区表的数据平均分布到各个节点表分区可减少数据的搜索范围，提高查询性能第26页，共103页。Full T

14、able Scan VS. Partition PruningSegment 1ASegment 1BSegment 1CSegment 1DSegment 2ASegment 2BSegment 2CSegment 2DSegment 3ASegment 3BSegment 3CSegment 3DSegment 1ASegment 1BSegment 1CSegment 1DSegment 2ASegment 2BSegment 2CSegment 2DSegment 3ASegment 3BSegment 3CSegment 3DSegment 1ASegment 1BSegment 1

15、CSegment 1DSegment 2ASegment 2BSegment 2CSegment 2DSegment 3ASegment 3BSegment 3CSegment 3DSELECT COUNT(*) FROM orders WHERE order_date= Oct 20 2005 AND order_date (non-superuser)To show a list of all databases:lTo connect to another database:c db_nameUse PGDATABASE environment variable to set the d

16、efault database第48页，共103页。SchemaTo Create: CREATE SCHEMATo Drop: DROP SCHEMATo Edit: ALTER SCHEMAChange nameAssign new ownerPSQL TipsTo see the current schema:SELECT current_schema();To see a list of all schemas in the database:dnTo see the schema search path:SHOW search_path;To set the search path

17、for a database:ALTER DATABASE SET search_path TO myschema, public, pg_catalog;第49页，共103页。表To Create: CREATE TABLEAdditional DISTRIBUTED BY or DISTRIBUTED RANDOMLY clauseSome syntax not supportedTo Edit: ALTER TABLECannot alter distribution key columnsTo Drop: DROP TABLEPSQL TipsTo list tables in the

18、 database:dtTo see structure of a table:d+ table_nameTo list system catalog tables:dtSTo list external tables only:dxTo see the distribution key columns of a table:d table_name第50页，共103页。表和字段约束CHECK table or column constraintsNOT NULL column constraintsUNIQUE column constraintsOne allowed per tableU

19、nique columns must also be in distribution keyNot allowed if table also has a primary keyPRIMARY KEY table constraintsUsed as distribution key by defaultFOREIGN KEY constraints definitions are supported but not enforcedForeign key relationships are utilized by the query planner to improve query plan

20、s.第51页，共103页。视图View SQL Commands: CREATE VIEWDROP VIEW PSQL Tips:To list all views while in psql: dv To see a view definition: d+ view_name EXAMPLE:CREATE VIEW topten AS SELECT name, rank, gender, year FROM names, rank WHERE rank 11 AND names.id=rank.id;SELECT * FROM topten ORDER BY year, rank;第52页，

21、共103页。索引在Greenplum数据库中应谨慎创建索引索引不一定都能优化查询应测试索引是否真正提升了性能删除没用的索引PRIMARY KEY索引会自动创建唯一性索引只能在分布键字段创建第53页，共103页。索引（续）索引类型: B-tree Bitmap索引相关SQL命令： CREATE INDEXALTER INDEXDROP INDEXREINDEXPSQL Tips:在PSQL显示所有索引： di显示索引定义: d+ index_name 大批量ETL加工最好不建索引，对性能提升作用不大B-TREE适用每次通过单一字段筛选查询少量数据B-MAP适用每次通过多个字段筛选查询大量数据第5

22、4页，共103页。其他数据库对象Functions and operatorsSequencesTriggersTablespaces第55页，共103页。数据类型常用数据类型CHAR,VARCHAR,TEXTSmallint ,integer,bigintNumeric, real,double precisionTimestamp,date,timeBooleanArray 类型。如 integer其它数据类型请参考第56页，共103页。常用系统表及视图所有系统表在 pg_catalog schema标准PostgreSQL系统表 (pg_*)常用系统表： pg_stat_activity

23、 pg_tables pg_class pg_attribute pg_namespace在psql显示所有系统表: dtSPsql显示所有系统视图: dvS其它 catalog 参考 第57页，共103页。函数日期函数Extract（day|month|year。 From date);Select date + 1 day:interval,date+ 1 month:intervalSELECT date_part(day, TIMESTAMP 2001-02-16 20:38:40); Result: 16SELECT date_trunc(hour, TIMESTAMP 2001-0

24、2-16 20:38:40); Result: 2001-02-16 20:00:00 pg_sleep(seconds);系统日期变量Current_dateCurrent_timeCurrent_timestampNow()Timeofday() 在 事务中发生变化，以上函数在事务中不变第58页，共103页。函数字符串处理函数Substr/length/lower/upper/trim/replace/positionrPad/lpadTo_char, | (字符串连接)substringlike，simillar to (模式匹配)其它杂类Case 。When/Coalescenulli

25、fgenerate_seriesIn/not in/exists/any/all第59页，共103页。Built-In Functions (SELECT)FunctionDescriptionExampleCURRENT_DATEReturns the current system date2006-11-06CURRENT_TIMEReturns the current system time16:50:54CURRENT_TIMESTAMPReturns the current system date and time2008-01-06 16:51:44.430000+00:00LOC

26、ALTIMEReturns the current system time with time zone adjustment19:50:54LOCALTIMESTAMP Returns the current system date and time with time zone adjustment2008-01-06 19:51:44.430000+00:00CURRENT_ROLE ROLEReturns the current database userjdoe第60页，共103页。Mathematical FunctionsFunctionReturnsDescriptionExa

27、mpleResults+ - * /sameAdd, Subtract, Multiply & Divide1 + 12%IntegerModulo10%20SameExponentiation224|/NumericSquare Root|/93|/NumericCube Root|/82!NumericFactorial!36& | # NumericBitwiseAnd, Or, XOR, Not91 & 1511NumericBitwiseShift left, right1 2162第61页，共103页。Mathematical Functions (Continued)Functi

28、onReturnsDescriptionExampleResultsabssameAbsolute Valueabs(-998.2)998.2ceiling(numeric)NumericReturns smallest integer not less than argumentceiling(48.2)49floor(numeric)NumericReturns largest integer not greater than argumentfloor(48.2)48pi()NumericThe constantpi()3.1419random()NumericRandom value

29、between 0.0 and 1.0random().87663round()NumericRound to nearest integerround(22.7)23第62页，共103页。String FunctionsFunctionReturnsDescriptionExampleResultsstring | stringTextString concatenationmy | mymymychar_length(string)Integernumber of chars in stringchar_length(mymy)4position(string in string)Inte

30、gerLocation of specified sub-stringposition(my in ohmy)3lower(string)TextConverts to lower caselower(MYMY)mymyupper(string)TextConverts to upper caseupper(mymy)MYMYsubstring(string from n for n)TextDisplays portion of stringsubstring(myohmy from 3 for 2)ohtrim(both,leading,trailing from string)TextR

31、emove leading and/or trailing characterstrim( mymy )mymy第63页，共103页。String Functions (Continued)FunctionReturnsDescriptionExampleResultsinitcap (string)TextChanges caseinitcap(my my)My Mylength(string)IntegerReturns string lengthlength(mymy)4split_part (string,delimiter,occurrence)TextSeparates delim

32、ited listsplit_part(one|two|three,|,2)two第64页，共103页。Date FunctionsFunctionReturnsDescriptionExampleResultsage (timestamp,timestamp)TimestampDifference in years, months and daysage(2008-08-12 timestamp, current_timestamp)0 years 1 month 11 daysextract (field from timestamp)IntegerReturns year, month,

33、 day, hour, minute or secondextract( day from current_date)11now()TimestampReturns current date & timenow()2008-09-22 11:00:01overlapsBooleanSimplifies comparing date rangesWHERE(2008-01-01,2008-02-11) overlaps(2008-02-01,2008-09-11)TRUE第65页，共103页。存储过程Greenplum支持SQL/PYTHON/PERL/C语言构建函数，以下着重介绍SQL 存储过

34、程。一个存储过程就是一个事务，包括对子过程的调用都在一个事务内存储过程结构：CREATE FUNCTION somefunc() RETURNS integer AS $DECLARE quantity integer := 30;BEGIN RETURN ;END;$ LANGUAGE plpgsql;赋值给一个变量或行/记录赋值用下面方法：identifier := expression例子：user_id := 20;执行一个没有结果的查询: PERFORM query;一个例子：PERFORM create_mv(cs_session_page_requests_mv, my_quer

35、y);存储过程请参考：第66页，共103页。存储过程动态SQLEXECUTE command-string INTO STRICT target;SELECT INTOExample：SELECT ID INTO VAR_ID FROM TABLEA获取结果状态GET DIAGNOSTICS variable = item , .;一个例子：GET DIAGNOSTICS integer_var = ROW_COUNT;SQL返回变量SQLERRM （SQL出错信息）， SQLSTATE （SQL执行返回状态编码）控制结构IF . THEN . ELSEIF . THEN . ELSELOOP

36、, EXIT, CONTINUE, WHILE, FOR 从函数返回有两个命令可以用来从函数中返回数据：RETURN 和 RETURN NEXT 。Syntax：RETURN expression;设置回调EXEC SQL WHENEVER condition action; condition 可以是下列之一： SQLERROR，SQLWARNING，NOT FOUND第67页，共103页。存储过程异常处理EXCEPTION WHEN unique_violation THEN - do nothing END;忽略错误：EXCEPTION WHEN OTHERS THEN RAISE NO

37、TICE an EXCEPTION is about to be raised; RAISE EXCEPTION NUM:%, DETAILS:%, SQLSTATE, SQLERRM;END;错误和消息RAISE level format , expression , .;Level:Info:信息输入Notice:信息提示Exception:产生一个例外，将退出存储过程Example: RAISE NOTICE Calling cs_create_job(%), v_job_id;第68页，共103页。OVER (PARTITION BY) ExampleSELECT * , row_nu

38、mber() OVER()FROM saleORDER BY cn;SELECT * , row_number() OVER(PARTITION BY cn)FROM saleORDER BY cn;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 |

39、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(8 rows)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

40、 | 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(8 rows)第69页，共103页。OVER (ORDER BY) ExampleSELECT vn, sum(prc*qty)FROM saleGROUP BY vnORDER BY 2 DESC

41、;SELECT vn, sum(prc*qty), rank() OVER (ORDER BY sum(prc*qty) DESC)FROM saleGROUP BY vnORDER BY 2 DESC;vn | sum | rank+40 | 2640002 | 130 | 180 | 250 | 0 | 320 | 0 | 310 | 0 | 3(5 rows) vn | sum+40 | 264000230 | 18050 | 020 | 010 | 0(5 rows)第70页，共103页。事务事务将多个语句捆绑为 all-or-nothing 操作事务相关命令BEGIN or STAR

42、T TRANSACTIONEND or COMMITROLLBACKSAVEPOINT and ROLLBACK TO SAVEPOINTPsql中设置自动提交模式：set autocommit on|off第71页，共103页。目录Greenplum概述和数据分布数据加载和外部表客户端工具Greenplum数据库基础Greenplum与Oracle优化策略其他要点及示例第72页，共103页。数据类型Data TypeORACLEGreenplumNumericNUMBER (p, s)SMALLINT (2 bytes)INTEGER (4 bytes)BIGINT (8 bytes)DEC

43、IMAL (p, s) (11 + p/2 bytes)NUMERIC (p, s) (11 + p/2 bytes)REAL (4 bytes)DOUBLE (8 bytes)CharacterCHAR and NCHARCHARVARCHAR2 and NVARCHAR2VARCHARDate & TimeDATE (includes time to sec)DATE or TIMESTAMP without Time ZoneTIMESTAMPTIMESTAMPINTERVALINTERVAL/TIMEBinaryBFILE ( 1 GB)Large Objects (up to 2 G

44、B)RAW BFILE ( 1 GB)BYTEACLOB and NCLOBTEXT第73页，共103页。Greenplum与Oracle比较（1）DifferenceOracleGreenplumDUALSELECT 1+1 FROM DUALSELECT 1+1; orCREATE VIEW dual AS SELECT X:VARCHAR(1) AS DUMMY; SELECT 1+1 FROM dual;NEXTVALSELECT A_TABLE_SEQUENCE.NEXTVAL FROM DUALSELECT NEXTVAL(A_TABLE_SEQUENCE) FROM DUALRO

45、WNUMSELECT * FROM AGE_TYPE WHERE ROWNUM=5SELECT * FROM AGE_TYPELIMIT 5 OFFSET 0SELECT * FROM AGE_TYPE WHERE CODE IS NOT NULLAND ROWNUM=5 ORDER BY CODE DESCSELECT * FROM AGE_TYPEWHERE CODE IS NOT NULLORDER BY CODE DESCLIMIT 5 OFFSET 0ASSELECT A.COL1 A_COL1, A.COL2 A_COL2FROM A_TABLE ASELECT A.COL1 AS

46、 A_COL1, A.COL2 AS A_COL2FROM A_TABLE A第74页，共103页。Greenplum与Oracle比较（2）DifferenceOracleGreenplum(+)SELECT * FROM A_TABLE A , B_TABLE B WHERE A.ID(+)=B.IDSELECT * FROM A_TABLE A RIGHT OUTER JOIN B_TABLE B ON A.ID=B.IDSELECT * FROM A_TABLE A , B_TABLE B WHERE A.ID(+)=B.IDAND A.COL1=COL1_VALUESELECT *

47、FROM A_TABLE A RIGHT OUTER JOIN B_TABLE B ON A.ID=B.ID AND A.COL1=COL1_VALUESELECT * FROM A_TABLE A, B_TABLE B,C_TABLE C,D_TABLE DWHERE A.ID=B.ID(+) AND A.ID=C.ID(+) ANDA.COL1=D.COL1SELECT * FROM (A_TABLE ALEFT OUTER JOIN B_TABLE BON A.ID=B.ID) LEFT OUTER JOIN C_TABLE CON A.ID=C.ID,D_TABLE DWHERE A.

48、COL1=D.COL1SELECT * FROM A_TABLE AWHERE A.COL1(+)=0 AND A.COL2(+) =A_VALUE2SELECT * FROM A_TABLE AWHERE A.COL1=0 AND A.COL2=A_VALUE2第75页，共103页。Greenplum与Oracle比较（3）DifferenceOracleGreenplumNVLSELECT NVL(SUM(VALUE11),0) FS_VALUE1,NVL(SUM(VALUE21),0) FS_VALUE2FROM FIELD_SUMSELECT COALESCE(SUM(VALUE11)

49、,0) AS FS_VALUE1,COALESCE(SUM(VALUE21),0) AS FS_VALUE2FROM FIELD_SUMTO_NUMBERSELECT COL1 FROM A_TABLEORDER BY TO_NUMBER(COL1)SELECT COL1 FROM A_TABLEORDER BY TO_NUMBER(COL1,999999)note：999999 is length of COL1DECODESELECT DECODE(ENDFLAG,1,A,B) ENDFLAGFROM TESTSELECT (CASE ENDFLAG WHEN 1 THEN A ELSE

50、B END) AS ENDFLAGFROM TEST|SELECT NULL|-|NULL AS VALUES1FROM DUALSELECT COALESCE(NULL,)|-|COALESCE(NULL,) AS VALUES1FROM DUAL第76页，共103页。Greenplum与Oracle比较（4）DifferenceOracleGreenplumSYSDATEUPDATE A_TABLESET ENTREDATE=SYSDATEUPDATE A_TABLESET ENTREDATE=CURRENT_TIMESTAMP; orUPDATE A_TABLESET ENTREDATE

51、=TO_TIMESTAMP (CURRENT_TIMESTAMP,YYYY-MM-DD HH24:MI:SS)SELECT TO_DATE(SYSDATE,YYYY-MM-DD) AS DAYFROM DUALSELECT TO_DATE (CURRENT_DATE,YYYY-MM-DD) AS DAYFROM DUALaggregateSELECT ROUND(AVG(SUM(BASICCNT1) BASICCNTFROM ACCESS_INFO_SUM1_VWHERE YEARCODE BETWEEN 200305 AND 200505GROUP BY SCCODESELECT ROUND

52、 (AVG(AIV.BASICCNT) AS BASICCNTFROM (SELECT SUM(BASICCNT1) AS BASICCNT FROM ACCESS_INFO_SUM1_V WHERE YEARCODE BETWEEN 200305 AND 200505 GROUP BY sccode ) AIVCEILSELECT CEIL (SYSDATE - TO_DATE (20051027 14:56:10,YYYYMMDD HH24:MI:SS) AS DAYSFROM DUALSELECT EXTRACT (DAY FROM (TO_TIMESTAMP(CURRENT_TIMES

53、TAMP,YYYY-MM-DD-HH24-MI-SS) -TO_TIMESTAMP (2005-10-27 14:56:10,YYYY-MM-DD-HH24-MI-SS) )+1 AS DAYSFROM DUAL第77页，共103页。Greenplum与Oracle比较（5）DifferenceOracleGreenplumSELECT LENGTH() AS VALUE1 FROM DUALResultVALUE1=NULLSELECT LENGTH() AS VALUE1 FROM DUALResultVALUE1=0SELECT TO_DATE(,YYYYMMDD) AS VALUE2

54、FROM DUALResultVALUE2=NULLSELECT TO_DATE(,YYYYMMDD) AS VALUE2 FROM DUALResultVALUE2=0001-01-01 BCSELECT TO_NUMBER(,1) AS VALUE3 FROM DUALResultVALUE3=NULLSELECT TO_NUMBER(,1) AS VALUE3 FROM DUALResult cannot executeINSERT INTO TEST(VALUE4) VALUES()ResultVALUE4=NULLnote: VALUE4 is numeric typeINSERT

55、INTO TEST(VALUE4) VALUES()ResultVALUE4=0note: VALUE4 is numeric typeINSERT INTO TEST(VALUE5) VALUES()ResultVALUE5=NULLnote: VALUE5 is character typeINSERT INTO TEST(VALUE5) VALUES()ResultVALUE5=note: VALUE5 is character type, length equal 0INSERT INTO TEST(VALUE6) VALUES(TO_DATE(,YYYYMMDD)ResultVALU

56、E6=NULLnote: VALUE6 is time typeINSERT INTO TEST(VALUE6) VALUES(TO_DATE(,YYYYMMDD)ResultVALUE6=0001-01-01 BCnote: VALUE6 is time type第78页，共103页。Greenplum与Oracle比较（6）DifferenceOracleGreenplumNULLIFnot support NULLIFSELECT NULLIF(VALUE1,VALUE2) AS COL1 FROM DUALCONCATCONCAT(CHAR,CHAR)CREATE FUNCTION C

57、ONCAT(CHAR,CHAR)RETURNS CHAR AS SELECT $1 | $2 LANGUAGE sql;ADD_MONTHSADD_MONTHS(date, int)CREATE FUNCTION add_months(date, int) RETURNS date AS SELECT ($1 + ( $2:text | months):interval):date; LANGUAGE sql;LAST_DAYLAST_DAY(DATE)CREATE FUNCTION LAST_DAY(DATE)RETURNS DATE ASSELECT date(substr(text($1

58、 + interval(1 month),1,7)|-01)-1LANGUAGE sql;MONTH_BETWEENMONTH_BETWEEN(DATA,DATA)CREATE FUNCTION MONTH_BETWEEN(DATA,DATA)RETURNS NUMERIC ASSELECT to_number(date($1)- date($2),999999999)/31LANGUAGE sql;BIN_TO_NUMSELECT BIN_TO_NUM(1,0,1,0) AS VALUE1 FROM DUALSELECT CAST(B1010 AS INTEGER) AS VALUE1BIT

59、ANDBITAND(int,int)SELECT int & int第79页，共103页。目录Greenplum概述和数据分布数据加载和外部表客户端工具Greenplum数据库基础Greenplum与Oracle优化策略其他要点及示例第80页，共103页。查看执行计划查看sql执行计划的命令:EXPLAIN EXPLAIN ANALYZE 查看执行计划:一般从最后面往上查看以下命令可能会包含Gather, Redistribute, Broadcast等操作JoinsSortsAggregations每步操作给出如下度量：cost (units of disk page fetches)row

60、s (rows output by this node)width (byte count of the widest row produced by this node)第81页，共103页。执行计划（EXPLAIN）EXPLAIN SELECT * FROM names WHERE name = Joelle; QUERY PLAN Gather Motion 2:1 (slice1) (cost=0.00.20.88 rows=1 width=13) - Seq Scan on names (cost=0.00.20.88 rows=1 width=13) Filter: name:te