Oracle 面試題與 SQL 調優精華：從基礎語法到高難度分析函數

Oracle 面試題與 SQL 調優精華：從基礎語法到高難度分析函數

本文匯總 Oracle 核心面試題、SQL 調優實戰原則、高難度分析函數應用、資料庫物理/邏輯結構、事務鎖機制、備份恢復架構等全維度知識點，適合面試準備、技術審核、日常查閱。

第一部分：高頻面試題精選 (含答案解析)

1. 日期格式與會話參數

-- 修改會話日期顯示
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

-- 永久生效需修改 SPFILE 或登入觸發器

2. RANK vs DENSE_RANK vs ROW_NUMBER

3. 約束查詢

SELECT constraint_name, constraint_type, search_condition
FROM user_constraints
WHERE table_name = 'PARENT';
-- C=Check, P=PK, R=FK, U=Unique

4. 表空間建立與管理

CREATE TABLESPACE mcp 
  DATAFILE 'E:\oracle\tablespace\data.dbf' SIZE 512M
  AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE
  SEGMENT SPACE MANAGEMENT AUTO;

5. VARCHAR2 字串處理

VARCHAR2 欄位不能直接用 TO_CHAR，需用 SUBSTR、INSTR、REPLACE、REGEXP_SUBSTR 等字串函數。

6. CASE WHEN 與 GROUP BY 規則

-- 必須在 GROUP BY 中重複完整 CASE 表達式，不能用別名
SELECT CASE WHEN deptno=10 THEN 'SALES' ELSE 'OTHER' END dept_grp, COUNT(*)
FROM emp
GROUP BY CASE WHEN deptno=10 THEN 'SALES' ELSE 'OTHER' END;

7. 快速備份表

CREATE TABLE emp_backup AS SELECT * FROM emp;           -- 含資料
CREATE TABLE emp_struct AS SELECT * FROM emp WHERE 1=2; -- 僅結構

8. 刪除重複資料 (保留 ROWID 最大)

DELETE FROM table_a a
WHERE a.rowid != (
  SELECT MAX(b.rowid) 
  FROM table_a b 
  WHERE a.key1 = b.key1 AND a.key2 = b.key2
);

第二部分：SQL 調優 19 條鐵律

第三部分：高難度 SQL 面試題實戰

題目 1：各科目最高分 + Java 最高分學生 + Java 第二高分學生

-- 表: scores(student, subject, score)
-- 1. 各科目最高分
SELECT subject, MAX(score) FROM scores GROUP BY subject;

-- 2. Java 最高分學生 (可能多名)
SELECT student FROM scores 
WHERE subject='Java' AND score = (SELECT MAX(score) FROM scores WHERE subject='Java');

-- 3. Java 第二高分學生 (使用 DENSE_RANK)
SELECT student FROM (
  SELECT student, score, 
         DENSE_RANK() OVER (ORDER BY score DESC) rk
  FROM scores WHERE subject='Java'
) WHERE rk = 2;

題目 2：雙表關聯聚合 (SELLER/COUPON/BAL)

-- table1: seller, non_seller
-- table2: seller, coupon, bal
-- 需求: 每個 non_seller 對應的 seller 在各 coupon 下的 bal 總和

SELECT DISTINCT t1.non_seller, t2.coupon,
       NVL((
         SELECT SUM(bal) FROM table2 
         WHERE seller IN (SELECT non_seller FROM table1 WHERE seller = t1.seller)
           AND coupon = t2.coupon
       ), 0) AS sum_bal
FROM table1 t1
CROSS JOIN (SELECT DISTINCT coupon FROM table2) t2
ORDER BY t2.coupon, t1.non_seller;

題目 3：日期分組累計 (OVER PARTITION BY)

-- 表 B: c1(date), c2(number)
-- 輸出: 每日小計 + 總計

SELECT NVL(TO_CHAR(c1,'YYYY-MM-DD'),'總計') AS c1, SUM(c2) AS c2
FROM B
GROUP BY ROLLUP(c1);
-- 或使用 GROUPING SETS
SELECT NVL(TO_CHAR(c1,'YYYY-MM-DD'),'總計') c1, SUM(c2) c2
FROM B
GROUP BY GROUPING SETS ((c1), ());

題目 4：並發事務可序列化驗證

-- T1: Session1 INSERT emp(102,2,60)
-- T2: Session2 UPDATE emp SET deptno=2 WHERE empno=100
-- T3: Session2 UPDATE dept SET sum_sal = (SELECT SUM(sal) FROM emp WHERE deptno=dept.deptno) WHERE deptno IN (1,2)
-- T4: Session1 UPDATE dept SET sum_sal = ... WHERE deptno IN (1,2)
-- T5: Session2 COMMIT
-- T6: Session2 SELECT SUM(sal) FROM emp GROUP BY deptno
-- T7: Session1 COMMIT
-- T8: SELECT SUM(sal) FROM emp GROUP BY deptno
-- T9: SELECT * FROM dept

-- 關鍵點: 
-- 1. T3 讀取 Session1 未提交的 INSERT (102,2,60) → 髒讀? 否，Oracle 預設 READ COMMITTED 阻塞
-- 2. T4 被 Session2 的 UPDATE dept 鎖阻塞
-- 3. 最終 dept.sum_sal 反映所有提交變更

題目 5：關聯更新與 NULL 陷阱

UPDATE student_grade s 
SET s.grade = (SELECT t.grade FROM student_makeup t WHERE s.id = t.id);

-- 學生 3 在 makeup 表無對應記錄 → 子查詢返回 NULL → grade 被設為 NULL
-- 正確寫法:
UPDATE student_grade s 
SET s.grade = NVL((SELECT t.grade FROM student_makeup t WHERE s.id = t.id), s.grade);
-- 或
UPDATE student_grade s 
SET s.grade = (SELECT t.grade FROM student_makeup t WHERE s.id = t.id)
WHERE EXISTS (SELECT 1 FROM student_makeup t WHERE s.id = t.id);

第四部分：資料庫核心架構必知必會

1. 物理 vs 邏輯結構

2. SGA 核心組件

• Buffer Cache：資料區塊快取，LRU 列表、Dirty List、DBWR 寫入
• Shared Pool：Library Cache (SQL/PLSQL 計畫)、Dictionary Cache、Result Cache
• Redo Log Buffer：變更向量快取，LGWR 寫入線上日誌
• Large Pool：RMAN、Shared Server、Parallel Execution 訊息緩衝
• Java Pool / Streams Pool / In-Memory Area：專用功能區

3. 背景行程核心職責

4. 鎖機制分層

• DML Locks (Enqueues)：TX (行鎖)、TM (表鎖)、UL (用戶鎖)
• Latches：Cache Buffers Chains、Library Cache、Shared Pool 分配
• Mutexes：Library Cache Pin、Cursor Pin (高並發輕量級)

5. 備份恢復核心

第五部分：OCP/OCM 風格選擇題精選

Q1: 下列哪個不屬於 DBA 職責？

A. 建立新用戶 B. 建立資料庫物件 C. 安裝 Oracle 軟體 D. 應用程式開發

Q2: RMAN 備份控制檔語法？

BACKUP CURRENT CONTROLFILE; 或 CONFIGURE CONTROLFILE AUTOBACKUP ON;

Q3: 10g 自動共享記憶體管理負責行程？

A. MMAN B. MMON C. MMNL D. PMON

Q4: PGA 不包含哪個組件？

A. 綁定資訊 B. 分析資訊 C. 會話變數 D. 排序空間 (屬 PGA 但非 PGA 組件分類)

Q5: 閃回有外鍵關係的兩個表最佳方法？

A. 先子後父 B. 先父後子 C. 單一操作閃回兩表 D. 無法保護外鍵

Q6: AWR 快照產生頻率？

A. 每小時 B. 每小時 (預設)，可配置 C. 按需 D. 定期生成

第六部分：實用 PL/SQL 代碼片段庫

1. 素數生成 (1-100)

DECLARE v_flag BOOLEAN;
BEGIN
  FOR i IN 1..100 LOOP
    v_flag := TRUE;
    FOR j IN 2..i-1 LOOP
      IF MOD(i,j)=0 THEN v_flag:=FALSE; END IF;
    END LOOP;
    IF v_flag THEN DBMS_OUTPUT.PUT_LINE(i); END IF;
  END LOOP;
END;

2. 游標條件更新

DECLARE CURSOR c1 IS SELECT * FROM emp FOR UPDATE OF sal;
BEGIN
  FOR r IN c1 LOOP
    IF r.job='MANAGER' AND (SELECT loc FROM dept WHERE deptno=r.deptno)='DALLAS' THEN
      UPDATE emp SET sal=sal*1.15 WHERE CURRENT OF c1;
    ELSIF r.job='CLERK' AND (SELECT loc FROM dept WHERE deptno=r.deptno)='NEW YORK' THEN
      UPDATE emp SET sal=sal*0.95 WHERE CURRENT OF c1;
    END IF;
  END LOOP;
END;

3. 觸發器薪資驗證

CREATE OR REPLACE TRIGGER trg_chk_sal
BEFORE UPDATE OF sal ON emp
FOR EACH ROW
BEGIN
  IF :NEW.sal <= :OLD.sal THEN
    RAISE_APPLICATION_ERROR(-20001,'薪資必須增長');
  ELSIF :NEW.sal > :OLD.sal * 1.1 THEN
    RAISE_APPLICATION_ERROR(-20002,'漲幅不得超過 10%');
  ELSIF :OLD.deptno IS NULL THEN
    RAISE_APPLICATION_ERROR(-20003,'無部門員工不可漲薪');
  END IF;
END;

4. 分頁查詢標準模板 (12c+ OFFSET/FETCH)

SELECT * FROM employees
ORDER BY hire_date DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
-- 11g 相容寫法:
SELECT * FROM (
  SELECT e.*, ROW_NUMBER() OVER (ORDER BY hire_date DESC) rn
  FROM employees e
) WHERE rn BETWEEN 21 AND 30;

Oracle 技術體系博大精深，面試與實戰的核心差異在於：「面試考察知識點覆蓋度與邏輯表達，實戰考察問題定位速度、解決方案穩健性、預防機制完備性」。建議建立個人知識庫：每解決一個生產問題、攻克一個調優案例、閱讀一份 MOS 文檔，即刻記錄並歸類，久而久之形成專屬「作戰手冊」。

1. 上一篇文章： Oracle REF CURSOR 與 SYS_REFCURSOR 完整實戰指南
2. 下一篇文章： Oracle Join 關聯機制深度解析：Hash Join、Nested Loops 與 Merge Join 效能優化指南

Oracle 教學

Oracle 入門掃盲：資料庫、實例、表空間、用戶、表的層級關係全圖解

Oracle 核心物件層級關係完整圖解：從安裝到建表的標準化

Windows下Oracle 11g R2安裝與卸載完整圖文教程

Windows下Oracle 11g R2安裝與卸載完整圖文

Ubuntu 12.04 Server 安裝 Oracle 11g R2 完整指南（含 VNC 圖形化安裝）

Ubuntu 12.04 Server 安裝 Oracle

Oracle 字串處理：使用 TRANSLATE 與 REGEXP_REPLACE 移除特定字元

在 Oracle 資料庫操作中，經常需要從字串中移除特定字元