Oracle 資料庫核心物件認知：表與視圖的建立、管理與進階特性

表是資料儲存的基本單元，視圖是邏輯層面的資料投影。掌握表與視圖的建立語法、儲存屬性、分區、臨時表、物化視圖等進階特性，是 Oracle 開發與管理的入門必修課。

一、表的建立與儲存屬性

基本建表語法

CREATE TABLE products (
  prod_id    NUMBER(4),
  prod_name  VARCHAR2(20),
  stock_qty  NUMBER(5,3)
);

完整儲存子句

CREATE TABLE sales (
  sale_id     NUMBER,
  prod_id     NUMBER,
  qty_sold    NUMBER(10,2),
  sale_date   DATE
)
TABLESPACE users
PCTFREE 10
PCTUSED 40
INITRANS 2
MAXTRANS 255
STORAGE (
  INITIAL 64K
  NEXT 1M
  MINEXTENTS 1
  MAXEXTENTS UNLIMITED
  PCTINCREASE 0
  FREELISTS 1
  FREELIST GROUPS 1
  BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
MONITORING;

二、進階表類型

1. 分區表

-- Range 分區
CREATE TABLE sales_range (
  sale_id   NUMBER,
  sale_date DATE,
  amount    NUMBER
)
PARTITION BY RANGE (sale_date) (
  PARTITION sales_q1_2024 VALUES LESS THAN (TO_DATE('2024-04-01','YYYY-MM-DD')),
  PARTITION sales_q2_2024 VALUES LESS THAN (TO_DATE('2024-07-01','YYYY-MM-DD')),
  PARTITION sales_q3_2024 VALUES LESS THAN (TO_DATE('2024-10-01','YYYY-MM-DD')),
  PARTITION sales_q4_2024 VALUES LESS THAN (TO_DATE('2025-01-01','YYYY-MM-DD'))
);

-- Interval 自動分區（12c+）
CREATE TABLE sales_interval (
  sale_id   NUMBER,
  sale_date DATE,
  amount    NUMBER
)
PARTITION BY RANGE (sale_date)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(
  PARTITION p0 VALUES LESS THAN (TO_DATE('2024-01-01','YYYY-MM-DD'))
);

2. 臨時表

-- 交易級臨時表：Commit 保留資料
CREATE GLOBAL TEMPORARY TABLE temp_session_data (
  session_id NUMBER,
  calc_result NUMBER
) ON COMMIT PRESERVE ROWS;

-- 會話級臨時表：Session 結束清空
CREATE GLOBAL TEMPORARY TABLE temp_txn_data (
  txn_id NUMBER,
  step   NUMBER
) ON COMMIT DELETE ROWS;

3. 物化視圖

CREATE MATERIALIZED VIEW mv_monthly_sales
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT TRUNC(sale_date,'MM') AS month, SUM(amount) AS total_sales
FROM sales
GROUP BY TRUNC(sale_date,'MM');

4. 索引組織表 (IOT)

CREATE TABLE iot_example (
  pk_id   NUMBER PRIMARY KEY,
  col1    VARCHAR2(50),
  col2    NUMBER
)
ORGANIZATION INDEX
OVERFLOW TABLESPACE users
PCTTHRESHOLD 20
INCLUDING col1;

三、視圖的建立與管理

基本視圖

CREATE VIEW v_high_value_products AS
SELECT prod_id, prod_name, list_price
FROM products
WHERE list_price > 1000
WITH READ ONLY;  -- 防止透過視圖修改基表

複雜視圖（含 Join、聚合）

CREATE VIEW v_dept_salary_summary AS
SELECT d.dept_name, COUNT(e.empno) AS emp_cnt, AVG(e.sal) AS avg_sal
FROM dept d
LEFT JOIN emp e ON d.deptno = e.deptno
GROUP BY d.dept_name;

Inline View / Subquery Factoring (WITH)

WITH dept_avg AS (
  SELECT deptno, AVG(sal) AS avg_sal FROM emp GROUP BY deptno
)
SELECT e.ename, e.sal, d.avg_sal,
       CASE WHEN e.sal > d.avg_sal THEN 'ABOVE' ELSE 'BELOW' END AS compare_avg
FROM emp e
JOIN dept_avg d ON e.deptno = d.deptno;

四、表與視圖的核心差異

五、常用管理操作

-- 查看表結構
DESC products;
SELECT * FROM user_tab_columns WHERE table_name='PRODUCTS';

-- 修改表
ALTER TABLE products ADD (category VARCHAR2(20));
ALTER TABLE products MODIFY (prod_name VARCHAR2(50));
ALTER TABLE products DROP COLUMN category;

-- 重命名
RENAME products TO prod_catalog;
ALTER TABLE prod_catalog RENAME COLUMN prod_name TO name;

-- 截斷/刪除
TRUNCATE TABLE staging_table;  -- 不產生 Undo、釋放 High Water Mark
DROP TABLE old_table PURGE;    -- 直接釋放空間，不進 Recyclebin

-- 視圖管理
DROP VIEW v_high_value_products;
CREATE OR REPLACE VIEW v_high_value_products AS ...;  -- 原子性替換

表設計原則：正規化至 3NF 為基線，效能需要時反正規化。
視圖設計原則：封裝複雜邏輯、實現安全邊界、提供穩定介面。
物化視圖：預計算昂貴聚合、支援 Query Rewrite、需維護 Refresh 策略。