-------------------------------------------------------------------------------------------------------
- --Lerning Content :Oracle 12C PL/SQL新特性（上半部分）
- --Author :如人饮水冷暖自知
- --Description :PL/SQL Lanaguage Reference 关于Change in This Release For Oracle Database PLSQL
Language Reference有具体描述的特性清单
- --Remark :
-------------------------------------------------------------------------------------------------------
1.对于部分12c的PL/SQL新特性改进如下：
1.1调用者权限函数结果集缓存技术
1.2Oracle PL/SQL特有的更多数据类型可以跨越PL/SQL到SQL的接口子句
1.3ACCESSIBLE BY新特性
1.4FETCH FIRST新特性
1.5可将角色授予PL/SQL包和独立子程序
1.6更多的数据类型在SQL和PL/SQL中具有相同的最大大小
1.7可插拔数据库上的触发器设置
1.8library可定义为directory对象，并可带有credential子句
1.9隐式语句结果集
1.10BEQUEATH CURRENT_USER视图
1.11INHERIT PRIVILEGES 和INHERIT ANY PRIVILEGES
1.12不可见列
1.13如何在sql中运行PL/SQL
1.14预定义的查询命令：$$PLSQL_UNIT_OWNER和$$PLSQL_UNIT_TYPE
1.15编译参数PLSQL_DEBUG弃用
-------------------------------------------------------------------------------------------------------
2.调用者权限函数结果集缓存技术：
2.1概念及其功能介绍：
PL/SQL函数结果缓存机制提供了一种语言支持和系统管理的方式，可以在共享全局区域(SGA)中缓存PL/SQL函数的
结果，每个运行应用程序的会话都可以使用这个共享全局区域。缓存机制既高效又易于使用，并且减轻了设计和
开发过程中带来的缓存管理策略的负担
当调用结果缓存的函数时，系统会检查缓存。如果缓存包含具有相同参数值的函数先前调用的结果，则系统将缓存
的结果返回给调用者，并且不会重新执行函数体。如果缓存不包含结果，系统将运行函数体，并将结果
(对于这些参数值)添加到缓存中，然后将控制权返回给调用者。
注意：如果函数执行导致未处理的异常，则异常结果不会存储在缓存中
Oracle数据库自动检测在运行结果缓存函数时查询的所有数据源(表和视图)。如果对这些数据源中的任何一个进行
了更改，那么缓存的结果将变得无效，并且必须重新计算。
2.2代码示例：
CREATE OR REPLACE PACKAGE PKG_DEPARTMENT AUTHID DEFINER IS
TYPE DEPT_INFO_RECORD IS RECORD(
DEPT_NAME SCOTT.DEPT.DNAME%TYPE,
V_MGR SCOTT.EMP.MGR%TYPE,
DEPT_SIZE PLS_INTEGER);
FUNCTION FUNC_GET_DEPT_INFO(P_DEPTNO NUMBER) RETURN DEPT_INFO_RECORD RESULT_CACHE;
END PKG_DEPARTMENT;
/
CREATE OR REPLACE PACKAGE BODY PKG_DEPARTMENT IS
FUNCTION FUNC_GET_DEPT_INFO(P_DEPTNO NUMBER) RETURN DEPT_INFO_RECORD RESULT_CACHE IS
REC DEPT_INFO_RECORD;
BEGIN
SELECT DNAME
INTO REC.DEPT_NAME
FROM SCOTT.DEPT
WHERE SCOTT.DEPT.DEPTNO = P_DEPTNO;
SELECT COUNT(*)
INTO REC.DEPT_SIZE
FROM SCOTT.EMP
WHERE SCOTT.EMP.DEPTNO = P_DEPTNO;
RETURN REC;
END FUNC_GET_DEPT_INFO;
END PKG_DEPARTMENT;
2.3结果集缓存注意事项：
2.3.1要进行结果缓存，函数必须满足所有这些条件:
2.3.2它没有在匿名块中定义。
2.4它不是表函数。
2.5它不引用字典表、临时表、序列或非确定性SQL函数。
2.6它没有OUT或IN OUT参数
2.7这些类型均不支持：BLOB CLOB NCLOB REF CURSOR Collection Object Record
2.8对于返回值均不支持：BLOB CLOB NCLOB REF CURSOR Object
2.4考虑一个具有配置参数的应用程序，可以在全局级别、应用程序级别或角色级别设置这些参数。
应用程序将配置信息存储在这些表中，例如如下代码
-- 全局配置设置
DROP TABLE T_600_CONFIG_PARAMS CASCADE CONSTRAINTS PURGE;
CREATE TABLE T_600_CONFIG_PARAMS
(name VARCHAR2(20), -- 参数名称
val VARCHAR2(20), -- 参数值
PRIMARY KEY (name)
)
SEGMENT CREATION IMMEDIATE
NOLOGGING
;
-- 应用程序级别配置及其设置
CREATE TABLE T_600_APPLICATION_LEVEL
(app_id VARCHAR2(20), -- 应用程序 ID
name VARCHAR2(20), -- 参数名称
val VARCHAR2(20), -- 参数值
PRIMARY KEY (app_id, name)
);
-- 规则级别设置
CREATE TABLE T_600_RULE_LEVEL
(role_id VARCHAR2(20), -- 应用程序的rule_id
name VARCHAR2(20), -- 参数名
val VARCHAR2(20), -- 参数值
PRIMARY KEY (role_id, name)
);
注：对于每个配置参数，角色级别设置覆盖应用程序级别设置，应用程序级别设置覆盖全局设置。为了确定应用于参数
的设置，应用程序定义了PL/SQL函数get_value。给定参数名、应用程序ID和角色ID, get_value返回应用于参数的设置
显示了get_value的可能定义。假设对于一组参数值，全局设置决定了get_value的结果。当get_value运行时，数
据库检测到三个表被查询——T_600_CONFIG_PARAMS，T_600_APPLICATION_LEVEL，T_600_RULE_LEVEL如果提交了对这三个
表中的任何一个的更改，那么这组参数值的缓存结果将无效，并且必须重新计算
CREATE OR REPLACE FUNCTION FUNC_GET_VALUE(P_PARAM VARCHAR2,
P_APP_ID NUMBER,
P_ROLE_ID NUMBER) RETURN VARCHAR2 RESULT_CACHE
AUTHID DEFINER IS
ANSWER VARCHAR2(20);
BEGIN
-- 参数设置在角色级别
BEGIN
SELECT VAL
INTO ANSWER
FROM T_600_RULE_LEVEL
WHERE ROLE_ID = P_ROLE_ID
AND NAME = P_PARAM;
RETURN ANSWER;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
--参数是否在应用程序级别设置
BEGIN
SELECT VAL
INTO ANSWER
FROM T_600_APPLICATION_LEVEL
WHERE APP_ID = P_APP_ID
AND NAME = P_PARAM;
RETURN ANSWER; -- Found
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL; -- Fall through to following code
END;
-- 参数是否设置在全局级别
SELECT VAL INTO ANSWER FROM T_600_CONFIG_PARAMS WHERE NAME = P_PARAM;
RETURN ANSWER;
END FUNC_GET_VALUE;
2.5总结：
2.5.1在oracle中创建存储过程子程序，可以把它创建成为定义者权限，也就是definer或者调用者权限，也就是
invoker,定义者权限以所有者权限执行，那么调用者权限则是以调用特定子程序的用户权限执行，缺省都是
定义者权限，除非特别指定，刚才是12c之前版本的范例，在12c之前调用者权限authid current_user创建的
函数结果集不能被缓存，那么12c后提供一种新的方式，例如如下代码：
CREATE OR REPLACE FUNCTION FUNC_GET_RESOURCE(P_PARAMETER IN NUMBER)
RETURN SCOTT.EMP%ROWTYPE AUTHID CURRENT_USER <------注意这里是调用者权限
RESULT_CACHE RELIES_ON(SCOTT.EMP) <------注意这里有语法关键字
IS
V_EMP SCOTT.EMP%ROWTYPE;
BEGIN
SELECT * INTO V_EMP FROM SCOTT.EMP WHERE EMPNO = P_PARAMETER;
RETURN V_EMP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
END FUNC_GET_RESOURCE;
2.6问题及其注意事项：
2.6.1对于PL/SQL缓存结果集在oracle rac环境的工作原理是什么，如何缓存的？
答：缓存的结果存储在系统全局区域(SGA)中。在Oracle RAC环境中，每个数据库实例管理自己的本地函数结
果缓存。但是，本地结果缓存的内容可以访问附加到其他Oracle RAC实例的会话。如果本地实例的结果缓存中
缺少所需的结果，则可以从另一个实例的本地缓存中检索结果，而不是本地计算结果
注意：实例的访问模式和工作负载决定其本地缓存中的结果集;因此，不同实例的本地缓存可以有不同的结果集。
虽然每个数据库实例可能有自己的缓存结果集，但是处理无效结果的机制是Oracle RAC环境范围内的。如果结果
仅在本地实例的结果缓存中无效，其他实例可能使用无效结果。例如，考虑从数据库表中的数据计算出的项价
格的结果缓存。如果以影响项目价格的方式更新这些数据库表中的任何一个，则必须在Oracle RAC环境中的每
个数据库实例中使该项目的缓存价格无效
2.6.2结果集缓存如何管理和查询呢？
答：DBA可以通过如下方式管理结果集缓存内容：
RESULT_CACHE_MAX_SIZE和RESULT_CACHE_MAX_RESULT初始化参数
注意：RESULT_CACHE_MAX_SIZE指定结果缓存可以使用的SGA内存(以字节为单位)的最大数量，
RESULT_CACHE_MAX_RESULT指定任何单个结果可以使用的结果缓存的最大百分比。
具体内容详见Oracle Database Reference for more information about RESULT_CACHE_MAX_SIZE
Oracle Database Reference for more information about RESULT_CACHE_MAX_RESULT
Oracle Database Performance Tuning Guide for more information about Result Cache concepts
2.6.3PL/SQL这个所谓的结果集缓存如何调用的？
答：DBMS_RESULT_CACHE包提供了一个接口，允许DBA管理SQL结果缓存和PL/SQL函数结果缓存使用的共享池的
一部分。有关这个包的更多信息，请参见Oracle Database PL/SQL Packages and Types Reference.以下为
具体问题排查用到的主要动态性能视图：
[G]V$RESULT_CACHE_STATISTICS
[G]V$RESULT_CACHE_MEMORY
[G]V$RESULT_CACHE_OBJECTS
[G]V$RESULT_CACHE_DEPENDENCY
-------------------------------------------------------------------------------------------------------
3.在Oracle12c中可以使用基于集合或者记录类型的绑定变量，但有个限制就是集合或者是记录数据类型必须在package
规范中声明，例如如下代码：
DROP TABLE T_600_TEST CASCADE CONSTRAINT PURGE;
/
CREATE TABLE T_600_TEST(
COL1 NUMBER,
COL2 VARCHAR2(50)
)
SEGMENT CREATION IMMEDIATE
NOLOGGING
TABLESPACE TBS_ORACLE
MONITORING
;
/
CREATE OR REPLACE PACKAGE PKG_ADMIN IS
TYPE TYPE_ROW_NUMBER IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
TYPE TYPE_TEXT IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
PROCEDURE PROC_POPULATE(P_ROW_NUMBER TYPE_ROW_NUMBER, P_TEXT TYPE_TEXT);
PROCEDURE PROC_UPDATE(P_ROW_NUMBER TYPE_ROW_NUMBER, P_TEXT TYPE_TEXT);
PROCEDURE PROC_DELETE(P_ROW_TABLE TYPE_ROW_NUMBER);
END PKG_ADMIN;
/
CREATE OR REPLACE PACKAGE BODY PKG_ADMIN IS
PROCEDURE PROC_POPULATE(P_ROW_NUMBER TYPE_ROW_NUMBER, P_TEXT TYPE_TEXT) IS
BEGIN
FORALL I IN 1 .. 10
INSERT INTO T_600_TEST
(COL1, COL2)
VALUES
(P_ROW_NUMBER(I), P_TEXT(I));
END PROC_POPULATE;
PROCEDURE PROC_UPDATE(P_ROW_NUMBER TYPE_ROW_NUMBER, P_TEXT TYPE_TEXT) IS
BEGIN
FORALL I IN 1 .. 10
UPDATE T_600_TEST SET COL2 = P_TEXT(I) WHERE COL1 = P_ROW_NUMBER(I);
END PROC_UPDATE;
PROCEDURE PROC_DELETE(P_ROW_TABLE TYPE_ROW_NUMBER) IS
BEGIN
FORALL I IN 1 .. 10
DELETE FROM T_600_TEST WHERE COL1 = P_ROW_TABLE(I);
END PROC_DELETE;
END PKG_ADMIN;
注意：1.此范例代码，有包头有包体，在包头部分有两个关联的数组类型TYPE_ROW_NUMBER和TYPE_TEXT，以及对应的
三个存储过程PROC_POPULATE PROC_UPDATE PROC_DELETE，注意对应的传入参数类型，都是基于此package定义的集合
类型，注意具体用法即可
-------------------------------------------------------------------------------------------------------
4.accessible by 特性是12c的新特性，它可以允许指定可访问正在创建或者修改的PL/SQL单元列表，accessible by
通常被加入到function ,procedure头部，可以简单理解为白名单。废话不多说，来看如下代码片段：
4.1现在我们可以将数据库应用程序实现为几个PL/SQL包—一个包，它提供应用程序编程接口(API)和帮助程序包来完成这项
工作。理想情况下，客户端只能访问API。此外，我们还可以创建一个实用程序包，仅向相同模式中的其他一些PL/SQL
单元提供服务。理想情况下，实用程序包只能对预期的PL/SQL单元访问。
CREATE OR REPLACE PACKAGE PKG_PUBLIC
IS
PROCEDURE PROC_ONLY_THIS
END PKG_PUBLIC;
/
CREATE OR REPLACE PACKAGE PKG_PRIVATE
IS
PROCEDURE PROC_DO_THIS;
PROCEDURE PROC_DO_THAT;
END PKG_PRIVATE;
/
CREATE OR REPLACE PACKAGE PKG_PRIVATE<----现在我告诉PL/SQL编译器只允许pkg_public中的子程序调用pkg_private中的子程序
ACCESSIBLE BY(PKG_PUBLIC)
IS
PROCEDURE PROC_DO_THIS;
PROCEDURE PROC_DO_THAT;
END PKG_PRIVATE;
/
CREATE OR REPLACE PACKAGE BODY PKG_PUBLIC
IS
PROCEDURE PROC_only_this
IS
BEGIN
PKG_PRIVATE.PROC_DO_THIS;
PKG_PRIVATE.PROC_DO_THAT;
END;
END;
/
CREATE OR REPLACE PACKAGE BODY PKG_PRIVATE
IS
PROCEDURE PROC_DO_THIS;
IS
BEGIN
DBMS_OUTPUT.put_line ('THIS');
END;
PROCEDURE PROC_DO_THAT;
IS
BEGIN
DBMS_OUTPUT.put_line ('THAT');
END;
END;
/
BEGIN
PKG_PUBLIC.PROC_ONLY_THIS;
END ;
ORA-06550: 第 2 行, 第 3 列:
PLS-00905: 对象 SCOTT.PKG_PUBLIC 无效
ORA-06550: 第 2 行, 第 3 列:
PL/SQL: Statement ignored
/
BEGIN
PKG_PRIVATE.PROC_DO_THIS;
END ;
ORA-06550: 第 2 行, 第 3 列:
PLS-00904: 没有足够的权限访问对象 PKG_PRIVATE
ORA-06550: 第 2 行, 第 3 列:
PL/SQL: Statement ignored
注意：两个程序都是单个模式创建的，并且两个PL/SQL块都是在所有者的单个会话执行的，当PKG_PRIVATE依托于
accessible by子句创建后，其结果只允许pkg_public中的子程序调用pkg_private中的子程序，这就形成了类似
白名单的效果。总之就是一句话：accessible by子句限制其他单元对单元和子程序的访问
4.2刚才我们了解了accessible by子句限制其他单元对单元和子程序的访问。访问器列表(也称为白列表)显式地
列出了可能具有访问权限的单元。在12.2中，这个特性被增强，用于包中的子程序。我们甚至还可以指定白名单应用
的类型或单元类型。当有相同名称的触发器和PL/SQL程序单元时，这是很有用的功能。
在12.2中，现在可以将ACCESSIBLE BY子句应用到包的子程序。此外，可以限定单元的名称及其类型,例如procedure如果
碰巧拥有与触发器同名的程序单元(不幸的是，它们不共享相同的名称空间)，那么这将非常有用。但是，请注意，在
ACCESSIBLE BY列表中，只能有模式级的程序单元名，而不能有子程序名
CREATE OR REPLACE PACKAGE PKG_DATA AUTHID DEFINER IS
PROCEDURE PROC_DO_THIS;
PROCEDURE PROC_THIS_FOR_PROC_ONLY ACCESSIBLE BY(PROCEDURE PROC_GENERIC_NAME);
PROCEDURE PROC_THIS_FOR_TRIGGER_ONLY ACCESSIBLE BY(TRIGGER GENERIC_NAME);
PROCEDURE PROC_THIS_FOR_ANY_GENERIC_NAME ACCESSIBLE BY(GENERIC_NAME);
PROCEDURE PROC_THIS_FOR_PROC1_ONLY ACCESSIBLE BY(PROCEDURE PKG1.PROC_PROC1);
END;
/
CREATE OR REPLACE PACKAGE BODY PKG_DATA IS
PROCEDURE PROC_DO_THIS IS
BEGIN
NULL;
END PROC_DO_THIS;
PROCEDURE PROC_THIS_FOR_PROC_ONLY ACCESSIBLE BY(PROCEDURE PROC_GENERIC_NAME) IS
BEGIN
NULL;
END PROC_THIS_FOR_PROC_ONLY;
PROCEDURE PROC_THIS_FOR_TRIGGER_ONLY ACCESSIBLE BY(TRIGGER PROC_GENERIC_NAME) IS
BEGIN
NULL;
END PROC_THIS_FOR_TRIGGER_ONLY;
PROCEDURE PROC_THIS_FOR_ANY_GENERIC_NAME ACCESSIBLE BY(GENERIC_NAME) IS
BEGIN
NULL;
END;
PROCEDURE PROC_THIS_FOR_PROC1_ONLY ACCESSIBLE BY(PROCEDURE PKG1.PROC_PROC1) IS
BEGIN
NULL;
END;
END PKG_DATA;
/
CREATE OR REPLACE PACKAGE PKG1 AUTHID DEFINER IS
PROCEDURE PROC_PROC1;
PROCEDURE PROC_PROC2;
END;
/
CREATE OR REPLACE PACKAGE BODY PKG1 IS
PROCEDURE PROC_PROC1 IS
BEGIN
PKG_DATA.PROC_THIS_FOR_PROC1_ONLY;
END;
PROCEDURE PROC_PROC1 IS
BEGIN
NULL;
END;
END;
/
CREATE OR REPLACE TRIGGER PROC_generic_name
BEFORE INSERT
ON my_data
FOR EACH ROW
DECLARE
BEGIN
PKG_DATA.PROC_THIS_FOR_TRIGGER_ONLY;
END;
/
CREATE OR REPLACE TRIGGER PROC_generic_name
BEFORE INSERT
ON my_data
FOR EACH ROW
DECLARE
BEGIN
PKG_DATA.PKG_DATA.PROC_THIS_FOR_PROC_ONLY;
END;
Errors: PACKAGE BODY PKG1 Line: 6 PLS-00904: insufficient privilege to access object xxx
4.3在管道中使用ACCESSIBLE BY
CREATE OR REPLACE PACKAGE PKG_TEST ACCESSIBLE BY(TEST_API) AS
TYPE TEST_DATA_ROW IS RECORD(
ID INTEGER,
TEXT VARCHAR2(100));
TYPE TEST_DATA_TAB IS TABLE OF TEST_DATA_ROW;
FUNCTION SEL_TEST_DATA(P_CNT INTEGER) RETURN TEST_DATA_TAB
PIPELINED;
END PKG_TEST;
/
CREATE OR REPLACE PACKAGE BODY PKG_TEST AS
FUNCTION SEL_TEST_DATA(P_CNT INTEGER) RETURN TEST_DATA_TAB
PIPELINED IS
L_ROW TEST_DATA_ROW;
BEGIN
FOR REC IN 0 .. P_CNT LOOP
L_ROW.ID := REC;
L_ROW.TEXT := 'Test data row ' || REC;
PIPE ROW(L_ROW);
END LOOP;
RETURN;
END SEL_TEST_DATA;
END PKG_TEST;
/
CREATE OR REPLACE PACKAGE test_api
AS
PROCEDURE print_test_data_to_dbms;
END test_api;
/
CREATE OR REPLACE PACKAGE BODY TEST_API AS
PROCEDURE PRINT_TEST_DATA_TO_DBMS IS
BEGIN
FOR REC IN (SELECT * FROM TABLE(TEST_DA.SEL_TEST_DATA(10))) LOOP
DBMS_OUTPUT.PUT_LINE(REC.ID || ' - ' || REC.TEXT);
END LOOP;
END PRINT_TEST_DATA_TO_DBMS;
END TEST_API;
/
BEGIN
TEST_API.print_test_data_to_dbms;
END ;
ORA-04063: package body "SCOTT.TEST_API" 有错误
ORA-06508: PL/SQL: 无法找到正在调用 : "SCOTT.TEST_API" 的程序单元
ORA-06512: 在 line 2
4.4这个例子表明，如果在ACCESSIBLE BY子句中没有指定PL/SQL unit_kind，那么如果单元名称匹配，则允许来
自任何单位类型的调用。如果在ACCESSIBLE BY子句中指定的unit_kind不匹配任何现有对象，则没有编译错误。可以
定义与函数同名的触发器。建议指定unit_kind
CREATE OR REPLACE PROCEDURE protected_proc2
ACCESSIBLE BY (top_trusted_f)
AS
BEGIN
DBMS_OUTPUT.put_line('Executed protected_proc2.');
END;
/
CREATE OR REPLACE FUNCTION TOP_TRUSTED_F RETURN NUMBER AUTHID DEFINER IS
FUNCTION G RETURN NUMBER DETERMINISTIC IS
BEGIN
RETURN 0.5;
END G;
BEGIN
PROTECTED_PROC2;
RETURN G() - DBMS_RANDOM.VALUE();
END TOP_TRUSTED_F;
/
--如果ACCESSIBLE BY子句中指定的类型与任何现有对象不匹配，则不会出现编译错误
CREATE OR REPLACE FUNCTION TOP_PROTECTED_F RETURN NUMBER ACCESSIBLE BY(TRIGGER TOP_TRUSTED_F) AS
BEGIN
RETURN 0.5;
END TOP_PROTECTED_F;
/
SELECT TOP_TRUSTED_F FROM DUAL
-------------------------------------------------------------------------------------------------------
5.FETCH FIRST新特性：
5.1感谢oracle在12c版本推出的分页查询特性FETCH FIRST ROWS,OFFSET ROWS FETCH NEXT ROW LIMIT Clause子句，
新特性的推出简化了ROW_NUM()或ROWNUM的分页排序写法，大幅节约开发花在分页查询语句上的时间
5.2了解一下新特性的关键功能：
5.2.1基于FETCH FIRST/NEXT关键字指定返回结果的行数
5.2.2基于PERCENT关键字指定返回结果的行数比例
5.2.3基于OFFSET关键字指定返回从结果集中的某一行之后行数
5.3来看下新特性和传统rownumber分页的对比。
--创建测试表
CREATE TABLE T_600_FETCH_FIRST NOLOGGING TABLESPACE TBS_ORACLE
AS
SELECT ROWNUM"COL1",
TO_DATE(TRUNC(DBMS_RANDOM.VALUE(2452641, 2452641 + 364)), 'J')"COL2",
DBMS_RANDOM.STRING('X', 10)"COL3",
ABS(ROUND(DBMS_RANDOM.VALUE(2, 3),2))"COL4"
FROM DUAL
CONNECT BY ROWNUM <=10000;
/
--创建索引
DROP INDEX IDX_COL1 ;
CREATE INDEX IDX_COL1 ON T_600_FETCH_FIRST(COL1) TABLESPACE TBS_INDEX storage(initial 256K next 256K pctincrease 0) ONLINE NOLOGGING;
--收集统计信息
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =>'SCOTT',
TABNAME =>'T_600_FETCH_FIRST',
ESTIMATE_PERCENT =>DBMS_STATS.AUTO_SAMPLE_SIZE,
METHOD_OPT =>'for all columns size AUTO',
CASCADE =>TRUE,
NO_INVALIDATE =>FALSE);
END;
/
SELECT TABLE_NAME, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, LAST_ANALYZED
FROM USER_TABLES
WHERE TABLE_NAME = Q'{T_600_FETCH_FIRST}';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS LAST_ANALYZED
1 T_600_FETCH_FIRST 10000 51 0 2018/7/23 17:59:53
/
SELECT *+ GATHER_PLAN_STATISTICS */ TT.*
FROM (SELECT ROWNUM "NUMS"
FROM (SELECT *+ INDEX(T_600_FETCH_FIRST,IDX_COL1) */
*
FROM T_600_FETCH_FIRST
WHERE COL1 IS NOT NULL
ORDER BY COL1) TT
WHERE ROWNUM <= 50) TT
WHERE TT.NUMS >= 1;
SELECT SQL_ID,SQL_TEXT,CHILD_NUMBER,FIRST_LOAD_TIME FROM V$SQL
WHERE SQL_TEXT LIKE'%SELECT *+ GATHER_PLAN_STATISTICS */ TT.*%'
/
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor('cjb8cvzjxn63x',NULL,'ADVANCED ALLSTATS LAST PEEKED_BINDS'));
SQL_ID cjb8cvzjxn63x, child number 0
-------------------------------------
SELECT *+ GATHER_PLAN_STATISTICS */ TT.* FROM (SELECT ROWNUM "NUMS"
FROM (SELECT *+ INDEX(T_600_FETCH_FIRST,IDX_COL1) */
* FROM T_600_FETCH_FIRST
WHERE COL1 IS NOT NULL ORDER BY COL1) TT
WHERE ROWNUM <= 50) TT WHERE TT.NUMS >= 1
Plan hash value: 2550086649
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 50 |00:00:00.01 | 2 |
|* 1 | VIEW | | 1 | 50 | 650 | 3 (0)| 00:00:01 | 50 |00:00:00.01 | 2 |
|* 2 | COUNT STOPKEY | | 1 | | | | | 50 |00:00:00.01 | 2 |
| 3 | VIEW | | 1 | 50 | | 3 (0)| 00:00:01 | 50 |00:00:00.01 | 2 |
|* 4 | INDEX FULL SCAN| IDX_COL1 | 1 | 10000 | | 2 (0)| 00:00:01 | 50 |00:00:00.01 | 2 |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("TT"."NUMS">=1)
2 - filter(ROWNUM<=50)
4 - filter("COL1" IS NOT NULL)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "TT"."NUMS"[NUMBER,22]
2 - ROWNUM[8]
4 - "T_600_FETCH_FIRST".ROWID[ROWID,10]
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
6 consistent gets <------逻辑读为6
0 physical reads
0 redo size
1423 bytes sent via SQL*Net to client
585 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50 rows processed
注：普通写法1，逻辑读为6
SELECT /*+ gather_plan_statistics */COL1
FROM (SELECT COL1, ROW_NUMBER() OVER(ORDER BY COL1 ASC) AS "NUMS"
FROM T_600_FETCH_FIRST T
WHERE ROWNUM <= 50)
WHERE NUMS >= 1;
/
SELECT SQL_ID,SQL_TEXT,CHILD_NUMBER,FIRST_LOAD_TIME FROM V$SQL
WHERE SQL_TEXT LIKE'%SELECT /*+ gather_plan_statistics */%'
/
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor('bun9yhvgh7nn5',NULL,'ADVANCED ALLSTATS LAST PEEKED_BINDS'));
/
SQL_ID bun9yhvgh7nn5, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */COL1 FROM (SELECT COL1,
ROW_NUMBER() OVER(ORDER BY COL1 ASC) AS "NUMS" FROM
T_600_FETCH_FIRST T WHERE ROWNUM <= 50) WHERE NUMS >= 1
Plan hash value: 2893096092
---------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 16 (100)| | 50 |00:00:00.01 | 3 | | | |
|* 1 | VIEW | | 1 | 50 | 1300 | 16 (13)| 00:00:01 | 50 |00:00:00.01 | 3 | | | |
| 2 | WINDOW SORT | | 1 | 50 | 200 | 16 (13)| 00:00:01 | 50 |00:00:00.01 | 3 | 2048 | 2048 | 2048 (0)|
|* 3 | COUNT STOPKEY | | 1 | | | | | 50 |00:00:00.01 | 3 | | | |
| 4 | TABLE ACCESS FULL| T_600_FETCH_FIRST | 1 | 10000 | 40000 | 14 (0)| 00:00:01 | 50 |00:00:00.01 | 3 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NUMS">=1)
3 - filter(ROWNUM<=50)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "COL1"[NUMBER,22], "NUMS"[NUMBER,22]
2 - (#keys=1) "COL1"[NUMBER,22], ROWNUM[8], ROW_NUMBER() OVER ( ORDER BY "COL1")[22]
3 - "COL1"[NUMBER,22], ROWNUM[8]
4 - "COL1"[NUMBER,22]
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets<------利用分析函数，做开窗，全表扫描为3
0 physical reads
0 redo size
1423 bytes sent via SQL*Net to client
585 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
50 rows processed
注意：传统写法，利用分析函数开窗后，全表扫描逻辑读为3
SELECT /*+ INDEX(T_600_FETCH_FIRST,IDX_COL1) GATHER_PLAN_STATISTICS */COL1
FROM T_600_FETCH_FIRST
WHERE COL1 IS NOT NULL
ORDER BY COL1 FETCH FIRST 50 ROWS ONLY;
/
SELECT SQL_ID,SQL_TEXT,CHILD_NUMBER,FIRST_LOAD_TIME FROM V$SQL
WHERE SQL_TEXT LIKE'%SELECT /*+ INDEX(T_600_FETCH_FIRST,IDX_COL1) GATHER_PLAN_STATISTICS */COL1%'
/
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor('9jjqz0s44nrv5',NULL,'ADVANCED ALLSTATS LAST PEEKED_BINDS'));
/
SQL_ID 9jjqz0s44nrv5, child number 0
-------------------------------------
SELECT /*+ INDEX(T_600_FETCH_FIRST,IDX_COL1) GATHER_PLAN_STATISTICS
*/COL1 FROM T_600_FETCH_FIRST WHERE COL1 IS NOT NULL ORDER BY COL1
FETCH FIRST 50 ROWS ONLY
Plan hash value: 1406095386
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 22 (100)| | 50 |00:00:00.01 | 2 |
|* 1 | VIEW | | 1 | 50 | 1950 | 22 (0)| 00:00:01 | 50 |00:00:00.01 | 2 |
|* 2 | WINDOW NOSORT STOPKEY| | 1 | 10000 | 40000 | 22 (0)| 00:00:01 | 50 |00:00:00.01 | 2 |
|* 3 | INDEX FULL SCAN | IDX_COL1 | 1 | 10000 | 40000 | 22 (0)| 00:00:01 | 51 |00:00:00.01 | 2 |
-----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=50)
2 - filter(ROW_NUMBER() OVER ( ORDER BY "COL1")<=50)
3 - filter("COL1" IS NOT NULL)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "from$_subquery$_002"."COL1"[NUMBER,22], "from$_subquery$_002"."rowlimit_$$_rownumber"[NUMBER,22]
2 - (#keys=1) "COL1"[NUMBER,22], "T_600_FETCH_FIRST".ROWID[ROWID,10], ROW_NUMBER() OVER ( ORDER BY "COL1")[22]
3 - "T_600_FETCH_FIRST".ROWID[ROWID,10], "COL1"[NUMBER,22]
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
6 consistent gets<------逻辑读为6
0 physical reads
0 redo size
1423 bytes sent via SQL*Net to client
585 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50 rows processed
注意：新特性的执行计划指标为WINDOW NOSORT STOPKEY，对应逻辑读也是6，和正常写法没什么差异
-------------------------------------------------------------------------------------------------------
6.从12c开始，可以将角色授予PL/SQL包和独立子程序，这里需要注意一下，将一个角色授予PLSQL包或者独立的子程序
不改变其编译，相反，它会影响PL/SQL单元在运行时候发出的sql语句所需权限的检查方式。例如：
GRANT READ TO FUNCTION FUNC_DEMO.
