資料庫從TXT檔案直接匯入或讀取 -- DIRECT PATH LOAD 與 External Tables

DIRECT PATH LOAD : 從TXT檔案直接匯入資料庫

ORACLE可以從純文字檔直接匯入到資料表，稱之為 DIRECT PATH LOAD ，有別於傳統的匯入方式 (CONVENTIONAL)，一定使用INSERT指令，一定有COMMIT。

1. 傳統的INSERT，會使用Database Buffer Cache，且資料寫入在HIGH WATER MARK範圍之內，DIRECT PATH LOAD跳過 DATABASE BUFFER CACHE，且寫入DATAFILE的時候， 從HIGH WATER MARK之後插入，稱之為 DATA SAVE
2. 傳統方式需要COMMIT， DIRECT PATH LOAD不用COMMIT
3. 傳統方式會受限於Referential Integrity Constraints ，且TRIGGER可被觸發。 DIRECT PATH LOAD必須停掉Referential Integrity Constraints，且TRIGGER不會觸發
4. DIRECT PATH LOAD時，TABLE會被LOCK不允許DML
5. DIRECT PATH LOAD插入資料時，只允許UNIQUE、PRIMARY KEY和NOT NULL 三個Constraints
6. DIRECT PATH LOAD 不能被用在CLUSTER TABLE

DIRECT PATH LOAD 需要下列條件或準備

1. INPUT DATA FILES : 純文字檔，通常以逗點來分隔欄位
2. CONTROL FILE : 跟資料庫的 CONTROLFILE沒有關係，是一個純文字檔，描述匯入的程序和指令
3. LOG FILES : 寫在前述 CONTROL FILE，紀錄匯入紀錄
4. BAD FILES : 寫在前述 CONTROL FILE，已經讀出，但被SQL LOADER拒絕不能匯入的資料寫入此，例如因為格式不合
5. REJECT FILES : 寫在前述 CONTROL FILE，因違反表格限制條件而不能匯入的資料寫入此
6. 匯入前，必須在資料庫先將TABLE建立好

在DB先建立下列表格

CREATE TABLE HR.DEPTS
(
  DEPTNO  NUMBER(2) NOT NULL,
  DNAME   VARCHAR2(14) ,
  LOC     VARCHAR2(13)
);

在主機中建立下列純文字檔，命名為 depts.txt

60,CONSULTING,TORONTO
70,HR,OXFORD
80,EDUCATION

在主機中建立下列 CONTROL FILE指令檔，命名為 DEPTS.CTL

load data
infile 'depts.txt'            --匯入的檔案
badfile 'depts.bad'           --壞掉的檔案放此
discardfile 'depts.dsc'       --被拒絕匯入的放此
append                        --以插入方式匯入到WATER MARK後面
into table depts              --插入新增到在資料庫中建好的表格
fields terminated by ','      --欄位，以逗點分隔
trailing nullcols             --空白的欄位，以NULL填寫
(deptno integer external(2),  --欄位的定義
dname,
loc)

指令檔建好以後，在命令列下執行下列命令

[oracle@db01 plsql]$ sqlldr userid=hr/XXXXX control=DEPTS.CTL direct=true

SQL*Loader: Release 11.2.0.1.0 - Production on Thu Jan 18 05:23:56 2018

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Load completed - logical record count 3.

匯入完成以後，看看紀錄檔產生些什麼

[oracle@db01 plsql]$ cat DEPTS.log

SQL*Loader: Release 11.2.0.1.0 - Production on Thu Jan 18 05:23:56 2018

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Control File:   DEPTS.CTL
Data File:      depts.txt
  Bad File:     depts.bad
  Discard File: depts.dsc
 (Allow all discards)

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

Table DEPTS, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect

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


Table DEPTS:
  3 Rows successfully loaded.
  0 Rows 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.

Bind array size not used in direct path.
Column array  rows :    5000
Stream buffer bytes:  256000
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:             3
Total logical records rejected:         0
Total logical records discarded:        0
Total stream buffers loaded by SQL*Loader main thread:        1
Total stream buffers loaded by SQL*Loader load thread:        0

Run began on Thu Jan 18 05:23:56 2018
Run ended on Thu Jan 18 05:23:58 2018

Elapsed time was:     00:00:01.15
CPU time was:         00:00:00.01

插入後的表格可看到結果如下

使用SQL*LOADER : 從TXT檔案直接匯入資料庫

使用 SQL*LOADER 需要下列條件或準備

1. DIRECTORY物件 : 需存在建好的目錄物件，並且賦予讀寫權限
2. INPUT DATA FILES : 純文字檔，通常以逗點來分隔欄位，需放在上述目錄物件指向之位置
3. SQL FILES : 在TOAD中執行即可

在上述目錄的位置放置要匯入的純文字檔，例如，在/home/oracle/bkup 目錄下有兩個純文字檔 empxt1.dat 和 empxt2.dat，如下圖所示

例如，文字檔有四個欄位

123,Dah-Lih,Jeng,01-jan-2018
124,Mary,Jeng,02-may-2017
125,Ezekiel,Jeng,12-feb-2015

然後，透過下列SQL將文字匯入到資料庫中

create table extab_employees
( employee_id   number(4),
  first_name    varchar2(20),
  last_name     varchar2(25),
  hire_date     date)
organization external
(  type  ORACLE_LOADER  DEFAULT DIRECTORY EXT_DIR
   ACCESS PARAMETERS
   (  records delimited by newline
      badfile EXT_DIR:'empxt%a_%p.bad'
      logfile EXT_DIR:'empxt%a_%p.log'
      fields terminated by ','
      missing field values are null
      ( employee_id, first_name, last_name,
        hire_date char date_format date mask "dd-mon-yyyy"))
        LOCATION ('empxt1.dat','empxt2.dat'))
        parallel reject limit unlimited;

匯入後查詢可確認是否成功

External Tables : 從資料庫直接讀取主機上以純文字建立之表格

External Tables 的使用是在有需要從主機的純文字檔讀取資料，可是又不想把該文字檔匯入成表格的時候，可以改用此方法，讀取外部的表格。該表格建立以後，可以用以查詢，使用於JOIN，或用以建立VIEWS或子查詢，但是不可以進行任何DML。

External Tables 的建立只要透過create table，配合 關鍵字 organization external ，就可以達成，但需要下列條件或準備

1. 建立DIRECTORY : 以SYSDBA登入，建立DIRECTORY以後，將讀寫權限ASSIGN給該TABLE的USER
2. 將純文字檔放至該DIRECTORY指定之目錄。
3. 建立 External Tables

先建立DIRECTORY (以SYSDBA)

CREATE OR REPLACE DIRECTORY 
EXT_DIR AS 
'/home/oracle/bkup';
GRANT READ, WRITE ON DIRECTORY EXT_DIR TO HR;

把剛才的純文字檔複製到該目錄去

[oracle@db01 plsql]$ cp depts.txt ../bkup/

複製完成後，使用TOAD或是SQLPLUS執行下列SQL，建立外部表格

create table new_dept
( deptno number(2),
  dname varchar2(14),
  loc varchar2(13))
  organization external (
  type oracle_loader
  default directory ext_dir
  access parameters
  ( records delimited by newline
    badfile 'depts.bad'
    discardfile 'depts.dsc'
    logfile 'depts.log'
    fields terminated by ','
    missing field values are null)
    location ('depts.txt'));

該語法跟前述 DIRECT PATH LOAD的CONTROL FILE非常接近，然後透過TOAD可以看到該表格已經建立成功

當然，上述的CONTROL FILE稍嫌複雜，也可反向操作，將前述使用SQL LOADER匯入表格的命令，改為讓它產生EXTERNAL TABLE的SQL

[oracle@db01 plsql]$  sqlldr userid=hr/xxxx control=DEPTS.CTL external_table=generate_only  

SQL*Loader: Release 11.2.0.1.0 - Production on Thu Jan 18 08:07:03 2018

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

執行完畢，去檢查所產生的LOG檔，該檔案有完整的步驟：建立目錄、執行SQL產生EXTERNAL TABLES ，但是在進行下一步前，使用編輯器將一些該檔案內的路徑、檔案名稱、TABLE名稱等資訊換成正確的路徑和名稱，同時先將文字檔放在目錄所指向的路徑上，最後再去執行該檔案的SQL就可順利建立一個EXTERNAL TABLE。

External Tables 支援以下的特性

1. 欄位加密 (Encrypted Columns)
2. Clustered Tables
3. Different partition at unload and load time, and others (see Notes)

與External Table 有關的資料字典

External Tables 有關的資料字典如下

1. (DBA/ALL/USER)_EXTERNAL_TABLES
2. (DBA/ALL/USER)_EXTERNAL_LOCATIONS
3. (DBA/ALL/USER)_TABLES, and others

ORACLE WITH 的用法

ORACLE 的 WITH 語法，可以將複雜的 SQL 語句，以 WITH 語法並給予其名稱，在其後的查詢中，透過呼叫該名稱，可反覆使用。

參考文章：其一、 其二 。

當查詢很複雜，又需要反覆使用子查詢的結果時，WITH便很方便。如果想查出各部門中，哪一部門的總薪資，高於所有部門的平均薪資，那總平均薪資就屬於反覆使用的子查詢。透過WITH語法，將查詢語句包圍在一個WITH語句中，透過反覆呼叫該語句，便可以達成子查詢。

下面的 SQL 想查詢在每個部門裡有多少人，原來的SQL如下

SELECT e.first_name,e.last_name AS employee_name,
       dc.department_id, dc.dept_count AS emp_dept_count
FROM   emp e,
       (SELECT department_id, COUNT(*) AS dept_count
        FROM   emp
        GROUP BY department_id) dc
WHERE  e.department_id = dc.department_id;

改用WITH語法後的SQL如下

WITH dept_count AS (
  SELECT department_id, COUNT(*) AS dept_count
  FROM   emp
  GROUP BY department_id)
SELECT e.first_name,e.last_name AS employee_name,
       dc.department_id,dc.dept_count AS emp_dept_count
FROM   emp e,
       dept_count dc
WHERE  e.department_id = dc.department_id;

從上面的語法，以WITH語法定義了一段子查詢，命名為 DEPT_COUNT，用在其後的SQL當中，如下圖所示。

要從本身具備階層關係的表格中，把部門員工和所屬主管及各部門人數拉出來，若用傳統SELF-JOIN的方式處理，其SQL如下：

SELECT e.last_name AS employee_name,
       dc1.dept_count AS emp_dept_count,
       m.last_name AS manager_name,
       dc2.dept_count AS mgr_dept_count
FROM   emp e,
       (SELECT department_id, COUNT(*) AS dept_count
        FROM   emp
        GROUP BY department_id) dc1,
       emp m,
       (SELECT department_id, COUNT(*) AS dept_count
        FROM   emp
        GROUP BY department_id) dc2
WHERE  e.department_id = dc1.department_id
AND    e.manager_id = m.employee_id
AND    m.department_id = dc2.department_id;

上述SQL的執行結果如下圖所示。

相同的查詢，若改以 WITH 的語法，看起來會較有結構。

WITH dept_count AS (
  SELECT department_id, COUNT(*) AS dept_count
  FROM   emp
  GROUP BY department_id)
SELECT e.last_name AS employee_name,
       dc1.dept_count AS emp_dept_count,
       m.last_name AS manager_name,
       dc2.dept_count AS mgr_dept_count
FROM   emp e,
       dept_count dc1,
       emp m,
       dept_count dc2
WHERE  e.department_id = dc1.department_id
AND    e.manager_id = m.employee_id
AND    m.department_id = dc2.department_id;

上述的語法可以結合兩種編譯註解 (HINTS) ，MATERIALIZE 使其以 global temporary table 的方式處理 WITH 的語句，INLINE 則是以內嵌的方式處理

WITH dept_count AS (
  SELECT /*+ MATERIALIZE */ department_id, COUNT(*) AS dept_count
  FROM   emp
  GROUP BY department_id)
SELECT ............ ;

WITH dept_count AS (
  SELECT /*+ INLINE */ department_id, COUNT(*) AS dept_count
  FROM   emp
  GROUP BY department_id)
SELECT ..............;

WITH 語法還可以讓重複使用的查詢，以代稱命名後，反覆呼叫使用。以下範例，透過 WITH 建立兩個子查詢，分別查詢部門總薪資(部門總成本或總薪資)，及總平均薪資，簡化後的 SQL ，主體SQL變得非常簡潔。

WITH 
  dept_costs AS (
    SELECT department_name, SUM(salary) dept_total
    FROM   emp e, departments d
    WHERE  e.department_id = d.department_id
    GROUP BY department_name),
  avg_cost AS (
    SELECT SUM(dept_total)/COUNT(*) avg
    FROM   dept_costs)
SELECT *
FROM   dept_costs
WHERE  dept_total > (SELECT avg FROM avg_cost)
ORDER BY department_name;

上述查詢得到銷售部門總薪資高於全體平均薪資。