2022年11月27日星期日

如何查詢 SQL ID

參考文獻 DocID :Doc ID 1627387.1

在進行 SQL STATEMENT的相關優化與效能問題，常常需要先查詢某STATEMENT的SQL ID

SELECT /* MY SQL */ * FROM dual;

SELECT sql_id, plan_hash_value, substr(sql_text,1,40) sql_text  
FROM  v$sql 
WHERE sql_text like 'SELECT /* MY SQL */%'

若是 SQL 區塊，則參考 DocID :Doc ID 741724.1

declare v1 number; 
begin 
  select /* CountTest */ count(*) into v1 from invalid_objects; 
end; 
/

然後用下列方式查詢SQL ID

SQL> select sql_id, sql_text from v$sql where sql_text like '%CountTest%'; 
/

2018年11月30日星期五

如何快速新增大筆資料至資料庫

如果測試時，希望一次新增10000筆資料該如何處理，以下整理一些常見語法

使用傳統FOR LOOP

       create table large(c1 char(1000));
       begin
          for i in 1..2000 loop
             insert into large values ('a row');
          end loop;
       end;
        /

使用 CONNECT BY LEVEL 語法

         CREATE TABLE test_tab (
              id          NUMBER,
              description VARCHAR2(50),
              CONSTRAINT test_tab_pk PRIMARY KEY (id)
          );

         INSERT /*+ APPEND */ INTO test_tab (id, description)
            SELECT level,
                   'Description for ' || level
            FROM   dual
             CONNECT BY level <= 10000;

2018年4月23日星期一

ORACLE AUDIT

參考文件

基本AUDIT設定
DBMS_AUDIT_MGMT增強套件(DBMS_AUDIT_MGMT)及稽核紀錄檔的處理

建立稽核專用Tablespace

        
    CREATE TABLESPACE AUX_AUDIT DATAFILE 
      'C:\SID\TBS_AUDIT_01.dbf' SIZE 900M AUTOEXTEND OFF
      LOGGING
      EXTENT MANAGEMENT LOCAL AUTOALLOCATE
      BLOCKSIZE 8K
      SEGMENT SPACE MANAGEMENT AUTO
      FLASHBACK ON;

AUDIT的環境設定

要設定的內容包含稽核預設的TABLESPACE，預計清除紀錄的時間間隔等。使用的工具為 DBMS_AUDIT_MGMT，必須以SYS身分使用SQLPLUS來執行。
ORACLE的AUDIT有兩個系統預設的TABLE，分別為 SYS.AUD$ (基本) 與SYS.FGA_LOG$ (細微)。此二TABLE的預設TABLESPACE皆為SYSTEM，若不先將其移至其他位置，若紀錄持續成長到佔滿空間，將使DB無法登入。

SELECT table_name, tablespace_name
FROM   dba_tables
WHERE  table_name IN ('AUD$', 'FGA_LOG$')
ORDER BY table_name;

更改標準AUDIT的位置

BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_location(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    audit_trail_location_value => ' AUX_AUDIT ');
END;

更改細微AUDIT的位置

BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_location(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
    audit_trail_location_value => ' AUX_AUDIT ');
END;

SELECT table_name, tablespace_name
FROM   dba_tables
WHERE  table_name IN ('AUD$', 'FGA_LOG$')
ORDER BY table_name;

設定清除區間

以下設定每12小時清除一次

BEGIN
  DBMS_AUDIT_MGMT.init_cleanup(
    audit_trail_type         => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
    default_cleanup_interval => 12 /* hours */);
END;

修改AUDIT基本參數(重啟DB)

開啟AUDIT前先使用SHOW PARAMETER AUDIT確認稽核檔的寫入位置有設定且路徑是對的。設定參數時須以SYS身分在DB主機以SQLPLUS執行。執行完畢需重啟DB。

SQL> alter system set audit_sys_operations=TRUE scope=spfile;

SQL> alter system set audit_trail=xml,extended scope=spfile;

SQL> shutdown immediate;

設定系統稽核範圍

以下CONSOLE以SYSDBA身分執行稽核指令稿如下：將下列寫成一個SCRIPT檔，稱為audit.sql
設定稽核範圍
--紀錄以下使用者的登錄(假設想記錄SUPERUSER的動作)

noaudit all;  --先取消所有稽核選項，重新設定選項
audit session by system;
audit session by sys;
audit session by SUPERUSER;

--紀錄重要異動
audit select any table by SUPERUSER;
audit insert any table by SUPERUSER;
audit delete any table by SUPERUSER;
audit update any table by SUPERUSER;

--紀錄對AUDIT TRAIL 本身之異動，以保護稽核紀錄
AUDIT INSERT, UPDATE, DELETE ON sys.aud$ BY ACCESS;

與AUDIT內容有關的資料字典

以下資料字典可觀察DB裡被稽核的內容。

Statement: DBA_STMT_AUDIT_OPTS
Object: DBA_OBJ_AUDIT_OPTS, USER_OBJ_AUDIT_OPTS
Privilege: DBA_PRIV_AUDIT_OPTS

與AUDIT設定有關的資料字典

AUDIT設定的項目可由dba_audit_mgmt_config_params 查詢，下列SQL查詢目前的設定值 SQL> select * from dba_audit_mgmt_config_params;

設定XML AUDIT FILE大小

由圖可知預設值是 10000kb，透過下列SQL可調整為15000kb

BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_property(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
    audit_trail_property       => DBMS_AUDIT_MGMT.OS_FILE_MAX_SIZE,
    audit_trail_property_value => 15000);
END;

設定XML AUDIT FILE最大天數

以下將其天數由五天改為10天。

BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_property(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML,
    audit_trail_property       => DBMS_AUDIT_MGMT.OS_FILE_MAX_AGE,
    audit_trail_property_value => 10);
END;

稽核紀錄查詢

audit_actions

稽核紀錄中的欄位ACTION是用代碼顯示，每個代碼的意義存放於此TABLE，代碼共計約118筆

V$XML_AUDIT_TRAIL

若稽核的選項是 XML, EXTENDED，它會將紀錄以XML方式記錄於指定位置，但可透過此VIEW來查詢它的內容，其中有DB_USER, OS_USER, TERMINAL, OBJECT_SCHEMA, OBJECT_NAME 和 SQL_TEXT等欄位。

稽核紀錄管理

稽核紀錄如果不處理，會持續成長到影響系統效能。紀錄可運用時間戳記來管理，為刪除或整理設立一個時間點。 AUDIT的時間戳記紀錄在dba_audit_mgmt_last_arch_ts，可透過指令來設定 : SQL> SELECT * FROM dba_audit_mgmt_last_arch_ts;

設定稽核的時間戳記

使用時間戳記來管理紀錄,先設定最後截止時間戳記,以下設定五天前,設定後可清除在此戳記前的資料

BEGIN
  DBMS_AUDIT_MGMT.set_last_archive_timestamp(
    audit_trail_type  => DBMS_AUDIT_MGMT. AUDIT_TRAIL_XML,
    last_archive_time => SYSTIMESTAMP-5);
END;

運用時間戳記管理紀錄

紀錄可透過CLEAR_LAST_ARCHIVE_TIMESTAMP來管理。以下將所有紀錄皆刪除(參數 use_last_arch_timestamp 設為FALSE )。

BEGIN
  DBMS_AUDIT_MGMT.clean_audit_trail(
   audit_trail_type        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML,
   use_last_arch_timestamp => FALSE);
END;

以下將時間戳記前的所有紀錄皆刪除(參數 use_last_arch_timestamp 設為TRUE)。

BEGIN
  DBMS_AUDIT_MGMT.clean_audit_trail(
   audit_trail_type        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML,
   use_last_arch_timestamp => TRUE);
END;

以下將時間戳記前的所有紀錄皆刪除 (使用CLEAR_LAST_ARCHIVE_TIMESTAMP)

BEGIN
  DBMS_AUDIT_MGMT.clear_last_archive_timestamp(
    audit_trail_type     =>  DBMS_AUDIT_MGMT. AUDIT_TRAIL_XML);
END;

2018年1月18日星期四

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

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

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

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

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

INPUT DATA FILES : 純文字檔，通常以逗點來分隔欄位
CONTROL FILE : 跟資料庫的 CONTROLFILE沒有關係，是一個純文字檔，描述匯入的程序和指令
LOG FILES : 寫在前述 CONTROL FILE，紀錄匯入紀錄
BAD FILES : 寫在前述 CONTROL FILE，已經讀出，但被SQL LOADER拒絕不能匯入的資料寫入此，例如因為格式不合
REJECT FILES : 寫在前述 CONTROL FILE，因違反表格限制條件而不能匯入的資料寫入此
匯入前，必須在資料庫先將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 需要下列條件或準備

DIRECTORY物件 : 需存在建好的目錄物件，並且賦予讀寫權限
INPUT DATA FILES : 純文字檔，通常以逗點來分隔欄位，需放在上述目錄物件指向之位置
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 ，就可以達成，但需要下列條件或準備

建立DIRECTORY : 以SYSDBA登入，建立DIRECTORY以後，將讀寫權限ASSIGN給該TABLE的USER
將純文字檔放至該DIRECTORY指定之目錄。
建立 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 支援以下的特性

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

與External Table 有關的資料字典

External Tables 有關的資料字典如下

(DBA/ALL/USER)_EXTERNAL_TABLES
(DBA/ALL/USER)_EXTERNAL_LOCATIONS
(DBA/ALL/USER)_TABLES, and others

2018年1月15日星期一

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;

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

2017年12月27日星期三

ORACLE分群彙總函數 ROLLUP、CUBE、GROUPING、GROUPING SET

GROUP的功能配合加總函數，可對資料分群後，以群為單位加總，如果資料當中，用以分群的欄位數超過一個以上，對分群欄位如何做到個別加總及最後的完整加總? ROLLUP、CUBE和GROUPING SET提供了很好的方法。

參考文件：文件一、文件二、文件三

以下範例假設有某資料表，有三個屬性欄位(如部門及子部門等)和一個數值欄位(如薪水或價格等)，該資料表的SQL請參考文件一。

對前三個欄位做群組，列出欄位數及合計

SELECT YEAR,
       AREA,
       DEPARTMENT_ID,
       COUNT(*) AS num_rows,
       SUM(sales) AS TOTAL_SALES
FROM   total_sales;

ROLLUP : 分群部分加總函數　　　

ROLLUP 函數可對一個以上的欄位進行分項加總，進行加總時，從最右邊的欄位進行分項加總。若 ROLLUP 有 N 個欄位，會產生 N+1 個階層的子項加總，以下針對年度與區域做子項加總，求分年和分區域的總和，查詢部分截圖如下，完整結果請參考結果檔案。

SELECT YEAR,
       AREA,
       SUM(sales) AS TOTAL_SALES
FROM   total_sales
GROUP BY ROLLUP (YEAR, AREA)
ORDER BY YEAR, AREA;

使用三個欄位進行分項加總，共有4個階層的子加總，其結果檔案和截圖如下所示。

SELECT YEAR,
       AREA,
       DEPARTMENT_ID,
       SUM(sales) AS TOTAL_SALES
FROM   total_sales
GROUP BY ROLLUP (YEAR, AREA, DEPARTMENT_ID)
ORDER BY YEAR, AREA, DEPARTMENT_ID;

分項加總也可以搭配一般的群組GROUP，其結果如檔案所示。

SELECT YEAR, AREA, DEPARTMENT_ID,
       SUM(sales) AS TOTAL_SALES
FROM   total_sales
GROUP BY YEAR, ROLLUP (AREA, DEPARTMENT_ID)
ORDER BY YEAR, AREA, DEPARTMENT_ID;

使用GROUP BY 和 UNION ALL 取代 ROLLUP 函數

上述分項加總的ROLLUP函數也有替代方案。若是三個欄位進行ROLLUP，則需四組SQL進行 UNION ALL 運算，第一組列出所有欄位，第二組從ROLLUP最右邊的欄位起，將該欄位以NULL取代，依此類推。下列ROLLUP查詢

SELECT YEAR,
       AREA,
       DEPARTMENT_ID,
       SUM(sales) AS TOTAL_SALES
FROM   total_sales
GROUP BY ROLLUP (YEAR, AREA, DEPARTMENT_ID)
ORDER BY YEAR, AREA, DEPARTMENT_ID;

與使用替代方案的UNION ALL 的下列查詢，產生完全相同的結果。

Select YEAR,AREA,DEPARTMENT_ID,sum(SALES) AS TOTAL_SALES from TOTAL_SALES group by YEAR,AREA,DEPARTMENT_ID  
union all  
Select YEAR,AREA,null,sum(SALES) AS TOTAL_SALES from TOTAL_SALES group by YEAR,AREA  
union all  
Select YEAR,null,null,sum(SALES) AS TOTAL_SALES from TOTAL_SALES group by YEAR  
union all  
Select null,null,null,sum(SALES) AS TOTAL_SALES from TOTAL_SALES  
order by 1,2,3;

CUBE 函數

使用 CUBE 函數，是對函數內的參數，列舉完整的加總結果。若是 N 個參數的 CUBE 函數，會產生 2^N 個子加總的組合。

SELECT YEAR, AREA,
       SUM(sales) AS TOTAL_SALES
FROM   total_sales
GROUP BY CUBE (YEAR, AREA)
ORDER BY YEAR, AREA;

上述CUBE函數的結果。當參數增加，子加總的結果也會大幅增加，前述兩個參數共產生18筆，若改為三個參數如下，則共產生198筆資料

SELECT YEAR, AREA, DEPARTMENT_ID,
       SUM(sales) AS TOTAL_SALES
FROM   total_sales
GROUP BY CUBE (YEAR, AREA, DEPARTMENT_ID)
ORDER BY YEAR, AREA,DEPARTMENT_ID;

使用GROUP BY 和 UNION ALL 取代 CUBE 函數的替代方法

前述三個參數的 CUBE 函數，若是使用 GROUP BY 和 UNION ALL，則需要 2^3 個 SQL，也就是 8 個SQL來組成替代方案。

Select YEAR,AREA,DEPARTMENT_ID,sum(SALES) AS TOTAL_SALES from TOTAL_SALES group by YEAR,AREA,DEPARTMENT_ID 
union all  
Select YEAR,AREA,null,sum(SALES) AS TOTAL_SALES from TOTAL_SALES group by YEAR, AREA 
union all  
Select YEAR,null,DEPARTMENT_ID,sum(SALES) AS TOTAL_SALES from TOTAL_SALES group by YEAR, DEPARTMENT_ID  
union all  
Select YEAR,null,null,sum(SALES) AS TOTAL_SALES from TOTAL_SALES group by YEAR  
union all  
Select null,AREA,DEPARTMENT_ID,sum(SALES) AS TOTAL_SALES from TOTAL_SALES group by AREA, DEPARTMENT_ID  
union all  
Select null,AREA,null,sum(SALES) AS TOTAL_SALES from TOTAL_SALES group by AREA  
union all  
Select null,null,DEPARTMENT_ID,sum(SALES) AS TOTAL_SALES from TOTAL_SALES group by DEPARTMENT_ID  
union all  
Select null,null,null,sum(SALES) AS TOTAL_SALES from TOTAL_SALES;

上述UNION的結果，共產生 198 筆資料。

GROUPING Functions

在所產生的子項加總資料列，所進行加總的欄位以NULL顯示，但是若原有資料本身就有NULL值，會難以分辨是原有的NULL還是因為加總而產生的NULL，GROUPING函數用來顯示該欄位是否因為執行加總(ROLLUP、CUBE)而產生NULL值，它接受一個參數(欄位)，1表示該欄位的NULL由加總產生，0 表示欄位是其他的值。

SELECT YEAR,
       AREA,
       SUM(sales) AS TOTAL_SALES,
       GROUPING(YEAR) as Grouping_Year,
       GROUPING(AREA) as Grouping_Area,
FROM   total_sales
GROUP BY ROLLUP (YEAR, AREA)
ORDER BY YEAR, AREA;

上述查詢結果多了兩個欄位Grouping_Year與Grouping_Area。當該欄位顯示為1，表示該分項子加總由該欄位產生，而其對應的欄位亦為NULL。

下圖顯示，Grouping_Area 的值為1，表示欄位Area的NULL是因為加總而產生，而子項加總 27435.34 也是由2015年各區域的總和而產生。

有此欄位，就可以對子項加總的結果，篩選你要的結果，比方說，你只想看2015年各分項加總以及總結的數量，可用下列方式，從113筆資料當中，篩選出7筆2015年的分項總計。

SELECT YEAR, AREA, DEPARTMENT_ID, SUM(sales) AS TOTAL_SALES,
       GROUPING(YEAR) as Grouping_Year,
       GROUPING(AREA) as Grouping_Area,
       GROUPING(DEPARTMENT_ID) as Grouping_Dept
FROM   total_sales
WHERE YEAR=2015
GROUP BY ROLLUP (YEAR, AREA, DEPARTMENT_ID)
HAVING GROUPING(YEAR)=1 or GROUPING(AREA)=1 or GROUPING(DEPARTMENT_ID)=1
ORDER BY YEAR, AREA, DEPARTMENT_ID;

其分項結果如下圖所示。

GROUPING_ID

此函數接受一個以上的欄位為參數，通常與ROLLUP或是CUBE的參數相同，它用來顯示加總欄位的階層，0表示第一階。以下使用兩個欄位以CUBE函數查詢所有分項加總及其組合。由結果顯示共有4個階層。

SELECT YEAR, AREA,
       SUM(sales) AS TOTAL_SALES, 
       GROUPING_ID(YEAR, AREA) AS GROUPING_LEVEL
FROM   total_sales
GROUP BY CUBE (YEAR, AREA)
ORDER BY YEAR, AREA;

GROUPING SETS

有時我們只想得到某些欄位組合的分項加總， GROUPING SETS用於此時，可將想得到的欄位組合以括號組合在一起，就只會列出該組合的子加總，例如，若只想得到(年度，區域)及(年度，部門)這兩種組合的子加總時，便可用 GROUPING SETS((YEAR, AREA), (YEAR, DEPARTMENT_ID))來取得，如下範例所示，其結果請參考。

SELECT YEAR, AREA, DEPARTMENT_ID,
       SUM(sales) AS TOTAL_SALES,
       GROUPING_ID(YEAR, AREA, DEPARTMENT_ID) AS GROUPING_LEVEL
FROM   total_sales
GROUP BY GROUPING SETS  ((YEAR, AREA),(YEAR, DEPARTMENT_ID))
ORDER BY YEAR, AREA,DEPARTMENT_ID;

Composite Columns

在ROLLUP與CUBE函數中的參數可用欄位組合方式來取得特定組合的加總，例如ROLLUP((YEAR, AREA), DEPARTMENT_ID)或是CUBE((YEAR, AREA), DEPARTMENT_ID)，如此，這些以括號放在一起的欄位就會一起列出。其結果請參考。

SELECT YEAR, AREA, DEPARTMENT_ID,
       SUM(sales) AS TOTAL_SALES,
       GROUPING_ID(YEAR, AREA, DEPARTMENT_ID) AS GROUPING_LEVEL
FROM   total_sales
GROUP BY CUBE  ((YEAR, AREA), DEPARTMENT_ID)
ORDER BY YEAR, AREA,DEPARTMENT_ID;

Concatenated Groupings

這是把組合的欄位，以交錯的方式組合輸出。多個GROUPING SETS，透過逗點組合在一起，例如GROUPING SETS(id1, id2), GROUPING SETS(id3, id4) ，會產生的組合是：

(id1, id3)

(id1, id4)

(id2, id3)

(id2, id4)

依此類推，以下範例就會產生出這些欄位的組合和子項加總，其結果請參考

(YEAR, DEPARTMENT_ID)

(YEAR, MANAGER_ID)

(AREA, DEPARTMENT_ID)

(AREA, MANAGER_ID)

SELECT YEAR, AREA, DEPARTMENT_ID,MANAGER_ID,
       SUM(sales) AS TOTAL_SALES,
       GROUPING_ID(YEAR, AREA, DEPARTMENT_ID) AS GROUPING_LEVEL
FROM   total_sales
GROUP BY GROUPING SETS  (YEAR, AREA),GROUPING SETS (DEPARTMENT_ID, MANAGER_ID)
ORDER BY YEAR, AREA,DEPARTMENT_ID;

2017年12月23日星期六

階層式查詢

參考連結： ORACLE 文件、簡單範例一、簡單範例二

語法架構

階層式查詢用於以下類型結構的資料：

資料彼此間存在從屬關係：比方說，員工資料表：員工編號、姓名、部門、主管編號。資料表內的員工可依照從屬關係畫出樹狀圖。

或是親屬資料表：姓名、性別、父母等

語法結構如下：

select ... start with initial-condition connect by nocycle recurse-condition

select ... connect by recurse-condition

select ... start with initial-condition connect by nocycle recurse-condition

select ... connect by recurse-condition

範例：列出所有從屬關係

此語法先從滿足 initial-condition 的根節點開始，以DFS(Deep First Search) 或是 LDR(Left-Dump-Right)的順序，列印出根節點的子節點且符合recurse-condition的其餘子節點。

以下範例，從第一筆資料起，列出所有從屬關係

      SELECT last_name, employee_id, manager_id, LEVEL
      FROM emp  
      CONNECT BY PRIOR employee_id =  manager_id
      ORDER SIBLINGS BY last_name;

UNARY OPERATOR PRIOR 的用法

CONNECT BY PRIOR employee_id = manager_id

PRIOR可以放在欄位 employee_id 前或是欄位 manager_id 之前，它是一個 UNARY OPERATOR ，放在EMPLOYEE_ID前，代表後續其他的子節點，需以根節點的這個EMPLOYEE_ID，當作子節點的MANAGER_ID，因此PRIOR放在EMPLOYEE_ID和放在MANAGER_ID所得的結果是不同的。

為根節點加上起始條件

根節點的起始條件用 START....WITH 語法來定義，可定義符合該條件的根節點，才會被查詢出來。

以下列出，從員工代碼 100 開始的員工，列出所有員工的從屬關係

      SELECT last_name, employee_id, manager_id, LEVEL
      FROM emp  
      START WITH employee_id = 100
      CONNECT BY PRIOR employee_id =  manager_id
      ORDER SIBLINGS BY last_name;

其中 LEVEL 是用於此階層查詢中的虛擬欄位，表示由根節點開始的階層編號，而根節點為 1 。

CONNECT_BY_ROOT 與 SYS_CONNECT_BY_PATH

CONNECT_BY_ROOT 可以列出根節點的欄位，而 SYS_CONNECT_BY_PATH 則可列出由根節點到子節點的完整路徑。參看下列範例

   SELECT last_name "Employee", CONNECT_BY_ROOT last_name "Manager",
   LEVEL-1 "Pathlen", SYS_CONNECT_BY_PATH(last_name, '/') "Path"
   FROM emp
   WHERE LEVEL > 1 and department_id = 100
   CONNECT BY PRIOR employee_id = manager_id;

上述範例列出所有子節點的管理者，並列出從根節點到子節點的完整階層。

訂閱：文章 (Atom)

ORACLE DB 的學習者們

2022年11月27日 星期日

如何查詢 SQL ID

2018年11月30日 星期五

如何快速新增大筆資料至資料庫

2018年4月23日 星期一

ORACLE AUDIT

建立稽核專用Tablespace

AUDIT的環境設定

更改標準AUDIT的位置

更改細微AUDIT的位置

設定清除區間

修改AUDIT基本參數(重啟DB)

設定系統稽核範圍

與AUDIT內容有關的資料字典

與AUDIT設定有關的資料字典

設定XML AUDIT FILE大小

設定XML AUDIT FILE最大天數

稽核紀錄查詢

audit_actions

V$XML_AUDIT_TRAIL

稽核紀錄管理

設定稽核的時間戳記

運用時間戳記管理紀錄

2018年1月18日 星期四

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

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

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

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

與External Table 有關的資料字典

2018年1月15日 星期一

ORACLE WITH 的用法

2017年12月27日 星期三

ORACLE分群彙總函數 ROLLUP、CUBE、GROUPING、GROUPING SET

ROLLUP : 分群部分加總函數

使用GROUP BY 和 UNION ALL 取代 ROLLUP 函數

CUBE 函數

使用GROUP BY 和 UNION ALL 取代 CUBE 函數的替代方法

GROUPING Functions

GROUPING_ID

GROUPING SETS

Composite Columns

Concatenated Groupings

2017年12月23日 星期六

階層式查詢

語法架構

範例：列出所有從屬關係

UNARY OPERATOR PRIOR 的用法

為根節點加上起始條件

CONNECT_BY_ROOT 與 SYS_CONNECT_BY_PATH

CODEBOOK

2022年11月27日星期日

2018年11月30日星期五

2018年4月23日星期一

2018年1月18日星期四

2018年1月15日星期一

2017年12月27日星期三

ROLLUP : 分群部分加總函數　　　

2017年12月23日星期六