作者：YongJing(敬勇)

ardinality Feedback基数反馈是版本11.2中引入的关于SQL 性能优化的新特性，该特性主要针对 统计信息陈旧、无直方图或虽然有直方图但仍基数计算不准确的情况， Cardinality基数的计算直接影响到后续的JOIN COST等重要的成本计算评估，造成CBO选择不当的执行计划
在几乎同时执行如下语句:

select count(1)
  from (select PKA020,
               PKA022,
               PKA023,
               rowno,
               hospital_id,
               hospital_name,
               serial_no,
               biz_type,
               case_id,
               biz_stat,
               name,
               sex,
               pers_type,
               begin_date,
               end_date,
               fin_date,
               indi_id,
               corp_id,
               idcard,
               district_code,
               office_grade,
               office_grade as official_code,
               injury_borth_sn,
               corp_name,
               disease,
               in_area_name,
               in_dept_name,
               in_bed,
               bed_type,
               patient_id,
               remark,
               pos_code,
               reimburse_flag,
               fin_disease,
               ic_no,
               treatment_type,
               treatment_name,
               decl_sn,
               sure_date,
               indi_code,
               insr_code
          from (select /*+ index(e IDX_KA06_AKA120) index(f IDX_KA06_AKA120) index(g IDX_KAA1_AKA130) index(h IDX_KAA2_PKA006)*/
                 b.PKA020,
                 b.PKA022,
                 b.PKA023,
                 rownum rowno,
                 t2.AKB020 as hospital_id,
                 a.AAB069 as hospital_name,
                 b.AAZ218 as serial_no,
                 b.PKA009 as case_id,
                 GETCODENAME('aka130', b.AKA130) as biz_type,
                 g.PKA154 as biz_stat,
                 b.AAC003 as name,
                 GETCODENAME('aac004', b.AAC004) as sex,
                 GETCODENAME('pka004', b.PKA004) as pers_type,
                 to_char(b.PKA017, 'yyyy-mm-dd') as begin_date,
                 to_char(b.PKA032, 'yyyy-mm-d d') as end_date,
                 b.PKA042 as injury_borth_sn,
                 to_char(b.PKA045, 'yyyy-mm-dd hh24:mi:ss') as fin_date,
                 b.AAC001 as indi_id,
                 b.AAB001 as corp_id,
                 b.AAC002 as idcard,
                 GETCODENAME('aaa027', b.AAA027) as district_code,
                 b.PKA005 as office_grade,
                 b.PKA005 as official_code,
                 b.PKA008 as corp_name,
                 e.AKA121 as disease,
                 b.PKA022 as in_area_name,
                 b.PKA020 as in_dept_name,
                 b.PKA023 as in_bed,
                 b.PKA024 as bed_type,
                 b.PKA025 as patient_id,
                 b.PKA043 as remark,
                 b.PKA040 as pos_code,
                 b.PKA037 as reimburse_flag,
                 f.AKA121 as fin_disease,
                 b.PKA100 as ic_no,
                 GETCODENAME('pka006', b.PKA006) as treatment_type,
                 h.PKA155 as treatment_name,
                 (select t1.PAC028 from ACK1 t1 where t1.aac001 = b.aac001) as indi_code,
                 (select t1.PAC028 from ACK1 t1 where t1.aac001 = b.aac001) as insr_code,
                 (select max(PAE010)
                    from KCE6 b1
                   where b1.AKB020 = b.AKB020
                     and b1.AAZ218 = b.AAZ218
                     and b1.AAE100 = '1') as decl_sn,
                 (select max(c2.AAE015)
                    from KCE6 b2, KCB4 c2
                   where b2.AKB020 = b.AKB020
                     and b2.AAZ218 = b.AAZ218
                     and b2.PAE010 = c2.PKB019
                     and b2.AAE100 = '1'
                     and c2.AAE100 = '1') as sure_date
                  from AEZ1 a,
                       KC21 b,
                       KA06 e,
                       KA06 f,
                       KAA1 g,
                       KAA2 h,
                       KB01 t2
                 where a.AAZ001 = t2.AAZ269
                   and t2.AKB020 = b.AKB020
                   and b.AKA130 = g.AKA130
                   and b.PKA026 = e.AKA120(+)
                   and b.PKA031 = f.AKA120(+)
                   and b.PKA006 = h.PKA006(+)
                   and b.AAE100 = '1'
                   and b.PKA004 in ('1', '4', '5', '6', '2', '7')
                   and ('A' = 'A' or 'A' = b.AAA027)
                   and b.akb020 = '002001'
                   and b.AKA130 = '12'
                   and nvl(b.PKA039, '0') = '1'
                   and b.PKA045 >=
                       to_date('20130901 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
                   and b.PKA045 < =
                       to_date('20130930 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
                   and b.PKA037 = '0') w) t1_

SQL>select plan_hash_value,id,operation,options,object_name,depth,cost,timestamp,child_address
  from v$SQL_PLAN
   where sql_id ='64q8v2p41c1vc'
  and plan_hash_value in (1059287951,3791045646);

PLAN_HASH_VALUE     ID       OPERATION            OPTIONS           OBJECT_NAME          DEPTH    COST       TIMESTAMP
---------------     --       ----------------     --------       ------------------     -------   ------     -------------
3791045646          0        SELECT STATEMENT                                            0        43960      2013-10-17 14:52:53
3791045646          1        SORT                 AGGREGATE                              1                   2013-10-17 14:52:53
3791045646          2        VIEW                                                        2        43960      2013-10-17 14:52:53
3791045646          3        COUNT                                                       3                   2013-10-17 14:52:53
3791045646          4        FILTER                                                      4                   2013-10-17 14:52:53
3791045646          5        HASH JOIN            OUTER                                  5        43960      2013-10-17 14:52:53
3791045646          6        NESTED LOOPS         OUTER                                  6        43873      2013-10-17 14:52:53
3791045646          7        NESTED LOOPS         OUTER                                  7        43822      2013-10-17 14:52:53
3791045646          8        MERGE JOIN           CARTESIAN                              8        43822      2013-10-17 14:52:53
3791045646          9        MERGE JOIN           CARTESIAN                              9        43822      2013-10-17 14:52:53
3791045646          10       NESTED LOOPS                                                10       2          2013-10-17 14:52:53
3791045646          11       TABLE ACCESS         BY INDEX ROWID    KB01                 11       2          2013-10-17 14:52:53
3791045646          12       INDEX                RANGE SCAN        IDX_KB01_AKB020      12       1          2013-10-17 14:52:53
3791045646          13       INDEX                UNIQUE SCAN       PK_AEZ1              11       0          2013-10-17 14:52:53
3791045646          14       BUFFER               SORT                                   10       43822      2013-10-17 14:52:53
3791045646          15       TABLE ACCESS         BY INDEX ROWID    KC21                 11       43820      2013-10-17 14:52:53
3791045646          16       INDEX                RANGE SCAN        IDX_KC21_PKA025      12       192        2013-10-17 14:52:53
3791045646          17       BUFFER               SORT                                   9        2          2013-10-17 14:52:53
3791045646          18       INDEX                RANGE SCAN        IDX_KAA1_AKA130      10       0          2013-10-17 14:52:53
3791045646          19       INDEX                RANGE SCAN        IDX_KAA2_PKA006      8        0          2013-10-17 14:52:53
3791045646          20       INDEX                RANGE SCAN        IDX_KA06_AKA120      7        1          2013-10-17 14:52:53
3791045646          21       INDEX                FULL SCAN         IDX_KA06_AKA120      6        86         2013-10-17 14:52:53
1059287951          0        SELECT STATEMENT                                            0        51         2013-10-17 14:52:03
1059287951          1        SORT                 AGGREGATE                              1                   2013-10-17 14:52:03
1059287951          2        VIEW                                                        2        51         2013-10-17 14:52:03
1059287951          3        COUNT                                                       3                   2013-10-17 14:52:03
1059287951          4        FILTER                                                      4                   2013-10-17 14:52:03
1059287951          5        NESTED LOOPS         OUTER                                  5        51         2013-10-17 14:52:03
1059287951          6        NESTED LOOPS         OUTER                                  6        50         2013-10-17 14:52:03
1059287951          7        NESTED LOOPS                                                7        49         2013-10-17 14:52:03
1059287951          8        NESTED LOOPS         OUTER                                  8        49         2013-10-17 14:52:03
1059287951          9        MERGE JOIN           CARTESIAN                              9        49         2013-10-17 14:52:03
1059287951          10       MERGE JOIN           CARTESIAN                              10       49         2013-10-17 14:52:03
1059287951          11       TABLE ACCESS         BY INDEX ROWID    KB01                 11       2          2013-10-17 14:52:03
1059287951          12       INDEX                RANGE SCAN        IDX_KB01_AKB020      12       1          2013-10-17 14:52:03
1059287951          13       BUFFER               SORT                                   11       47         2013-10-17 14:52:03
1059287951          14       TABLE ACCESS         BY INDEX ROWID    KC21                 12       47         2013-10-17 14:52:03
1059287951          15       INDEX                RANGE SCAN        IDX_KC21_PKA045      13       3          2013-10-17 14:52:03
1059287951          16       BUFFER               SORT                                   10       2          2013-10-17 14:52:03
1059287951          17       INDEX                RANGE SCAN        IDX_KAA1_AKA130      11       0          2013-10-17 14:52:03
1059287951          18       INDEX                RANGE SCAN        IDX_KAA2_PKA006      9        0          2013-10-17 14:52:03
1059287951          19       INDEX                UNIQUE SCAN       PK_AEZ1              8        0          2013-10-17 14:52:03
1059287951          20       INDEX                RANGE SCAN        IDX_KA06_AKA120      7        1          2013-10-17 14:52:03
1059287951          21       INDEX                RANGE SCAN        IDX_KA06_AKA120      6        1          2013-10-17 14:52:03
1059287951          0        SELECT STATEMENT                                            0        51         2013-10-17 14:50:37
1059287951          1        SORT                 AGGREGATE                              1                   2013-10-17 14:50:37
1059287951          2        VIEW                                                        2        51         2013-10-17 14:50:37
1059287951          3        COUNT                                                       3                   2013-10-17 14:50:37
1059287951          4        FILTER                                                      4                   2013-10-17 14:50:37
1059287951          5        NESTED LOOPS         OUTER                                  5        51         2013-10-17 14:50:37
1059287951          6        NESTED LOOPS         OUTER                                  6        50         2013-10-17 14:50:37
1059287951          7        NESTED LOOPS                                                7        49         2013-10-17 14:50:37
1059287951          8        NESTED LOOPS         OUTER                                  8        49         2013-10-17 14:50:37
1059287951          9        MERGE JOIN           CARTESIAN                              9        49         2013-10-17 14:50:37
1059287951          10       MERGE JOIN           CARTESIAN                              10       49         2013-10-17 14:50:37
1059287951          11       TABLE ACCESS         BY INDEX ROWID    KB01                 11       2          2013-10-17 14:50:37
1059287951          12       INDEX                RANGE SCAN        IDX_KB01_AKB020      12       1          2013-10-17 14:50:37
1059287951          13       BUFFER               SORT                                   11       47         2013-10-17 14:50:37
1059287951          14       TABLE ACCESS         BY INDEX ROWID    KC21                 12       47         2013-10-17 14:50:37
1059287951          15       INDEX                RANGE SCAN        IDX_KC21_PKA045      13       3          2013-10-17 14:50:37
1059287951          16       BUFFER               SORT                                   10       2          2013-10-17 14:50:37
1059287951          17       INDEX                RANGE SCAN        IDX_KAA1_AKA130      11       0          2013-10-17 14:50:37
1059287951          18       INDEX                RANGE SCAN        IDX_KAA2_PKA006      9        0          2013-10-17 14:50:37
1059287951          19       INDEX                UNIQUE SCAN       PK_AEZ1              8        0          2013-10-17 14:50:37
1059287951          20       INDEX                RANGE SCAN        IDX_KA06_AKA120      7        1          2013-10-17 14:50:37
1059287951          21       INDEX                RANGE SCAN        IDX_KA06_AKA120      6        1          2013-10-17 14:50:37

得到以下不同的执行计划(这个语句执行了两次同一个sql_id产生不两个子游标使用不同的执行计划)

SQL_ID  64q8v2p41c1vc, child number 0
-------------------------------------
select count(1)
  from (select PKA020,
               PKA022,
               PKA023,
               rowno,
               hospital_id,
               hospital_name,
               serial_no,
               biz_type,
               case_id,
               biz_stat,
               name,
               sex,
               pers_type,
               begin_date,
               end_date,
               fin_date,
               indi_id,
               corp_id,
               idcard,
               district_code,
               office_grade,
               office_grade as official_code,
               injury_borth_sn,
               corp_name,
               disease,
               in_area_name,
               in_dept_name,
               in_bed,
               bed_type,
               patient_id,
               remark,
               pos_code,
               reimburse_flag,
               fin_disease,
               ic_no,
               treatment_type,
               treatment_name,
               decl_sn,
               sure_date,
               indi_code,
               insr_code
          from (select /*+ index(e IDX_KA06_AKA120) index(f IDX_KA06_AKA120) index(g IDX_KAA1_AKA130) index(h IDX_KAA2_PKA006)*/
                 b.PKA020,
                 b.PKA022,
                 b.PKA023,
                 rownum rowno,
                 t2.AKB020 as hospital_id,
                 a.AAB069 as hospital_name,
                 b.AAZ218 as serial_no,
                 b.PKA009 as case_id,
                 GETCODENAME('aka130', b.AKA130) as biz_type,
                 g.PKA154 as biz_stat,
                 b.AAC003 as name,
                 GETCODENAME('aac004', b.AAC004) as sex,
                 GETCODENAME('pka004', b.PKA004) as pers_type,
                 to_char(b.PKA017, 'yyyy-mm-dd') as begin_date,
                 to_char(b.PKA032, 'yyyy-mm-d d') as end_date,
                 b.PKA042 as injury_borth_sn,
                 to_char(b.PKA045, 'yyyy-mm-dd hh24:mi:ss') as fin_date,
                 b.AAC001 as indi_id,
                 b.AAB001 as corp_id,
                 b.AAC002 as idcard,
                 GETCODENAME('aaa027', b.AAA027) as district_code,
                 b.PKA005 as office_grade,
                 b.PKA005 as official_code,
                 b.PKA008 as corp_name,
                 e.AKA121 as disease,
                 b.PKA022 as in_area_name,
                 b.PKA020 as in_dept_name,
                 b.PKA023 as in_bed,
                 b.PKA024 as bed_type,
                 b.PKA025 as patient_id,
                 b.PKA043 as remark,
                 b.PKA040 as pos_code,
                 b.PKA037 as reimburse_flag,
                 f.AKA121 as fin_disease,
                 b.PKA100 as ic_no,
                 GETCODENAME('pka006', b.PKA006) as treatment_type,
                 h.PKA155 as treatment_name,
                 (select t1.PAC028 from ACK1 t1 where t1.aac001 = b.aac001) as indi_code,
                 (select t1.PAC028 from ACK1 t1 where t1.aac001 = b.aac001) as insr_code,
                 (select max(PAE010)
                    from KCE6 b1
                   where b1.AKB020 = b.AKB020
                     and b1.AAZ218 = b.AAZ218
                     and b1.AAE100 = '1') as decl_sn,
                 (select max(c2.AAE015)
                    from KCE6 b2, KCB4 c2
                   where b2.AKB020 = b.AKB020
                     and b2.AAZ218 = b.AAZ218
                     and b2.PAE010 = c2.PKB019
                     and b2.AAE100 = '1'
                     and c2.AAE100 = '1') as sure_date
                  from AEZ1 a,
                       KC21 b,
                       KA06 e,
                       KA06 f,
                       KAA1 g,
                       KAA2 h,
                       KB01 t2
                 where a.AAZ001 = t2.AAZ269
                   and t2.AKB020 = b.AKB020
                   and b.AKA130 = g.AKA130
                   and b.PKA026 = e.AKA120(+)
                   and b.PKA031 = f.AKA120(+)
                   and b.PKA006 = h.PKA006(+)
                   and b.AAE100 = '1'
                   and b.PKA004 in ('1', '4', '5', '6', '2', '7')
                   and ('A' = 'A' or 'A' = b.AAA027)
                   and b.akb020 = '002001'
                   and b.AKA130 = '12'
                   and nvl(b.PKA039, '0') = '1'
                   and b.PKA045 >=
                       to_date('20130901 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
                   and b.PKA045 < =
                       to_date('20130930 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
                   and b.PKA037 = '0') w) t1_

Plan hash value: 1059287951

----------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                 |       |       |    51 (100)|          |
|   1 |  SORT AGGREGATE                        |                 |     1 |       |            |          |
|   2 |   VIEW                                 |                 |     1 |       |    51   (0)| 00:00:01 |
|   3 |    COUNT                               |                 |       |       |            |          |
|*  4 |     FILTER                             |                 |       |       |            |          |
|   5 |      NESTED LOOPS OUTER                |                 |     1 |   115 |    51   (0)| 00:00:01 |
|   6 |       NESTED LOOPS OUTER               |                 |     1 |   106 |    50   (0)| 00:00:01 |
|   7 |        NESTED LOOPS                    |                 |     1 |    97 |    49   (0)| 00:00:01 |
|   8 |         NESTED LOOPS OUTER             |                 |     1 |    90 |    49   (0)| 00:00:01 |
|   9 |          MERGE JOIN CARTESIAN          |                 |     1 |    85 |    49   (0)| 00:00:01 |
|  10 |           MERGE JOIN CARTESIAN         |                 |     1 |    82 |    49   (0)| 00:00:01 |
|  11 |            TABLE ACCESS BY INDEX ROWID | KB01            |     1 |    15 |     2   (0)| 00:00:01 |
|* 12 |             INDEX RANGE SCAN           | IDX_KB01_AKB020 |     1 |       |     1   (0)| 00:00:01 |
|  13 |            BUFFER SORT                 |                 |     1 |    67 |    47   (0)| 00:00:01 |
|* 14 |             TABLE ACCESS BY INDEX ROWID| KC21            |     1 |    67 |    47   (0)| 00:00:01 |
|* 15 |              INDEX RANGE SCAN          | IDX_KC21_PKA045 |    60 |       |     3   (0)| 00:00:01 |
|  16 |           BUFFER SORT                  |                 |     1 |     3 |     2   (0)| 00:00:01 |
|* 17 |            INDEX RANGE SCAN            | IDX_KAA1_AKA130 |     1 |     3 |     0   (0)|          |
|* 18 |          INDEX RANGE SCAN              | IDX_KAA2_PKA006 |     1 |     5 |     0   (0)|          |
|* 19 |         INDEX UNIQUE SCAN              | PK_AEZ1         |     1 |     7 |     0   (0)|          |
|* 20 |        INDEX RANGE SCAN                | IDX_KA06_AKA120 |     1 |     9 |     1   (0)| 00:00:01 |
|* 21 |       INDEX RANGE SCAN                 | IDX_KA06_AKA120 |     1 |     9 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$F5BB74E1
   2 - SEL$3        / W@SEL$2
   3 - SEL$3
  11 - SEL$3        / T2@SEL$3
  12 - SEL$3        / T2@SEL$3
  14 - SEL$3        / B@SEL$3
  15 - SEL$3        / B@SEL$3
  17 - SEL$3        / G@SEL$3
  18 - SEL$3        / H@SEL$3
  19 - SEL$3        / A@SEL$3
  20 - SEL$3        / F@SEL$3
  21 - SEL$3        / E@SEL$3

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      NO_PARALLEL
      OUTLINE_LEAF(@"SEL$4")
      OUTLINE_LEAF(@"SEL$5")
      OUTLINE_LEAF(@"SEL$6")
      OUTLINE_LEAF(@"SEL$7")
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"SEL$F5BB74E1")
      MERGE(@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      NO_ACCESS(@"SEL$F5BB74E1" "W"@"SEL$2")
      INDEX_RS_ASC(@"SEL$3" "T2"@"SEL$3" ("KB01"."AKB020"))
      INDEX_RS_ASC(@"SEL$3" "B"@"SEL$3" ("KC21"."AKB020" "KC21"."PKA045"))
      INDEX(@"SEL$3" "G"@"SEL$3" ("KAA1"."AKA130"))
      INDEX(@"SEL$3" "H"@"SEL$3" ("KAA2"."PKA006"))
      INDEX(@"SEL$3" "A"@"SEL$3" ("AEZ1"."AAZ001"))
      INDEX(@"SEL$3" "F"@"SEL$3" ("KA06"."AKA120"))
      INDEX(@"SEL$3" "E"@"SEL$3" ("KA06"."AKA120"))
      LEADING(@"SEL$3" "T2"@"SEL$3" "B"@"SEL$3" "G"@"SEL$3" "H"@"SEL$3" "A"@"SEL$3" "F"@"SEL$3"
              "E"@"SEL$3")
      USE_MERGE_CARTESIAN(@"SEL$3" "B"@"SEL$3")
      USE_MERGE_CARTESIAN(@"SEL$3" "G"@"SEL$3")
      USE_NL(@"SEL$3" "H"@"SEL$3")
      USE_NL(@"SEL$3" "A"@"SEL$3")
      USE_NL(@"SEL$3" "F"@"SEL$3")
      USE_NL(@"SEL$3" "E"@"SEL$3")
      INDEX_RS_ASC(@"SEL$7" "B2"@"SEL$7" ("KCE6"."AKB020" "KCE6"."AAZ218" "KCE6"."AAE100"))
      INDEX(@"SEL$7" "C2"@"SEL$7" ("KCB4"."PKB019"))
      LEADING(@"SEL$7" "B2"@"SEL$7" "C2"@"SEL$7")
      USE_NL(@"SEL$7" "C2"@"SEL$7")
      NLJ_BATCHING(@"SEL$7" "C2"@"SEL$7")
      INDEX_RS_ASC(@"SEL$6" "B1"@"SEL$6" ("KCE6"."AKB020" "KCE6"."AAZ218" "KCE6"."AAE100"))
      INDEX_RS_ASC(@"SEL$5" "T1"@"SEL$5" ("ACK1"."AAC001"))
      INDEX_RS_ASC(@"SEL$4" "T1"@"SEL$4" ("ACK1"."AAC001"))
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter(TO_DATE('20130901 00:00:00','yyyy-mm-dd hh24:mi:ss')<=TO_DATE('20130930
              23:59:59','yyyy-mm-dd hh24:mi:ss'))
  12 - access("T2"."AKB020"='002001')
  14 - filter(("B"."AKA130"='12' AND "B"."AAE100"='1' AND "B"."PKA039"='1' AND "B"."PKA037"='0'
              AND INTERNAL_FUNCTION("B"."PKA004")))
  15 - access("B"."AKB020"='002001' AND "B"."PKA045">=TO_DATE('20130901 00:00:00','yyyy-mm-dd
              hh24:mi:ss') AND "B"."PKA045"< =TO_DATE('20130930 23:59:59','yyyy-mm-dd hh24:mi:ss'))
  17 - access("G"."AKA130"='12')
  18 - access("B"."PKA006"="H"."PKA006")
  19 - access("A"."AAZ001"="T2"."AAZ269")
  20 - access("B"."PKA031"="F"."AKA120")
  21 - access("B"."PKA026"="E"."AKA120")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]
   6 - "B"."PKA026"[VARCHAR2,20]
   7 - "B"."PKA026"[VARCHAR2,20], "B"."PKA031"[VARCHAR2,20]
   8 - "T2"."AAZ269"[NUMBER,22], "B"."PKA026"[VARCHAR2,20], "B"."PKA031"[VARCHAR2,20]
   9 - "T2"."AAZ269"[NUMBER,22], "B"."PKA006"[VARCHAR2,6], "B"."PKA026"[VARCHAR2,20],
       "B"."PKA031"[VARCHAR2,20]
  10 - "T2"."AAZ269"[NUMBER,22], "B"."PKA006"[VARCHAR2,6], "B"."PKA026"[VARCHAR2,20],
       "B"."PKA031"[VARCHAR2,20]
  11 - "T2"."AAZ269"[NUMBER,22]
  12 - "T2".ROWID[ROWID,10]
  13 - (#keys=0) "B"."PKA006"[VARCHAR2,6], "B"."PKA026"[VARCHAR2,20], "B"."PKA031"[VARCHAR2,20]
  14 - "B"."PKA006"[VARCHAR2,6], "B"."PKA026"[VARCHAR2,20], "B"."PKA031"[VARCHAR2,20]
  15 - "B".ROWID[ROWID,10]
  16 - (#keys=0)

Note
-----
   - automatic DOP: skipped because of IO calibrate statistics are missing

SQL_ID  64q8v2p41c1vc, child number 1
-------------------------------------
select count(1)
  from (select PKA020,
               PKA022,
               PKA023,
               rowno,
               hospital_id,
               hospital_name,
               serial_no,
               biz_type,
               case_id,
               biz_stat,
               name,
               sex,
               pers_type,
               begin_date,
               end_date,
               fin_date,
               indi_id,
               corp_id,
               idcard,
               district_code,
               office_grade,
               office_grade as official_code,
               injury_borth_sn,
               corp_name,
               disease,
               in_area_name,
               in_dept_name,
               in_bed,
               bed_type,
               patient_id,
               remark,
               pos_code,
               reimburse_flag,
               fin_disease,
               ic_no,
               treatment_type,
               treatment_name,
               decl_sn,
               sure_date,
               indi_code,
               insr_code
          from (select /*+ index(e IDX_KA06_AKA120) index(f IDX_KA06_AKA120) index(g IDX_KAA1_AKA130) index(h IDX_KAA2_PKA006)*/
                 b.PKA020,
                 b.PKA022,
                 b.PKA023,
                 rownum rowno,
                 t2.AKB020 as hospital_id,
                 a.AAB069 as hospital_name,
                 b.AAZ218 as serial_no,
                 b.PKA009 as case_id,
                 GETCODENAME('aka130', b.AKA130) as biz_type,
                 g.PKA154 as biz_stat,
                 b.AAC003 as name,
                 GETCODENAME('aac004', b.AAC004) as sex,
                 GETCODENAME('pka004', b.PKA004) as pers_type,
                 to_char(b.PKA017, 'yyyy-mm-dd') as begin_date,
                 to_char(b.PKA032, 'yyyy-mm-d d') as end_date,
                 b.PKA042 as injury_borth_sn,
                 to_char(b.PKA045, 'yyyy-mm-dd hh24:mi:ss') as fin_date,
                 b.AAC001 as indi_id,
                 b.AAB001 as corp_id,
                 b.AAC002 as idcard,
                 GETCODENAME('aaa027', b.AAA027) as district_code,
                 b.PKA005 as office_grade,
                 b.PKA005 as official_code,
                 b.PKA008 as corp_name,
                 e.AKA121 as disease,
                 b.PKA022 as in_area_name,
                 b.PKA020 as in_dept_name,
                 b.PKA023 as in_bed,
                 b.PKA024 as bed_type,
                 b.PKA025 as patient_id,
                 b.PKA043 as remark,
                 b.PKA040 as pos_code,
                 b.PKA037 as reimburse_flag,
                 f.AKA121 as fin_disease,
                 b.PKA100 as ic_no,
                 GETCODENAME('pka006', b.PKA006) as treatment_type,
                 h.PKA155 as treatment_name,
                 (select t1.PAC028 from ACK1 t1 where t1.aac001 = b.aac001) as indi_code,
                 (select t1.PAC028 from ACK1 t1 where t1.aac001 = b.aac001) as insr_code,
                 (select max(PAE010)
                    from KCE6 b1
                   where b1.AKB020 = b.AKB020
                     and b1.AAZ218 = b.AAZ218
                     and b1.AAE100 = '1') as decl_sn,
                 (select max(c2.AAE015)
                    from KCE6 b2, KCB4 c2
                   where b2.AKB020 = b.AKB020
                     and b2.AAZ218 = b.AAZ218
                     and b2.PAE010 = c2.PKB019
                     and b2.AAE100 = '1'
                     and c2.AAE100 = '1') as sure_date
                  from AEZ1 a,
                       KC21 b,
                       KA06 e,
                       KA06 f,
                       KAA1 g,
                       KAA2 h,
                       KB01 t2
                 where a.AAZ001 = t2.AAZ269
                   and t2.AKB020 = b.AKB020
                   and b.AKA130 = g.AKA130
                   and b.PKA026 = e.AKA120(+)
                   and b.PKA031 = f.AKA120(+)
                   and b.PKA006 = h.PKA006(+)
                   and b.AAE100 = '1'
                   and b.PKA004 in ('1', '4', '5', '6', '2', '7')
                   and ('A' = 'A' or 'A' = b.AAA027)
                   and b.akb020 = '002001'
                   and b.AKA130 = '12'
                   and nvl(b.PKA039, '0') = '1'
                   and b.PKA045 >=
                       to_date('20130901 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
                   and b.PKA045 < =
                       to_date('20130930 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
                   and b.PKA037 = '0') w) t1_

Plan hash value: 3791045646

---------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                 |       |       | 43960 (100)|          |
|   1 |  SORT AGGREGATE                       |                 |     1 |       |            |          |
|   2 |   VIEW                                |                 |  1079 |       | 43960   (1)| 00:08:48 |
|   3 |    COUNT                              |                 |       |       |            |          |
|*  4 |     FILTER                            |                 |       |       |            |          |
|*  5 |      HASH JOIN OUTER                  |                 |  1079 |   121K| 43960   (1)| 00:08:48 |
|   6 |       NESTED LOOPS OUTER              |                 |  1079 |   111K| 43873   (1)| 00:08:47 |
|   7 |        NESTED LOOPS OUTER             |                 |  1079 |   102K| 43822   (1)| 00:08:46 |
|   8 |         MERGE JOIN CARTESIAN          |                 |  1079 | 99268 | 43822   (1)| 00:08:46 |
|   9 |          MERGE JOIN CARTESIAN         |                 |  1079 | 96031 | 43822   (1)| 00:08:46 |
|  10 |           NESTED LOOPS                |                 |     1 |    22 |     2   (0)| 00:00:01 |
|  11 |            TABLE ACCESS BY INDEX ROWID| KB01            |     1 |    15 |     2   (0)| 00:00:01 |
|* 12 |             INDEX RANGE SCAN          | IDX_KB01_AKB020 |     1 |       |     1   (0)| 00:00:01 |
|* 13 |            INDEX UNIQUE SCAN          | PK_AEZ1         |     1 |     7 |     0   (0)|          |
|  14 |           BUFFER SORT                 |                 |  1079 | 72293 | 43822   (1)| 00:08:46 |
|* 15 |            TABLE ACCESS BY INDEX ROWID| KC21            |  1079 | 72293 | 43820   (1)| 00:08:46 |
|* 16 |             INDEX RANGE SCAN          | IDX_KC21_PKA025 | 56948 |       |   192   (2)| 00:00:03 |
|  17 |          BUFFER SORT                  |                 |     1 |     3 |     2   (0)| 00:00:01 |
|* 18 |           INDEX RANGE SCAN            | IDX_KAA1_AKA130 |     1 |     3 |     0   (0)|          |
|* 19 |         INDEX RANGE SCAN              | IDX_KAA2_PKA006 |     1 |     5 |     0   (0)|          |
|* 20 |        INDEX RANGE SCAN               | IDX_KA06_AKA120 |     1 |     9 |     1   (0)| 00:00:01 |
|  21 |       INDEX FULL SCAN                 | IDX_KA06_AKA120 | 22655 |   199K|    86   (2)| 00:00:02 |
---------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$F5BB74E1
   2 - SEL$3        / W@SEL$2
   3 - SEL$3
  11 - SEL$3        / T2@SEL$3
  12 - SEL$3        / T2@SEL$3
  13 - SEL$3        / A@SEL$3
  15 - SEL$3        / B@SEL$3
  16 - SEL$3        / B@SEL$3
  18 - SEL$3        / G@SEL$3
  19 - SEL$3        / H@SEL$3
  20 - SEL$3        / F@SEL$3
  21 - SEL$3        / E@SEL$3

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      NO_PARALLEL
      OUTLINE_LEAF(@"SEL$4")
      OUTLINE_LEAF(@"SEL$5")
      OUTLINE_LEAF(@"SEL$6")
      OUTLINE_LEAF(@"SEL$7")
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"SEL$F5BB74E1")
      MERGE(@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      NO_ACCESS(@"SEL$F5BB74E1" "W"@"SEL$2")
      INDEX_RS_ASC(@"SEL$3" "T2"@"SEL$3" ("KB01"."AKB020"))
      INDEX(@"SEL$3" "A"@"SEL$3" ("AEZ1"."AAZ001"))
      INDEX_RS_ASC(@"SEL$3" "B"@"SEL$3" ("KC21"."AKB020" "KC21"."PKA025"))
      INDEX(@"SEL$3" "G"@"SEL$3" ("KAA1"."AKA130"))
      INDEX(@"SEL$3" "H"@"SEL$3" ("KAA2"."PKA006"))
      INDEX(@"SEL$3" "F"@"SEL$3" ("KA06"."AKA120"))
      INDEX(@"SEL$3" "E"@"SEL$3" ("KA06"."AKA120"))
      LEADING(@"SEL$3" "T2"@"SEL$3" "A"@"SEL$3" "B"@"SEL$3" "G"@"SEL$3" "H"@"SEL$3" "F"@"SEL$3"
              "E"@"SEL$3")
      USE_NL(@"SEL$3" "A"@"SEL$3")
      USE_MERGE_CARTESIAN(@"SEL$3" "B"@"SEL$3")
      USE_MERGE_CARTESIAN(@"SEL$3" "G"@"SEL$3")
      USE_NL(@"SEL$3" "H"@"SEL$3")
      USE_NL(@"SEL$3" "F"@"SEL$3")
      USE_HASH(@"SEL$3" "E"@"SEL$3")
      INDEX_RS_ASC(@"SEL$7" "B2"@"SEL$7" ("KCE6"."AKB020" "KCE6"."AAZ218" "KCE6"."AAE100"))
      INDEX(@"SEL$7" "C2"@"SEL$7" ("KCB4"."PKB019"))
      LEADING(@"SEL$7" "B2"@"SEL$7" "C2"@"SEL$7")
      USE_NL(@"SEL$7" "C2"@"SEL$7")
      NLJ_BATCHING(@"SEL$7" "C2"@"SEL$7")
      INDEX_RS_ASC(@"SEL$6" "B1"@"SEL$6" ("KCE6"."AKB020" "KCE6"."AAZ218" "KCE6"."AAE100"))
      INDEX_RS_ASC(@"SEL$5" "T1"@"SEL$5" ("ACK1"."AAC001"))
      INDEX_RS_ASC(@"SEL$4" "T1"@"SEL$4" ("ACK1"."AAC001"))
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter(TO_DATE('20130901 00:00:00','yyyy-mm-dd hh24:mi:ss')<=TO_DATE('20130930
              23:59:59','yyyy-mm-dd hh24:mi:ss'))
   5 - access("B"."PKA026"="E"."AKA120")
  12 - access("T2"."AKB020"='002001')
  13 - access("A"."AAZ001"="T2"."AAZ269")
  15 - filter(("B"."AKA130"='12' AND "B"."AAE100"='1' AND "B"."PKA039"='1' AND "B"."PKA037"='0'
              AND INTERNAL_FUNCTION("B"."PKA004") AND "B"."PKA045"<=TO_DATE('20130930 23:59:59','yyyy-mm-dd
              hh24:mi:ss') AND "B"."PKA045">=TO_DATE('20130901 00:00:00','yyyy-mm-dd hh24:mi:ss')))
  16 - access("B"."AKB020"='002001')
  18 - access("G"."AKA130"='12')
  19 - access("B"."PKA006"="H"."PKA006")
  20 - access("B"."PKA031"="F"."AKA120")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]
   5 - (#keys=1)
   6 - "B"."PKA026"[VARCHAR2,20]
   7 - "B"."PKA026"[VARCHAR2,20], "B"."PKA031"[VARCHAR2,20]
   8 - "B"."PKA006"[VARCHAR2,6], "B"."PKA026"[VARCHAR2,20], "B"."PKA031"[VARCHAR2,20]
   9 - "B"."PKA006"[VARCHAR2,6], "B"."PKA026"[VARCHAR2,20], "B"."PKA031"[VARCHAR2,20]
  11 - "T2"."AAZ269"[NUMBER,22]
  12 - "T2".ROWID[ROWID,10]
  14 - (#keys=0) "B"."PKA006"[VARCHAR2,6], "B"."PKA026"[VARCHAR2,20], "B"."PKA031"[VARCHAR2,20]
  15 - "B"."PKA006"[VARCHAR2,6], "B"."PKA026"[VARCHAR2,20], "B"."PKA031"[VARCHAR2,20]
  16 - "B".ROWID[ROWID,10]
  17 - (#keys=0)
  21 - "E"."AKA120"[VARCHAR2,50]

Note
-----
   - automatic DOP: skipped because of IO calibrate statistics are missing
   - cardinality feedback used for this statement

从上面两个游标的执行计划可以看到对于表kc21的访问 plan_hash_value=1059287951使用
INDEX RANGE SCAN | IDX_KC21_PKA045 | 60 | 使用索引IDX_KC21_PKA045进行索引范围扫描评估的记录是60
而plan_hash_value=3791045646使用
INDEX RANGE SCAN | IDX_KC21_PKA025 | 56948 | 使用索引IDX_KC21_PKA025 进行索引范围扫描评估的记录是56948

Plan hash value: 1059287951

----------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                 |       |       |    51 (100)|          |
|   1 |  SORT AGGREGATE                        |                 |     1 |       |            |          |
|   2 |   VIEW                                 |                 |     1 |       |    51   (0)| 00:00:01 |
|   3 |    COUNT                               |                 |       |       |            |          |
|*  4 |     FILTER                             |                 |       |       |            |          |
|   5 |      NESTED LOOPS OUTER                |                 |     1 |   115 |    51   (0)| 00:00:01 |
|   6 |       NESTED LOOPS OUTER               |                 |     1 |   106 |    50   (0)| 00:00:01 |
|   7 |        NESTED LOOPS                    |                 |     1 |    97 |    49   (0)| 00:00:01 |
|   8 |         NESTED LOOPS OUTER             |                 |     1 |    90 |    49   (0)| 00:00:01 |
|   9 |          MERGE JOIN CARTESIAN          |                 |     1 |    85 |    49   (0)| 00:00:01 |
|  10 |           MERGE JOIN CARTESIAN         |                 |     1 |    82 |    49   (0)| 00:00:01 |
|  11 |            TABLE ACCESS BY INDEX ROWID | KB01            |     1 |    15 |     2   (0)| 00:00:01 |
|* 12 |             INDEX RANGE SCAN           | IDX_KB01_AKB020 |     1 |       |     1   (0)| 00:00:01 |
|  13 |            BUFFER SORT                 |                 |     1 |    67 |    47   (0)| 00:00:01 |
|* 14 |             TABLE ACCESS BY INDEX ROWID| KC21            |     1 |    67 |    47   (0)| 00:00:01 |
|* 15 |              INDEX RANGE SCAN          | IDX_KC21_PKA045 |    60 |       |     3   (0)| 00:00:01 |
|  16 |           BUFFER SORT                  |                 |     1 |     3 |     2   (0)| 00:00:01 |
|* 17 |            INDEX RANGE SCAN            | IDX_KAA1_AKA130 |     1 |     3 |     0   (0)|          |
|* 18 |          INDEX RANGE SCAN              | IDX_KAA2_PKA006 |     1 |     5 |     0   (0)|          |
|* 19 |         INDEX UNIQUE SCAN              | PK_AEZ1         |     1 |     7 |     0   (0)|          |
|* 20 |        INDEX RANGE SCAN                | IDX_KA06_AKA120 |     1 |     9 |     1   (0)| 00:00:01 |
|* 21 |       INDEX RANGE SCAN                 | IDX_KA06_AKA120 |     1 |     9 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------


Plan hash value: 3791045646

---------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                 |       |       | 43960 (100)|          |
|   1 |  SORT AGGREGATE                       |                 |     1 |       |            |          |
|   2 |   VIEW                                |                 |  1079 |       | 43960   (1)| 00:08:48 |
|   3 |    COUNT                              |                 |       |       |            |          |
|*  4 |     FILTER                            |                 |       |       |            |          |
|*  5 |      HASH JOIN OUTER                  |                 |  1079 |   121K| 43960   (1)| 00:08:48 |
|   6 |       NESTED LOOPS OUTER              |                 |  1079 |   111K| 43873   (1)| 00:08:47 |
|   7 |        NESTED LOOPS OUTER             |                 |  1079 |   102K| 43822   (1)| 00:08:46 |
|   8 |         MERGE JOIN CARTESIAN          |                 |  1079 | 99268 | 43822   (1)| 00:08:46 |
|   9 |          MERGE JOIN CARTESIAN         |                 |  1079 | 96031 | 43822   (1)| 00:08:46 |
|  10 |           NESTED LOOPS                |                 |     1 |    22 |     2   (0)| 00:00:01 |
|  11 |            TABLE ACCESS BY INDEX ROWID| KB01            |     1 |    15 |     2   (0)| 00:00:01 |
|* 12 |             INDEX RANGE SCAN          | IDX_KB01_AKB020 |     1 |       |     1   (0)| 00:00:01 |
|* 13 |            INDEX UNIQUE SCAN          | PK_AEZ1         |     1 |     7 |     0   (0)|          |
|  14 |           BUFFER SORT                 |                 |  1079 | 72293 | 43822   (1)| 00:08:46 |
|* 15 |            TABLE ACCESS BY INDEX ROWID| KC21            |  1079 | 72293 | 43820   (1)| 00:08:46 |
|* 16 |             INDEX RANGE SCAN          | IDX_KC21_PKA025 | 56948 |       |   192   (2)| 00:00:03 |
|  17 |          BUFFER SORT                  |                 |     1 |     3 |     2   (0)| 00:00:01 |
|* 18 |           INDEX RANGE SCAN            | IDX_KAA1_AKA130 |     1 |     3 |     0   (0)|          |
|* 19 |         INDEX RANGE SCAN              | IDX_KAA2_PKA006 |     1 |     5 |     0   (0)|          |
|* 20 |        INDEX RANGE SCAN               | IDX_KA06_AKA120 |     1 |     9 |     1   (0)| 00:00:01 |
|  21 |       INDEX FULL SCAN                 | IDX_KA06_AKA120 | 22655 |   199K|    86   (2)| 00:00:02 |
---------------------------------------------------------------------------------------------------------

我们可以通过可以通过V$SQL_SHARED_CURSOR和来找出现有系统shared pool中存在的使用了Cardinality Feedback基数反馈的子游标

SQL>select sql_id,child_number,executions,loads,child_address from v$sql where sql_id='64q8v2p41c1vc';
SQL_ID        CHILD_NUMBER  EXECUTIONS	LOADS	CHILD_ADDRESS
------------- ------------  ----------  ----- -----------------
64q8v2p41c1vc 	1	            5	         3	  07000010AC4EAAE8
64q8v2p41c1vc	  2            	1	         1	  07000010AA093B00


SQL>select sql_id,address,child_address,child_number,user_feedback_stats
 from v$sql_shared_cursor where sql_id='64q8v2p41c1vc';
SQL_ID	      ADDRESS	      CHILD_ADDRESS	CHILD_NUMBER    USE_FEEDBACK_STATS
------------- ----------------      ----------------  ------------    ---------------
64q8v2p41c1vc	07000010AD35F368	07000010AC4EAAE8	1                Y
64q8v2p41c1vc	07000010AD35F368	07000010AA093B00	2                N

可以看到两个子游标的USE_FEEDBACK_STATS的值不一样,正是因这Cardinality Feedback基数反馈造成的

如果当这个特性产生更差的执行计划时可以考虑禁用这个特性
可以通过多种方法禁用该特性
1. 使用 _optimizer_use_feedback 隐藏参数 session 级别

SQL> alter session set “_optimizer_use_feedback”=false;
会话已更改。

system级别

SQL> alter system set “_optimizer_use_feedback”=false;
系统已更改。

2. 使用opt_param(‘_optimizer_use_feedback’ ‘false’) HINT在语句级进行禁用

select /*+ opt_param(‘_optimizer_use_feedback’ ‘false’)*/ count(*) from jy;

如果要强制使用Cardinality Feedback可以使用cardinality HINT

select /*+ cardinality(jy,  1) */ count(*) from jy;

SQL*Loader（SQLLDR）是Oracle的高速批量数据加载工具。这是一个非常有用的工具，可用于多种平面文件格式向Oralce数据库 中加载数据。SQLLDR可以在极短的时间内加载数量庞大的数据。它有两种操作模式：
传统路径：（conventional path）：SQLLDR会利用SQL插入为我们加载数据。
直接路径(direct path)：采用这种模式，SQLLDR不使用SQL；而是直接格式化数据库块。
利用直接路径加载，能从一个平面文件读数据，并将其直接写至格式化的数据库块，而绕过整个SQL引擎和undo生成，同时还 可能避开redo生成。要在一个没有任何数据的数据库中充分加载数据，最快的方法就是采用并行直接路径加载。

如果不带任何输入地从命令行执行SQLLDR，它会提供以下帮助：

[oracle@jy ~]$ sqlldr

SQL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 11:32:22 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Usage: SQLLDR keyword=value [,keyword=value,...]

Valid Keywords:

userid -- ORACLE username/password
control -- control file name
log -- log file name
bad -- bad file name
data -- data file name
discard -- discard file name
discardmax -- number of discards to allow (Default all)
skip -- number of logical records to skip (Default 0)
load -- number of logical records to load (Default all)
errors -- number of errors to allow (Default 50)
rows -- number of rows in conventional path bind array or between direct path data saves
(Default: Conventional path 64, Direct path all)
bindsize -- size of conventional path bind array in bytes (Default 256000)
silent -- suppress messages during run (header,feedback,errors,discards,partitions)
direct -- use direct path (Default FALSE)
parfile -- parameter file: name of file that contains parameter specifications
parallel -- do parallel load (Default FALSE)
file -- file to allocate extents from
skip_unusable_indexes -- disallow/allow unusable indexes or index partitions (Default FALSE)
skip_index_maintenance -- do not maintain indexes, mark affected indexes as unusable (Default FALSE)
commit_discontinued -- commit loaded rows when load is discontinued (Default FALSE)
readsize -- size of read buffer (Default 1048576)
external_table -- use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE (Default NOT_USED)
columnarrayrows -- number of rows for direct path column array (Default 5000)
streamsize -- size of direct path stream buffer in bytes (Default 256000)
multithreading -- use multithreading in direct path
resumable -- enable or disable resumable for current session (Default FALSE)
resumable_name -- text string to help identify resumable statement
resumable_timeout -- wait time (in seconds) for RESUMABLE (Default 7200)
date_cache -- size (in entries) of date conversion cache (Default 1000)

PLEASE NOTE: Command-line parameters may be specified either by
position or by keywords. An example of the former case is 'sqlldr
scott/tiger foo'; an example of the latter is 'sqlldr control=foo
userid=scott/tiger'. One may specify parameters by position before
but not after parameters specified by keywords. For example,
'sqlldr scott/tiger control=foo logfile=log' is allowed, but
'sqlldr scott/tiger control=foo log' is not, even though the
position of the parameter 'log' is correct.

要使用SQLLDR,需要有一个控制文件(control file).控制文件中包含描述输入数据的信息(如输入数据的布局、数据类型等),另外还包含有关目标表的信息.控制文件甚至还可以包含要加载的数据.在下面的例子中,我们将一步一步地建立一个简单的控制文件，并对这些命令提供必须的解释
(1) LOAD DATA
(2) INFILE *
(3) INTO TABLE DEPT
(4) FIELDS TERMINATED BY ‘,’
(5) (DEPTNO, DNAME, LOC )
(6) BEGINDATA
(7) 10,Sales,Virginia
(8) 20,Accounting,Virginia
(9) 30,Consulting,Virginia
(10) 40,Finance,Virginia

[oracle@jy ~]$ vi demo1.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
FIELDS TERMINATED BY ','
(DEPTNO, DNAME, LOC )
BEGINDATA
10,Sales,Virginia
20,Accounting,Virginia
30,Consulting,Virginia
40,Finance,Virginia

LOAD DATA:这会告诉SQLLDR要做什么(在这个例子中,则指示要加载数据).SQLLDR还可以执行CONTINUE_LOAD,也就是继续加载.只有在继续一个多表直接路径加载时才能使用后面这个选项.

INFILE *:这会告诉SQLLDR所要加载的数据实际上包含在控制文件本身上,如第6～10行所示.也可以指定包含数据的另一个文件的文件名.如果愿意,可以使用一个命令行参数覆盖这个INFILE语句.要注意的是命令行选项总会涵盖控制文件设置.

INTO TABLE DEPT:这会告诉SQLLDR要把数据加载到哪个表中(在这个例子中,数据要加载到DEPT表中).

FIELDS TERMINATED BY ‘,’:这会告诉SQLLDR数据的形式应该是用逗号分隔的值.为SQLLDR描述输入数据的方式有数十种;这只是其中较为常用的方法之一.

(DEPTNO, DNAME, LOC):这会告诉SQLLDR所要加载的列,这些列在输入数据中的顺序以及数据类型.这是指输入流中数据的数据类型,而不是数据库中的数据类型.在这个例子中,列的数据类型默认为CHAR(255),这已经足够了.

BEGINDATA:这会告诉SQLLDR你已经完成对输入数据的描述,后面的行(第7～10行)是要加载到DEPT表的具体数据.

这个控制文件采用了最简单,最常用的格式之一:将定界数据加载到一个表.要使用这个控制文件(名为demo1.ctl),只需创建一个空的DEPT表：

sys@JINGYONG> create table dept
2 (deptno number(2) constraint dept_pk primary key,
3 dname varchar2(14),
4 loc varchar2(14)
5 );

表已创建。

并运行以下命令：

[oracle@jy ~]$ sqlldr userid=jy/jy control=demo1.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 11:43:20 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 5

如果表非空，就会收到一个错误消息：

SQLLDR-601: For INSERT option, table must be empty. Error on table DEPT

这是因为,这个控制文件中几乎所有选项都取默认值,而默认的加载选项是INSERT(而不是APPEND,TRUNCATE或REPLACE).要执行INSERT,SQLLDR就认为表为空.如果想向DEPT表中增加记录,可以指定加载选项为APPEND;或者,为了替换DEPT表中的数据,可以使用REPLACE或TRUNCATE.REPLACE使用一种传统DELETE语句;因此,如果要加载的表中已经包含许多记录,这个操作可能执行得很慢.TRUNCATE则不同,它使用TRUNCATE SQL命令,通常会更快地执行,因为它不必物理地删除每一行.

每个加载都会生成一个日志文件,以上这个简单加载的日志文件如下：

[oracle@jy ~]$ cat demo1.log

SQL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 11:43:20 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Control File: demo1.ctl
Data File: demo1.ctl
Bad File: demo1.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional

Table DEPT, loaded from every logical record.
Insert option in effect for this table: INSERT

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DEPTNO FIRST * , CHARACTER
DNAME NEXT * , CHARACTER
LOC NEXT * , CHARACTER

Record 5: Rejected - Error on table DEPT, column DEPTNO.
Column not found before end of logical record (use TRAILING NULLCOLS)

Table DEPT:
4 Rows successfully loaded.
1 Row not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

Space allocated for bind array: 49536 bytes(64 rows)
Read buffer bytes: 1048576

Total logical records skipped: 0
Total logical records read: 5
Total logical records rejected: 1
Total logical records discarded: 0

Run began on Thu Oct 03 11:43:20 2013
Run ended on Thu Oct 03 11:43:21 2013

Elapsed time was: 00:00:00.38
CPU time was: 00:00:00.09

日志文件会告诉我们关于加载的很多方面,从中可以看到我们所用的选项(默认或默认选项);可以看到读取了多少记录,加载 了多少记录等.日志文件指定了所有BAD文件和DISCARD文件的位置,甚至还会告诉我们加载用了多长时间.每个日志文件对于验 证加载是否成功至关重要,另外对于诊断错误也很有意义.如果所加载的数据导致SQL错误(也就是说,输入数据是”坏的”,并在BAD文件中建立了记录),这些错误就会记录在这个日志文件中.

如何加载定界数据
定界数据(delimited data)即用某个特定字符分隔的数据,可以用引号括起,这是当前平面文件最常见的数据格式.在大型机 上,定长,固定格式的文件可能是最可识别的文件格式,但是在UNIX和NT上,定界文件才是”标准”.

对于定界数据,最常用的格式是逗号分隔值(comma-separated values，CSV)格式.采用这种文件格式,数据中的每个字段与 下一个字段用一个逗号分隔.文本串可以用引号括起,这样就允许串本身包含逗号.如果串还必须包含引号,一般约定是使用两个引号(在下面的代码中,将使用””而不是”).要加载定界数据,相应的典型控制文件与前面第一个例子很相似,但是FIELDS TERMINATED BY 子句通常如下指定:

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

它指定用逗号分隔数据字段,每个字段可以用双引号括起,如果我们把这个控制文件的最后部分修改如下:

[oracle@jy ~]$ cat demo2.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(DEPTNO, DNAME, LOC )
BEGINDATA
10,Sales,"Virginia,USA"
20,Accounting,"Va,""USA"""
30,Consulting,Virginia
40,Finance,Virginia

使用这个控制文件运行SQLLDR时，结果如下：

[oracle@jy ~]$ sqlldr userid=jy/jy control=demo2.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 12:01:03 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 5

使用这个控制文件运行SQLLDR时，结果如下：

jy@JINGYONG> select * from dept;

DEPTNO DNAME LOC
---------- -------------- --------------
10 Sales Virginia,USA
20 Accounting Va,"USA"
30 Consulting Virginia
40 Finance Virginia

要注意以下几点：
部门10中的Virginia.USA:这是因为输入数据是”Virginia,USA”.输入数据字段必须包括在引号里才能保留数据中的逗号.否 则,数据中的这个逗号会被认为是字段结束标记,这样就会只加载Virginia,而没有USA文本.

Va,”USA”:这是因为输入数据是”Va,””USA”””.对于引号括起的串,SQLLDR会把其中”的两次出现计为一次出现.要加载一个包含可选包围字符(enclosure character)的串,必须保证这个包围字符出现两次.

另一种常用的格式是制表符定界数据(tag-delimited data),这是用制表符分隔而不是逗号分割的数据.有两种方法使用 TERMINATED BY子句来加载这种数据：
TERMINATED BY X’09′(使用十六进制格式的制表符;采用ASCII时,制表符为9)
TERMINATED BY WHITESPACE
这两种方法在实现上有很大差异,下面将会说明.还是用前面的DEPT表,我们将使用以下控制文件加载这个表:

[oracle@jy ~]$ vi demo3.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
FIELDS TERMINATED BY WHITESPACE
(DEPTNO, DNAME, LOC )
BEGINDATA
10 Sales Virginia

从字面上不太容易看得出来,不过要知道,在这里各部分数据之间都有两个制表符.这里的数据行实际上是:
10\t\tSales\t\tVirginia
在此\t是普通可识别的制表符转义字符.使用这个控制文件时(包含如前所示的TERMINATED BY WHITESPACE),表DEPT中的数据将是:

jy@JINGYONG> select * from dept;

DEPTNO DNAME LOC
---------- -------------- --------------
10 Sales Virginia

TERMINATED BY WHITESPACE会解析这个串,查找空白符(制表符,空格和换行符)的第一次出现,然后继续查找,直至找到下一 个非空白符.

另一方面,如果要使用FIELDS TERMINATED BY X’09’,如以下控制文件所示,这里稍做修改:

[oracle@jy ~]$ cat demo4.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY "X'09'"
TRAILING NULLCOLS
(DEPTNO,DNAME,LOC )
BEGINDATA
10 sales virginia

[oracle@jy ~]$ sqlldr userid=jy/jy control=demo4.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Fri Oct 4 08:07:43 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 1

加载这样的定界数据时,很可能想逃过输入记录中的某些列.例如,你可能加载字段1,3和5,而跳过第2列和第4列.为此SQLLDR提供了FILLER关键字.这允许你映射一个输入记录中的一列,但不把它放在数据库中.例如,给定DEPT表以及先前的一个控制文件,可以修改这个控制文件,使用FILLER关键字正确地加载数据(跳过制表符):

[oracle@jy ~]$ vi demo3.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
FIELDS TERMINATED BY WHITESPACE
(DEPTNO, dummy1 filler, DNAME, dummy2 filler, LOC)
BEGINDATA
10 Sales Virginia

所得到的表DEPT现在如下所示：

jy@JINGYONG> select * from dept;

DEPTNO DNAME LOC
---------- -------------- --------------
10 Sales Virginia

如何加载固定格式数据
通常会有一个有某个外部系统生成的平面文件,而且这是一个定长文件,其中包含着固定位置的数据(positional data).例 如,NAME字段位于第1～10字节,ADDRESS字段位于地11～35字节等.

这种定宽的固定位置数据是最适合SQLLDR加载的数据格式.要加载这种数据,使用SQLLDR是最快的处理方法,因为解析输入数据流相当容易.SQLLDR会在数据记录中存储固定字节的偏移量和长度,因此抽取某个给定字段相当简单.如果要加载大量数据，将其转换为一种固定位置格式通常是最好的办法.当然,定宽文件也有一个缺点,它比简单的定界文件格式可能要大得多.

要加载定宽的固定位置数据,将会在控制文件中使用POSITION关键字,例如:

[oracle@jy ~]$ vi demo5.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
(DEPTNO position(1:2),DNAME position(3:16),LOC position(17:30) )
BEGINDATA
10Accounting Virginia,USA

QL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 12:21:04 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 2

jy@JINGYONG> select * from dept;

DEPTNO DNAME LOC
---------- -------------- --------------
10 Accounting Vir ginia,USA

这个控制文件没有使用FIELDS TERMINATED BY子句;而是使用了POSITION来告诉SQLLDR字段从哪里开始,到哪里结束.关于 POSITION子句有意思的是,我们可以使用重叠的位置,可以在记录中来回反复.例如,如果如下修改DEPT表:

jy@JINGYONG> alter table dept add entire_line varchar2(30);

表已更改。

并使用以下控制文件:

[oracle@jy ~]$ vi demo6.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
(DEPTNO position(1:2),
DNAME position(3:16),
LOC position(17:30),
ENTIRE_LINE position(1:30)
)
BEGINDATA
10Accounting Virginia,USA

字段ENTIRE_LINE定义的POSITION(1:30).这会从所有30字节的输入数据中抽取出这个字段的数据,而其他字段都是输入数据的子串.这个控制文件的输出如下:

jy@JINGYONG> select * from dept;

DEPTNO DNAME LOC ENTIRE_LINE
---------- -------------- -------------- -----------------------------
10 Accounting Vir ginia,USA 10Accounting Virginia,USA

使用POSITION时,可以使用相对偏移量,也可以使用绝对偏移量.在前面的例子中使用了绝对偏移量,我们明确地指示了字段从 哪里开始,到哪里结束.也可以把前面的控制文件写作:

[oracle@jy ~]$ vi demo7.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
(DEPTNO position(1:2),
DNAME position(*:16),
LOC position(*:30),
ENTIRE_LINE position(1:30)
)
BEGINDATA
10Accounting Virginia,USA

[oracle@jy ~]$ sqlldr userid=jy/jy control=demo7.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 12:25:53 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 2

jy@JINGYONG> select * from dept;

DEPTNO DNAME LOC ENTIRE_LINE
---------- -------------- -------------- -----------------------------
10 Accounting Vir ginia,USA 10Accounting Virginia,USA

*指示控制文件得出上一个字段在哪里结束.因此,在这种情况下,(*:16)与(3:16)是一样的.注意,控制文件中可以混合使用相对位置和绝对位置.另外,使用*表示法时,可以把它与偏移量相加.例如,如果DNAME从DEPTNO结束之后的2个字节处开始,可以使用(*+2:16).在这个例子中,其作用就相当于使用(5:16).

POSITION子句中的结束位置必须是数据结束的绝对列位置.有时,可能指定每个字段的长度更为容易,特别是如果这些字段是连 续的(就像前面的例子一样).采用这种方式,只需告诉SQLLDR:记录从第1个字节开始,然后指定每个字段的长度就行了.这样我们就可以免于计算记录中的开始和结束偏移量,这个计算有时可能很困难.为此,可以不指定结束位置,而是指定定长记录中各个字段的长度,如下:

[oracle@jy ~]$ vi demo8.ctl
REPLACE
LOAD DATA
INFILE *
INTO TABLE DEPT
(DEPTNO position(1) char(2),
DNAME position(*) char(14),
LOC position(*) char(14),
ENTIRE_LINE position(1:30)
)
BEGINDATA
10Accounting Virginia,USA

[oracle@jy ~]$ sqlldr userid=jy/jy control=demo8.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 12:29:01 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 2

jy@JINGYONG> select * from dept;

DEPTNO DNAME LOC ENTIRE_LINE
---------- -------------- -------------- -----------------------------
10 Accounting Vir ginia,USA 10Accounting Virginia,USA

在此只需告诉SQLLDR第一个字段从哪里开始及其长度.后面的每个字段都从上一个字段结束处开始,并具有指定的长度.直至最 后一个字段才需要再次指定位置,因为这个字段又要从记录起始处开始.

如何加载日期
使用SQLLDR加载日期相当简单,但是看起来这个方面经常导致混淆.你只需在控制文件中使用DATE数据类型,并指定要使用的日 期格式,这个日期格式与数据库中TO_CHAR和TO_DATE中使用的日期格式是一样的.SQLLDR会向数据应用这个日期格式,并为你完成加载.

例如,如果再把DEPT表修改如下:

jy@JINGYONG> alter table dept add last_updated date;

表已更改。

可以用以下控制文件加载它：

[oracle@jy ~]$ vi demo9.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
(DEPTNO,
DNAME,
LOC,
ENTIRE_LINE,
LAST_UPDATED date 'yyyy-mm-dd'
)
BEGINDATA
10,Sales,Virginia,USA,2000-05-01
20,Accounting,Virginia,USA,1999-06-21
30,Consulting,Virginia,USA,2000-01-05
40,Finance,Virginia,USA,2001-03-15

[oracle@jy ~]$ sqlldr userid=jy/jy control=demo9.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 12:47:17 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 5

jy@JINGYONG> select * from dept;

DEPTNO DNAME LOC ENTIRE_LINE LAST_UPDATED
---------- -------------- -------------- ----------------------------- --------------
10 Sales Virginia USA 01-5月 -00
20 Accounting Virginia USA 21-6月 -99
30 Consulting Virginia USA 05-1月 -00
40 Finance Virginia USA 15-3月 -01

就这么简单,只需在控制文件中应用格式,SQLLDR就会为我们完成日期转换.在某些情况下可能使用一个更强大的SQL函数更为合适.例如,如果你的输入文件包含多种不同格式的日期:有些有时间分量,有些没有;有些采用DD-MON-YYYY格式;有些格式为DD/MM/YYYY等等.

如果使用函数加载数据
在SQLLDR中使用函数很容易.要在SQLLDR脚本中向某个字段应用一个函数,只需将这个函数增加到控制文件中(用两个引号括起).例如,假设有前面的DEPT表,你想确保所加载的数据都是大写的.可以使用以下控制文件来加载:

[oracle@jy ~]$ vi demo10.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
ENTIRE_LINE "upper(:entire_line)",
LAST_UPDATED date 'yyyy-mm-dd'
)
BEGINDATA
10,Sales,Virginia,USA,2000-05-01
20,Accounting,Virginia,USA,1999-06-21
30,Consulting,Virginia,USA,2000-01-05
40,Finance,Virginia,USA,2001-03-15

"demo10.ctl" 17L, 342C written
[oracle@jy ~]$ sqlldr userid=jy/jy control=demo10.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 12:50:46 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 5

jy@JINGYONG> select * from dept;

DEPTNO DNAME LOC ENTIRE_LINE LAST_UPDATED
---------- -------------- -------------- ----------------------------- --------------
10 SALES VIRGINIA USA 01-5月 -00
20 ACCOUNTING VIRGINIA USA 21-6月 -99
30 CONSULTING VIRGINIA USA 05-1月 -00
40 FINANCE VIRGINIA USA 15-3月 -01

可以注意到,只需向一个绑定变量应用UPPER函数就可以很容易地将数据变为大写.要注意SQL函数可以引用任何列,而不论将 函数实际上应用于哪个列.这说明一个列可以是对两个或更多其他列应用一个函数的结果.例如,如果你想加载ENTIRE_LINE 列,可以使用SQL连接运算符.不过这种情况下这样做稍有些麻烦.现在,输入数据集中有4个数据元素.如果只是向控制文件中加入如下字符ENTIRE_LINE：

[oracle@jy ~]$ vi demo11.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED date 'yyyy-mm-dd',
ENTIRE_LINE ":deptno||:dname||:loc||:last_updated"

)
BEGINDATA
10,Sales,Virginia,USA,2000-05-01
20,Accounting,Virginia,USA,1999-06-21
30,Consulting,Virginia,USA,2000-01-05
40,Finance,Virginia,USA,2001-03-15

~
~
~
~
~
"demo11.ctl" 18L, 360C written
[oracle@jy ~]$ sqlldr userid=jy/jy control=demo11.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 12:55:32 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 5

在demo11.log日志文件中可以看到:

Record 5: Rejected - Error on table DEPT, column DEPTNO.
Column not found before end of logical record (use TRAILING NULLCOLS)

在此,SQLLDR告诉你,没等处理完所有列,记录中就没有数据了.这种情况下,解决方案很简单.实际上,SQLLDR甚至已经告诉了我们该怎么做:这就是使用TRAILING NULLCOLS.这样一来,如果输入记录中不存在某一列的数据,SQLLDR就会为该列绑定一个NULL值.在这种情况下,增加TRAILING NULLCOLS会导致绑定变量:ENTIRE_LINE成为NULL,所以再尝试这个控制文件：

[oracle@jy ~]$ vi demo11.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
ENTIRE_LINE ":deptno||:dname||:loc||:last_updated",
LAST_UPDATED date 'yyyy-mm-dd'
)
BEGINDATA
10,Sales,Virginia,USA,2000-05-01
20,Accounting,Virginia,USA,1999-06-21
30,Consulting,Virginia,USA,2000-01-05
40,Finance,Virginia,USA,2001-03-15

[oracle@jy ~]$ sqlldr userid=jy/jy control=demo11.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 13:00:49 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 5

jy@JINGYONG> select * from dept;

DEPTNO DNAME LOC ENTIRE_LINE LAST_UPDATED
---------- -------------- -------------- ------------------------------ --------------
10 SALES VIRGINIA 10SalesVirginia2000-05-01 01-5月 -00
20 ACCOUNTING VIRGINIA 20AccountingVirginia1999-06-21 21-6月 -99
30 CONSULTING VIRGINIA 30ConsultingVirginia2000-01-05 05-1月 -00
40 FINANCE VIRGINIA 40FinanceVirginia2001-03-15 15-3月 -01

之所以可以这样做,原因在于SQLLDR构建其INSERT语句的做法.SQLLDR会查看前面的控制文件,并看到控制文件中的DEPTNO, DNAME,LOC,LAST_UPDATED和ENTIRE_LINE这几列.它会根据这些列建立5个绑定变量.通常,如果没有任何函数,所建立的 INSERT语句就是:

INSERT INTO DEPT ( DEPTNO, DNAME, LOC, LAST_UPDATED, ENTIRE_LINE )

VALUES (&DEPTNO,&DNAME,&LOC,&LAST_UPDATED,&ENTIRE_LINE);

然后再解析输入流,将值赋给相应的绑定变量,然后执行语句.如果使用函数,SQLLDR会把这些函数结合到INSERT语句中.在上 一个例子中,SQLLDR建立的INSERT语句如下所示：

INSERT INTO T (DEPTNO, DNAME, LOC, LAST_UPDATED, ENTIRE_LINE)
VALUES ( &DEPTNO, upper(&dname), upper(&loc), &last_updated,
&deptno||&dname||&loc||&last_updated );

然后再做好准备,把输入绑定到这个语句,再执行语句.所以SQL中能做的事情都可以结合到SQLLDR脚本中.由于SQL中增加了 CASE语句,所以这样做不仅功能极为强大,而且相当容易.例如,假设你的输入文件有以下格式的日期:
HH24:MI:SS:只有一个时间;日期默认为SYSDATE.
DD/MM/YYYY:只有一个日期;时间默认为午夜0点.
HH24:MI:SS DD/MM/YYYY:日期和时间都要显式提供。
可以使用如下的一个控制文件:

[oracle@jy ~]$ vi demo12.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
ENTIRE_LINE ":deptno||:dname||:loc||:last_updated",
LAST_UPDATED
"case
when length(:last_updated)>9
then to_date(:last_updated,'yyyy-mm-dd hh24:mi:ss')
when instr(:last_updated,':')>0
then to_date(:last_updated,'hh24:mi:ss')
else to_date(:last_updated,'yyyy-mm-dd')
end"
)
BEGINDATA
10,Sales,Virginia,USA,2000-05-01 12:03:03
20,Accounting,Virginia,USA,02:23:54
30,Consulting,Virginia,USA,2000-01-05 01:24:00
40,Finance,Virginia,USA,2001-03-15

[oracle@jy ~]$ sqlldr userid=jy/jy control=demo12.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 13:06:49 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 5

jy@JINGYONG> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

会话已更改。

jy@JINGYONG> select * from dept;

DEPTNO DNAME LOC ENTIRE_LINE LAST_UPDATED
---------- -------------- -------------- --------------------------------------- ---------------------
10 SALES VIRGINIA 10SalesVirginia2000-05-01 12:03:03 2000-05-01 12:03:03
20 ACCOUNTING VIRGINIA 20AccountingVirginia02:23:54 2013-10-01 02:23:54
30 CONSULTING VIRGINIA 30ConsultingVirginia2000-01-05 01:24:00 2000-01-05 01:24:00
40 FINANCE VIRGINIA 40FinanceVirginia2001-03-15 2001-03-15 00:00:00

如何加载有内嵌换行符的数据
过去,如果要加载可能包含换行符的自由格式的数据,这对于SQLLDR来说很成问题.换行符是SQLLDR的默认行结束符,要加载有内嵌换行符的数据有以下方法:
加载数据,其中用非换行符的其他字符来表示换行符(例如,在文本中应该出现换行符的位置上放上串\n),并在加载时使用一个SQL函数用一个CHR(10)替换该文本.

在INFILE指令上使用FIX属性,加载一个定长平面文件.

在INFILE指令上使用VAR属性,加载一个定宽文件,在该文件使用的格式中,每一行的前几个字节指定了这一行的长度(字节数 ).

在INFILE指令上使用STR属性,加载一个变宽文件,其中用某个字符序列来表示行结束符,而不是用换行符来表示.

使用一个非换行符的字符
如果你能对如何生成输入数据加以控制,这就是一种很容易的方法.如果创建数据文件时能很容易地转换数据,这种方法就能奏 效.其思想是,就数据加载到数据库时对数据应用一个SQL函数,用某个字符串来替换换行符.下面向DEPT表再增加另一个列:

jy@JINGYONG> alter table dept add comments varchar2(4000);

表已更改。

将使用这一列来加载文本,下面是一个有内联数据的示例控制文件:

[oracle@jy ~]$vi demo13.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
ENTIRE_LINE ":deptno||:dname||:loc||:last_updated",
LAST_UPDATED
"case
when length(:last_updated)>9
then to_date(:last_updated,'yyyy-mm-dd hh24:mi:ss')
when instr(:last_updated,':')>0
then to_date(:last_updated,'hh24:mi:ss')
else to_date(:last_updated,'yyyy-mm-dd')
end",
COMMENTS "replace(:comments,'\\n',chr(10))"
)
BEGINDATA
10,Sales,Virginia,USA,2000-05-01 12:03:03,This is the Sales\nOffice in Virginia
20,Accounting,Virginia,USA,02:23:54,This is the Accountin\nOffice in Virginia
30,Consulting,Virginia,USA,2000-01-05 01:24:00,This is the Consulting\nOffice in Virginia
40,Finance,Virginia,USA,2001-03-15,This is the Finance\nOffice in Virginia

[oracle@jy ~]$ sqlldr userid=jy/jy control=demo13.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 14:45:48 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 5

注意,调用中必须使用\\n来替换换行符,而不只是\n.这是因为\n会被SQLLDR识别为一个换行符,而且SQLLDR会把它转换为一 个换行符,而不是一个两字符的串.利用以上控制文件执行SQLLDR时,DEPT表中将加载以下数据:

jy@JINGYONG> select * from dept;

DEPTNO DNAME LOC ENTIRE_LINE LAST_UPDATED COMMENTS
--------------------------------------------------------------------------------------------------------------- -------------------------------------------------
10 SALES VIRGINIA 10SalesVirginia2000-05-01 12:03:03 2000-05-01 12:03:03 This is the Sales Office in Virginia

20 ACCOUNTING VIRGINIA 20AccountingVirginia02:23:54 2013-10-01 02:23:5 This is the Accountin Office in Virginia

30 CONSULTING VIRGINIA 30ConsultingVirginia2000-01-05 01:24:00 2000-01-05 01:24:00 This is the Consulting Office in Virginia

40 FINANCE VIRGINIA 40FinanceVirginia2001-03-15 2001-03-15 00:00:00 This is the Finance Office in Virginia

使用IFX属性
另一种可用的方法是使用FIX属性.如果使用这种方法,输入数据必须出现在定长记录中.每个记录与输入数据集中所有其他记 录的长度都相同,即有相同的字节数.对于固定位置的数据,使用FIX属性就特别适合.这些文件通常是定长输入文件.使用自由格式的定界数据时,则不太可能是一个定长文件,因为这些文件通常是变长的(这正是定界文件的关键:每一行不会不必要地过长).
使用FIX属性时,必须使用一个INFILE子句,因为FIX属性是INFILE的一个选项.另外,如果使用这个选项,数据必须在外部存储 ,而并非存储在控制文件本身.因此,假设有定长的输入记录,可以使用如下的一个控制文件:

[oracle@jy ~]$ vi demo14.ctl
LOAD DATA
INFILE demo14.dat "fix 80"
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
ENTIRE_LINE ":deptno||:dname||:loc||:last_updated",
LAST_UPDATED
"case
when length(:last_updated)>9
then to_date(:last_updated,'yyyy-mm-dd hh24:mi:ss')
when instr(:last_updated,':')>0
then to_date(:last_updated,'hh24:mi:ss')
else to_date(:last_updated,'yyyy-mm-dd')
end",
COMMENTS
)

[oracle@jy ~]$ vi demo14.dat
10,Sales,Virginia,USA,2000-05-01 12:03:03,This is the Sales\nOffice in Virginia
20,Accounting,Virginia,USA,02:23:54,This is the Accountin\nOffice in Virginia\n
30,Consulting,Virginia,USA,2000-01-05 01:24:00,This is the Consulting\nOffice\n
40,Finance,Virginia,USA,2001-03-15,This is the Finance\nOffice in Virginia \n

这个文件指定了一个输入数据文件(domo14.dat),这个文件中每个记录有80字节,这包括尾部的换行符(每个记录最后可能有换行符,也可能没有).在这种情况下,输入数据文件中的换行符并不是特殊字符.这只是要加载(或不加载)的另一个字符而已.要知道:记录的最后如果有换行符,它会成为这个记录的一部分.为了充分理解这一点,我们需要一个实用程序将文件的内容转储在屏幕上,以便我们看到文件中到底有什么.使用Linux(或任何Unix版本)利用od就很容易做到,这个程序可以将文件以八进制(和其他格式)转储到屏幕上.我们将使用下面的demo.dat文件.注意以下输入中的第一列实际上是八进制,所以第2行上的数字0000012是一个八进制数,不是十进制数10.由此我们可以知道所查看的文件中有哪些字节.我对这个输出进行了格式化,使得每行显示10个字符(使用-w10),所以0,12,24和36实际上就是0,10,20和30.

[oracle@jy ~]$ od -c -w10 -v demo14.dat
0000000 a l e s , V i r g i
0000012 n i a , U S A , 2 0
0000024 0 0 - 0 5 - 0 1 1
0000036 2 : 0 3 : 0 3 , T h
0000050 i s i s t h e
0000062 S a l e s \ n O f f
0000074 i c e i n V i r
0000106 g i n i a \n 2 0 , A
0000120 c c o u n t i n g ,
0000132 V i r g i n i a , U
0000144 S A , 0 2 : 2 3 : 5
0000156 4 , T h i s i s
0000170 t h e A c c o u n
0000202 t i n \ n O f f i c
0000214 e i n V i r g i
0000226 n i a \n 3 0 , C o n
0000240 s u l t i n g , V i
0000252 r g i n i a , U S A
0000264 , 2 0 0 0 - 0 1 - 0
0000276 5 0 1 : 2 4 : 0 0
0000310 , T h i s i s t
0000322 h e C o n s u l t
0000334 i n g \ n O f f i c
0000346 e i n V i r g i
0000360 n i a \n 4 0 , F i n
0000372 a n c e , V i r g i
0000404 n i a , U S A , 2 0
0000416 0 1 - 0 3 - 1 5 , T
0000430 h i s i s t h e
0000442 F i n a n c e \ n
0000454 O f f i c e i n
0000466 V i r g i n i a \n
0000477

注意,在这个输入文件中,并没有用换行符(\n)来指示SQLLDRE记录在哪里结束;这里的换行符只是要加载的数据而已.SQLLDR使用FIX宽度(80字节)来得出要读取多少数据.实际上,如果查看输入数据,可以看到,输入文件中提供给SQLLDR的记录甚至并非以\n结束.部门20的记录之前的字符是一个空格,而不是换行符.

[oracle@jy ~]$ sqlldr userid=jy/jy control=demo14.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 15:03:05 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 4

我们知道了每个记录的长度为80字节,现在就可以用前面有FIX80子句的控制文件来加载这些数据了.完成加载后,可以看到以 下结果:

jy@JINGYONG> select * from dept;

DEPTNO DNAME LOC ENTIRE_LINE LAST_UPDATED COMMENTS
--------------------------------------------------------------------------------------------------------------- -------------------
10 SALES VIRGINIA10SalesVirginia2000-05-01 12:03:03 2000-05-01 12:03:03 This is the Sales\nOffice in Virginia

20 ACCOUNTING VIRGINIA20AccountingVirginia02:23:54 2013-10-01 02:23:54 This is the Accountin\nOffice in Virginia\n

30 CONSULTING VIRGINIA30ConsultingVirginia2000-01-05 01:24:00 2000-01-05 01:24:00 This is the Consulting\nOffice\n

40 FINANCE VIRGINIA40FinanceVirginia2001-03-15 2001-03-15 00:00:00 This is the Finance\nOffice in Virginia \n

你可能需要截断这个数据,因为尾部的空白符会保留.可以在控制文件中使用TRIM内置SQL函数来完成截断.
Linux上 ,行结束标记就是\n(SQL中的CHR(10)).在Windows NT上,行结束标记却是\r\n(SQL中的CHR(13)||CHR(10)).一般来讲,如果使用FIX方法,就要确保是在同构平台上创建和加载文件(Linux上创建,Linux上加载;或者Windows上创建,Windows上加载)

使用VAR属性
要加载有内嵌换行符的数据,另一种方法是使用VAR属性.使用这种格式时,每个记录必须以某个固定的字节数开始,这表示这 个记录的总长度.通过使用这种格式,可以加载包含内嵌换行符的变长记录,但是每个记录的开始处必须有一个记录长度字段. 因此,如果使用如下的一个控制文件:

[oracle@jy ~]$ vi demo15.ctl
LOAD DATA
INFILE 'demo15.dat' "var 3"
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
ENTIRE_LINE ":deptno||:dname||:loc||:last_updated",
LAST_UPDATED
"case
when length(:last_updated)>9
then to_date(:last_updated,'yyyy-mm-dd hh24:mi:ss')
when instr(:last_updated,':')>0
then to_date(:last_updated,'hh24:mi:ss')
else to_date(:last_updated,'yyyy-mm-dd')
end",
COMMENTS
)

VAR 3指出每个输入记录的前3个字节是输入记录的长度,如果取以下数据文件：

[oracle@jy ~]$ cat demo15.dat
07910,Sales,Virginia,USA,2000-05-01 12:03:03,This is the Sales Office in Virginia
07820,Accounting,Virginia,USA,02:23:54,This is the Accounting Office in Virginia
08930,Consulting,Virginia,USA,2000-01-05 01:24:00,This is the Consulting Office in Virginia
07440,Finance,Virginia,USA,2001-01-15,This is the Finance Office in Virginia

可以使用该控制文件来加载.在输入数据文件中有4行数据.第一行从079开始,这说明接下来79字节是第一个输入记录.这79字 节包括单词Virginia后的结束换行符.下一行从078开始,这一行有78字节的文本,依此类推.使用这种格式数据文件,可以很 容易地加载有内嵌换行符的数据.
同样,如果在使用Linux和Windows(前面的例子都在Linux上完成,其中换行符只是一个字符长),就必须调整每个记录的长度字 段.在Windows上,前例.dat文件中的长度字段应该是80,79,90和75.

使用STR属性 要加载有内嵌换行符的数据,这可能是最灵活的一种方法.通过使用STR属性,可以指定一个新的行结束符(或字符序列).就能创建一个输入数据文件,其中每一行的最后有某个特殊字符,换行符不再有特殊含义. 我更喜欢使用字符序列,通常会使用某个特殊标记,然后再加一个换行符.这样,在一个文本编辑器或某个实用程序中查看输入 数据时,就能很容易地看到行结束符,因为每个记录的最后仍然有一个换行符.STR属性以十六进制指定,要得到所需的具体十 六进制串,最容易的方法是使用SQL和UTL_RAW来生成十六进制串.例如,假设使用的是Linux平台,行结束标记是CHR(10)(换行 ),我们的特殊标记字符是一个管道符号(|),则可以写为:

jy@JINGYONG>select utl_raw.cast_to_raw('|'||chr(10)) from dual;

UTL_RAW.CAST_TO_RAW('|'||CHR(1
--------------------------------------------------------------------------------
7C0A

由此可知,在Linux上我们需要使用的STR是X’7C0A’.
在Windows上,要使用UTL_RAW.CAST_TO_RAW(‘|’||chr(13)||chr(10))
为了使用这个方法,要有以下控制文件:

[oracle@jy ~]$ vi demo16.ctl
LOAD DATA
INFILE demo16.dat "str X'7C0A'"
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
ENTIRE_LINE ":deptno||:dname||:loc||:last_updated",
LAST_UPDATED
"case
when length(:last_updated)>9
then to_date(:last_updated,'yyyy-mm-dd hh24:mi:ss')
when instr(:last_updated,':')>0
then to_date(:last_updated,'hh24:mi:ss')
else to_date(:last_updated,'yyyy-mm-dd')
end",
COMMENTS
)

因此,如果输入数据如下:

[oracle@jy ~]$ cat demo16.dat
10,Sales,Virginia,USA,2000-05-01 12:03:03,This is the Sales Office in Virginia|
20,Accounting,Virginia,USA,02:23:54,This is the Accounting Office in Virginia|
30,Consulting,Virginia,USA,2000-01-05 01:24:00,This is the Consulting Office in Virginia|
40,Finance,Virginia,USA,2001-01-15,This is the Finance Office in Virginia|

其中,数据文件中的每个记录都以|\n结束,前面的控制文件就会正确地加载这些数据.

[oracle@jy ~]$ sqlldr userid=jy/jy control=demo16.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Fri Oct 4 07:45:30 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 4

jy@JINGYONG> select * from dept;

DEPTNO DNAME LOC ENTIRE_LINE LAST_UPDATED COMMENTS
--------------------------------------------------------------------------------------------------------------- -----------------
10 SALES VIRGINIA 10SalesVirginia2000-05-01 12:03:03 2000-05-01 12:03:03 This is the Sales Office in Virginia

20 ACCOUNTING VIRGINIA 20AccountingVirginia02:23:54 2013-10-01 02:23:54 This is the Accounting Office in Virginia

30 CONSULTING VIRGINIA 30ConsultingVirginia2000-01-05 01:24:00 2000-01-05 01:24:00 This is the Consulting Office in Virginia

40 FINANCE VIRGINIA 40FinanceVirginia2001-01-15 2001-01-15 00:00:00 This is the Finance Office in Virginia

如果加载LOB
现在来考虑在LOB的一些方法.这不是一个LONG或LONG RAW字段,而是更可取的数据类型BLOB和CLOB.这些数据类型是Oracle 8.0及以后版本中引入的,与遗留的LONG和LONG RAW类型相比,它们支持更丰富的接口/功能集.

将分析两种加载这些字段的方法:SQLLDR和PL/SQL.
通过PL/SQL加载LOB
DBMS_LOB包的入口点为LoadFromFile,LoadBLOBFromFile和LoadCLOBFromFile.通过这些过程,我们可以使用一个BFILE(用于读取操作系统文件)来填充数据库中的BLOB或CLOB.LoadFromFile和LoadBLOBFromFile例程之间没有显著的差别,只不过后者会返回一些OUT参数,指示已经向BLOB列中加载了多少数据.不过,LoadCLOBFromFile例程还提供了一个突出的特性:字符集转换.使用LoadCLOBFromFile时,我们可以告诉数据库:这个文件将以另外某种字符集(不同于数据库正在使用的字符集)来加载,而且要执行必要的字符集转换.例如,可能有一个UTF8兼容的数据库,但是收到的要加载的文件却以WE8ISO8859P1字符集编码,或反之利用这个函数就能成功地加载这些文件.

要使用这些过程,需要在数据库中创建一个DIRECTORY对象.这个对象允许我们创建并打开BFILE(BFILE指向文件系统上数据库 服务器能访问的一个现有文件).DBMS_LOB包完全在服务器中执行.它只能看到服务器能看到的文件系统.特别是,如果你通过 网络访问Oracle,DBMS_LOB包将无法看到你的本地文件系统.

所以,需要先在数据库中创建一个DIRECTORY对象.

jy@JINGYONG> create or replace directory dir1 as '/home/oracle';

目录已创建。

jy@JINGYONG> create or replace directory "dir2" as '/home/oracle';

目录已创建。

SQL> select * from dba_directories;

OWNER DIRECTORY_NAME DIRECTORY_PATH
------ --------------- -----------------
SYS dir2 /home/oracle
SYS DIR1 /home/oracle

我们创建的dir1在数据字典中为大写

下面,将一些数据加载到BLOB或CLOB中:

jy@JINGYONG> create table demo
2 (id int primary key,
3 theClob clob
4 );

表已创建。

[oracle@jy ~]$ echo 'Hello World!'>/home/oracle/demo.txt

jy@JINGYONG> declare
2 l_clob clob;
3 l_bfile bfile;
4 begin
5 insert into demo values(1,empty_clob()) returning theclob into l_clob;
6 l_bfile:=bfilename('DIR1','demo.txt');
7 dbms_lob.fileopen(l_bfile);
8 dbms_lob.loadfromfile(l_clob,l_bfile,dbms_lob.getlength(l_bfile));
9 dbms_lob.fileclose(l_bfile);
10 end;
11 /

PL/SQL 过程已成功完成。

jy@JINGYONG> select dbms_lob.getlength(theClob), theClob from demo;

DBMS_LOB.GETLENGTH(THECLOB) THECLOB
--------------------------- ----------------------------------------------------
13 Hello World!

在第5行我们在表中创建了一行,将CLOB设置为一个EMPTY_CLOB(),并从一个调用获取其值.除了临时LOB外,其余的LOB都住在数据库中,如果没有指向一个临时LOB的指针,或者指向一个已经在数据库中的LOB,将无法写至LOB变量.EMPTY_CLOB()不是一个NULL CLOB;而是指向一个空结构的合法指针(非NULL),它还有一个作用,可以得到一个LOB定位器,指向已锁定行中的数据.如果要选择这个值,而没有锁定底层的行,写数据就会失败,因为LOB在写之前必须锁定(不同于其他结构化数据).通过插入一行,当然我们也就锁定了这一行.如果我们要修改一个现有的行而不是插入新行,则可以使用SELECT FOR UPDATE来获取和锁定这一行.

在第6行上,我们创建了一个BFILE对象.注意,这里DIR1用的是大写,稍后就会看到,这是一个键.这是因为我们向BFILENAME() 传入了一个对象的名称,而不是对象本身.因此,必须确保这个名称与Oracle所存储的对象名称大小写匹配.

第7行打开了LOB,以便读取.

在第8行将操作系统文件/home/oracle/demo.txt的完整内容加载到刚插入的LOB定位器.这里使用DBMS_LOB.GETLENGTH()告诉LOADFROMFILE()例程要加载多少字节的BFILE(这里就是要加载全部字节).
最后,在第9行我们关闭了所打开的BFILE,CLOB已加载.

如果需要在加载文件的同时处理文件的内容,还可以在BFILE上使用DBMS_LOB.READ来读取数据.如果读取的数据实际上是文本,而不是RAW,那么使用UTL_RAW.CAST_TO_VARCHAR2会很方便.然后可以使用DBMS_LOB.WRITE或WRITEAPPEND将数据放入一个CLOB或BLOB.

通过SQLLDR加载LOB数据
现在我们来分析如何通过SQLLDR向LOB加载数据.对此方法不止一种,但是我们主要讨论两种最常用的方法:
数据内联在其他数据中。
数据外联存储(在外部存储),输入数据包含一个文件名,指示该行要加载的数据在哪个文件中.在SQLLDR术语中,这也称为二级数据文件(secondary data file，SDF).
先从内联数据谈起.
加载内联的LOB数据,这些LOB通常内嵌有换行符和其他特殊字符.

下面先来修改dept表,使COMMENTS列是一个CLOB而不是一个大的VARCHAR2字段:

jy@JINGYONG> truncate table dept;

表被截断。

jy@JINGYONG> alter table dept drop column comments;

表已更改。

jy@JINGYONG> alter table dept add comments clob;

表已更改。

例如,假设有一个数据文件(demo17.dat),它有以下内容:

[oracle@jy ~]$ cat demo17.dat
10,Sales,Virginia,USA,2000-05-01 12:03:03,This is the Sales Office in Virginia|
20,Accounting,Virginia,USA,02:23:54,This is the Accounting Office in Virginia|
30,Consulting,Virginia,USA,2000-01-05 01:24:00,This is the Consulting Office in Virginia|
40,Finance,Virginia,USA,2001-01-15,"This is the Finance
Office in Virginia, it has embedded commas and is
much longer than the other comments field. If you
feel the need to add double quoted text in here like
this: ""You will need to double up those quotes!"" to
preserve them in the string. This field keeps going for up to
1000000 bytes (because of the control file definition I used)
or until we hit the magic end of record marker,
the | followed by an end of line - it is right here ->"|

每个记录最后都是一个管道符号(|),后面是行结束标记.部门40的文本比其他部门的文本长得多,有多个换行符,内嵌的引号以及逗号.给定这个数据文件,可以创建一个如下的控制文件:

[oracle@jy ~]$ cat demo17.ctl
LOAD DATA
INFILE demo17.dat "str X'7C0A'"
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
ENTIRE_LINE ":deptno||:dname||:loc||:last_updated",
LAST_UPDATED
"case
when length(:last_updated)>9
then to_date(:last_updated,'yyyy-mm-dd hh24:mi:ss')
when instr(:last_updated,':')>0
then to_date(:last_updated,'hh24:mi:ss')
else to_date(:last_updated,'yyyy-mm-dd')
end",
COMMENTS char(1000000)
)

注意:这个例子在Linux上执行,Linux平台上行结束标记长度为1字节,因此可以使用以上控制文件中的STR设置.在Windows上,STR设置则必须是’7C0D0A’.

要加载这个数据文件,我们在COMMENTS列上指定了CHAR(1000000),因为SQLLDR默认所有人们字段都为CHAR(255).CHAR (1000000)则允许SQLLDR处理多达1,000,000字节的输入文本.可以把这个长度值设置为大于输入文件中任何可能文本块的大小.通过查看所加载的数据,可以看到以下结果:

[oracle@jy ~]$ sqlldr userid=jy/jy control=demo17.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Fri Oct 4 09:08:31 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 1
Commit point reached - logical record count 2
Commit point reached - logical record count 3
Commit point reached - logical record count 4

SQL> set long 999;
SQL> select comments from jy.dept;

COMMENTS
--------------------------------------------------------------------------------
This is the Sales Office in Virginia
This is the Accounting Office in Virginia
This is the Consulting Office in Virginia
This is the Finance
Office in Virginia, it has embedded commas and is
much longer than the other comments field. If you
feel the need to add double quoted text in here like
this: "You will need to double up those quotes!" to
preserve them in the string. This field keeps going for up to
1000000 bytes (because of the control file definition I used)
or until we hit the magic end of record marker,
the | followed by an end of line - it is right here ->

这里可以观察到:原来重复两次的引号不再重复,SQLLDR去除了在此放置的额外的引号.

加载外联的LOB数据,可能要把包含有一些文件名的数据文件加载在LOB中,而不是让LOB数据与结构化数据混在一起,这种情况 很常见.这提供了更大程度的灵活性,因为提供给SQLLDR的数据文件不必使用上述的4种方法之一来避开输入数据中的内嵌换行 符问题,而这种情况在大量的文本或二进制数据中会频繁出现.SQLLDR称这种额外的数据文件为LOBFILE.

SQLLDR还可以支持加载结构化数据文件(指向另外单独一个数据文件).我们可能告诉SQLLDR如何从另外这个文件分析LOB数据 ,这样就可以加载其中的一部分作为结构化数据中的每一行.这种模式的用途很有限.SQLLDR把这种外部引用的文件称为复杂二级数据文件(complex secondary data file).

LOBFILE是一种相对简单的数据文件,旨在简化LOB加载.在LOBFILE中,没有记录的概念,因此换行符不会成为问题,正是这个 性质使得LOBFILE与主要数据文件有所区别.在LOBFILE中,数据总是采用以下某种格式:
定长字段(例如,从LOBFILE加载字节100到1,000)
定界字段(以某个字符结束,或者用某个字符括起)
长度/值对,这是一个变长字段

其中最常见的类型是定界字段,实际上就是以一个文件结束符(EOF)结束.一般来讲,可能有这样一个目录,其中包含你想加载到LOB列中的文件,每个文件都要完整地放在一个BLOB中.此时,就可以使用带TERMINATED BY EOF子句的LOBFILE语句.

假设我们有一个目录,其中包含想要加载到数据库中的文件.我们想加载文件的OWNER,文件的TIME_STAMP,文件的NAME以及文 件本身.要加载数据的表如下所示:

jy@JINGYONG> create table lob_demo
2 (owner varchar2(255),
3 time_stamp date,
4 filename varchar2(255),
5 data blob
6 );

表已创建。

在Linux上使用一个简单的ls –l来捕获输出(或者在Windows上使用dir/q/n),我们就能生成输入文件,并使用如下的一个控制文件加载:

[oracle@jy ~]$ cat demo19.ctl
LOAD DATA
INFILE *
INTO TABLE LOB_DEMO
REPLACE
(owner position(14:28),
time_stamp position(36:46) date "MM DD HH24:MI",
filename position(48:100),
data LOBFILE(filename) TERMINATED BY EOF
)
BEGINDATA
-rw-r--r-- 1 oracle oinstall 18432 10 3 10:42 bifile.bbd
-rw-r--r-- 1 oracle oinstall 1 10 3 12:50 demo10.bad
-rw-r--r-- 1 oracle oinstall 342 10 3 12:50 demo10.ctl
-rw-r--r-- 1 oracle oinstall 1948 10 3 12:50 demo10.log
-rw-r--r-- 1 oracle oinstall 144 10 3 12:59 demo11.bad
-rw-r--r-- 1 oracle oinstall 377 10 3 13:00 demo11.ctl
-rw-r--r-- 1 oracle oinstall 1913 10 3 13:00 demo11.log
-rw-r--r-- 1 oracle oinstall 89 10 3 13:06 demo12.bad
-rw-r--r-- 1 oracle oinstall 582 10 3 13:05 demo12.ctl
-rw-r--r-- 1 oracle oinstall 2147 10 3 13:06 demo12.log
-rw-r--r-- 1 oracle oinstall 790 10 3 14:45 demo13.ctl
-rw-r--r-- 1 oracle oinstall 2289 10 3 14:45 demo13.log
-rw-r--r-- 1 oracle oinstall 80 10 3 15:01 demo14.bad
-rw-r--r-- 1 oracle oinstall 441 10 3 15:00 demo14.ctl
-rw-r--r-- 1 oracle oinstall 320 10 3 15:02 demo14.dat
-rw-r--r-- 1 oracle oinstall 2230 10 3 15:03 demo14.log
-rw-r--r-- 1 oracle oinstall 332 10 4 05:42 demo15_bak.dat
-rw-r--r-- 1 oracle oinstall 442 10 4 05:31 demo15.ctl
-rw-r--r-- 1 oracle oinstall 332 10 4 05:45 demo15.dat
-rw-r--r-- 1 oracle oinstall 256 10 4 05:36 demo15.dat.bak
-rw-r--r-- 1 oracle oinstall 2229 10 4 05:45 demo15.log
-rw-r--r-- 1 oracle oinstall 3 10 4 07:44 demo16.bad
-rw-r--r-- 1 oracle oinstall 446 10 4 07:44 demo16.ctl
-rw-r--r-- 1 oracle oinstall 324 10 4 07:45 demo16.dat
-rw-r--r-- 1 oracle oinstall 2235 10 4 07:45 demo16.log
-rw-r--r-- 1 oracle oinstall 487 10 4 09:07 demo17.ctl
-rw-r--r-- 1 oracle oinstall 741 10 4 09:02 demo17.dat
-rw-r--r-- 1 oracle oinstall 2321 10 4 09:08 demo17.log
-rw-r--r-- 1 oracle oinstall 213 10 4 09:05 demo18.ctl
-rw-r--r-- 1 oracle oinstall 665 10 4 09:05 demo18.dat
-rw-r--r-- 1 oracle oinstall 1860 10 4 09:06 demo18.log
-rw-r--r-- 1 oracle oinstall 1 10 3 11:43 demo1.bad
-rw-r--r-- 1 oracle oinstall 177 10 3 11:43 demo1.ctl
-rw-r--r-- 1 oracle oinstall 1648 10 3 11:43 demo1.log
-rw-r--r-- 1 oracle oinstall 1 10 3 12:01 demo2.bad
-rw-r--r-- 1 oracle oinstall 214 10 3 12:00 demo2.ctl
-rw-r--r-- 1 oracle oinstall 1648 10 3 12:01 demo2.log
-rw-r--r-- 1 oracle oinstall 2 10 4 07:59 demo3.bad
-rw-r--r-- 1 oracle oinstall 121 10 4 07:59 demo3.ctl
-rw-r--r-- 1 oracle oinstall 1525 10 4 07:59 demo3.log
-rw-r--r-- 1 oracle oinstall 18 10 4 08:07 demo4.bad
-rw-r--r-- 1 oracle oinstall 141 10 4 08:11 demo4.ctl
-rw-r--r-- 1 oracle oinstall 1657 10 4 08:11 demo4.log
-rw-r--r-- 1 oracle oinstall 137 10 3 12:20 demo5.ctl
-rw-r--r-- 1 oracle oinstall 1560 10 3 12:21 demo5.log
-rw-r--r-- 1 oracle oinstall 175 10 3 12:23 demo6.ctl
-rw-r--r-- 1 oracle oinstall 1641 10 3 12:23 demo6.log
-rw-r--r-- 1 oracle oinstall 174 10 3 12:25 demo7.ctl
-rw-r--r-- 1 oracle oinstall 1641 10 3 12:25 demo7.log
-rw-r--r-- 1 oracle oinstall 184 10 3 12:28 demo8.ctl
-rw-r--r-- 1 oracle oinstall 1640 10 3 12:29 demo8.log
-rw-r--r-- 1 oracle oinstall 1 10 3 12:47 demo9.bad
-rw-r--r-- 1 oracle oinstall 290 10 3 12:47 demo9.ctl
-rw-r--r-- 1 oracle oinstall 1809 10 3 12:47 demo9.log
-rw-r--r-- 1 oracle oinstall 13 10 4 08:22 demo.txt
-rw-r--r-- 1 oracle oinstall 547 10 3 10:44 log.bbd

[oracle@jy ~]$ sqlldr userid=jy/jy control=demo19.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Fri Oct 4 10:26:54 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 56

现在,运行SQLLDR之后检查LOB_DEMO表的内容,会发现以下结果:

SQL> select owner, time_stamp, filename, dbms_lob.getlength(data) from jy.lob_demo;

OWNER TIME_STAMP FILENAME DBMS_LOB.GETLENGTH(DATA)
----------------------------- --------------------------------------------
oracle oinstall 2013-10-4 9 demo17.log 2321
oracle oinstall 2013-10-4 9 demo18.dat 665
oracle oinstall 2013-10-4 9 demo18.log 1860
oracle oinstall 2013-10-3 1 demo1.log 1648
oracle oinstall 2013-10-3 1 bifile.bbd 18432
oracle oinstall 2013-10-3 1 demo10.bad 1
oracle oinstall 2013-10-3 1 demo10.ctl 342
oracle oinstall 2013-10-3 1 demo10.log 1948
oracle oinstall 2013-10-3 1 demo11.bad 144
oracle oinstall 2013-10-3 1 demo11.ctl 377
oracle oinstall 2013-10-3 1 demo12.bad 89
oracle oinstall 2013-10-3 1 demo12.ctl 582
oracle oinstall 2013-10-3 1 demo14.bad 80
oracle oinstall 2013-10-3 1 demo14.ctl 441
oracle oinstall 2013-10-4 5 demo15.dat.bak 256
oracle oinstall 2013-10-4 7 demo16.bad 3
oracle oinstall 2013-10-4 9 demo18.ctl 213
oracle oinstall 2013-10-3 1 demo1.bad 1
oracle oinstall 2013-10-3 1 demo1.ctl 177
oracle oinstall 2013-10-3 1 demo2.bad 1
oracle oinstall 2013-10-3 1 demo2.ctl 214
oracle oinstall 2013-10-4 7 demo3.bad 2
oracle oinstall 2013-10-4 7 demo3.ctl 121
oracle oinstall 2013-10-4 8 demo4.bad 18
oracle oinstall 2013-10-3 1 demo5.ctl 137
oracle oinstall 2013-10-3 1 demo6.ctl 175
oracle oinstall 2013-10-3 1 demo8.ctl 184
oracle oinstall 2013-10-3 1 demo9.bad 1
oracle oinstall 2013-10-4 8 demo.txt 13
oracle oinstall 2013-10-3 1 demo11.log 1913
oracle oinstall 2013-10-3 1 demo12.log 2147
oracle oinstall 2013-10-3 1 demo13.ctl 790
oracle oinstall 2013-10-3 1 demo13.log 2289
oracle oinstall 2013-10-3 1 demo14.dat 320
oracle oinstall 2013-10-3 1 demo14.log 2230
oracle oinstall 2013-10-4 5 demo15_bak.dat 332
oracle oinstall 2013-10-4 5 demo15.ctl 442
oracle oinstall 2013-10-4 5 demo15.dat 332
oracle oinstall 2013-10-4 7 demo16.ctl 446
oracle oinstall 2013-10-4 5 demo15.log 2229
oracle oinstall 2013-10-4 7 demo16.dat 324
oracle oinstall 2013-10-4 7 demo16.log 2235
oracle oinstall 2013-10-4 9 demo17.ctl 487
oracle oinstall 2013-10-4 9 demo17.dat 741
oracle oinstall 2013-10-3 1 demo2.log 1648
oracle oinstall 2013-10-4 7 demo3.log 1525
oracle oinstall 2013-10-4 8 demo4.ctl 141
oracle oinstall 2013-10-4 8 demo4.log 1657
oracle oinstall 2013-10-3 1 demo5.log 1560
oracle oinstall 2013-10-3 1 demo6.log 1641
oracle oinstall 2013-10-3 1 demo7.ctl 174
oracle oinstall 2013-10-3 1 demo7.log 1641
oracle oinstall 2013-10-3 1 demo8.log 1640
oracle oinstall 2013-10-3 1 demo9.ctl 290
oracle oinstall 2013-10-3 1 demo9.log 1809
oracle oinstall 2013-10-3 1 log.bbd 547

这不光适用于BLOB,也适用于CLOB.以这种方式使用SQLLDR来加载文本文件的目录会很容易.

将LOB数据加载到对象列.既然知道了如何将数据加载到我们自己创建的一个简单表中,可能会发现,有时需要将数据加载到一 个复杂的表中,其中可能有一个包含LOB的复杂对象类型(列).使用图像功能时这种情况最为常见.图像功能使用一个复杂的对象类型ORDSYS.ORDIMAGE来实现,我们需要告诉SQLLDR如何向其中加载数据.

要把一个LOB加载到一个ORDIMAGE类型的列中,首先必须对ORDIMAGE类型的结构有所了解.在SQL*Plus中使用要加载的一个目标表以及该表上的DESCRIBE,可以发现表中有一个名为IMAGE的ORDSYS.ORDIMAGE列,最终我们想在这一列中加载 IMAGE.SOURCE.LOCALDATA,只有安装并配置好interMedia,项目的例子才能正常工作;否则,数据类型ORDSYS.ORDIMAGE将是一 个未知类型:

jy@JINGYONG> create table image_load
2 (id number,
3 name varchar2(255),
4 image ordsys.ordimage
5 );

表已创建。

SQL> desc jy.image_load
Name Type Nullable Default Comments
----- --------------- -------- ------- --------
ID NUMBER Y
NAME VARCHAR2(255) Y
IMAGE ORDSYS.ORDIMAGE Y

SQL> desc ordsys.ordimage
Element Type
-------------------- ----------------
SOURCE ORDSYS.ORDSOURCE
HEIGHT INTEGER
WIDTH INTEGER
CONTENTLENGTH INTEGER
FILEFORMAT VARCHAR2(4000)
CONTENTFORMAT VARCHAR2(4000)
COMPRESSIONFORMAT VARCHAR2(4000)
MIMETYPE VARCHAR2(4000)
INIT FUNCTION
COPY PROCEDURE
PROCESS PROCEDURE
PROCESSCOPY PROCEDURE
SETPROPERTIES PROCEDURE
CHECKPROPERTIES FUNCTION
GETHEIGHT FUNCTION
GETWIDTH FUNCTION
GETFILEFORMAT FUNCTION
GETCONTENTFORMAT FUNCTION
GETCOMPRESSIONFORMAT FUNCTION
GETMETADATA FUNCTION
PUTMETADATA PROCEDURE
SETLOCAL PROCEDURE
CLEARLOCAL PROCEDURE
ISLOCAL FUNCTION
GETUPDATETIME FUNCTION
SETUPDATETIME PROCEDURE
GETMIMETYPE FUNCTION
SETMIMETYPE PROCEDURE
GETCONTENTLENGTH FUNCTION
GETCONTENT FUNCTION
GETBFILE FUNCTION
DELETECONTENT PROCEDURE
GETDICOMMETADATA FUNCTION
SETSOURCE PROCEDURE
GETSOURCE FUNCTION
GETSOURCETYPE FUNCTION
GETSOURCELOCATION FUNCTION
GETSOURCENAME FUNCTION
IMPORT PROCEDURE
IMPORTFROM PROCEDURE
EXPORT PROCEDURE
PROCESSSOURCECOMMAND FUNCTION
OPENSOURCE FUNCTION
CLOSESOURCE FUNCTION
TRIMSOURCE FUNCTION
READFROMSOURCE PROCEDURE
WRITETOSOURCE PROCEDURE
GETPROPERTIES PROCEDURE

SQL> desc ordsys.ordsource
Element Type
-------------------- --------------
LOCALDATA BLOB
SRCTYPE VARCHAR2(4000)
SRCLOCATION VARCHAR2(4000)
SRCNAME VARCHAR2(4000)
UPDATETIME DATE
LOCAL NUMBER
SETLOCAL PROCEDURE
CLEARLOCAL PROCEDURE
ISLOCAL FUNCTION
GETUPDATETIME FUNCTION
SETUPDATETIME PROCEDURE
SETSOURCEINFORMATION PROCEDURE
GETSOURCEINFORMATION FUNCTION
GETSOURCETYPE FUNCTION
GETSOURCELOCATION FUNCTION
GETSOURCENAME FUNCTION
GETBFILE FUNCTION
IMPORT PROCEDURE
IMPORTFROM PROCEDURE
EXPORT PROCEDURE
GETCONTENTLENGTH FUNCTION
GETSOURCEADDRESS FUNCTION
GETLOCALCONTENT FUNCTION
GETCONTENTINTEMPLOB PROCEDURE
DELETELOCALCONTENT PROCEDURE
OPEN FUNCTION
CLOSE FUNCTION
TRIM FUNCTION
READ PROCEDURE
WRITE PROCEDURE
PROCESSCOMMAND FUNCTION

加载这种数据的控制文件可能如下所示:

[oracle@jy ~]$ cat demo20.ctl
LOAD DATA
INFILE *
INTO TABLE image_load
REPLACE
FIELDS TERMINATED BY ','
(ID,
NAME,
file_name FILLER,
IMAGE column object
(
SOURCE column object
(
LOCALDATA LOBFILE(file_name) TERMINATED BY EOF
NULLIF file_name='NONE'
)
)
)
BEGINDATA
1,psu3,psu3.jpg

这里引入了两个新构造:
COLUMN OBJECT:这会告诉SQLLDR这不是一个列名;而是列名的一部分.它不会映射到输入文件中的一个字段,只是用来构建正 确的对象列引用,从而在加载中使用.在前面的文件中有两个列对象标记,其中一个SOURCE嵌入在另一个SOURCE嵌入在 另一个IMAGE中.因此,根据我们的需要,要使用的列名是IMAGE.SOURCE.LOCALDATA.注意,我们没有加载这两个对象类型 的任何其他属性(例如,IMAGE.HEIGHT,IMAGE.CONTENTLENGTH和IMAGE.SOURCE.SRCTYPE).

NULL IF FILE_NAME = ‘NONE’:这会告诉SQLLDR,如果字段FILE_NAME包含单词NONE,则向对象列中加载一个NULL.

[oracle@jy ~]$ sqlldr userid=jy/jy control=demo20.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Fri Oct 4 10:38:14 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 1

SQL> select * from jy.image_load ;

ID NAME IMAGE
--- ------- -----
1 psu3 <Obje

使用sqlldr的控制文件生成创建外部表的语句
先创建一个目录

SQL> create or replace directory dir1 as '/home/oracle';

Directory created

根据sqlldr的控制文件来生成创建外部表的语句,先查看SQLLDR向DEPT表中加载批量数据的例子,如下：

[oracle@jy ~]$ cat demo21.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
FIELDS TERMINATED BY ','
(DEPTNO, DNAME, LOC )
BEGINDATA
10,Sales,Virginia
20,Accounting,Virginia
30,Consulting,Virginia
40,Finance,Virginia

以下SQLLDR命令会为我们的外部表生成CREATE TABLE语句：

[oracle@jy ~]$ sqlldr userid=jy/jy control=demo21.ctl external_table=generate_only

SQL*Loader: Release 10.2.0.1.0 - Production on Sat Oct 5 00:22:15 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

EXTERNAL_TABLE参数有以下3个值：
NOT_USED:这是默认值.
EXECUTE:这个值说明SQLLDR不会生成并执行一个SQL INSERT语句;而是会创建一个外部表,并使用一个批量SQL语句来加载.
GENERATE_ONLY:这个值使得SQLLDR并不具体加载任何数据,而只是会生成所执行的SQL DDL和DML语句,并放到它创建的日志文 件中.
但是要注意:DIRECT=TRUE覆盖EXTENAL_TABLE=GENERATE_ONLY.如果指定了DIRECT=TRUE,则会加载数据,而不会生成外部表.

使用GENERATE_ONLY时,可以在demo21.log文件中看到以下内容：

[oracle@jy ~]$ cat demo21.log

SQL*Loader: Release 10.2.0.1.0 - Production on Sat Oct 5 00:22:15 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Control File:   demo21.ctl
Data File:      demo21.ctl
  Bad File:     demo21.bad
  Discard File:  none specified

 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation:    none specified
Path used:      External Table

Table DEPT, loaded from every logical record.
Insert option in effect for this table: INSERT

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DEPTNO                              FIRST     *   ,       CHARACTER
DNAME                                NEXT     *   ,       CHARACTER
LOC                                  NEXT     *   ,       CHARACTER

下面为外部表生成CREATE TABLE语句：

CREATE TABLE statement for external table:
------------------------------------------------------------------------
CREATE TABLE "SYS_SQLLDR_X_EXT_DEPT"
(
  "DEPTNO" NUMBER(2),
  "DNAME" VARCHAR2(14),
  "LOC" VARCHAR2(14)
)

SQLLDR已经登录到数据库;只有这样它才知道这个外部表定义中要用的具体数据类型(例如,DEPTNO是一个NUMBER(2)). SQLLDR根据数据字典来确定这些数据类型.

ORGANIZATION EXTERNAL子句告诉Oracle:这不是一个”正常”表.

ORGANIZATION external
(
  TYPE oracle_loader
  DEFAULT DIRECTORY DIR1
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK
    BADFILE 'DIR1':'demo21.bad'
    LOGFILE 'demo21.log_xt'
    READSIZE 1048576
    SKIP 6
    FIELDS TERMINATED BY "," LDRTRIM
    REJECT ROWS WITH ALL NULL FIELDS
    (
      "DEPTNO" CHAR(255)
        TERMINATED BY ",",
      "DNAME" CHAR(255)
        TERMINATED BY ",",
      "LOC" CHAR(255)
        TERMINATED BY ","
    )
  )
  location
  (
    'demo21.ctl'
  )
)REJECT LIMIT UNLIMITED

ORACEL_LOADER类型是目前支持的两种类型之一(Oracle9i中只支持这一种类型),另一种类型是ORACLE_DATAPUMP,
这是Oracle 10g及以上版本中Oracle的专用数据泵格式.这种格式不仅可以用于加载数据,也可以卸载数据.

这些访问参数显示了如何建立一个外部表,使之能像SQLLDR一样几乎以同样的方式处理文件:
RECORDS:记录默认以换行符结束,SQLLDR中的记录就是如此.

BADFILE:在刚创建的目录中建立了一个坏文件(无法处理的记录都记录到这个文件中).

LOGFILE:在当前的工作目录中记录了一个等价于SQLLDR日志文件的日志文件.

READSIZE:这是Oracle读取输入数据文件所用的默认缓冲区.在这里是1MB.如果采用专用服务器模式,这个内存来自PGA,如果 采用共享服务器模式,则来自SGA,它用于缓存输入数据文件中对应一个会话的信息.

SKIP 6:在确定了应该跳过输入文件中的多少记录.你可能会问:为什么有skip 6.是这样,在这个例子中使用了INFILE *;使用SKIP 6就是跳过控制文件本身来得到内嵌的数据.如果没有使用INFILE *,就根本不会有SKIP子句.

FIELDS TERMINATED BY:这与控制文件中的用法一样.不过,外部表增加LDRTRIM,这代表Loader TRIM.这是一种截断模式,模 拟了SQLLDR截断数据的默认做法.还有另外一些选项,包括LRTRIM,LTRIM和RTRIM,表示左截断/右截断空白符;NOTRIM表示保 留所有前导/尾随的空白符.

REJECT ROWS WITH ALL NULL FIELDS:这导致外部表会在坏文件中记录所有全空的行,而且不加载这些行.

列定义本身:这是有关所期望输入数据值的元数据.它们是所加载数据文件中的字符串,长度最多可达255个字符(SQLLDR的默 认大小),以逗号(,)结束,还可以选择用引号括起来.

location部分告诉Oracle所加载文件的文件名,在这里就是demo21.ctl,因为我们在原控制文件中使用了INFILE *.控制文件中的下一条语句是默认的INSERT,可以用于从外部表本身加载表:

INSERT statements used to load internal tables:
------------------------------------------------------------------------
INSERT /*+ append */ INTO DEPT
(
  DEPTNO,
  DNAME,
  LOC
)
SELECT
  "DEPTNO",
  "DNAME",
  "LOC"
FROM "SYS_SQLLDR_X_EXT_DEPT"

如果可能的话,这会执行一个逻辑上与直接路径加载等价的操作(如果可以遵循APPEND提示;如果存在触发器或外键约束,可能 不允许发生直接路径操作).

最后,在日志文件中,我们会看到一些语句,这些语句可以用于删除加载完成之后SQLLDR我我们创建的对象:

statements to cleanup objects created by previous statements:
------------------------------------------------------------------------
DROP TABLE "SYS_SQLLDR_X_EXT_DEPT"

可能会看到日志文件中有一个CREATE DIRECTORY语句(也可能看不到).在生成外部表脚本期间,SQLLDR连接到数据库,并查询 数据字典来查看是否已经存在合适的目录.在这个例子中,由于有合适的目录,所以SQLLDR为我们选择了我们之前所创建的目录DIR1

CREATE DIRECTORY statements needed for files
------------------------------------------------------------------------
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/oracle'

通过sqldr生成外部全创建语句的完整语句如下:

CREATE TABLE "SYS_SQLLDR_X_EXT_DEPT"
(
  "DEPTNO" NUMBER(2),
  "DNAME" VARCHAR2(14),
  "LOC" VARCHAR2(14)
)
ORGANIZATION external
(
  TYPE oracle_loader
  DEFAULT DIRECTORY DIR1
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK
    BADFILE 'DIR1':'demo21.bad'
    LOGFILE 'demo21.log_xt'
    READSIZE 1048576
    SKIP 6
    FIELDS TERMINATED BY "," LDRTRIM
    REJECT ROWS WITH ALL NULL FIELDS
    (
      "DEPTNO" CHAR(255)
        TERMINATED BY ",",
      "DNAME" CHAR(255)
        TERMINATED BY ",",
      "LOC" CHAR(255)
        TERMINATED BY ","
    )
  )
  location
  (
    'demo21.ctl'
  )
)REJECT LIMIT UNLIMITED;

执行创建外部表的语句:

jy@JINGYONG> CREATE TABLE "SYS_SQLLDR_X_EXT_DEPT"
  2  (
  3    "DEPTNO" NUMBER(2),
  4    "DNAME" VARCHAR2(14),
  5    "LOC" VARCHAR2(14)
  6  )
  7  ORGANIZATION external
  8  (
  9    TYPE oracle_loader
 10    DEFAULT DIRECTORY DIR1
 11    ACCESS PARAMETERS
 12    (
 13      RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK
 14      BADFILE 'DIR1':'demo21.bad'
 15      LOGFILE 'demo21.log_xt'
 16      READSIZE 1048576
 17      SKIP 6
 18      FIELDS TERMINATED BY "," LDRTRIM
 19      REJECT ROWS WITH ALL NULL FIELDS
 20      (
 21        "DEPTNO" CHAR(255)
 22          TERMINATED BY ",",
 23        "DNAME" CHAR(255)
 24          TERMINATED BY ",",
 25        "LOC" CHAR(255)
 26          TERMINATED BY ","
 27      )
 28    )
 29    location
 30    (
 31      'demo21.ctl'
 32    )
 33  )REJECT LIMIT UNLIMITED;

表已创建。

然后通过所创建的外部表向dept表插入数据:

jy@JINGYONG> INSERT /*+ append */ INTO DEPT
  2  (
  3    DEPTNO,
  4    DNAME,
  5    LOC
  6  )
  7  SELECT
  8    "DEPTNO",
  9    "DNAME",
 10    "LOC"
 11  FROM "SYS_SQLLDR_X_EXT_DEPT";

已创建4行。

jy@JINGYONG> commit;

提交完成。

创建了日志文件

[oracle@jy ~]$ ls -lrt demo21.log_xt
-rw-r--r-- 1 oracle oinstall 737 Oct  5 00:43 demo21.log_xt

SQL> select * from jy.dept;

DEPTNO DNAME          LOC            ENTIRE_LINE  LAST_UPDATED COMMENTS
------ -------------- -------------- ------------ ------------ ---------
    10 Sales          Virginia
    20 Accounting     Virginia
    30 Consulting     Virginia
    40 Finance        Virginia

平面文件卸载
要把数据从一个系统移动到另一个系统,如果没有使用EXP/IMP或EXPDP/IMPDP(用于取代EXP和IMP的新数据泵),平面卸载就很有用.尽管使用EXP(DP)/IMP(DP)可以很好地将数据从一个系统移到另一个系统,但要求两个系统都是Oracle.

jy@JINGYONG> create or replace package unloader
  2  authid current_user
  3  as
  4  /* Funaction run--unloads data from any query into a  file
  5               and creates a control file to reload that
  6               data into another table
  7     p_query=SQL query to "unload".May be virtually and query.
  8     p_tname=Table to load into.Will be put into control file.
  9     p_mode=REPLACE|APPEND|TRUNCATE--how to reload the data
 10     p_dir=directory we will write the ctl and dat file to.
 11     p_filename=name of file to write to.I will add .ctl and .dat to this
 12        name
 13     p_separator=field delimiter. I default this to a comma.
 14     p_enclosure=what each field will be wrapped in
 15     p_terminator=end of line character. We use this so we can unload
 16        and reload data with newlines in it. I default to
 17        "|\n"(a pipe and a newline together) and "|\r\n" on NT.
 18     You need only to override this if you believe your
 19     data will have that sequence in it. I ALWAYS add the
 20     OS "end of line" marker to this sequence,you should not
 21  */
 22     function run(p_query in varchar2,
 23                  p_tname in varchar2,
 24                  p_mode in varchar2 default 'REPLACE',
 25                  p_dir in varchar2,
 26                  p_filename in varchar2,
 27                  p_separator in varchar2 default ',',
 28                  p_enclosure in varchar2 default '"',
 29                  p_terminator in varchar2 default '|')
 30      return number;
 31  end;
 32  /

程序包已创建。

注意这里使用了AUTHID CURRENT_USER.这样一来,这个包就可以在数据库上只安装一次,可由任何人用来卸载数据.要卸载数 据,只要求一点:对所卸载的表要有SELECT权限,另外对这个包有EXECUTE权限.如果这里没有使用AUTHID CURRENT_USER,则需 要这个包的所有者在要卸载的所有表上都有直接的SELECT权限.

注意:SQL会以这个例程的调用者的权限执行.不过,所有PL/SQL调用都会以所调用例程定义者的权限运行;因此,对于具有这个 包执行权限的所有人,都隐含地允许他使用UTL_FILE写至一个目录.

包体如下.我们使用UTL_FILE来写一个控制文件和一个数据文件.DBMS_SQL用于动态地处理所有查询.我们在查询中使用了一个数据类型:VARCHAR2(4000).这说明,如果LOB大于4,000字节,就不能使用这个方法来卸载LOB.不过,只需使用DBMS_LOB.SUBSTR,我们就可以使用这个方法卸载任何最多4,000字节的LOB.另外,由于我们用一个VARCHAR2作为惟一的输出数据类型,所以可以处理长度最多2,000字节的RAW(4,000个十六进制字符),除了LONG RAW和LOB外,这对其他类型都足够了.另 外,如果查询引用了一个非标量属性(一个复杂的对象类型,嵌套表等),则不能使用这个简单的实现.以下是一个90%可用的解决方案,这说明90%的情况下它都能解决问题:

create or  replace package body unloader
as
     g_theCursor integer default dbms_sql.open_cursor;
     g_descTbl dbms_sql.desc_tab;
     g_nl varchar2(2) default chr(10);

以上是这个包体中使用的一些全局变量.全局游标打开一次,即第一次引用这个包时打开,它会一起打开,直到我们注销.这就 不用每次调用这个包时都要得到一个新游标,从而避免相应的开销.G_DESCTBL是一个PL/SQL表,将保存DBMS_SQL.DESCRIBE调用的输出.G_NL是一个换行符.在需要内嵌有换行符的串中会使用这个变量.我们无需针对Windows调整这个变量,UTL_FILE会看到字符串中的CHR(10),并自动为我们将其转换为一个回车/换行符.
接下来,我们使用了一个很小的便利函数,它能将字符转换为一个十六进制数,为此使用了内置函数:

     function to_hex(p_str in varchar2) return varchar2
     is
     begin
         return to_char(ascii(p_str),'fm0x');
     end;

最后,我们又创建了另一个便利函数IS_WINDOWS,它会返回TRUE或FALSE,这取决于我们所用的是否是Windows平台,如果在 Windows平台上,行结束符就是一个两字符的串,而大多数其他平台上的行结束符只是单字符.我们使用了内置DBMS_UTILITY函 数:GET_PARAMETER_VALUE,可以用这个函数读取几乎所有参数.我们获取了CONTROL_FILES参数,并查找其中是否存在\,如果 有,则说明在Windows平台上:

     function is_windows return boolean
     is
        l_cfiles varchar2(4000);
        l_dummy number;
     begin
        if(dbms_utility.get_parameter_value('control_files',l_dummy,l_cfiles)>0) then
            return instr(l_cfiles,'\')>0;
        else
            return FALSE;
        end if;
     end;

注意:IS_WINDOWS函数依赖于CONTROL_FILES参数中使用了\.要记住,其中也有可能使用/,但这极为少见.

下面的过程会创建一个控制文件来重新加载卸载的数据,这里使用了DBMS_SQL.DESCRIBE_COLUMN生成的DESCRIBE表.它会为我们处理有关操作系统的细节,如操作系统是否使用回车/换行符(用于STR属性):

     procedure dump_ctl(
                p_dir in varchar2,
                p_filename in varchar2,
                p_tname in varchar2,
                p_mode in varchar2,
                p_separator in varchar2,
                p_enclosure in varchar2,
                p_terminator in varchar2)
     is
         l_output utl_file.file_type;
         l_sep varchar2(5);
         l_str varchar2(5):=chr(10);
     begin
         if(is_windows) then
            l_str:=chr(13)||chr(10);
         end if;
         l_output:=utl_file.fopen(p_dir,p_filename|\'.ctl','w');
         utl_file.put_line(l_output,'load data');
         utl_file.put_line( l_output, 'infile ''' ||
                                      p_filename || '.dat'' "str x''' ||
                                      utl_raw.cast_to_raw( p_terminator ||
                                      l_str ) || '''"' );
         utl_file.put_line( l_output, 'into table ' || p_tname );
         utl_file.put_line( l_output, p_mode );
         utl_file.put_line( l_output, 'fields terminated by X''' ||
                                       to_hex(p_separator) ||
                                       ''' enclosed by X''' ||
                                       to_hex(p_enclosure) || ''' ' );
         utl_file.put_line( l_output, '(' );

         for i in 1 .. g_descTbl.count  loop
            if ( g_descTbl(i).col_type = 12 ) then
                 utl_file.put( l_output, l_sep || g_descTbl(i).col_name ||
                               ' date ''ddmmyyyyhh24miss'' ');
            else
                 utl_file.put( l_output, l_sep || g_descTbl(i).col_name ||
                               ' char(' ||
                               to_char(g_descTbl(i).col_max_len*2) ||' )' );
            end if;
            l_sep := ','||g_nl ;
         end loop;
         utl_file.put_line( l_output, g_nl || ')' );
         utl_file.fclose( l_output );
     end;

这是一个简单的函数,会返回一个加引号的串(使用所选择的包围字符作为引号);注意,串不只是包含字符,倘若串中还存在包围字符,还会把包围字符重复两次,从而保留这些包围字符:

     function quote(p_str in varchar2,p_enclosure in varchar2)
         return varchar2
     is
     begin
         return p_enclosure||
                replace(p_str,p_enclosure,p_enclosure||p_enclosure)||
                p_enclosure;
     end;

下面是主函数RUN.因为这个函数相当大,会列出函数的解释:

     function run(p_query in varchar2,
                  p_tname in varchar2,
                  p_mode in varchar2 default 'REPLACE',
                  p_dir in varchar2,
                  p_filename in varchar2,
                  p_separator in varchar2 default ',',
                  p_enclosure in varchar2 default '"',
                  p_terminator in varchar2 default '|')
         return number
     is
         l_output utl_file.file_type;
         l_columnValue varchar2(4000);
         l_colCnt number default 0;
         l_separator varchar2(10) default '';
         l_cnt number default 0;
         l_line long;
         l_datefmt varchar2(255);
         l_descTbl dbms_sql.desc_tab;
     begin

我们将NLS_DATE_FORMAT保存到一个变量中,从而在将数据转储到磁盘上时可以把它改为一种保留日期和时间的格式.采用这种方式,我们会保留日期的时间分量.然后建立一个异常块,从而在接收到错误时重置NLS_DATE_FORMAT:

         select value into l_datefmt
         from nls_session_parameters where parameter='NLS_DATE_FORMAT';
         /* 设置日期格式为是一个大数字字符串避免所有的NLS问题并保留时间和日期 */
         execute immediate 'alter session set nls_date_format="yyyymmddhh24miss"';
         /* 设置一个异常块在出现任何错误时重新设置日期格式 */

接下来,解析并描述这个查询.将G_DESCTBL设置为L_DESCTBL来重置全局表;否则,其中会包含前一个DESCRIBE生成的数据,而不只是当前查询生成的数据.一旦完成,再调用DUMP_CTL具体创建控制文件:

           begin
            /*
              解析和描述这个查询.将重设descTbl为了一个空表
            */
            dbms_sql.parse( g_theCursor, p_query, dbms_sql.native );
            g_descTbl := l_descTbl;
            dbms_sql.describe_columns( g_theCursor, l_colCnt, g_descTbl );
            /*
               创建一个控制文件来重新加载数据到你所期望的表中
            */
            dump_ctl( p_dir, p_filename, p_tname, p_mode, p_separator,
            p_enclosure, p_terminator );
            /*
            绑定每一个列转换为一个长度为4000字符串.我们不在乎我们获取是数字还是日期,因为它们都可以
            看成是一个字符串
            */

现在可以将具体数据转储到磁盘上了.首先将每个列定义为VARCHAR2(4000)来获取数据.所有类型(NUMBER,DATE,RAW)都要 转换为VARCHAR2.在此之后,执行查询来准备获取:

            for i in 1 .. l_colCnt loop
                dbms_sql.define_column(g_theCursor,i,l_columnValue,4000);
            end loop;
            /*
              运行这个查询--忽略执行的输出,它只是来验证一个插入/更新或删除操作
            */

现在打开数据文件准备写,从查询获取所有行,并将其打印到数据文件:

            l_cnt := dbms_sql.execute(g_theCursor);
            /*
              打开文件然后将带有分隔符的数据写入到文件中
            */
            l_output := utl_file.fopen( p_dir, p_filename || '.dat', 'w',32760 );
            loop
               exit when ( dbms_sql.fetch_rows(g_theCursor) < = 0 );
               l_separator := '';
               l_line := null;
               for i in 1 .. l_colCnt loop
                   dbms_sql.column_value( g_theCursor, i,l_columnValue );
                   l_line := l_line || l_separator ||quote( l_columnValue, p_enclosure );
                   l_separator := p_separator;
               end loop;
               l_line := l_line || p_terminator;
               utl_file.put_line( l_output, l_line );
               l_cnt := l_cnt+1;
            end loop;
            utl_file.fclose( l_output );

最后,将日期格式设置回原来的样子(如果先前的代码由于某种原因失败了,异常块也会做这个工作),并返回:

            /*
               现在重新设置日期格式并将返回的行数据写入到输出文件中
            */
            execute immediate  'alter session set nls_date_format=''' || l_datefmt || '''';
            return l_cnt;
            exception
            /*
              如果发现任何错误会重新设置日期并重新触发错误
            */
            when others then
               execute immediate 'alter session set nls_date_format=''' || l_datefmt || '''';
               RAISE;
         end;
     end run;
end unloader;

执行完整的编译

SQL> create or replace package body unloader
  2  as
  3       g_theCursor integer default dbms_sql.open_cursor;
  4       g_descTbl dbms_sql.desc_tab;
  5       g_nl varchar2(2) default chr(10);
  6
  7       function to_hex(p_str in varchar2) return varchar2
  8       is
  9       begin
 10           return to_char(ascii(p_str),'fm0x');
 11       end;
 12
 13       function is_windows return boolean
 14       is
 15          l_cfiles varchar2(4000);
 16          l_dummy number;
 17       begin
 18          if(dbms_utility.get_parameter_value('control_files',l_dummy,l_cfiles)>0) then
 19              return instr(l_cfiles,'\')>0;
 20          else
 21              return FALSE;
 22          end if;
 23       end;
 24
 25       procedure dump_ctl(
 26                  p_dir in varchar2,
 27                  p_filename in varchar2,
 28                  p_tname in varchar2,
 29                  p_mode in varchar2,
 30                  p_separator in varchar2,
 31                  p_enclosure in varchar2,
 32                  p_terminator in varchar2)
 33       is
 34           l_output utl_file.file_type;
 35           l_sep varchar2(5);
 36           l_str varchar2(5):=chr(10);
 37       begin
 38           if(is_windows) then
 39              l_str:=chr(13)||chr(10);
 40           end if;
 41           l_output:=utl_file.fopen(p_dir,p_filename||'.ctl','w');
 42           utl_file.put_line(l_output,'load data');
 43           utl_file.put_line( l_output, 'infile ''' ||
 44                                        p_filename || '.dat'' "str x''' ||
 45                                        utl_raw.cast_to_raw( p_terminator ||
 46                                        l_str ) || '''"' );
 47           utl_file.put_line( l_output, 'into table ' || p_tname );
 48           utl_file.put_line( l_output, p_mode );
 49           utl_file.put_line( l_output, 'fields terminated by X''' ||
 50                                         to_hex(p_separator) ||
 51                                         ''' enclosed by X''' ||
 52                                         to_hex(p_enclosure) || ''' ' );
 53           utl_file.put_line( l_output, '(' );
 54
 55           for i in 1 .. g_descTbl.count  loop
 56              if ( g_descTbl(i).col_type = 12 ) then
 57                   utl_file.put( l_output, l_sep || g_descTbl(i).col_name ||
 58                                 ' date ''ddmmyyyyhh24miss'' ');
 59              else
 60                   utl_file.put( l_output, l_sep || g_descTbl(i).col_name ||
 61                                 ' char(' ||
 62                                 to_char(g_descTbl(i).col_max_len*2) ||' )' );
 63              end if;
 64              l_sep := ','||g_nl ;
 65           end loop;
 66           utl_file.put_line( l_output, g_nl || ')' );
 67           utl_file.fclose( l_output );
 68       end;
 69
 70       function quote(p_str in varchar2,p_enclosure in varchar2)
 71           return varchar2
 72       is
 73       begin
 74           return p_enclosure||
 75                  replace(p_str,p_enclosure,p_enclosure||p_enclosure)||
 76                  p_enclosure;
 77       end;
 78
 79
 80       function run(p_query in varchar2,
 81                    p_tname in varchar2,
 82                    p_mode in varchar2 default 'REPLACE',
 83                    p_dir in varchar2,
 84                    p_filename in varchar2,
 85                    p_separator in varchar2 default ',',
 86                    p_enclosure in varchar2 default '"',
 87                    p_terminator in varchar2 default '|')
 88           return number
 89       is
 90           l_output utl_file.file_type;
 91           l_columnValue varchar2(4000);
 92           l_colCnt number default 0;
 93           l_separator varchar2(10) default '';
 94           l_cnt number default 0;
 95           l_line long;
 96           l_datefmt varchar2(255);
 97           l_descTbl dbms_sql.desc_tab;
 98       begin
 99           select value into l_datefmt
100           from nls_session_parameters where parameter='NLS_DATE_FORMAT';
101           /* 设置日期格式为是一个大数字字符串避免所有的NLS问题并保留时间和日期 */
102           execute immediate 'alter session set nls_date_format="yyyymmddhh24miss"';
103           /* 设置一个异常块在出现任何错误时重新设置日期格式 */
104           begin
105               /*
106                  解析和描述这个查询.将重设descTbl为了一个空表
107               */
108               dbms_sql.parse( g_theCursor, p_query, dbms_sql.native );
109               g_descTbl := l_descTbl;
110               dbms_sql.describe_columns( g_theCursor, l_colCnt, g_descTbl );
111               /*
112                  创建一个控制文件来重新加载数据到你所期望的表中
113               */
114               dump_ctl( p_dir, p_filename, p_tname, p_mode, p_separator,
115               p_enclosure, p_terminator );
116               /*
117                  绑定每一个列转换为一个长度为4000字符串.我们不在乎我们获取是数字还是日期,因为它们都可以
118                  看成是一个字符串
119               */
120               for i in 1 .. l_colCnt loop
121                   dbms_sql.define_column(g_theCursor,i,l_columnValue,4000);
122               end loop;
123               /*
124                  运行这个查询--忽略执行的输出,它只是来验证一个插入/更新或删除操作
125               */
126               l_cnt := dbms_sql.execute(g_theCursor);
127               /*
128                  打开文件然后将带有分隔符的数据写入到文件中
129               */
130               l_output := utl_file.fopen( p_dir, p_filename || '.dat', 'w',32760 );
131               loop
132                 exit when ( dbms_sql.fetch_rows(g_theCursor) < = 0 );
133                 l_separator := '';
134                 l_line := null;
135                 for i in 1 .. l_colCnt loop
136                     dbms_sql.column_value( g_theCursor, i,l_columnValue );
137                     l_line := l_line || l_separator ||quote( l_columnValue, p_enclosure );
138                     l_separator := p_separator;
139                 end loop;
140                 l_line := l_line || p_terminator;
141                 utl_file.put_line( l_output, l_line );
142                 l_cnt := l_cnt+1;
143               end loop;
144               utl_file.fclose( l_output );
145               /*
146                 现在重新设置日期格式并将返回的行数据写入到输出文件中
147               */
148               execute immediate  'alter session set nls_date_format=''' || l_datefmt || '''';
149               return l_cnt;
150               exception
151               /*
152                 如果发现任何错误会重新设置日期并重新触发错误
153               */
154               when others then
155                  execute immediate 'alter session set nls_date_format=''' || l_datefmt || '''';
156                 RAISE;
157               end;
158       end run;
159  end unloader;
160  /

Package body created

要运行这个代码,可以使用以下命令(要注意,当然以下代码需要你将SCOTT.EMP的SELECT权限授予某个角色,或者直接授予你 自己):

jy@JINGYONG> set serveroutput on
jy@JINGYONG> create or replace directory dir1 as '/home/oracle';

目录已创建。

jy@JINGYONG> declare
  2  l_rows number;
  3  begin
  4     l_rows:=unloader.run
  5           (p_query=>'select * from scott.emp order by empno',
  6            p_tname=>'emp',
  7            p_mode=>'replace',
  8            p_dir=>'DIR1',
  9            p_filename=>'emp',
 10            p_separator=>',',
 11            p_enclosure=>'"',
 12            p_terminator=>'~');
 13     dbms_output.put_line(to_char(l_rows)||' rows extracted to ascii file');
 14  end;
 15  /
14 rows extracted to ascii file

PL/SQL 过程已成功完成。

由此生成的emp.ctl控制文件显示如下(注意,括号里粗体显示的数字并不是真的包括在文件中:加上这些数字只是为了便于引 用):

[oracle@jy ~]$ cat emp.ctl
load data
infile 'emp.dat' "str x'7E0A'"
into table emp
replace
fields terminated by X'2c' enclosed by X'22'
(
EMPNO char(44 ),
ENAME char(20 ),
JOB char(18 ),
MGR char(44 ),
HIREDATE date 'ddmmyyyyhh24miss' ,
SAL char(44 ),
COMM char(44 ),
DEPTNO char(44 )
)

关于这个控制文件,要注意以下几点:
第2行:使用了SQLLDR的STR特性.可以指定用什么字符或串来结束一个记录.这样就能很容易地加载有内嵌换行符的数据.串x ‘7E0A’只是换行符后面跟一个波浪号”~”.

第5行:使用了我们的分隔符和包围符.这里没有使用OPTIONALLY ENCLOSED BY,因为我们将把原数据中包围字符的所有出现都 重复两次,再把每个字段括起来.

第11行:使用了一个很大的”数值”日期格式.这有两个作用:可以避免与日期有关的所有NLS问题,还可以保留日期字段的时间分量.

从前面的代码生成的原始数据（emp.dat）文件如下：

[oracle@jy ~]$ cat emp.dat
"7369","SMITH","CLERK","7902","19801217000000","800","","20"~
"7499","ALLEN","SALESMAN","7698","19810220000000","1600","300","30"~
"7521","WARD","SALESMAN","7698","19810222000000","1250","500","30"~
"7566","JONES","MANAGER","7839","19810402000000","2975","","20"~
"7654","MARTIN","SALESMAN","7698","19810928000000","1250","1400","30"~
"7698","BLAKE","MANAGER","7839","19810501000000","2850","","30"~
"7782","CLARK","MANAGER","7839","19810609000000","2450","","10"~
"7788","SCOTT","ANALYST","7566","19870419000000","3000","","20"~
"7839","KING","PRESIDENT","","19811117000000","5000","","10"~
"7844","TURNER","SALESMAN","7698","19810908000000","1500","0","30"~
"7876","ADAMS","CLERK","7788","19870523000000","1100","","20"~
"7900","JAMES","CLERK","7698","19811203000000","950","","30"~
"7902","FORD","ANALYST","7566","19811203000000","3000","","20"~
"7934","MILLER","CLERK","7782","19820123000000","1300","","10"~

emp.dat文件中要注意的问题如下:
每个字段都用包围字符括起来.
DATE卸载为很大的数字>
这个文件中的数据行按要求以一个～结束>
现在可以使用SQLLDR很容易地重新加载这个数据,你可以向SQLLDR命令行增加你认为合适的选项.

数据泵卸载
Oracle9i引入了外部表,作为向数据库中读取数据的一种方法.Oracle 10g则从另一个方向引入了这个特性,可以使用CREATE TABLE语句创建外部数据,从而由数据库卸载数据.从Oracle 10g起,这个数据从一种专用二进制格式抽取,这种格式称为数据 泵格式(Data Pump format),Oracle提供的EXPDP和IMPDP工具将数据从一个数据库移动另一个数据库所用的就是这种格式.

使用外部表卸载确实相当容易,就像使用CREATE TABLE AS SELECT语句一样简单.首先,需要一个DIRECTORY对象:

jy@JINGYONG> create or replace directory dir1 as '/home/oracle';

目录已创建。

现在,准备使用一个简单的SELECT语句向这个目录中卸载数据,例如:

jy@JINGYONG> create table my_object_unload
  2  organization external
  3  (
  4  type oracle_datapump
  5  default directory DIR1
  6  location('myobjects.dat')
  7  )
  8  as
  9  select * from all_objects;

表已创建。

[oracle@jy ~]$ ls -lrt myobjects.dat
-rw-r----- 1 oracle oinstall 4923392 Oct  5 04:09 myobjects.dat

从上面的信息可知已经生成了myobjects.dat文件
获取外部表my_object_unload的创建语句

SQL> select dbms_metadata.get_ddl( 'TABLE', 'MY_OBJECT_UNLOAD' ) from dual;

DBMS_METADATA.GET_DDL('TABLE',
--------------------------------------------------------------------------------

  CREATE TABLE "JY"."MY_OBJECT_UNLOAD"
   (	"OWNER" VARCHAR2(30),
	"OBJECT_NAME" VARCHAR2(30),
	"SUBOBJECT_NAME" VARCHAR2(30),
	"OBJECT_ID" NUMBER,
	"DATA_OBJECT_ID" NUMBER,
	"OBJECT_TYPE" VARCHAR2(19),
	"CREATED" DATE,
	"LAST_DDL_TIME" DATE,
	"TIMESTAMP" VARCHAR2(19),
	"STATUS" VARCHAR2(7),
	"TEMPORARY" VARCHAR2(1),
	"GENERATED" VARCHAR2(1),
	"SECONDARY" VARCHAR2(1)
   )
   ORGANIZATION EXTERNAL
    ( TYPE ORACLE_DATAPUMP
      DEFAULT DIRECTORY "DIR1"

      LOCATION
       ( 'myobjects.dat'
       )
    )

现在把生成的myobjects.dat文件拷贝到你要加载数据的服务器上创建目录(directory)并在要加载数据的用户下使用上面创建外部表的语句(记得要修改DEFAULT DIRECTORY “DIR1″指向你所存放myobjects.dat的目录)来创建外部表

CREATE TABLE "MY_OBJECT_UNLOAD"
   (  "OWNER" VARCHAR2(30),
  "OBJECT_NAME" VARCHAR2(30),
  "SUBOBJECT_NAME" VARCHAR2(30),
  "OBJECT_ID" NUMBER,
  "DATA_OBJECT_ID" NUMBER,
  "OBJECT_TYPE" VARCHAR2(19),
  "CREATED" DATE,
  "LAST_DDL_TIME" DATE,
  "TIMESTAMP" VARCHAR2(19),
  "STATUS" VARCHAR2(7),
  "TEMPORARY" VARCHAR2(1),
  "GENERATED" VARCHAR2(1),
  "SECONDARY" VARCHAR2(1)
   )
   ORGANIZATION EXTERNAL
    ( TYPE ORACLE_DATAPUMP
      DEFAULT DIRECTORY "DIR1"

      LOCATION
       ( 'myobjects.dat'
       )
    );
/

SQL> select * from my_object_unload ;

OWNER   OBJECT_NAME            SUBOBJECT_NAME  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED      LAST_DDL_TIME TIMESTAMP           STATUS  TEMPORARY GENERATED SECONDARY
------- ---------------------- -------------- ---------- -------------- ------------------- ----------- ------- ------ ------------------- ------- --------- --------- ---------
SYS     ICOL$                                         20              2 TABLE               2005-6-30 1 2005-6- 30 19: 2005-06-30:19:10:16 VALID   N         N         N
SYS     I_USER1                                       44             44 INDEX               2005-6-30 1 2005-6- 30 19: 2005-06-30:19:10:16 VALID   N         N         N
SYS     CON$                                          28             28 TABLE               2005-6-30 1 2013-3- 24 12: 2005-06-30:19:10:16 VALID   N         N         N
SYS     UNDO$                                         15             15 TABLE               2005-6-30 1 2005-6- 30 19: 2005-06-30:19:10:16 VALID   N         N         N
SYS     C_COBJ#                                       29             29 CLUSTER             2005-6-30 1 2005-6- 30 19: 2005-06-30:19:10:16 VALID   N         N         N
SYS     I_OBJ#                                         3              3 INDEX               2005-6-30 1 2005-6- 30 19: 2005-06-30:19:10:16 VALID   N         N         N
SYS     PROXY_ROLE_DATA$                              25             25 TABLE               2005-6-30 1 2005-6- 30 19: 2005-06-30:19:10:16 VALID   N         N         N
SYS     I_IND1                                        39             39 INDEX               2005-6-30 1 2005-6- 30 19: 2005-06-30:19:10:16 VALID   N         N         N
SYS     I_CDEF2                                       51             51 INDEX               2005-6-30 1 2005-6- 30 19: 2005-06-30:19:10:16 VALID   N         N         N
SYS     I_PROXY_ROLE_DATA$_1                          26             26 INDEX               2005-6-30 1 2005-6- 30 19: 2005-06-30:19:10:16 VALID   N         N         N

dbms_rowid.rowid_create函数,此函数可以创建一个rowid,它的语法如下
DBMS_ROWID.ROWID_CREATE (
rowid_type IN NUMBER,
object_number IN NUMBER,
relative_fno IN NUMBER,
block_number IN NUMBER,
row_number IN NUMBER)
RETURN ROWID;

rowid_type Type (restricted or extended).
Set the rowid_type parameter to 0 for a restricted ROWID. Set
it to 1 to create an extended ROWID.
If you specify rowid_type as 0, then the required object_
number parameter is ignored, and ROWID_CREATE returns a
restricted ROWID.

object_number Data object number (rowid_object_undefined for restricted).

relative_fno Relative file number.

block_number Block number in this file.

row_number Returns row number in this block.

下面是使用dbms_rowid.rowid_create的一个例子

sys@JINGYONG> select rowid, t.*,dbms_rowid.rowid_relative_fno(rowid) relative_fno ,
  2  dbms_rowid.rowid_object(rowid) object_number ,
  3  dbms_rowid.rowid_block_number(rowid) block_number ,
  4  dbms_rowid.rowid_row_number(rowid) row_number
  5   from t where rownum<2;

ROWID                      ID TEXT                           RELATIVE_FNO OBJECT_NUMBER BLOCK_NUMBER ROW_NUMBER
------------------ ---------- ------------------------------ ------------ ------------- ------------ ----------
AAANB1AABAAAPAaAAA         20 ICOL$                                     1  53365        61466          0


sys@JINGYONG>  select data_object_id from dba_objects where object_id=53365;

DATA_OBJECT_ID
--------------
         53365

sys@JINGYONG>  select dbms_rowid.rowid_create(1,53365,1,61466,0) create_rowid fr
om dual;

CREATE_ROWID
------------------
AAANB1AABAAAPAaAAA

通过dbms_rowid.rowid_create(1,53365,1,61466,0)构造的rowid与原始的rowid是一样的.
参数的意思：1表示rowid的类型为扩展rowid，类型为1； data_object_id 为53365，也就是文中t表对象的id； 数据文件id为1 ，即system表空间文件；块的编号为61466号； 行数为第一行（第一行为值0） 。

通过dbms_rowid.rowid_create函数查询被锁定的具体行
查询被锁的会话和持有锁的会话，如果是exclusive锁则xidusn非零的表示已经执行并持有锁

column o_name format a10
column lock_type format a20
column object_name format a15
select rpad(oracle_username,10) o_name,
       session_id sid,
       decode(locked_mode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_type,
       object_name,
       all_objects.object_id,
       xidusn,
       xidslot,
       xidsqn
from v$locked_object,all_objects
where v$locked_object.object_id=all_objects.object_id;
O_NAME SID     LOCK_TYPE      OBJECT_NAME   OBJECT_ID   XIDUSN    XIDSLOT   XIDSQN
---------- ------- -------------------- ---------------        -------------- ----------   ------------ ----------
SYS 145      Row Exclusive    T                    53365            8             17               724
SYS   148     Row Exclusive    T                    53365           0              0                 0

下面的查询可以得到被锁定的session,被锁定的对象id和row number

select sid,
       row_wait_obj# object_id,
       row_wait_file# file_no,
       row_wait_block# block,
       row_wait_row# row_num
from v$session
where row_wait_obj#=&object_id;
Enter value for object_id: 53365
old   7: where row_wait_obj#=&object_id
new   7: where row_wait_obj#=53365
       SID OBJECT_ID        FILE_NO          BLOCK        ROW_NUM
---------- --------------   ----------       ----------   ----------
       148   51207           1               61466            0

如果要对应行rowid则：

sys@JINGYONG>  select dbms_rowid.rowid_create(1,53365,1,61466,0) create_rowid fr
om dual;

CREATE_ROWID
------------------
AAANB1AABAAAPAaAAA

有了rowid，具体的行就能定位了

并行管道函数
这个例子中要使用两个表：T1和T2。T1是先读的表，T2表用来插入这个信息。我们要用的两个表如下：

sys@JINGYONG> create table t1
  2  as
  3  select object_id id,object_name text
  4  from all_objects;

表已创建。

sys@JINGYONG> begin
  2  dbms_stats.set_table_stats
  3  (user,'T1',numrows=>100000000,numblks=>100000);
  4  end;
  5  /

PL/SQL 过程已成功完成。

sys@JINGYONG> create table t2
  2  as
  3  select t1.*,0 session_id
  4  from t1
  5  where 1=0;

表已创建。

这里使用DBMS_STATS来骗过优化器，让它以为输入表中有10,000,000行，而且占用了100,000个数据库块。在此模拟 一个大表。第二个表T2是第一个表的一个副本，只是在结构中增加了一个SESSION_ID列。可以通过它具体看到是否发生了并行化。接下来，需要建立管道函数返回的对象类型。在这个例子中，对象类型类似于T2：

sys@JINGYONG> create or replace type t2_type
  2  as object
  3  (
  4  id number,
  5  text varchar2(30),
  6  session_id number
  7  );
  8  /

类型已创建。

sys@JINGYONG> create or replace type t2_tab_type as table of t2_type;
  2  /

类型已创建。

现在这个过程是一个生成行的函数。它接收数据作为输入，并在一个引用游标（ref cursor）中处理。这个函数返回一个 T2_TAB_TYPE，这就是我们刚才创建的对象类型。这是一个PARALLEL_ENABLED（启用子并行）的管道函数。在此使用了分区 （partition）子句，这就告诉Oracle：以任何最合适的方式划分或分解数据。我们不需要对数据的顺序做任何假设。

在此，我们只想划分数据。数据如何划分对于我们的处理并不重要，所以定义如下：

sys@JINGYONG> create or replace function parallel_pipelined(l_cursor in sys_refcursor)
  2  return t2_tab_type
  3  pipelined
  4  parallel_enable(partition l_cursor by any)
  5  is
  6   l_session_id number;
  7   TYPE type_t1_data IS TABLE OF t1%ROWTYPE INDEX BY PLS_INTEGER;
  8   l_t1  type_t1_data;
  9
 10  begin
 11  select sid into l_session_id
 12  from v$mystat
 13  where rownum=1;
 14  loop
 15    fetch l_cursor bulk collect into l_t1;--用bulk collect来一次性获取数据
 16    exit when l_t1.count=0;
 17    for i in 1 .. l_t1.count loop
 18          pipe row(t2_type(l_t1(i).id,l_t1(i).text,l_session_id));
 19    end loop;
 20    null;
 21  end loop;
 22  close l_cursor;
 23  return;
 24  end;
 25  /

Function created

或者用下面的过程来一行一行来获取

create or replace
function parallel_pipelined( l_cursor in sys_refcursor )
return t2_tab_type
pipelined
parallel_enable ( partition l_cursor by any )
is
 l_session_id number;
 l_rec t1%rowtype;
begin
 select sid into l_session_id
 from v$mystat
 where rownum =1;
 loop
 fetch l_cursor into l_rec;
 exit when l_cursor%notfound;
 pipe row(t2_type(l_rec.id,l_rec.text,l_session_id));
 end loop;
 close l_cursor;
 return;
end;

这样就创建了函数。我们准备并行地处理数据，让Oracle根据可用的资源来确定最合适的并行度：

SQL> insert /*+ append */
  2  into t2(id,text,session_id)
  3   select *
  4   from table(parallel_pipelined
  5   (CURSOR(select /*+ parallel(t1) */ *
  6   from t1 )
  7  ))
  8  ;

50333 rows inserted

SQL> commit;

Commit complete

为了查看这里发生了什么，可以查询新插入的数据，并按SESSION_ID分组，先来看使用了多少个并行执行服务器，再看每个并行 执行服务器处理了多少行：

SQL> select session_id,count(*) from t2 group by session_id;

SESSION_ID   COUNT(*)
---------- ----------
       136      31006
       145      19327

显然，对于这个并行操作的SELECT部分，我们使用了2个并行执行服务器,可以看到，Oracle对我们的过程进行了并行化

索引分区
索引与表类似，也可以分区。对索引进行分区有两种可能的方法：
随表对索引完成相应的分区：这也称为局部(本地)分区索引（locally pertitioned index）。每个表分区都有一个索引分区， 而且只索引该表分区。一个给定索引分区中的所有条目都指向一个表分区，表分区中的所有行都表示在一个索引分区中。

按区间对索引分区：这也称为全局分区索引（globally partitioned index）。在此，索引按区间分区（或者在Oracle 10g中该 可以按散列分区），一个索引分区可能指向任何（和所有）表分区。

对于全局分区索引，要注意实际上索引分区数可能不同于表分区数。
由于全局索引只按区间或散列分区，如果希望有一个列表或组合分区索引，就必须使用局部索引。局部索引会使用底层表相同的 机制分区。

注意 全局索引的散列分区是Oracle 10g Release 1及以后的版本中才有的新特性。在Oracle9i及以前的版本中，只能按区间进 行全局分区。

局部索引
Oracle划分了以下两类局部索引：
局部前缀索引（local prefixed index）：在这些索引中，分区键在索引定义的前几列上。例如，一个表在名为LOAD_DATE的列 上进行区间分区，该表上的局部前缀索引就是LOAD_DATE作为其索引列列表中的第一列。
局部非前缀索引（local nonprefixed index）：这些索引不以分区键作为其列列表的前几列。索引可能包含分区键列，也可能 不包含。
这两类索引都可以利用分区消除，它们都支持惟一性（只有前缀索引包含分区键）等。事实上，使用局部前缀索引的查询总允许 索引分区消除，而使用局部非前缀索引的查询可能不允许。正是由于这个原因，所以在某些人看来局部非前缀索引“更慢”，它 们不能保证分区消除（但确实可以支持分区消除）。

如果查询中将索引用作访问表的初始路径，那么从本质来讲，局部前缀索引并不比局部非前缀索引更好。也就是说，如何查询把 “扫描一个索引”作为第一步，那么前缀索引和非前缀索引之间并没有太大的差别。

分区消除行为
如果查询首先访问索引，它是否能消除分区完全取决于查询中的谓词。下面的代码创建了一个表PARTITIONED_TABLE，它在一个 数字列A上进行区间分区，使得小于2的值都在分区PART_1中，小于3的值则都在分区PART_2中：

sys@JINGYONG> create table partitioned_table
  2  (a int,
  3   b int,
  4   data varchar2(20)
  5  )
  6  partition by range(a)
  7  (
  8   partition part_1 values less than(2) tablespace p1,
  9   partition part_2 values less than(3) tablespace p2
 10  )
 11  ;

表已创建。

然后我们创建一个局部前缀索引LOCAL_PREFIXED和一个局部非前缀索引LOCAL_NONPREFIXED。注意，非前缀索引在其定义中没有 以A作为其最前列，这是这一点使之成为一个非前缀索引：

sys@JINGYONG> create index local_prefixed on partitioned_table(a,b) local;

索引已创建。

sys@JINGYONG> create index local_nonprefixed on partitioned_table(b) local;

索引已创建。

接下来，我们向一个分区中插入一些数据，并收集统计信息：

sys@JINGYONG> insert into partitioned_table
  2  select mod(rownum-1,2)+1,rownum,'x' from all_objects;

已创建50324行。

sys@JINGYONG> commit;

提交完成。

sys@JINGYONG> exec dbms_stats.gather_table_stats(user,'PARTITIONED_TABLE',estima
te_percent=>100,method_opt=>'for all columns size repeat',cascade=>true);

PL/SQL 过程已成功完成。

将表空间P2脱机，其中包含用于表和索引的PART_2分区：

sys@JINGYONG> alter tablespace p2 offline;

表空间已更改。

表空间P2脱机后，Oracle就无法访问这些特定的索引分区。这就好像是我们遭遇了“介质故障”，导致分区不可用。现在我们查 询这个表，来看看不同的查询需要哪些索引分区。第一个查询编写为允许使用局部前缀索引：

sys@JINGYONG> select * from partitioned_table where a=1 and b=1;

         A          B DATA
---------- ---------- --------------------
         1          1 x

这个查询成功了，通过查看解释计划，可以看到这个查询为什么能成功。我们将使用内置包DBMS_XPLAN来查看这个查询访问了哪 些分区。输出中的PSTART (分区开始)和PSTOP（分区结束）这两列准确地显示出，这个查询要想成功需要哪些分区必须在线而且 可用：

sys@JINGYONG> delete from plan_table;

已删除0行。

sys@JINGYONG> explain plan for
  2  select * from partitioned_table where a=1 and b=1;

已解释。

sys@JINGYONG> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------- ---------
Plan hash value: 1622054381

--------------------------------------------------------------------------------------------------------------- ---------
| Id  | Operation                          | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart|  Pstop |
--------------------------------------------------------------------------------------------------------------- ---------
|   0 | SELECT STATEMENT                   |                   |     1 |     9 |     2   (0)| 00:00:01 |       |        |
|   1 |  PARTITION RANGE SINGLE            |                   |     1 |     9 |     2   (0)| 00:00:01 |     1 |      1 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| PARTITIONED_TABLE |     1 |     9 |     2   (0)| 00:00:01 |     1 |      1 |
|*  3 |    INDEX RANGE SCAN                | LOCAL_PREFIXED    |     1 |       |     1   (0)| 00:00:01 |     1 |      1 |
--------------------------------------------------------------------------------------------------------------- ---------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("A"=1 AND "B"=1)

已选择15行。

因此，使用LOCAL_PREFIXED的查询成功了。优化器能消除LOCAL_PREFIXED的PART_2不予考虑，因为我们在查询中指定了A=1，而 且在计划中可以清楚地看到PSTART和PSTOP都等于1.分区消除帮助了我们。不过，第二个查询却失败了

sys@JINGYONG> select * from partitioned_table where b=1;
ERROR:
ORA-00376: file 7 cannot be read at this time
ORA-01110: data file 7: '/u01/app/oracle/product/10.2.0/oradata/jingyong/jingyon
g/p201.dbf'

我们可以通过查看这个查询的执行计划看到为什么会失败

sys@JINGYONG> delete from plan_table;

已删除4行。

sys@JINGYONG> explain plan for
  2  select * from partitioned_table where b=1;

已解释。

sys@JINGYONG> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------- -----------------------------------------------------------------------------------------
Plan hash value: 440752652

--------------------------------------------------------------------------------------------------------------- ---------
| Id  | Operation                          | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart|  Pstop |
--------------------------------------------------------------------------------------------------------------- ---------
|   0 | SELECT STATEMENT                   |                   |     1 |     9 |     4   (0)| 00:00:01 |       |        |
|   1 |  PARTITION RANGE ALL               |                   |     1 |     9 |     4   (0)| 00:00:01 |     1 |      2 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| PARTITIONED_TABLE |     1 |     9 |     4   (0)| 00:00:01 |     1 |      2 |
|*  3 |    INDEX RANGE SCAN                | LOCAL_NONPREFIXED |     1 |       |     3   (0)| 00:00:01 |     1 |      2 |
--------------------------------------------------------------------------------------------------------------- ---------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("B"=1)

已选择15行。

在此，优化器不能不考虑LOCAL_NONPREFIXED的PART_2，为了查看是否有B=1，索引的PART_1和PART_2都必须检查。在此，局部非 前缀索引存在一个性能问题：它不能像前缀索引那样，在谓词中使用分区键。并不是说前缀索引更好，我们的意思是：要使用非 前缀索引，必须使用一个允许分区消除的查询

sys@JINGYONG> drop index local_prefixed;

索引已删除。

sys@JINGYONG> select * from partitioned_table where a=1 and b=1;

         A          B DATA
---------- ---------- --------------------
         1          1 x

它会成功，但是正如我们所见，这里使用了先前失败的索引。该计划显示出，在此Oracle能利用分区消除，有了谓词A=1，就有 了足够的信息可以让数据库消除索引分区PART_2而不予考虑：

sys@JINGYONG> delete from plan_table;

已删除4行。

sys@JINGYONG> explain plan for
  2  select * from partitioned_table where a=1 and b=1;

已解释。

sys@JINGYONG> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------- ---------
Plan hash value: 904532382

--------------------------------------------------------------------------------------------------------------- ---------
| Id  | Operation                          | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart|  Pstop |
--------------------------------------------------------------------------------------------------------------- ---------
|   0 | SELECT STATEMENT                   |                   |     1 |     9 |     2   (0)| 00:00:01 |       |        |
|   1 |  PARTITION RANGE SINGLE            |                   |     1 |     9 |     2   (0)| 00:00:01 |     1 |      1 |
|*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| PARTITIONED_TABLE |     1 |     9 |     2   (0)| 00:00:01 |     1 |      1 |
|*  3 |    INDEX RANGE SCAN                | LOCAL_NONPREFIXED |     1 |       |     1   (0)| 00:00:01 |     1 |      1 |
--------------------------------------------------------------------------------------------------------------- ---------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("A"=1)
   3 - access("B"=1)

已选择16行。

注意PSTART和PSTOP列值为1和1.这就证明，优化器甚至对非前缀局部索引也能执行分区消除。
如果你频繁地用以下查询来查询先前的表：
select … from partitioned_table where a = :a and b = :b;
select … from partitioned_table where b = :b;
可以考虑在（b,a）上使用一个局部非前缀索引。这个索引对于前面的两个查询都是有用的。（a,b）上的局部前缀索引只对第一 个查询有用。
这里的关键是，不必对非前缀索引退避三舍，也不要认为非前缀索引是主要的性能障碍。如果你有多个如前所列的查询（可以得 益于非前缀索引），就应该考虑使用一个非前缀索引。重点是，要尽可能保证查询包含的谓词允许索引分区消除。使用前缀局部 索引可以保证这一点，使用非前缀索引则不能保证。还要考虑如何使用索引。如果将索引用作查询计划中的第一步，那么这两种 类型的索引没有多少差别。

局部索引和惟一约束
为了保证惟一性（这包括UNIQUE约束或PRIMARY KEY约束），如果你想使用一个局部索引来保证这个约束，那么分区键必须包括 在约束本身中。在我看来，这是局部索引的最大限制。Oracle只保证索引分区内部的惟一性，而不能跨分区。这说明什么呢？例 如，这意味着不能一方面在一个TIMESTAMP字段上执行区间分区，而另一方面在ID上有一个主键（使用一个局部分区索引来保证 ）。Oracle会利用全局索引来保证惟一性。
在下面的例子中，我们将创建一个区间分区表，它按一个名为LOAD_DATE的列分区，却在ID列上有一个主键。通过查看这个分表 的每一个段，就能很容易地看出到底创建了哪些对象：

sys@JINGYONG> create table partitioned
  2  (load_date date,
  3  id int,
  4  constraint partitioned_pk primary key(id)
  5  )
  6  partition by range(load_date)
  7  (
  8  partition part_1 values less than(to_date('2000-01-01','yyyy-mm-dd')),
  9  partition part_2 values less than(to_date('2001-01-01','yyyy-mm-dd'))
 10  );

表已创建。

sys@JINGYONG> select segment_name,partition_name,segment_type
  2  from user_segments where segment_name like'PARTITIONED%';

SEGMENT_NAME      PARTITION_NAME    SEGMENT_TYPE
--------------    --------------    ------------------
PARTITIONED       PART_1            TABLE PARTITION
PARTITIONED       PART_2            TABLE PARTITION
PARTITIONED_PK                      INDEX

PARTITIONED_PK索引没有分区.而且我们将会看到，它根本无法进行局部分区。由于认识到非惟一索引也能像惟一索引一样保证 主键，我们想以此骗过Oracle，但是可以看到这种方法也不能奏效：

sys@JINGYONG> create table partitioned
  2  (timestamp date,
  3  id int
  4  )
  5  partition by range(timestamp)
  6  (
  7  partition part_1 values less than
  8  (to_date('2000-01-01','yyyy-mm-dd')),
  9  partition part_2 values less than
 10  (to_date('2001-01-01','yyyy-mm-dd'))
 11  );

表已创建。

sys@JINGYONG> create index partitioned_idx on partitioned(id) local;

索引已创建。

sys@JINGYONG> select segment_name,partition_name,segment_type
  2  from user_segments where segment_name like'PARTITIONED%';

SEGMENT_NAME            PARTITION_NAME    SEGMENT_TYPE
--------------------    ---------------   ------------------
PARTITIONED             PART_1            TABLE PARTITION
PARTITIONED             PART_2            TABLE PARTITION
PARTITIONED_IDX         PART_2            INDEX PARTITION
PARTITIONED_IDX         PART_1            INDEX PARTITION

sys@JINGYONG> alter table partitioned
  2  add constraint partitioned_pk primary key(id);
alter table partitioned
*
第 1 行出现错误:
ORA-01408: such column list already indexed

在此，Oracle试图在ID上创建一个全局索引，却发现办不到，这是因为ID上已经存在一个索引。如果已创建的索引没有分区，前 面的语句就能工作，Oracle会使用这个索引来保证约束。
为什么局部分区索引不能保证惟一性（除非分区键是约束的一部分），原因有两方面。首先，如果Oracle允许如此，就会丧失分 区的大多数好处。可用性和可扩缩性都会丧失殆尽，因为对于任何插入和更新，总是要求所有分区都一定可用，而且要扫描每一 个分区。你的分区越多，数据就会变得越不可用。另外，分区越多，要扫描的索引分区就越多，分区也会变得越发不可扩缩。这 样做不仅不能提供可用性和可扩缩性，相反，实际上 反倒会削弱可用性和可扩缩性。

另外，倘若局部分区索引能保证惟一性，Oracle就必须在事务级对这个表的插入和更新有效地串行化。这是因为，如果向PART_1 增加ID=1，Oracle就必须以某种方式防止其他人向PART_2增加ID=1。对此惟一的做法是防止别人修改索引分区PART_2，因为无法 通过对这个分区中的内容“锁定”来做到（找不出什么可以锁定）。

在一个OLTP系统中，惟一性约束必须由系统保证（也就是说，由Oracle保证），以确保数据的完整性。这意味着，应用的逻辑模 型会对物理设计产生影响。惟一性约束能决定底层的表分区机制，影响分区键的选择，或者指示你应该使用全局索引。下面将更 深入地了解全局索引。

全局索引
全局索引使用一种有别于底层表的机制进行分区。表可以按一个TIMESTAMP列划分为10个分区，而这个表上的一个全局索引可以 按REGION列划分为5个分区。与局部索引不同，全局索引只有一类，这就是前缀全局索引（prefixed global index）。如果全局 索引的索引键未从该索引的分区键开始，这是不允许的。这说明，不论用什么属性对索引分区，这些属性都必须是索引键的前几 列。
下面继续看前面的例子，这里给出一个使用全局索引的小例子。它显示全局分区索引可以用于保证主键的惟一性，这样一来，即 使不包括表的分区键，也能保证惟一性的分区索引。下面的例子创建了一个按TIMESTAMP分区的表，它有一个按ID分区的索引：

sys@JINGYONG> create table partitioned
  2  (timestamp date,
  3  id int
  4  )
  5  partition by range(timestamp)
  6  (
  7  partition part_1 values less than(to_date('2000-01-01','yyyy-mm-dd')),
  8  partition part_2 values less than(to_date('2001-01-01','yyyy-mm-dd'))
  9  );

表已创建。

sys@JINGYONG> create index partitioned_index on partitioned(id) global
  2  partition by range(id)
  3  (
  4  partition part_1 values less than(1000),
  5  partition part_2 values less than(maxvalue)
  6  );

索引已创建。

注意，这个索引中使用了MAXVALUE。MAXVALUE不仅可以用于索引中，还可以用于任何区间分区表中。它表示区间的“无限上界” 。在此前的所有例子中，我们都使用了区间的硬性上界（小于< 某个值>的值）。不过，全局索引有一个需求，即最高分区（最后 一个分区）必须有一个值为MAXVALUE的分区上界。这可以确保底层表中的所有行都能放在这个索引中。
下面，在这个例子的最后，我们将向表增加主键：

sys@JINGYONG> alter table partitioned add constraint
  2  partitioned_pk primary key(id);

表已更改。

从这个代码还不能明显看出Oracle在使用我们创建的索引来保证主键,所以可以试着删除这个索引来证明这一点：

sys@JINGYONG> drop index partitioned_index;
drop index partitioned_index
           *
第 1 行出现错误:
ORA-02429: cannot drop index used for enforcement of unique/primary key

为了显示Oracle不允许创建一个非前缀全局索引，只需执行下面的语句：

sys@JINGYONG> create index partitioned_index2 on partitioned(timestamp,id)
  2  global
  3  partition by range(id)
  4  (
  5  partition part_1 values less than(1000),
  6  partition part_2 values less than(maxvalue)
  7  );
partition by range(id)
                     *
第 3 行出现错误:
ORA-14038: GLOBAL partitioned index must be prefixed

为了创建一个全局索引那么索引的分区键必须是全局索引中的前几列才行如下:

sys@JINGYONG> create index partitioned_index2 on partitioned(id,timestamp)
  2  global
  3  partition by range(id)
  4  (
  5  partition part_1 values less than(1000),
  6  partition part_2 values less than(maxvalue)
  7  );

索引已创建。

错误信息相当明确。全局索引必须是前缀索引。那么，要在什么时候使用全局索引呢？我们将分析两种不同类型的系统（数据仓 库和OLTP）。来看看何时可以应用全局索引

数据仓库和全局索引
原先数据仓库和全局索引是相当互斥的。数据仓库就意味着系统有某些性质，如有大量的数据出入。许多数据仓库都实现了一种 滑动窗口（sliding window）方法来管理数据，也就是说，删除表中最旧的分区，并为新加载的数据增加一个新分区。在过去（ Oracle8i及以前的版本），数据仓库系统都避免使用全局索引，对此有一个很好的原因：全局索引缺乏可用性。大多数分区操作 （如删除一个旧分区）都会使全局索引无效，除非重建全局索引，否则无法使用，这会严重地影响可用性，以前往往都是如此。

滑动窗口和索引
下面的例子实现了一个经典的数据滑动窗口。在许多实现中，会随着时间的推移向仓库中增加数据，而最旧的数据会老化。在很 多时候，这个数据会按一个日期属性进行区间分区，所以最旧的数据多存储在一个分区中，新加载的数据很可能都存储在一个新 分区中。每月的加载过程涉及：
去除老数据：最旧的分区要么被删除，要么与一个空表交换（将最旧的分区变为一个表），从而允许对旧数据进行归档。
加载新数据并建立索引：将新数据加载到一个“工作”表中，建立索引并进行验证。
关联新数据：一旦加载并处理了新数据，数据所在的表会与分区表中的一个空分区交换，将表中的这些新加载的数据变成分区表 中的一个分区（分区表会变得更大）。
这个过程会没有重复，或者执行加载过程的任何周期重复；可以是每天或每周。我们将在这一节实现这个非常典型的过程，显示 全局分区索引的影响，并展示分区操作期间可以用哪些选项来提高可用性，从而能实现一个数据滑动窗口，并维持数据的连续可 用性。
在这个例子中，我们将处理每年的数据，并加载2004和2005财政年度的数据。这个表按TIMESTAMP列分区，并创建了两个索引， 一个是ID列上的局部分区索引，另一个是TIMESTAMP列上的全局索引（这里为分区）：

sys@JINGYONG> create table partitioned
  2  (timestamp date,
  3  id int
  4  )
  5  partition by range(timestamp)
  6  (
  7  partition fy_2004 values less than(to_date('2005-01-01','yyyy-mm-dd')),
  8  partition fy_2005 values less than(to_date('2006-01-01','yyyy-mm-dd'))
  9  );

表已创建。

sys@JINGYONG> insert into partitioned partition(fy_2004)
  2  select to_date('2004-12-31','yyyy-mm-dd')-mod(rownum,360),object_id
  3  from all_objects;

已创建50318行。

sys@JINGYONG> insert into partitioned partition(fy_2005)
  2  select to_date('2005-12-31','yyyy-mm-dd')-mod(rownum,360),object_id
  3  from all_objects;

已创建50318行。

sys@JINGYONG> create index partitioned_idx_local on partitioned(id) local;

索引已创建。

sys@JINGYONG> create index partitioned_idx_global
  2  on partitioned(timestamp) global;

索引已创建。

这就建立了我们的“仓库”表。数据按财政年度分区，而且最后两年的数据在线。这个表有两个索引：一个是LOCAL索引，另一 个是GLOBAL索引。现在正处于年末，我们想做下面的工作：
（1） 删除最旧的财政年度数据。我们不想永远地丢掉这个数据，而只是希望它老化，并将其归档。
（2） 增加最新的财政年度数据。加载、转换、建索引等工作需要一定的时间。我们想做这个工作，但是希望尽可能不影响当前 数据的可用性。
第一步是为2004财政年度建立一个看上去就像分区表的空表。我们将使用这个表与分区表中的FY_2004分区交换，将这个分区转 变成一个表，相应地使分区表中的分区为空。这样做的效果就是分区表中最旧的数据（实际上）会在交换之后被删除：

sys@JINGYONG> create table fy_2004(timestamp date,id int);

表已创建。

sys@JINGYONG> create index fy_2004_idx on fy_2004(id);

索引已创建。

对要加载的新数据做同样的工作。我们将创建并加载一个表，其结构就像是现在的分区表（但是它本身并不是分区表）：

sys@JINGYONG> create table fy_2006(timestamp date,id int);

表已创建。

sys@JINGYONG> insert into fy_2006
  2  select to_date('2006-12-31','yyyy-mm-dd')-mod(rownum,360),object_id
  3  from all_objects;

已创建50325行。

sys@JINGYONG> create index fy_2006_idx on fy_2006(id) nologging;

索引已创建。

我们将当前的满分区变成一个空分区，并创建了一个包含FY_2004数据的“慢”表。而且，我们完成了使用FY_2006数据的所有必 要工作，这包括验证数据、进行转换以及准备这些数据所需完成的所有复杂任务。
现在可以使用一个交换分区来更新“活动”数据：

sys@JINGYONG> alter table partitioned
  2  exchange partition fy_2004
  3  with table fy_2004
  4  including indexes
  5  without validation;

表已更改。

sys@JINGYONG> alter table partitioned drop partition fy_2004;

表已更改。

要把旧数据“老化”，所要做的仅此而已。我们将分区变成一个满表，而将空表变成一个分区。这是一个简单的数据字典更新， 瞬时就会完成，而不会发生大量的I/O。现在可以将FY_2004表从数据库中导出（可能要使用一个可移植的表空间）来实现归档。 如果需要，还可以很快地重新关联这些数据。
接下来，我们想“滑入”（即增加）新数据：

sys@JINGYONG> alter table partitioned
  2  add partition fy_2006
  3  values less than(to_date('2007-01-01','yyyy-mm-dd'));

表已更改。

sys@JINGYONG> alter table partitioned
  2  exchange partition fy_2006
  3  with table fy_2006
  4  including indexes
  5  without validation;

表已更改。

sys@JINGYONG> select index_name,status from user_indexes where table_name=’PARTI
TIONED’;

INDEX_NAME STATUS
—————————— ——–
PARTITIONED_IDX_LOCAL N/A
PARTITIONED_IDX_GLOBAL UNUSABLE

sys@JINGYONG> select index_name,status from user_indexes where index_name like’F
Y%’;

INDEX_NAME STATUS
—————————— ——–
FY_2004_IDX VALID
FY_2006_IDX VALID

当然，在这个操作之后，全局索引是不可用的。由于每个索引分区可能指向任何表分区，而我们刚才取走了一个分区，并增加了 一个分区，所以这个索引已经无效了。 其中有些条目指向我们已经生成的分区，却没有任何条目指向刚增加的分区。使用了这 个索引的任何查询可能会失败而无法执行，或者如果我们跳过不可用的索引， 尽管查询能执行，但查询的性能会受到负面影响 （因为无法使用这个索引）：

sys@JINGYONG> set autotrace on explain
sys@JINGYONG> select /*+ index(partitioned partitioned_idx_global) */ count(*)
  2  from partitioned
  3  where timestamp between sysdate-50 and sysdate;
select /*+ index(partitioned partitioned_idx_global) */ count(*)
*
第 1 行出现错误:
ORA-01502: index 'SYS.PARTITIONED_IDX_GLOBAL' or partition of such index is in u
nusable state


sys@JINGYONG> select  count(*)
  2  from partitioned
  3  where timestamp between sysdate-50 and sysdate;

  COUNT(*)
----------
         0


执行计划
----------------------------------------------------------
Plan hash value: 2869581836

----------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name        | Rows  | Bytes | Cost (%CPU)|Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |             |     1 |     9 |    63  (12)|00:00:01 |       |       |
|   1 |  SORT AGGREGATE            |             |     1 |     9 |            |         |       |       |
|*  2 |   FILTER                   |             |       |       |            |         |       |       |
|   3 |    PARTITION RANGE ITERATOR|             |     3 |    27 |    63  (12)|00:00:01 |   KEY |   KEY |
|*  4 |     TABLE ACCESS FULL      | PARTITIONED |     3 |    27 |    63  (12)|00:00:01 |   KEY |   KEY |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(SYSDATE@!-50< =SYSDATE@!)
   4 - filter("TIMESTAMP"<=SYSDATE@! AND "TIMESTAMP">=SYSDATE@!-50)

Note
-----
   - dynamic sampling used for this statement

因此，执行这个分区操作后，对于全局索引，我们有以下选择：
跳过索引，可以像这个例子中一样（Oracle 10g会透明地这样做），在9i中则可以通过设置会话参数 SKIP_UNUSABLE_INDEXES=TRUE来跳过索引（Oracle 10g将这个设置默认为TRUE）。但是这样一来，就丢失了索引所提供的性能提 升。让查询接收到一个错误，就像9i中一样（SKIP_UNUSABLE_INDEX设置为FALSE），在10g中，显式地请求使用提示的任何查询 都会接收到错误。要想让数据再次真正可用，必须重建这个索引。

到此为止滑动窗口过程几乎不会带来任何停机时间，但是在我们重建全局索引时，需要相当长的时间才能完成。如果查询依赖于 这些索引，在此期间它们的运行时查询 性能就会受到负面影响，可能根本不会运行，也可能运行时得不到索引提供的好处。所 有数据都必须扫描，而且要根据数据重建整个索引。如果表的大小为数百GB，这会占用相当多的资源。

活动全局索引维护
从Oracle9i开始，对于分区维护又增加了另一个选项：可以在分区操作期间使用UPDATE GLOBAL INEXES子句来维护全局索引。这 意味着，在你删除一个分区、分解一个分区以及在分区上执行任何必要的操作时，Oracle会对全局索引执行必要的修改，保证它 是最新的。由于大多数分区操作都会导致全局索引无效，这个特征对于需要提供数据连续访问的系统来说是一个大福音。你会发 现，通过牺牲分区操作的速度（但是原先重建索引后会有一个可观的不可用窗口，即不可用的停机时间相当长），可以换取100% 的数据可用性（尽管分区操作的总体响应时间会更慢）。简单地说，如果数据仓库不允许有停机时间，而且必须支持数据的滑入 滑出等数据仓库技术，这个特性就再合适不过了，但是你必须了解它带来的影响。

再来看前面的例子，如果分区操作在必要时使用了UPDATE GLOBAL INDEXES子句（在这个例子中，在ADD PARTITION语句上就没有 必要使用这个子句，因为新增加的分区中没有任何行）：

sys@JINGYONG> alter table partitioned
2 exchange partition fy_2004
3 with table fy_2004
4 including indexes
5 without validation
6 UPDATE GLOBAL INDEXES
7 /
Table altered.

sys@JINGYONG> alter table partitioned
2 drop partition fy_2004
3 UPDATE GLOBAL INDEXES
4 /
Table altered.

sys@JINGYONG> alter table partitioned
2 add partition fy_2006
3 values less than ( to_date('01-jan-2007','dd-mon-yyyy') )
4 /
Table altered.

sys@JINGYONG> alter table partitioned
2 exchange partition fy_2006
3 with table fy_2006
4 including indexes
5 without validation
6 UPDATE GLOBAL INDEXES
7 /
Table altered.

就会发现索引完全有效，不论在操作期间还是操作之后这个索引都是可用的：

sys@JINGYONG> select index_name, status from user_indexes;
INDEX_NAME                     STATUS
------------------------------ --------
FY_2006_IDX                    VALID
FY_2004_IDX                    VALID
PARTITIONED_IDX_GLOBAL         VALID
PARTITIONED_IDX_LOCAL          N/A
4 rows selected.

sys@JINGYONG> set autotrace on explain
sys@JINGYONG> select count(*)
2 from partitioned
3 where timestamp between sysdate-50 and sysdate;
COUNT(*)
----------
6750
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=9 Card=1 Bytes=9)
1 0 SORT (AGGREGATE)
2 1 FILTER
3 2 INDEX (RANGE SCAN) OF 'PARTITIONED_IDX_GLOBAL' (INDEX) (Cost=9...

但是这里要做一个权衡：我们要在全局索引结构上执行INSERT和DELETE操作的相应逻辑操作。删除一个分区时，必须删除可能指 向该分区的所有全局索引条目。执行表与分区的交换时，必须删除指向原数据的所有全局索引条目，再插入指向刚滑入的数据的 新条目。所以ALTER命令执行的工作量会大幅增加。

索引重建方法比使用UPDATE GLOBAL INDEXES子句来更新全局分区索引确实运行得更快一些,所以，UPDATE GLOBAL INDEXES是一 种允许用资源耗费的增加来换取可用性的选项。如果需要提供连续的可用性，这就是一个必要的选择。但是，你必须理解相关的 问题，并且适当地确定系统中其他组件的大小。具体地将，许多数据仓库过一段时间都会改为使用大批量的直接路径操作，而绕 过undo生成，如果允许的话，还会绕过redo生成。但是倘若使用UPDATE GLOBAL INDEXES,就不能绕过undo或redo生成。在使用这 个特性之前，需要检查确定组件大小所用的规则，从而确保这种方法在你的系统上确实能正常工作。

OLTP和全局索引
OLTP系统的特点是会频繁出现许多小的读写事务，一般来讲，在OLTP系统中，首要的是需要快速访问所需的行，而且数据完整性 很关键，另外可用性也非常重要。

在OLTP系统中，许多情况下全局索引很有意义。表数据可以按一个键（一个列键）分区。不过，你可能需要以多种不同的方式访 问数据。例如，可能会按表中的LOCATION来划分EMPLOYEE数据，但是还需要按以下列快速访问EMPLOYEE数据：
DEPARTMENT：部门的地理位置很分散。部门和位置之间没有任何关系。
EMPLOYEE_ID：尽管员工ID能确定位置，但是你不希望必须按EMPLOYEE_ID和LOCATION搜索，因为这样一来索引分区上将不能发生 分区消除。而且EMPLOYEE_ID本身必然是惟一的。
JOB_TITLE：JOB_TITLE和LOCATION之间没有任何关系。任何LOCATION上都可以出现所有JOB_TITLE值。
这里需要按多种不同的键来访问应用中不同位置的EMPLOYEE数据，而且速度至上。在一个数据仓库中，可以只使用这些键上的局 部分区索引，并使用并行索引区间扫描来快速收集大量数据。