Oracle专题培训

个人2011Oracle培训-专题主题详细描述hanshouzhong中国.上海目 录一 并行执行4n含义4n启用并行查询4n启用PDML4nPDML限制4nPDDL范围5nPDDL说明5二 分析函数5n语法5n子句总结11n窗口实验12n分类13n分析函数描述13n例子14三 直接路径插入36n含义36n支持类型36n使用方法36n限制36n缺点36四 物化视图37五 聚合函数扩展37nROLLUP子句37nCUBE子句39nGROUPING()函数与ROLLUP、CUBE的结合使用40nGROUPING_ID（）函数的使用41六 DBMS_SQL43n使用场合43n使用过程43七 DBMS_SCHEDULER44八 层次查询44九 自治事务44一 并行执行n 含义 所谓并行执行,是指能够将一个大型串行任务(任何DML,或一般的DDL)物理地划分为多个较 小的部分,这些较小的部分能够可以同时得到处理.包括并行查询,并行DML(INSERT,UPDATE,DELETE,MERGE),并行DDL(索引重建,创建索 引,数据加载,表重组等),并行恢复和过程并行化.并行执行本质上是一个不可扩缩的解决方案,设计为允许单个用户或一个特定SQL语句占用 数据库的所有资源.所以,在用并行执行之前,需要保证以下两点:1) 必须有一个非常大的任务;2) 必须有足够的可用资源.n 启用并行查询默认情况下，并不启用并行查询。有两种方法启用该功能：1) Hint2) 直接修改表并行度 alter table big_table parallel;由Oracle根据系统的负载情况决定并行数。alter table big_table parallel 4;n 启用PDMLPDML和并行查询是不同的,除非显示的请求PDML,否则不能执行PDML.1) alter table big_table parallel 4;2) alter session enable parallel dml;n PDML限制1)PDML操作期间不支持触发器;2) PDML操作期间不支持某些声明方式引用完整性约束 3) 在提交或回滚之前,不能访问用PDML修改的表 4) PDML不支持高级复制(因为复制特性的实现是给予触发器)5) 不支持延迟约束 6) 不支持分布式事务 7) 不支持聚促表 8) 如果表是分区的,PDML只可能在有位图索引或LOB列的表上执行,并且并行度取决于分区数.如果违反了其中任何一个原则,则语句会串行执行或报错.n PDDL范围 PDDL包括下面几种操作:1) Create index2) Create table as select3) Alter index rebuild4) Alter table move5) Alter table split|coalesce partition6) Alter index split partition1) 到4)还适应于单个的表/索引分区. n PDDL说明 1)PDDL操作依赖于直接路径操作,也就是说,数据不传递到缓冲区缓存,而是由一个操作来创建新的 extend,并直接写入这些extend. 2) PDDL3) 在提交或回滚之前,不能访问用PDML修改的表 二 分析函数n 语法Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。其语法如下：FUNCTION_NAME(,)OVER()解释如下：1) FUNCTION_NAME分析函数名；2) Argement分析函数的参数，每个函数一般有03个参数，参数可以是任何数字类型或是可以隐式转换为数字类型的数据类型，参数可以是表达式；3) Over:关键字，用于标识分析函数，否则不能区别是分析函数还是聚合函数，Over后面的子句描述了分析函数将其执行的数据片段；Over后面的()部分，即使analytic_clause的三个部分全不使用，()也必不可省，否则Oracle因不能识别函数为分析函数而报错。分析函数在查询结果集确定之后才开始进行计算，Analytic_clause就是用来定义函数怎样对查询结果集进行分组计算的。4) Partition-Clause:其语法为PARTITION BY expression .是可选的分区子句，可以使用它来将结果集分组，并将分析函数应用于各组中，而不是全部的结果集中，如果不存在任何的分区子句，则全部的结果集可看做一个单一的大区。5) Order-by-Clause：其语法为ORDER BY expression , expr ASC | DESC NULLS FIRST | NULLS LAST . 。是可选的排序子句，有些分析函数需要它，有些分析函数不需要它。依靠已排序数据的那些分析函数，必须使用该子句，如LAG，其他函数则不需要，如AVG。在使用任何排序的开窗函数时，该子句是强制性的，它指定了在计算分析函数时一组内的数据是如何排序的。Order-by-Clause轮流应用于每个分区中。6) Windowing-Clause：该子句给出了一个定义变化或固定的数据窗口方法，分析函数将对这些数据进行操作。在一组内基于任意变化或固定的窗口中，可用该子句来让分析函数计算它的值。可根据两个标准建立窗口：数据值的范围（RANGE）或与当前行的偏移量（ROW）。注意的是，要使用窗口，必须使用ORDER BY子句。Range窗口：Range指定窗口由物理行构成。该窗口仅对NUMBERS和DATE起作用，否则是无效的。另外的限制是ORDER BY中只能有一列，因为范围实际上是一维的，不能在N维空间中。ROW窗口ROW指定窗口由逻辑偏移量构成。利用ROW窗口，就没有RANGE那样的限制了，数据可以是任意类型的，且ORDER BY可以包括很多列。specifying窗口范围：A ) UNBOUNDED PRECEDING: 指明窗口开始于分组的第一行；B） Unbounded following指明窗口结束于分组的最后一行；C) CURRENT ROW：作为起始点，指明窗口开始于当前行或当前值；作为终结点，指明窗口结束于当前行或当前值；D) BETWEEN: betweenand是关键字，用来指定窗口的起始点和终结点。当窗口范围为Range between unbounded preceding and current row 或Range between current row and unbounded following 时，可以在order_by_clause中使用多个expr对分组进行排序。其他range窗口范围只能使用一个expr。使用rows定义窗口范围无此限制。E） Numberic Expression PRECEDING:对ROW来说，该窗口从当前行之前的数字表达式（Numberic Expression）的行开始；对RANGE来说，则从行序值小于数字表达式的 当前行的值开始,并结束于正在处理的当前行。不论rows还是range窗口，窗口总是在分组中从上至下滑动的。窗口范围可以由betweenand限定，也可以不用betweenand，不用的都表示窗口到当前行结束。ROWS窗口是由分组排序后分组中若干连续的行构成的窗口。以下是全部合法的ROWS窗口范围:1. Rows between unbounded preceding and unbounded following窗口开始于分组第一行，结束于分组最后一行。2. Rows between unbounded preceding and current row窗口开始于分组第一行，结束于当前行。3. Rows between unbounded preceding and value_expr preceding窗口开始于分组第一行，结束于当前行前value_expr行。4. Rows between unbounded preceding and value_expr following窗口开始于分组第一行，结束于当前行后value_expr行。5. Rows between current row and unbounded following窗口开始于当前行，结束于分组最后一行。6. Rows between current row and current row窗口开始于当前行，结束于当前行。7. Rows between current row and value_expr following窗口开始于当前行，结束于当前行后value_expr行。8. Rows between value_expr preceding and unbounded following窗口开始于当前行前value_expr行，结束于分组最后一行。9. Rows between value_expr preceding and current row窗口开始于当前行前value_expr行，结束于当前行。10. Rows between value_expr1 preceding and value_expr2 preceding窗口开始于当前行前value_expr1行，结束于当前行前value_expr2行。11. Rows between value_expr1 preceding and value_expr2 following窗口开始于当前行前value_expr1行，结束于当前行后value_expr2行。12. Rows between value_expr following and unbounded following窗口开始于当前行后value_expr行，结束于分组最后一行。13. Rows between value_expr1 following and value_expr2 following窗口开始于当前行后value_expr1行，结束于当前行后value_expr2行。14. Rows unbounded preceding与2等价。15. Rows current row与6等价。16. Rows value_expr preceding与9等价。RANGE窗口(1).对于单个expr，相当于给order_by_clause中的expr加一个where限定条件，即where expr between a and b构成了一个逻辑窗口，此窗口在expr包含的行上滑动求值。a和b的值可结合windowing_clause来求值确定。order_by_clause子句为asc和desc，a和b的求值可能有所不同。假设分组排序后第一行的值为first_value，最后一行的值为last_value。当前行的值为current_value。unbounded preceding = first_valueunbounded following = last_valuecurrent row = current_valuevalue_expr preceding = current_value -/+ value_exprvalue_expr following = current_value +/- value_expr下面是具体情况分类分析：1. Range between unbounded preceding and unbounded following当order_by_clause使用asc排序时，逻辑窗口如下：Where expr between first_value and last_value 当order_by_clause使用desc排序时，逻辑窗口如下：Where expr between last_value and first_value 2. Range between unbounded preceding and current row当order_by_clause使用asc排序时，逻辑窗口如下：Where expr between first_value and current_value 当order_by_clause使用desc排序时，逻辑窗口如下：Where expr between current_value and first_value3. Range between unbounded preceding and value_expr preceding当order_by_clause使用asc排序时，逻辑窗口如下：Where expr between first_value and current_value-value_expr 当order_by_clause使用desc排序时，逻辑窗口如下：Where expr between current_value+value_expr and first_value4. Range between unbounded preceding and value_expr following当order_by_clause使用asc排序时，逻辑窗口如下：Where expr between first_value and current_value+value_expr 当order_by_clause使用desc排序时，逻辑窗口如下：Where expr between current_value-value_expr and first_value5. Range between current row and unbounded following当order_by_clause使用asc排序时，逻辑窗口如下：Where expr between current_value and last_value 当order_by_clause使用desc排序时，逻辑窗口如下：Where expr between last_value and current_value6. Range between current row and current row不论order_by_clause使用何种排序时，逻辑窗口如下：Where expr = current_value7. Range between current row and value_expr following当order_by_clause使用asc排序时，逻辑窗口如下：Where expr between current_value and current_value+value_expr 当order_by_clause使用desc排序时，逻辑窗口如下：Where expr between current_value-value_expr and current_value 8. Range between value_expr preceding and unbounded following当order_by_clause使用asc排序时，逻辑窗口如下：Where expr between current_value-value_expr and last_value当order_by_clause使用desc排序时，逻辑窗口如下：Where expr between last_value and current_value+value_expr9. Range between value_expr preceding and current row当order_by_clause使用asc排序时，逻辑窗口如下：Where expr between current_value-value_expr and current_value当order_by_clause使用desc排序时，逻辑窗口如下：Where expr between current_value and current_value+value_expr10. Range between value_expr1 preceding and value_expr2 preceding当order_by_clause使用asc排序时，逻辑窗口如下：Where expr between current_value-value_expr1 and current_value-value_expr2当order_by_clause使用desc排序时，逻辑窗口如下：Where expr between current_value+value_expr2and current_value+value_expr111. Range between value_expr1 preceding and value_expr2 following当order_by_clause使用asc排序时，逻辑窗口如下：Where expr between current_value-value_expr1 and current_value+value_expr2当order_by_clause使用desc排序时，逻辑窗口如下：Where expr between current_value+value_expr2 and current_value-value_expr112. Range between value_expr following and unbounded following当order_by_clause使用asc排序时，逻辑窗口如下：Where expr between current_value+value_expr and last_value 当order_by_clause使用desc排序时，逻辑窗口如下：Where expr between last_value and current_value-value_expr13. Range between value_expr1 following and value_expr2 following当order_by_clause使用asc排序时，逻辑窗口如下：Where expr between current_value+value_expr1 and current_value+value_expr2当order_by_clause使用desc排序时，逻辑窗口如下：Where expr between current_value-value_expr2 and current_value-value_expr114. Range unbounded preceding与2等价。15. Range current row与6等价。16. Range value_expr preceding与9等价。(2).对于在order_by_clause中可以使用多个expr的窗口： u Range between unbounded preceding and current row Range betweenu current row and unbounded following当它们使用多个expr排序时(注意不是一个)分别等价于： u Rows between unbounded preceding and current row Rows betweenu current row and unbounded following注意事项：1.若windowing_clause由rows指定，则：(1)value_expr是物理偏移量，它必须是常量或值为非负数的表达式。(2)若value_expr是起点的一部分，那末它必须在终点之前对行求值。2.若windowing_clause由range指定，则:(1)value_expr是逻辑偏移量。它必须是常量或值为非负数的表达式或时间间隔文字常量。(2)若value_expr值为一个数字，那末order_by_clause中 expr必须为数字或date类型。(3)若value_expr为一个间隔值，那末order_by_clause中expr必须是一个date类型。3.若完全忽略windowing_clause，那末默认的窗口范围为 range between unbounded preceding and current rown 子句总结query_partition_by、order_by_clause和windowing_clause三个子句都是可选的，将三个子句分别简记为P，O，W。合法的组合方式有如下6种：1). POW(query_partition_clause order_by_clause windowing_clause)分组，排序，定义窗口范围2). PO(query_partition_clause order_by_clause)分组，排序，窗口默认为range between unbounded preceding and current row3). P(query_partition_clause)分组，不排序，没有窗口(range between unbounded preceding and unbounded following)4). OW(order_by_clause windowing_clause)分组为整个查询结果集，排序，定义窗口范围5). O(order_by_clause)分组为整个查询结果集，排序，窗口默认为range between unbounded preceding and current row6). NULL()分组为整个查询结果集，不排序，没有窗口(range between unbounded preceding and unbounded following)因为只有存在order_by_clause，才能有windowing_clause，故不存在如下两种形式的组合：PW(query_partition_clause windowing_clause)W(windowing_clause)n 窗口实验create table window_tmp(aa number(5) NOT NULL);insert into window_tmp values(1);insert into window_tmp values(2);insert into window_tmp values(2);insert into window_tmp values(2);insert into window_tmp values(3);insert into window_tmp values(4);insert into window_tmp values(5);insert into window_tmp values(6);insert into window_tmp values(7);insert into window_tmp values(9);insert into window_tmp values(11);commit;- 排序后select aa,sum(aa) over (order by aa range between 1 preceding and 2 following) from window_tmp;AASUM(AA)OVER(ORDERBYAARANGEBETW11021421421431841852261872292011111. 帮助理解:就是说，对于aa=5的一行 ，sum为 5-1=aa=5+2 的和, 所以求的是4,5,6,7的和=22 对于aa=2来说 ，求的是2-1=aa=2+2, 即1,2,2,2,3,4的和=10 对于aa=9 ，9-1=aa=9+2 有9和11两个数，所以sum=20 对于aa=11, 11-1=aa=11+2,只有11,所以sum=112. 其它： over（order by salary rows between 2 preceding and 4 following） 每行对应的数据窗口是之前2行，之后4行 3. 下面三条语句等效： over（order by salary rows between unbounded preceding and unbounded following） 每行对应的数据窗口是从第一行到最后一行，等效： over（order by salary range between unbounded preceding and unbounded following） 等效 over(partition by null)n 分类总结：1). 对于是否存在order_by_clause，分析函数可以分为两类，含有order_by_clause的一般称为windowing function，不含的称为reporting function。2). Windowing function，对查询结果集进行分组，排序，根据窗口范围计算分组中每一行的函数结果。3). Reporting function，对查询结果集进行分组，不排序，窗口范围为整个分组，在每一个分组内，计算整个分组的函数值，再将函数值分别赋给分组内的每一行。n 分析函数描述SUM ：该函数计算组中表达式的累积和MIN ：在一个组中的数据窗口中查找表达式的最小值MAX ：在一个组中的数据窗口中查找表达式的最大值AVG ：用于计算一个组和数据窗口内表达式的平均值。COUNT ：对一组内发生的事情进行累积计数-RANK ：根据ORDER BY子句中表达式的值，从查询返回的每一行，计算它们与其它行的相对位置DENSE_RANK ：根据ORDER BY子句中表达式的值，从查询返回的每一行，计算它们与其它行的相对位置FIRST ：从DENSE_RANK返回的集合中取出排在最前面的一个值的行LAST ：从DENSE_RANK返回的集合中取出排在最后面的一个值的行FIRST_VALUE ：返回组中数据窗口的第一个值LAST_VALUE ：返回组中数据窗口的最后一个值。LAG ：可以访问结果集中的其它行而不用进行自连接LEAD ：LEAD与LAG相反，LEAD可以访问组中当前行之后的行ROW_NUMBER ：返回有序组中一行的偏移量，从而可用于按特定标准排序的行号-STDDEV ：计算当前行关于组的标准偏离STDDEV_POP：该函数计算总体标准偏离，并返回总体变量的平方根STDDEV_SAMP：该函数计算累积样本标准偏离，并返回总体变量的平方根VAR_POP ：该函数返回非空集合的总体变量（忽略null）VAR_SAMP ：该函数返回非空集合的样本变量（忽略null）VARIANCE ：如果表达式中行数为1，则返回0，如果表达式中行数大于1，则返回VAR_SAMPCOVAR_POP ：返回一对表达式的总体协方差COVAR_SAMP ：返回一对表达式的样本协方差CORR ：返回一对表达式的相关系数-CUME_DIST ：计算一行在组中的相对位置NTILE ：将一个组分为表达式的散列表示PERCENT_RANK ：和CUME_DIST（累积分配）函数类似PERCENTILE_DISC ：返回一个与输入的分布百分比值相对应的数据值PERCENTILE_CONT ：返回一个与输入的分布百分比值相对应的数据值RATIO_TO_REPORT ：该函数计算expression/(sum(expression)的值，它给出相对于总数的百分比REGR_ (Linear Regression) Functions ：这些线性回归函数适合最小二乘法回归线，有9个不同的回归函数可使用-n 例子下面的例子都基于scott模式下的emp表。SELECT * FROM emp;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO7839 KING PRESIDENT 1981-11-17 5000.00 107698 BLAKE MANAGER 7839 1981-5-1 2850.00 307782 CLARK MANAGER 7839 1981-6-9 2450.00 107566 JONES MANAGER 7839 1981-4-2 2975.00 207654 MARTIN SALESMAN 7698 1981-9-281250.00 1400.00 307499 ALLEN SALESMAN 7698 1981-2-201600.00300.00 307844 TURNERSALESMAN 7698 1981-9-8 1500.00 0.00 307900 JAMES CLERK 7698 1981-12-3 950.00 307521 WARD SALESMAN 7698 1981-2-221250.00500.00 307902 FORD ANALYST 7566 1981-12-33000.00 207369 SMITH CLERK 7902 1980-12-17 800.00 207788 SCOTT ANALYST 7566 1982-12-9 3000.00 207876 ADAMS CLERK 7788 1983-1-12 1100.00 207934 MILLER CLERK 7782 1982-1-23 1300.00 106.1. E1SELECT SUM(sal) over() sal_sum FROM emp;1. 聚集函数用作分析函数时，不需要group by子句；2. 分析函数作为表列可以有别名，但必须放在over()之后。6.2. E2SELECT empno, ename, job, SUM(sal) over(PARTITION BY mgr ORDER BY job)FROM emp;1. 分析函数的分组与选择列也没有任何关系，比如上例，在选择列中可以有mgr，也可以没有mgr；2. 分析函数的排序与选择列也没有任何关系，比如上例，在选择列中可以没有job。6.3. E3SELECT empno, ename, mgr, salFROM empORDER BY empno, row_number() over(PARTITION BY mgr ORDER BY sal), SUM(1) over(PARTITION BY empno)1. 分析函数可用于order by子句中；2. Order by后可以用多个分析函数，也可以与其他排序列混合使用。6.4. E4(pow)SELECT empno, ename, mgr, sal, hiredate, SUM(sal) over(PARTITION BY mgr ORDER BY hiredate RANGE BETWEEN unbounded preceding AND unbounded following)FROM emp; EMPNO ENAME MGR SAL HIREDATE SAL_SUM7902 FORD 7566 3000.00 1981-12-3 60007788 SCOTT 7566 3000.00 1982-12-9 60007499 ALLEN 7698 1600.00 1981-2-20 65507521 WARD 7698 1250.00 1981-2-22 65507844 TURNER7698 1500.00 1981-9-8 65507654 MARTIN7698 1250.00 1981-9-28 65507900 JAMES 7698 950.00 1981-12-3 65507934 MILLER 7782 1300.00 1982-1-23 13007876 ADAMS 7788 1100.00 1983-1-12 11007566 JONES 7839 2975.00 1981-4-2 82757698 BLAKE 7839 2850.00 1981-5-1 82757782 CLARK 7839 2450.00 1981-6-9 82757369 SMITH 7902 800.00 1980-12-178007839 KING 5000.00 1981-11-17 5000按mgr分组，hiredate排序，窗口范围为整个分组。分析函数sum对每个窗口中的所有雇员薪水求和，再将它赋给每一个雇员，故相同mgr的雇员的sal_sum都是相同的。6