數據庫對象與權限管理-視圖與索引管理

一、視圖（View）管理

1. 視圖的定義與本質

視圖（View）是Oracle數據庫中的邏輯表，它不直接存儲數據，而是通過預定義的SQL查詢動態生成結果集。視圖的本質可以理解為：

虛擬表：用戶可像操作普通表一樣查詢視圖
查詢模板：保存復雜的SELECT語句邏輯
安全屏障：隱藏底層表結構和敏感數據

2. 視圖的核心作用

應用場景	說明
簡化復雜查詢	將多表關聯、聚合計算等操作封裝為簡單查詢
數據權限控制	僅暴露部分列或行數據（如屏蔽薪資字段）
邏輯抽象層	業務系統無需關心物理表結構變化
數據整合	合并多個異構數據源（需配合物化視圖）

3. 視圖的創建與維護

基礎語法

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW schema.view_name 
[(column1, column2...)]
AS SELECT_statement
[WITH CHECK OPTION] 
[WITH READ ONLY];

參數解析

OR REPLACE：覆蓋同名視圖
FORCE：即使基表不存在也強制創建（默認NOFORCE）
WITH CHECK OPTION：插入/更新數據時需滿足視圖條件
WITH READ ONLY：禁止通過視圖修改數據

創建示例

-- 創建高薪員工視圖（薪資>=10000）
CREATE OR REPLACE VIEW v_high_salary 
AS
SELECT employee_id, name, salary, department_id
FROM employees
WHERE salary >= 10000
WITH CHECK OPTION;

視圖維護操作

-- 查看視圖定義
SELECT text FROM user_views WHERE view_name = 'V_HIGH_SALARY';-- 修改視圖（使用OR REPLACE）
CREATE OR REPLACE VIEW v_high_salary AS ...;-- 刪除視圖
DROP VIEW v_high_salary;

4. 視圖更新限制

以下類型的視圖不允許直接進行DML操作：

包含聚合函數（SUM/AVG等）
包含GROUP BY或HAVING子句
包含DISTINCT關鍵字
包含ROWNUM偽列
多表連接查詢（部分情況）

示例：只讀視圖創建

CREATE VIEW v_dept_stats AS
SELECT department_id, AVG(salary) avg_sal
FROM employees
GROUP BY department_id
WITH READ ONLY;

二、索引（Index）管理

1. 索引的核心價值

索引是提高查詢性能的數據結構，其作用類似書籍目錄。Oracle支持多種索引類型：

索引類型	特點
B-Tree索引	默認類型，適合高基數列（唯一值多）
位圖索引	低基數列（如性別），適合OLAP系統
函數索引	基于列的函數表達式創建（如`UPPER(name)`）
組合索引	多列聯合索引，列順序影響查詢效率

2. 索引的創建與維護

基礎語法

CREATE [UNIQUE] [BITMAP] INDEX schema.index_name
ON table_name (column1 [ASC|DESC], ...)
[TABLESPACE tablespace_name]
[STORAGE (...)]
[ONLINE];  -- 在線創建不影響業務

創建示例

-- 單列B-Tree索引
CREATE INDEX idx_emp_salary ON employees(salary);-- 組合索引
CREATE INDEX idx_emp_dept_sal ON employees(department_id, salary DESC);-- 函數索引
CREATE INDEX idx_emp_name_upper ON employees(UPPER(name));

索引維護操作

-- 重建索引（優化存儲）
ALTER INDEX idx_emp_salary REBUILD;-- 監控索引使用
SELECT * FROM v$object_usage WHERE index_name = 'IDX_EMP_SALARY';-- 刪除索引
DROP INDEX idx_emp_salary;

3. 索引優化策略

創建原則

高頻查詢字段：WHERE、JOIN、ORDER BY常用列
選擇高選擇性列：區分度高的列（如ID）
避免過度索引：單表索引不超過5個
組合索引列順序：等值查詢列在前，范圍查詢列在后

性能驗證方法

-- 查看執行計劃
EXPLAIN PLAN FOR 
SELECT * FROM employees WHERE salary >= 10000;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);-- 統計信息收集
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');

4. 索引的代價

存儲開銷：索引占用表空間的20%-30%
DML性能下降：每次INSERT/UPDATE/DELETE需維護索引
優化器誤判：低效索引可能導致執行計劃劣化

三、視圖、索引實戰

1. 環境準備

基礎表結構（已提供）

-- 員工表（含薪資、部門等信息）
CREATE TABLE employees (employee_id NUMBER(6) PRIMARY KEY,name VARCHAR2(50) NOT NULL,email VARCHAR2(50),phone_number VARCHAR2(20),hire_date DATE NOT NULL,job_id VARCHAR2(10),salary NUMBER(8,2),commission_pct NUMBER(2,2),manager_id NUMBER(6),department_id NUMBER(4)
);-- 部門表
CREATE TABLE departments (department_id NUMBER(4) PRIMARY KEY,department_name VARCHAR2(30) NOT NULL,manager_id NUMBER(6),location_id NUMBER(4)
);

模擬數據（供驗證）

-- 插入部門數據
INSERT INTO departments VALUES (10, 'IT', 100, 1700);
INSERT INTO departments VALUES (20, 'Sales', 101, 1800);-- 插入員工數據
INSERT INTO employees VALUES 
(100, 'Alice', 'alice@company.com', '515.123.4567', DATE '2020-01-01', 'IT_PROG', 15000, NULL, NULL, 10);
INSERT INTO employees VALUES 
(101, 'Bob', 'bob@company.com', '515.123.4568', DATE '2019-06-01', 'SA_MAN', 12000, 0.2, 100, 20);

2. 視圖高級應用實戰

1. 安全視圖：隱藏敏感字段

CREATE VIEW v_hr_employee AS
SELECT employee_id,name,email,phone_number,hire_date,job_id,department_id
FROM employees
WITH CHECK OPTION;  -- 防止通過視圖插入不符合條件的數據-- 測試查詢
SELECT * FROM v_hr_employee WHERE department_id = 10;

2. 計算視圖：薪資分析

CREATE OR REPLACE VIEW v_employee_annual_income AS
SELECT employee_id,name,salary,commission_pct,-- 計算年薪：月薪*13 + 傭金(salary * 13 + NVL(salary * commission_pct, 0)) AS annual_income,department_id
FROM employees
WHERE salary IS NOT NULL;-- 查詢年薪超過15萬的員工
SELECT * FROM v_employee_annual_income 
WHERE annual_income > 150000
ORDER BY annual_income DESC;

3. 多表關聯視圖：員工詳情

CREATE VIEW v_emp_details AS
SELECT e.employee_id,e.name,d.department_name,e.hire_date,TRUNC(MONTHS_BETWEEN(SYSDATE, e.hire_date)/12) AS years_of_service
FROM employees e
JOIN departments d ON e.department_id = d.department_id;-- 查詢IT部門工齡超過3年的員工
SELECT * FROM v_emp_details 
WHERE department_name = 'IT' AND years_of_service > 3;

4. 帶權限的物化視圖

CREATE MATERIALIZED VIEW mv_dept_salary_stats
REFRESH COMPLETE START WITH SYSDATE NEXT SYSDATE + 1
AS
SELECT d.department_name,COUNT(e.employee_id) AS emp_count,AVG(e.salary) AS avg_salary,MAX(e.salary) AS max_salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name;-- 查詢物化視圖
SELECT * FROM mv_dept_salary_stats WHERE avg_salary > 10000;

3. 索引深度優化實戰

1. 高頻查詢優化

-- 創建函數索引
CREATE INDEX idx_emp_email_upper ON employees(UPPER(email));-- 使用索引的查詢
SELECT * FROM employees 
WHERE UPPER(email) = UPPER('alice@company.com');  -- 命中索引

2. 組合索引設計

-- 創建組合索引（部門ID + 薪資降序）
CREATE INDEX idx_emp_dept_salary ON employees(department_id, salary DESC);-- 高效查詢：IT部門薪資前10名
SELECT * FROM (SELECT * FROM employees WHERE department_id = 10 ORDER BY salary DESC
) WHERE ROWNUM <= 10;  -- 利用索引避免全表排序

3. 覆蓋索引加速統計

-- 創建覆蓋索引
CREATE INDEX idx_emp_dept_covering ON employees(department_id, employee_id);-- 查詢可直接從索引獲取數據
SELECT department_id, COUNT(employee_id) 
FROM employees 
GROUP BY department_id;  -- 使用INDEX FAST FULL SCAN

4. 索引監控與維護

-- 步驟1：監控索引使用情況
ALTER INDEX idx_emp_dept_salary MONITORING USAGE;-- 步驟2：分析無效索引
SELECT * FROM v$object_usage 
WHERE index_name = 'IDX_EMP_DEPT_SALARY'
AND used = 'NO';  -- 若長期未使用，考慮刪除-- 步驟3：重建索引優化空間
ALTER INDEX idx_emp_dept_salary REBUILD ONLINE;

4. 執行計劃分析案例

1. 未使用索引的慢查詢

EXPLAIN PLAN FOR
SELECT * FROM employees 
WHERE UPPER(name) = 'ALICE';  -- 未使用索引SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
/* 輸出關鍵信息：TABLE ACCESS FULL | EMPLOYEES
*/

2. 優化后使用函數索引

-- 創建函數索引
CREATE INDEX idx_emp_name_upper ON employees(UPPER(name));-- 再次分析
EXPLAIN PLAN FOR
SELECT * FROM employees 
WHERE UPPER(name) = 'ALICE';SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
/* 輸出關鍵信息：INDEX RANGE SCAN | IDX_EMP_NAME_UPPER
*/

四、開發注意事項

1. 視圖更新陷阱

-- 錯誤示例：嘗試更新計算視圖
UPDATE v_employee_annual_income 
SET annual_income = 200000 
WHERE employee_id = 100;  -- 將拋出ORA-01733錯誤

2. 索引設計禁忌

過度索引：在頻繁更新的列上創建多個索引
冗余索引：已有組合索引 (a,b) 再單獨創建 (a)
無效索引：在性別等低基數列上使用B-Tree索引

3. 高級技巧

不可見索引測試：

CREATE INDEX idx_test ON employees(phone_number) INVISIBLE;
-- 按需切換可見性
ALTER INDEX idx_test VISIBLE;

位圖索引適用場景：

CREATE BITMAP INDEX idx_emp_job ON employees(job_id);  -- 適合重復值多的列

附錄：常用系統視圖查詢

-- 查看所有索引
SELECT * FROM user_indexes WHERE table_name = 'EMPLOYEES';-- 查看視圖定義
SELECT text FROM user_views WHERE view_name = 'V_HR_EMPLOYEE';-- 分析索引碎片率
SELECT name, del_lf_rows/lf_rows AS frag_ratio 
FROM index_stats WHERE lf_rows > 0;