![Greenplum-数据库开发基础ppt课件(PPT 103页)_第1页](http://file4.renrendoc.com/view/ef5091765314c5e82e03a078376cff53/ef5091765314c5e82e03a078376cff531.gif)
![Greenplum-数据库开发基础ppt课件(PPT 103页)_第2页](http://file4.renrendoc.com/view/ef5091765314c5e82e03a078376cff53/ef5091765314c5e82e03a078376cff532.gif)
![Greenplum-数据库开发基础ppt课件(PPT 103页)_第3页](http://file4.renrendoc.com/view/ef5091765314c5e82e03a078376cff53/ef5091765314c5e82e03a078376cff533.gif)
![Greenplum-数据库开发基础ppt课件(PPT 103页)_第4页](http://file4.renrendoc.com/view/ef5091765314c5e82e03a078376cff53/ef5091765314c5e82e03a078376cff534.gif)
![Greenplum-数据库开发基础ppt课件(PPT 103页)_第5页](http://file4.renrendoc.com/view/ef5091765314c5e82e03a078376cff53/ef5091765314c5e82e03a078376cff535.gif)
版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领
文档简介
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
温馨提示
- 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
- 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
- 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
- 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
- 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
- 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
- 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。
最新文档
- 教师培训反思心得体会
- 充电桩施工合同(充电桩租赁合同)
- 公务员体检流程
- 浅谈生态环境制约条件下的村庄规划探索-以天津市蓟县东头百户村为例
- 初中教科研课题:“初中课程《历史》教材用图现状分析及策略研究”课题研究方案
- 高中二年级上学期英语《选择性必修一 Unit 1 Grammar》教学设计
- 团体心理治疗疗效因子课件
- 桂平市2023-2024学年数学三下期末质量跟踪监视试题含解析
- 贵州省遵义市务川仡佬族苗族自治县2024届数学四下期末教学质量检测试题含解析
- 人工肺产品市场环境与对策分析
- 安全科学基本概念
- 中国银行银行面试问题及答案
- 地下室工程文明施工及安全管理措施
- 2022年中图版七年级地理下册期末考试卷及答案【可打印】
- 校本课程的含义、历史、意义校本课程研究之一
- 四年级语文上册 第四单元 大单元一等奖创新教学设计
- 第二单元整合主题课《重拾爱的记忆》 统编版语文七年级上册
- 服装款式廓形设计
- 《水调歌头》PPT-完美版
- 形象礼仪培训教材课件
- 医疗影像未来发展趋势报告PPT
评论
0/150
提交评论