Today, a friend asked me on QQ whether there is a way to merge two SQL like:

insert into table（a,b） values('aa','bb');
insert into table（a,b） values('cc','dd');

I think there's no meaning to do that. But there is a way to implement it. Is is a new feature in Oracle 9i:  Multitable Inserts.

There are two syntaxes:

1. Unconditional insert into ALL tables

INSERT ALL
  INTO sal_history VALUES(empid,hiredate,sal)
  INTO mgr_history VALUES(empid,mgr,sysdate)
SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, manager_id MGR
  FROM employees WHERE employee_id > 200; 

2. Conditionally insert into ALL / First tables

INSERT ALL / FIRST
  WHEN SAL > 25000  THEN
    INTO special_sal VALUES(DEPTID,SAL)
  WHEN HIREDATE like ('%00%') THEN
    INTO hiredate_history_00 VALUES(DEPTID,HIREDATE)
  WHEN HIREDATE like ('%99%') THEN
    INTO hiredate_history_99 VALUES(DEPTID,HIREDATE)
  ELSE
    INTO hiredate_history VALUES(DEPTID, HIREDATE)
SELECT department_id DEPTID, SUM(salary) SAL,
    MAX(hire_date) HIREDATE
  FROM employees GROUP BY department_id;

Semantics:
ALL: insert into all the table.
        You can only use ALL keyword in Unconditional insert.
        If you use ALL keyword in Conditional insert, it means insert data into all the table that meet the condition.

FIRST:  If you use FIRST keyword in Conditional insert, it means insert data into the first table that meet the condition.

ELSE: If you use ELSE keyword, the row that don't meet any condition will be inserted into this table. Otherwise, the row will not be inserted into any table.

Now I'll give an example that resolve the issue above with multitable inserts:

STSC@SBOX> create table alltest (a varchar2(10), b varchar2(10));

Table created.

Elapsed: 00:00:00.00
STSC@SBOX> insert all
  2  into alltest values(a,b)
  3  into alltest values(c,d)
  4  select 'aa' a,'bb' b,'cc' c,'dd' d from dual;

2 rows created.

Elapsed: 00:00:00.00
STSC@SBOX> select * from alltest;

A                    B
-------------------- --------------------
aa                   bb
cc                   dd

2 rows selected.

Elapsed: 00:00:00.00 

It's easy!!

Reference: SQL New Features In Oracle9i  

在数据库操作中经常会碰到使用随机数的问题，几天前翔哥来问我关于 SQLServer 批量产生随机数的问题，记得之前他也问过我 Oracle 下面随机数的问题，当时找到了一些资料但没来得及整理，今天正好把 Oracle 和 SQLServer 下的随机数问题整理一下。

Collections

Collections Overview
  Types of Collections
    Associative arrays
      他是同种类型的一维、无边界的稀疏集合，只能用于 PL/SQL
      DECLARE TYPE t_name IS TABLE OF varchar2(10) INDEX BY PLS_INTEGER;  --创建 Collection
              i_name t_name;      --创建 instance
              l_row PLS_INTEGER;
      BEGIN
              i_name(202020):='aaa';
              i_name(-125):='bbb';
              i_name(88):='ccc';              --赋值，row number 可以为任何整数，且可以跳跃（稀疏的），也不用按照顺序赋值，其内部最终按照 row number 排序
              l_row := i_name.FIRST;          --返回第一个 row number
              l_row := i_name.NEXT (l_row);   --返回 l_row 之后的一个 row number，会自动跳过为空的行
              DBMS_OUTPUT.put_line (i_name (l_row));  --返回指定行的值
      END;

    Nested tables
      也是同种类型的一维无边界集合。起初他是密集的，经过删除操作会变成稀疏。他可以在 PL/SQL 和 Database（某一列为一个嵌套表）中被定义。他是 multisets 的，也就是说 nested table 中的元素没有内在的顺序
      DECLARE TYPE t_name IS TABLE OF varchar2(10); --创建 Collection，和 Associative arrays 相差一个 INDEX BY
                   i_n1 t_name := t_name();  --创建 instance，需要使用构造函数
                   i_n2 t_name := t_name();
                   i_n3 t_name := t_name();
      BEGIN
                   i_n1.EXTEND(2);  --赋值前要分配空间
                   i_n1(1):='aaa';  --赋值，row number 最小为 1，最大为 EXTEND 分配的空间数，可以跳过某个行不分配则为 null
                   i_n1(2):='bbb';
                   i_n2.EXTEND;     --分配一个空间
                   i_n2(1):='bbb';
                   i_n3 := i_n1 MULTISET EXCEPT i_n2; --10g 开始提供的功能，将 i_n1 中除去 i_n2 的值，赋值给 i_n3
                   FOR l_row IN i_n1.FIRST .. i_n1.LAST  --从行首到行尾，依次输出
                   LOOP
                     DBMS_OUTPUT.put_line (i_n1(l_row));
                   END LOOP;
      END;
    VARRAYs
      VARRAYs (variable-sized arrays)，也是同种类型的一维集合。但他是有界且不稀疏的。在定义 VARRAYs 时要指定他的最大范围。VARRAYs 也可以在 PL/SQL 和 Database 中被定义，但他其中的元素是有顺序的
      DECLARE TYPE t_name IS VARRAY (2) OF VARCHAR2 (10); --创建 Collection，需要指定最大范围
                   i_n1 t_name := t_name();  --创建 instance，需要使用构造函数
      BEGIN
                   i_n1.EXTEND(2);  --赋值前要分配空间，不能超过最大范围
                   i_n1(1):='aaa';  --赋值，row number 最小为 1，最大为 EXTEND 分配的空间数
                   i_n1(2):='bbb';
      END;
     
  Where You Can Use Collections
    作为 record 的成分
    作为程序的参数
      由于 Oracle 没有预定的集合类型，当作为参数使用前，需要定义集合类型：
        1.用 CREATE TYPE 定义 schema-level 类型
        2.在 package specification 中声明
        3.在外层作用域定义
    作为函数的返回值
      1.返回集合直接赋值给 collection variable，这时这个 collection variable 不需要初始化
      2.将返回集合中的一个元素赋值给一个类型兼容的变量
          variable_of_element_type := function() (subscript);
          如果函数返回空值，在赋值时会产生 COLLECTION_IS_NULL 异常，应该捕获并适当处理该异常
    作为数据库表的列
      当使用 nested table datatype 作为列时，必须指定 store table 的名字：
        CREATE TABLE personality_inventory (
           person_id NUMBER,
           favorite_colors Color_tab_t,
           date_tested DATE,
           test_results BLOB)
        NESTED TABLE favorite_colors STORE AS favorite_colors_st;
      不能对 store table 进行维护或者试图直接查询、存储数据，只能通过 outer table 来获取他的属性。也不能指定他的 storage parameters，他继承与 outermost table。
      nested tables 和 VARRAYs 的区别在于，VARRAY 和其他数据一起存于表内，而 nested table 存于表外，VARRAY 适合 "small" arrays，nested table 适合 "large" arrays。
    作为 object type 的属性
   
  Choosing a Collection Type
    1.如果要用稀疏 array，那么只能使用 associative array，虽然可以先分配 nested table 在删除其中的项目，但效率很低
    2.如果在 PL/SQL 中要用负数的下标，只能用 associative array
    3.如果使用 10g，希望使用 set 层面的操作，那么选择 nested tables + MULTISET EXCEPT 的方法
    4.如果要限制存储的行数，使用 VARRAYs
    5.如果要在 column 中存储大数据量集合，那么使用 nested table，Oracle可以使用单独的表来存储他
    6.如果你想将存在 collection column 中的数据保持原有顺序，并且数据量很小，可以使用 VARRAY，小的概念可以按照 BLOCK 的大小来判断，如果数据量超过一个 BLOCK，将会产生行连接
    7.还有些情况适合使用 VARRAY：you don't want to worry about deletions occurring in the middle of the data set; your data has an intrinsic upper bound; or you expect, in general, to retrieve the entire collection simultaneously.

Collection Methods (Built-Ins)
  Collection methods 只能用于 PL/SQL，不能在 SQL 中使用
 
  The COUNT Method
    用于计算 associative array, nested table, or VARRAY 中元素的个数，使用 DELETEd or TRIMmed 将减少 COUNT
    定义：
      FUNCTION COUNT RETURN PLS_INTEGER;
    对于初始化了，但不包含元素的 collection 返回 0，对于空的 associative array 也返回 0
    如果对未初始化的 nested table or a VARRAY 使用 COUNT 将返回 COLLECTION_IS_NULL 异常，associative arrays 无需初始化，故不会返回异常
   
  The DELETE Method
    DELETE 用于删除 collection 中的一个、多个或者全部元素：
      1.不加参数，删除所有元素，对于 VARRAYs 只能使用这种方法，因为 VARRAYs 不能使稀疏的，要删除一行，只能用 TRIM 删除最后一行
      2.DELETE(i) 删除第 i 行
      3.DELETE(i,j) 删除第 i~j 行
    对于使用参数的 DELETE，其实使用一个占位符来代替删除的元素，以后还可以为他们再赋值，赋值之后在 COUNT 中又开始计数。从物理角度来考虑，PL/SQL 只有在删除足够多数量的元素，以至于可以释放 entire page of memory 时才释放内存，但不加参数的 DELETE 是立即释放所有内存的
    定义：
      PROCEDURE DELETE;
      PROCEDURE DELETE (i [BINARY_INTEGER | VARCHAR2(size_limit)]);
      PROCEDURE DELETE (i [BINARY_INTEGER | VARCHAR2(size_limit)],
                        j [BINARY_INTEGER | VARCHAR2(size_limit)]);
    如果 i,j 超出范围，不会出现异常，只是删除在范围内的，超出部分忽略
    如果对未初始化的 nested table or a VARRAY 使用 DELETE 将返回 COLLECTION_IS_NULL 异常
   
  The EXISTS Method
    判断 collection 中的指定行是否存在，如果以前存在，后来被 DELETE 了，那也返回 false
    定义：
      FUNCTION EXISTS (i IN [BINARY_INTEGER | VARCHAR2(size_limit)]) RETURN BOOLEAN;
     
  The EXTEND Method
    用于为 nested table or VARRAY 分配空间
      1.不加参数，分配一个 null element
      2.EXTEND(n)，分配 n 个 null element
      3.EXTEND(n,i)，分配 n 个和第 i 值相同的 element，这对于有 NOT NULL 限制的 element 是必要的
    定义：
      PROCEDURE EXTEND (n PLS_INTEGER:=1);
      PROCEDURE EXTEND (n PLS_INTEGER, i PLS_INTEGER);
    如果用 DELETEd or TRIMmed 删除了 collection 的最后一个元素，再 EXTEND 时，会跳过这个，在后面分配空间
    如果对未初始化的 nested table or a VARRAY 使用 DELETE 将返回 COLLECTION_IS_NULL 异常，如果 EXTEND 超过 VARRAY 的最大限制，将返回 SUBSCRIPT_BEYOND_LIMIT 异常
   
  The FIRST and LAST Methods
    分别返回 COLLECTION 中可用的最低和最高下标，被 DELETE 的不会显示
    定义：
      FUNCTION FIRST RETURN PLS_INTEGER;
      FUNCTION LAST RETURN PLS_INTEGER;
    对于初始化了，但没有元素的 COLLECTION，都返回 null；对于至少有一个 element 的 VARRAYs，FIRST 总为 1，LAST 总等于 COUNT
    如果对未初始化的 nested table or a VARRAY 使用 FIRST and LAST 将返回 COLLECTION_IS_NULL 异常
   
  The LIMIT Method
    返回 VARRAY 的最大数目限制，如果用于 nested tables or to associative arrays 将返回 NULL
    定义：
      FUNCTION LIMIT RETURN PLS_INTEGER;
    如果对未初始化的 nested table or a VARRAY 使用 LIMIT 将返回 COLLECTION_IS_NULL 异常
   
  The PRIOR and NEXT Methods
    用于遍历 COLLECTION 中的内容
    定义：
      FUNCTION PRIOR (i [BINARY_INTEGER | VARCHAR2(size_limit)])
          RETURN [BINARY_INTEGER | VARCHAR2(size_limit)];
      FUNCTION NEXT (i [BINARY_INTEGER | VARCHAR2(size_limit)])
          RETURN [BINARY_INTEGER | VARCHAR2(size_limit)];
    如果应用于初始化了但为空的 COLLECTION 时，返回 null；如果 i 大于等于 COUNT，则 NEXT 返回 null；如果 i 小于等于 FIRST，则 PRIOR 返回 null
    就目前来说，如果 i 大于 COUNT，则 PRIOR 返回 LIMIT；如果 i 小于 FIRST，则 NEXT 返回 LIMIT，但以后的版本不知道是不是这样了
    如果对未初始化的 nested table or a VARRAY 使用 PRIOR and NEXT 将返回 COLLECTION_IS_NULL 异常
   
  The TRIM Method
    由于删除 nested table or VARRAY 末尾的 n 行，如果不加参数，删除最后一行，如果用于 associative array 将产生编译时错误
    DELETE and TRIM 联合使用将产生重复删除的问题，比如 DELETE 删除最后一行之后，再用 TRIM 删除最后 n 行时，其实 TRIM 重复操作了被 DELETE 删除的那行（因为删除之后变成了占位符），使得实际上少删除一行，因此 Oracle 建议两者不要混合使用
    定义：
      PROCEDURE TRIM (n PLS_INTEGER:=1);
    如果 n 为 null，TRIM 不做任何事情
    如果对未初始化的 nested table or a VARRAY 使用 TRIM 将返回 COLLECTION_IS_NULL 异常
    如果 TRIM 的 n 大于实际存在的元素个数，那么返回 SUBSCRIPT_BEYOND_COUNT 异常
   
   
Working with Collections
  Declaring Collection Types
    两种声明方法：
      1.在 PL/SQL 中使用 TYPE 语句，如果定义在 package 中，对于使用该类型的用户要将 package 的 EXECUTE 权限授予该用户
      2.对于 nested table type or VARRAY type 在 schema-level 使用 CREATE TYPE 命令，并将该类型的 EXECUTE 权限授予需要使用的用户
     
    Declaring an associative array collection type
      TYPE table_type_name IS TABLE OF datatype [ NOT NULL ]
         INDEX BY index_type;
      datatype 包括：
        1.Scalar datatype：VARCHAR2, CLOB, POSITIVE, DATE, or BOOLEAN 等等
        2.Anchored datatype：使用 %TYPE、%ROWTYPE
        3.Complex datatype：9i R2 开始可以使用 object types and collection types
      index_type 包括：
        INDEX BY BINARY_INTEGER;（9i R2 之前只能使用该类型）
        INDEX BY PLS_INTEGER;
        INDEX BY POSITIVE;
        INDEX BY NATURAL;
        INDEX BY SIGNTYPE;
        INDEX BY VARCHAR2(32767);
        INDEX BY table.column%TYPE;
        INDEX BY cursor.column%TYPE;
        INDEX BY package.variable%TYPE;
        INDEX BY package.subtype;
       
    Declaring a nested table or VARRAY
      CREATE [ OR REPLACE ] TYPE type_name AS | IS
         TABLE OF element_datatype [ NOT NULL ];
      CREATE [ OR REPLACE ] TYPE type_name AS | IS
         VARRAY (max_elements) OF element_datatype [ NOT NULL ];
      DROP TYPE type_name [ FORCE ];
      element_datatype 包括：大部分 scalar datatypes, an object type, or a REF object type，如果是 object type，那么他的属性不能是 collection 类型；在 PL/SQL 中，如果是 RECORD，那么他只能包含 scalars or objects 类型
      FORCE：即使该 collection type 被其他类型引用，也 drop 他
     
    Changing nested table or VARRAY characteristics
      1.改变 VARRAY 元素最大个数：
          ALTER TYPE list_t MODIFY LIMIT 100 INVALIDATE | CASCADE
      2.改变元素类型为 variable character, RAW, or numeric 的类型长度或精度
          CREATE TYPE list_t AS VARRAY(10) OF VARCHAR2(80);
          ALTER TYPE list_t MODIFY ELEMENT TYPE VARCHAR2(100) INVALIDATE | CASCADE
      INVALIDATE：所有使用该类型的对象失效
      CASCADE：将改变传递给所有使用该类型的对象
     
  Declaring and Initializing Collection Variables
    collection_name collection_type [:= collection_type (...)];
    对于 nested table or VARRAY 需要用构造函数初始化，构造函数可以可以接受用逗号分割的初始值作为参数，如果不初始化就使用会得到错误：ORA-06531: Reference to uninitialized collection
   
    Initializing implicitly during direct assignment
      同一 collection type （必须同一个类型，哪怕元素类型相同的不同 collection type 也不行）的不同变量，可以通过直接赋值进行隐式初始化
        DECLARE
           earth_colors Color_tab_t := Color_tab_t('BRICK', 'RUST', 'DIRT');
           wedding_colors Color_tab_t; --必须同一类型，都是 Color_tab_t，这里没有初始化
        BEGIN
           wedding_colors := earth_colors;  --赋值时同时初始化
           wedding_colors(3) := 'CANVAS';
        END;
       
    Initializing implicitly via FETCH
      如果表中的列是 Collection type，那么可以像一般类型一样通过 select ... into、FETCH 直接将值传递给相同类型的变量，并可以自动给该变量初始化
   
  Populating Collections with Data
    Using the assignment operator
      1.单个赋值：
          countdown_test_list (43) := 'Internal pressure';
      2.整个 record （结构要一致）赋值：
          DECLARE
             TYPE emp_copy_t IS TABLE OF emp%ROWTYPE;
             l_emps emp_copy_t := emp_copy_t(  );
             l_emprec emp%ROWTYPE;
          BEGIN
             l_emprec.ename := 'Steven';
             l_emprec.salary := 10000;
             l_emps.EXTEND
             l_emps (l_emps.LAST) := l_emprec;
          END;
        相同的还可以使用 select ... into、cursor FOR loop、select ... BULK COLLECT into（9i R2 之后，可将整个表赋值给 Collection，不需要初始化） 进行赋值
      3.相同类型的 Collection 直接赋值（不需要初始化）
     
  Accessing Data Inside a Collection
    需要注意几点：
      1.对于稀疏 Collection，当访问不存在的元素时会产生 NO_DATA_FOUND 异常
      2.访问超出 EXTEND 分配空间的元素时产生 ORA-06533: Subscript beyond count 异常
      3.对于 VARRAY，访问大于最大元素个数的元素时产生 ORA-06532: Subscript outside of limit 异常
     
  Collections of Complex Datatypes
    Collections of records
      可以定义元素类型为 record 的 collection，使用 %ROWTYPE or a programmer-defined record type。适用于 PL/SQL，如果是定义在表的列上，不能使用 %ROWTYPE
     
    Multilevel collections
      9i R2 开始可以定义 collection 的元素类型也为 collection，既 collection 嵌套，被称为 multilevel collections，通过这种方法，可以构造多维 collection。
     
  Using String-Indexed Collections
    9i R2 开始可以使用 strings 作为 index type，FIRST, LAST, PRIOR, and NEXT 方法返回的也是 strings
   
  Working with Collections in SQL
    Oracle8 开始提供 collection pseudo-functions，他们可以使数据表和 collection 的数据互相转换，只能用于 SQL 语句中
   
    1.The CAST pseudo-function
        在 SQL 中，用于将 built-in datatype or collection type 转换为另一种 built-in datatype or collection type
          CREATE TYPE Color_nt AS TABLE OF VARCHAR2(30);
          CREATE TYPE Color_vat AS VARRAY(16) OF VARCHAR2(30);
          CREATE TABLE color_models (
             model_type VARCHAR2(12),
             colors Color_vat);
          SELECT COLUMN_VALUE FROM
             TABLE (SELECT CAST(colors AS Color_nt)
                   FROM color_models
                   WHERE model_type = 'FGB');
                  
    2.The MULTISET pseudo-function
        MULTISET 必须和 CASTs 一起使用，MULTISET 将数据集转换为 collection，SQL MULTISET function 和操作 nested tables 的 PL/SQL MULTISET 是完全不同的
          SELECT CAST (MULTISET (SELECT field FROM table) AS collection-type)
          FROM DUAL;
        As with the CAST pseudo-function, MULTISET cannot serve as the target of an INSERT, UPDATE, or DELETE statement.
       
    3.The TABLE pseudo-function
        TABLE 将 collection 转换为可以 SELECT from 的结果集，既将 collection 中的内容像数据表一样 select 出来
        例如，表中含有 collection 字段，现在要找出 collection 中包含某个内容的所有数据：
          SELECT *
            FROM color_models c
           WHERE 'RED' IN
                (SELECT COLUMN_VALUE FROM TABLE(c.colors));
               
          MODEL_TYPE   COLORS
          ------------ ------------------------------------------------------
          RGB          COLOR_TAB_T('RED', 'GREEN', 'BLUE')
         
        COLUMN_VALUE 是 TABLE operator 创建的 system-defined 列名

Nested Table Multiset Operations
  10g 之后针对 nested tables 提供了 high-level set operations，将其作为 multisets 对待
 
  Operation             Return value    Description
  =                     BOOLEAN         比较两个 nested tables，如果 named type、cardinality、elements 相等，返回 TRUE，如果 nested table 中包含 null 则必不相等。10g 之前要比较，只能通过一个个元素来比较
  <> or !=              BOOLEAN         比较两个 nested tables 是否不同
  [NOT] IN ( )          BOOLEAN         判断左边的 nested table 是否在右边括号中的 nested tables list 中
  x MULTISET EXCEPT     NESTED TABLE    返回一个 NESTED TABLE，他的元素在 x 中，但不在 y 中，所有的 NESTED TABLE 都必须是相同类型的。DISTINCT 表示返回的 nested table 中除去重复元素
    [DISTINCT] y
  x MULTISET INTERSECT  NESTED TABLE    返回一个 NESTED TABLE，他的元素是 x 和 y 中元素的交集
    [DISTINCT] y
  x MULTISET UNION      NESTED TABLE    返回一个 NESTED TABLE，他的元素是 x 和 y 中元素的并集
    [DISTINCT] y
  SET(x)                NESTED TABLE    返回一个 NESTED TABLE，包含 x 中不重复的所有元素
  x IS [NOT] A SET      BOOLEAN         判断 x 中的元素是否有重复值
  x IS [NOT] EMPTY      BOOLEAN         判断 x 是否为空
  e [NOT] MEMBER        BOOLEAN         判断表达式 e 是否包含在 x 的元素中
    [OF] x
  y [NOT] SUBMULTISET   BOOLEAN         判断 y 的元素是否全都包含在 x 的元素中
    [OF] x
   
   
Maintaining Schema-Level Collections
  Necessary Privileges
    如果创建 Schema-Level 的 nested tables and VARRAYS，其他 Schema 如果需要使用该类型必须有该类型的 EXECUTE 权限：
      GRANT EXECUTE on Color_tab_t TO JOE;
    对于包含 Schema-Level Collections 的 packages, procedures, functions 只要有这些过程的 EXECUTE 权限就可以使用这些类型了，但对于 PL/SQL anonymous blocks 中使用这些类型，只能直接将这些类型的 EXECUTE 权限授予执行的用户
    如果某表的一列为 Schema-Level Collection 类型，对该表要有 SELECT, INSERT, UDPATE, and DELETE 权限才能做相应的操作，其次在 INSERT or UPDATE 时还需要有该 Collection 类型的 EXECUTE 权限

  Collections and the Data Dictionary
    1.USER_TYPES
        所有创建的类型
        SELECT type_name FROM user_types
        WHERE typecode ='COLLECTION';
    2.USER_SOURCE
        类型的定义
        SELECT text FROM user_source
        WHERE name = 'FOO_T' AND type = 'TYPE'
        ORDER BY line;
    3.USER_DEPENDENCIES
        依赖创建的类型的对象
        SELECT name, type FROM user_dependencies
        WHERE referenced_name='FOO_T';

Optimizer Operations

How the Optimizer Performs Operations
    How the CBO Evaluates IN-List Iterators
        当 IN clause 指定了特定的值，并且在该列上有索引，优化器选择 IN-list iterator。如果有多个 OR clauses 使用相同的索引，那么优化器选择更高效的 IN-list iterator，而不使用 CONCATENATION or UNION ALL。
   
    How the CBO Evaluates Concatenation
        当不同的条件使用 OR clause 连接起来，并且不同的条件都可以通过不同的索引生成较好的执行计划，那么 Concatenation 是很有用的。
        HINT：
          USE_CONCAT
          NO_EXPAND 会禁止使用 Concatenation，他其实是阻止 QUERY 扩展为多个 QUERY
        当一下情况时不要使用 Concatenation：
          1.OR conditions 在同一个列上，可以使用 IN-list，后者更高效。
          2.每一个 concatenation 都重复昂贵的步骤。
     
      How the CBO Evaluates Remote Operations
          影响执行计划的因素：
            1.Network round trips 比物理和逻辑 I/Os 昂贵几个数量级
            2.如果远程数据库不是 Oracle 数据库，优化器无法获得远程数据库的任何 statistics
          一般来说，优化器在访问本地表之前先访问远程表
   

      How the CBO Executes Distributed Statements
          1.如果 SQL 语句中的所有表来自同一个远程数据库，Oracle 把语句发送给远程数据库，远程数据库执行完之后把结果发还给本地数据库。
          2.如果表来自不同的数据库，Oracle 把语句拆分，每一个部分访问单个数据库上的表，把他们分别发送给各数据库，各数据库执行自己部分的语句，并把结果发还给本地数据库，本地数据库再执行语句的其余处理部分。
   
          如果是 CBO，优化器会考虑远程数据库上的索引，就像本地数据库一样，还会考虑远程的 statistics，此外，在估计访问的 cost 时，还会考虑数据的位置，比如远程的一个全表扫描比本地相同表的全表扫描估计的 cost 要高。
          对于 RBO，优化器不会考虑远程数据库上的索引。
   
      How the CBO Executes Sort Operations
          SORT UNIQUE
              如果使用了 DISTINCT clause 或者 unique values 在下一步中需要，就会发生 SORT UNIQUE
     
          SORT AGGREGATE
              SORT AGGREGATE 实际上不发生 sort，他使用于对整个 set of rows 进行聚合计算。
     
          SORT GROUP BY
              SORT GROUP BY 用于对不同组上的数据进行聚合计算，这种情况下 sort 是需要的，sort 用于将行拆分成不同的组。
     
          SORT JOIN
              在 SORT MERGE JOIN 中，如果数据需要根据 join key 排序，就会发生 SORT JOIN。
     
          SORT ORDER BY
              当语句中使用 ORDER BY，并且没有任何索引适合这种排序方式，那么 SORT ORDER BY 就需要。
     
      How the CBO Executes Views
          以下情况 CBO 产生 VIEW：
              1.语句中有没有被 decomposed 的 View
              2.语句中有 temporary or inline view 被使用
     
      How the CBO Evaluates Constants
          常量的计算只在语句被优化时执行一次，而不是每次语句被执行的时候。
          比如：salary > 24000/12 会被优化器简化为 salary > 2000
          优化器不会跨过比较符简化表达式，salary*12 > 24000 不能被简化为 salary > 2000，因此写语句时应尽量用常量和列作比较，而不要将列作计算之后再去比较。
   
      How the CBO Evaluates the UNION and UNION ALL Operators
          对于将 OR clauses 组合为一个复合语句，或者将一个复杂语句分解为包含简单 select 语句的复合语句很有用，他们更易于优化和理解。
          就和 concatenation 一样，如果 UNION ALL 重复了昂贵的操作，就不应该使用。
   
      How the CBO Evaluates the LIKE Operator
          对于没有通配符的 like 条件，优化器会将他简化为等于操作
              last_name LIKE 'SMITH' -->> last_name = 'SMITH'
          但这种简化只能用于变长的类型，对于固定长度的，比如 CHAR(10) 就不能简化，因为等于操作遵循 blank-padded semantics，而 like 不是（此规则只适合 9i 以上）。
              CREATE TABLE ducks (f CHAR(6), v VARCHAR2(6));
              INSERT INTO ducks VALUES ('DUCK', 'DUCK');
              commit;
              select * from ducks where f='DUCK';         <<---'DUCK' 自动填充空格至 6 位长度
              F            V
              ------------ ------
              DUCK         DUCK
              select * from ducks where f like 'DUCK';    <<---不填充空格
              no rows selected
              以上结果在 9i 上有效，8i 下两者都返回行
     
      How the CBO Evaluates the IN Operator
          优化器将 IN comparison operator 条件扩展为等价的 equality comparison operators and OR logical operators 条件
   
      How the CBO Evaluates the ANY or SOME Operator
          1.列表
                优化器将其扩展为等价的 comparison operators and OR logical operators 条件
                   salary > ANY (:first_sal, :second_sal) -> salary > :first_sal OR salary > :second_sal
          2.子查询
                优化器将其转化为等价的 EXISTS operator and a correlated subquery 条件
                   x > ANY (SELECT salary
                        FROM employees
                        WHERE job_id = 'IT_PROG')
                变为
                   EXISTS (SELECT salary
                     FROM employees
                     WHERE job_id = 'IT_PROG'
                     AND x > salary)
             
      How the CBO Evaluates the ALL Operator
          1.列表
                优化器将其扩展为等价的 comparison operators and AND logical operators 条件
          2.子查询
                优化器将其转化为等价的 ANY comparison operator and a complementary comparison operator 条件
                  x > ALL (SELECT salary
                      FROM employees
                      WHERE department_id = 50)
                变为
                  NOT (x <= ANY (SELECT salary
                        FROM employees
                        WHERE department_id = 50) )
              然后再进一步根据 ANY Operator 的转换规则再将其转换：
                  NOT EXISTS (SELECT salary
                        FROM employees
                        WHERE department_id = 50
                        AND x <= salary)
               
      How the CBO Evaluates the BETWEEN Operator
          优化器总是将 BETWEEN 条件用 >= and <= 条件来代替
   
      How the CBO Evaluates the NOT Operator
          优化器使用除去 NOT logical operator 并使用相反的 comparison operator 代替原来 comparison operator 的方法来简化条件，使 NOT logical operator 消除。
          优化器会将 NOT 传递到子条件中，以便尽可能的简化条件，即使子条件中会产生更多的 NOTs：
              NOT (salary < 1000 OR commission_pct IS NULL)
          =>
              NOT salary < 1000 AND commission_pct IS NOT NULL
          =>
              salary >= 1000 AND commission_pct IS NOT NULL
     
      How the CBO Evaluates Transitivity
          如果两个条件涉及到同一个 column，且这个 column 的其中一个条件是和 constant expressions 进行比较，那么有时候优化器会推断出一个条件，这个推断的条件可以使用 index access path，而原始的条件却不能使用：
              WHERE column1 comp_oper constant
                AND column1 = column2
          其中：
              comp_oper 为任何比较操作：=, !=, ^=, <, <>, >, <=, or >=
              constant 为任何常量表达式（不能为其他 column）：SQL functions, literals, bind variables, and correlation variables
          这时，优化器会推断一个条件：
              column2 comp_oper constant
          如果 column2 上有索引，就能使用该索引
          注：Transitivity 只用于 CBO
   
      How the CBO Optimizes Common Subexpressions
          公共的子表达式优化是一种启发式的优化，可以鉴别、移出、收集在各 disjunctive (OR) branches 中的公共子表达式，绝大数情况下，可以减少 join 的数量。
          在满足一下情况时，可使用公共子表达式优化（从最顶层至最内层的顺序）：
              1.顶层条件是一个 disjunction（几个以 or 连接的条件）
              2.每个 or 分支中是 simple predicate 或者 a conjunction（几个以 and 连接的条件）
              3.每个 and 分支中是 simple predicate 或者 a disjunction of simple predicates
              4.表达式在每个 or 分支中都出现，即公共子表达式
                simple predicate 只不含有 AND or OR 连接的条件
           满足以上条件的公共子表达式，优化器会将其移到最顶层，去除重复，再和被移去公共子表达式的原 disjunction 做 conjunction，这样可以减少 join 操作。
   
      How the CBO Evaluates DETERMINISTIC Functions
          某些情况，优化器不需要计算 user-written function 的值，而用以前计算的值来代替他。
          这种 function 必须有一定的限制：
              1.Function 的返回值不能随着 package variables、database、session parameters 的不同而改变
              2.如果 function 被重定义了，那么他的返回值和以前的要保持一致
              3.使用预计算结果代替执行 function 必须没有重大副作用
          使用 DETERMINISTIC 关键字创建的 function 告诉 Oracle 该 function 满足以上限制，Oracle 不会去检查该 function，即使 function 很明显不满足以上限制，因此，程序员应负责检查以上限制，只有满足了才能加 DETERMINISTIC 关键字。
   
   
How the Optimizer Transforms SQL Statements
      How the CBO Transforms ORs into Compound Queries
          如果一个查询包含多个用 OR 连接的条件，优化器会将其转换为用 UNION ALL 连接的混合查询，如果转换后的语句执行更加高效
              1.如果每个条件都可以单独使用 index access path，那么可以转换。优化器将转换后的语句生成执行计划，他们用不同的索引多次访问表，并把结果放到一起
              2.如果任何一个条件使用 full table scan，那么不会转换，因为一个 full table scan 和几个 index scan 结合的效率不如直接使用一个 full table scan 效率高
              3.对于 CBO，优化器会估计并比较转换前后的语句所需的 COST，并决定是否转换
              4.CBO 不会转换 IN-lists 或者条在在同一列上的 OR 操作，而使用 INLIST iterator operator
     
      How the CBO Unnests Subqueries
          优化复杂语句，优化器选择下面两种方法的其中一种：
              1.如果复杂语句可以转换为等同的 join 语句，那么先转换，再优化 join 语句，这样可以利用 join 语句的优势
              2.如果无法转换，那么优化复杂语句本身
                 子查询包含 aggregate functions，比如 AVG 的复杂语句，无法转换为 join 语句
       
      How the CBO Merges Views
          merge view 的方法：
                在语句中将 view 的名字用 view 基表的名字代替，将 view 中的条件加到语句的条件中去
      
          Mergeable and Nonmergeable Views
              merge 只适合 select-project-join views，即 view 中只包含 selections, projections, and joins
              如果 view 中包含以下元素，不能 merge：
                1.Set operators (UNION, UNION ALL, INTERSECT, MINUS) 
                2.A CONNECT BY clause 
                3.A ROWNUM pseudocolumn 
                4.Aggregate functions (AVG, COUNT, MAX, MIN, SUM) in the select list 
              当 view 中包含以下元素时，只有 Complex View Merging is enabled 的时候才能 merge：
                1.A GROUP BY clause 
                2.A DISTINCT operator in the select list 
              如果 view 中包含多个基表，那么当该 view 在 outer join 语句的右边时，无法 merge，如果只有一个基表，那么可以使用 complex view merging，即使 an expression in the view can return a nonnull value for a NULL.
              如果一个查询语句包含 CURSOR expression，那么即使 view 可以被正常 merge 优化器也不会将它 merge
      
      Complex View Merging
          如果 complex view merging is enabled 的，那么包含 GROUP BY clause or DISTINCT 的 view 可以被 merge
          Complex merging 还可以用于 merge an IN subquery，只要 subquery 是不相关的
          Complex merging 不是 cost-based 的，必须用 OPTIMIZER_FEATURES_ENABLE or the MERGE hint 开启才能使用，如果没有 hint or parameter setting，优化器会使用另外一种方法：pushing predicates
      
      How the CBO Pushes Predicates
          优化器可以将访问 nonmergeable view 的 query block 通过 pushing the query block's predicates inside the view's query 来转换
    
      How the CBO Applies an Aggregate Function to the View
          优化器可以转换包含 aggregate function 的 query，通过将 aggregate function 应用到 view's query
      
      How the CBO Executes Views in Outer Joins
          如果 view 在一个 outer join 的右边： 
              1.如果 view 只有一个基表，优化器使用 view merging
              2.如果 view 有多个基表，优化器可以 push the join predicate into the view
        
      How the CBO Accesses the View's Rows with the Original Statement
          如果优化器无法将访问 view 的语句转换为等价的访问基表的语句，比如 view 中包含 ROWNUM pseudocolumn，view 不能被 merge，也不能 push predicate，那么 Oracle 执行 view 中的语句，获得结果集，original statement 将其当作一个表来处理。
      
      How the CBO Executes Compound Queries
          为了为混合查询选择执行计划，优化器首先为混合查询的每个部分选择执行计划，然后用 set operator 联合各 resulting row sources 

Records in PL/SQL

1.Declaring Records
    Table-based record
      DECLARE one_book books%ROWTYPE;
     
    Cursor-based record
      DECLARE CURSOR my_books_cur IS
                       SELECT * FROM books
                        WHERE author LIKE '%FEUERSTEIN%';
              one_SF_book my_books_cur%ROWTYPE;
     
    以上两种方法都使用了 %ROWTYPE 属性，语法为：
      record_name [schema_name.]object_name%ROWTYPE
         [ DEFAULT|:= compatible_record ];
     
      schema_name 是可选的，默认为编译该代码的 schema
      object_name 可以是 explicit cursor, cursor variable, table, view, or synonym
      可以指定一个默认值，默认值必须是和 record 相同或者兼容的类型
      使用 cursor variable 的例子：
        DECLARE TYPE book_rc IS REF CURSOR RETURN books%ROWTYPE;
                book_cv book_rc;
                one_book book_cv%ROWTYPE;
        BEGIN
          ...

    Programmer-defined record
      DECLARE TYPE book_info_rt IS RECORD (
          author books.author%TYPE,
          category VARCHAR2(100),
          total_page_count POSITIVE);
      steven_as_author book_info_rt;
     
    隐式声明 record
      BEGIN
        FOR book_rec IN (SELECT * FROM books)
        LOOP
          calculate_total_sales (book_rec);
        END LOOP;
      END;
      其中 book_rec 就是 PL/SQL 用 %ROWTYPE 隐式声明的
     
     
2.Programmer-Defined Records
    声明步骤：
      1.声明或者定义一个 record TYPE 包含你需要的结构
      2.使用这个 record TYPE 声明你需要的实际 records
     
    Declaring programmer-defined record TYPEs
      TYPE type_name IS RECORD
         (field_name1 datatype1,
          field_name2 datatype2,
          ...
          field_nameN datatypeN
         );
      field_name 必须是唯一的
      datatype 可以为：
        1.Hardcoded, scalar datatype (VARCHAR2, NUMBER, etc.).
        2.Programmer-defined SUBTYPE
        3.Anchored declarations using %TYPE or %ROWTYPE attributes.
        4.PL/SQL collection type
        5.REF CURSOR（cursor variable）
      除了 datatype 还可以用 DEFAULT or := 指定默认值；还可以使用约束，比如 NOT NULL（使用 NOT NULL 必须要指定默认值）
       
    Declaring the record
      一旦声明了 record types，就可以用它声明任何该类型的 records：
        record_name record_type;
       
       
3.Working with Records
    Record-level operations
      PL/SQL 支持的：
        1.两个 record 之间复制内容（只要两者结构兼容，比如：相同的 fields 数量，相同或者兼容的数据类型）
        2.将 NULL 赋值给 record
        3.可以在参数中定义 record，或将 record 传递给参数
        4.可以作为 function 的返回值
      不支持的：
        1.不能对 record 使用 IS NULL 来判断每个 fields 都为空，而应该分别判断每个 field 是否 IS NULL
        2.不能直接比较两个 record，比如是否相等或者不相等、是否一个比另一个大，只能一个个 field 单独比较
        3.在 9i R2 之前，不能将 record 插入表中，只能将每个 field 单独插入相应的 column 中
       
    Field-level operations
      访问 field 的语法：
        [schema_name.][package_name.]record_name.field_name
        如果 record 定义在包中，那么 schema_name 是包所属的 schema，而不是编译该代码所使用的 schema
       
       
4.Trigger Pseudo-Records
    在写 trigger 时，Oracle 提供了两个 pseudo-records，他们的结构和使用 %ROWTYPE 声明的 table-based records 一样：
      OLD 表示当前事务完成前，表中每列的值
      NEW 表示当前事务完成后，表中每列的值
    在使用他们时在前面要加上冒号，在 WHEN clause 里面使用不需要加：
      CREATE OR REPLACE TRIGGER check_raise
         AFTER UPDATE OF salary
         ON employee
         FOR EACH ROW
      WHEN  (OLD.salary != NEW.salary) OR
            (OLD.salary IS NULL AND NEW.salary IS NOT NULL) OR
            (OLD.salary IS NOT NULL AND NEW.salary IS NULL)
      BEGIN
         IF :NEW.salary > 100000 THEN ...