CREATE TABLE orders (
order_id NUMBER PRIMARY KEY,
customer_id NUMBER NOT NULL,
order_date DATE NOT NULL,
amount NUMBER(12,2),
create_time TIMESTAMP DEFAULT SYSTIMESTAMP,
status VARCHAR2(20) DEFAULT 'COMPLETED'
);
CREATE TABLE customer (
cust_id NUMBER PRIMARY KEY,
cust_name VARCHAR2(50) NOT NULL,
region VARCHAR2(30)
);
CREATE TABLE order_detail (
detail_id NUMBER PRIMARY KEY,
order_id NUMBER NOT NULL,
product_name VARCHAR2(100),
quantity NUMBER,
CONSTRAINT fk_detail_order FOREIGN KEY (order_id) REFERENCES orders(order_id)
);
-- 关键索引
CREATE INDEX idx_emp_dept_sal ON emp(dept_no, salary);
CREATE INDEX idx_emp_status ON emp(status);
CREATE INDEX idx_emp_dept ON emp(dept_no);
CREATE INDEX idx_order_ctime ON orders(create_time);
CREATE INDEX idx_order_cust ON orders(customer_id, order_date);
CREATE INDEX idx_order_status ON orders(status); -- 选择性差的索引，FULL 案例用
-- 插入测试数据
-- 1. 先插入 dept（无外键依赖）
BEGIN
FOR i IN 1..50 LOOP
INSERT INTO dept(dept_no, dept_name, location) VALUES ('D'||i, 'Dept_'||i, CASE WHEN MOD(i,3)=0 THEN 'BEIJING' WHEN MOD(i,3)=1 THEN 'SHANGHAI' ELSE 'GUANGZHOU' END);
END LOOP;
COMMIT;
END;
/
-- 2. 插入 customer（无外键依赖）
BEGIN
FOR i IN 1..2000 LOOP
INSERT INTO customer(cust_id, cust_name, region) VALUES (i, 'Cust_'||i, CASE WHEN MOD(i,5)=0 THEN 'NORTH' ELSE 'SOUTH' END);
END LOOP;
COMMIT;
END;
/
-- 3. 插入 emp（依赖 dept）
BEGIN
FOR i IN 1..10000 LOOP
INSERT INTO emp(emp_no,emp_name,dept_no,salary,hire_date,status)
VALUES('E'||i,'Emp_'||i,'D'||(MOD(i,50)+1),
5000+MOD(i,50)*200,
ADD_MONTHS(SYSDATE,-MOD(i,120)),
DECODE(MOD(i,100),0,'LEAVE','ACTIVE'));
END LOOP;
COMMIT;
END;
/
-- 4. 插入 orders（依赖 customer）
BEGIN
FOR i IN 1..50000 LOOP
INSERT INTO orders(order_id, customer_id, order_date, amount, create_time, status)
VALUES (i, MOD(i,2000)+1, ADD_MONTHS(SYSDATE, -MOD(i,24)), 100+MOD(i,500)*10,
SYSTIMESTAMP - MOD(i,10000)/24,
DECODE(MOD(i,10), 0, 'CANCELLED', 1, 'PENDING', 'COMPLETED'));
END LOOP;
COMMIT;
END;
/
-- 5. 插入 order_detail（依赖 orders）
BEGIN
FOR i IN 1..150000 LOOP
INSERT INTO order_detail(detail_id, order_id, product_name, quantity) VALUES (i, MOD(i,50000)+1, 'Product_'||MOD(i,100), MOD(i,10)+1);
END LOOP;
COMMIT;
END;
/
-- 6. APPEND 案例专用：订单历史归档表（结构跟 orders 一样）
CREATE TABLE orders_history (
order_id NUMBER PRIMARY KEY,
customer_id NUMBER NOT NULL,
order_date DATE NOT NULL,
amount NUMBER(12,2),
create_time TIMESTAMP DEFAULT SYSTIMESTAMP,
status VARCHAR2(20) DEFAULT 'COMPLETED'
);
-- 7. RESULT_CACHE 案例专用：省份代码表（50 条，几乎不变）
CREATE TABLE province_code_table (
province_code VARCHAR2(10) PRIMARY KEY,
province_name VARCHAR2(50) NOT NULL
);
BEGIN
FOR i IN 1..50 LOOP
INSERT INTO province_code_table VALUES ('P'||i, 'Province_'||i);
END LOOP;
COMMIT;
END;
/
-- 注意：坏块案例中的 suspect_table 不在建表脚本里。
-- 那个案例是演示如何处理已有坏块的表，你不能故意造坏块来测试。
-- 如需验证 FULL HINT 的基本效果，可以用 orders 表替代：
-- SELECT /*+FULL(o)*/ COUNT(*) FROM orders o;
收集统计信息（建完表后执行）：
SELECT 'dept' AS table_name, COUNT(*) AS row_count FROM dept
UNION ALL
SELECT 'emp', COUNT(*) FROM emp
UNION ALL
SELECT 'customer', COUNT(*) FROM customer
UNION ALL
SELECT 'orders', COUNT(*) FROM orders
UNION ALL
SELECT 'order_detail', COUNT(*) FROM order_detail
UNION ALL
SELECT 'orders_history', COUNT(*) FROM orders_history
UNION ALL
SELECT 'province_code_table', COUNT(*) FROM province_code_table;
-- 不加 HINT：优化器选了 idx_order_status，回表代价巨大
EXPLAIN PLAN FOR
SELECT COUNT(*), SUM(amount) FROM orders WHERE status = 'COMPLETED';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- 加 FULL：全表扫，多块读，一趟搞定
EXPLAIN PLAN FOR
SELECT /*+FULL(o)*/ COUNT(*), SUM(amount) FROM orders o WHERE o.status = 'COMPLETED';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
什么时候加： 选择性差的索引（回表代价 > 全表扫）、小表（几百行全表扫比索引快）、统计信息不准导致优化器误选索引。
-- 优化器选了 idx_emp_dept（单列），再多读一轮过滤 salary
EXPLAIN PLAN FOR
SELECT e.emp_no, e.emp_name FROM emp e WHERE e.dept_no = 'D5' AND e.salary > 8000;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- 强制走组合索引，一次索引扫描搞定两个条件
EXPLAIN PLAN FOR
SELECT /*+INDEX(e idx_emp_dept_sal)*/ e.emp_no, e.emp_name FROM emp e WHERE e.dept_no = 'D5' AND e.salary > 8000;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
什么时候加： 优化器没选最优索引（选了前缀索引而非组合索引、选了选择性差的索引）、需要精确控制访问路径。
-- 取最近 10 条订单，INDEX_DESC + ROWNUM 组合
SELECT /*+INDEX_DESC(o idx_order_ctime)*/ o.order_id, o.customer_id, o.amount
FROM orders o
WHERE o.create_time IS NOT NULL
AND ROWNUM <= 10;
INDEX_DESC 的隐藏陷阱： 即使你加了 IS NOT NULL，Oracle 有时候仍然不走 INDEX RANGE SCAN DESCENDING，而是选 FULL SCAN + SORT ORDER BY + COUNT STOPKEY。原因是优化器觉得全表扫+排序的成本比降序索引扫描更低。
验证方法——加了 HINT 后一定要确认执行计划：
-- 执行后确认是否真的走了 INDEX RANGE SCAN DESCENDING
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));
-- 如果看到 SORT ORDER BY + STOPKEY，说明没走降序索引
更稳的做法：给列加 NOT NULL 约束（而不是只在 WHERE 里过滤），这样优化器确定索引覆盖所有行，更容易选降序扫描：
-- INDEX_FFS：索引全扫，不回表，适合覆盖索引场景
EXPLAIN PLAN FOR
SELECT /*+INDEX_FFS(e idx_emp_dept)*/ e.emp_no, e.dept_no FROM emp e WHERE e.dept_no = 'D5';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
什么时候加： 查询列全在索引里（覆盖索引），且索引远小于表。
什么时候别加： 需要排序结果时。INDEX_FFS 是无序的多块读，不能替代 ORDER BY，这时候该用普通 INDEX SCAN。
-- 禁止走 idx_emp_status，让优化器选其他路径
EXPLAIN PLAN FOR
SELECT /*+NO_INDEX(e idx_emp_status)*/ e.emp_no, e.emp_name
FROM emp e WHERE e.status = 'ACTIVE' AND e.dept_no = 'D5';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
什么时候加： 知道某个索引是坑，想排除它但不指定替代路径。
-- 优化器选了 orders 做驱动表（表最大），慢
EXPLAIN PLAN FOR
SELECT e.emp_name, d.dept_name, o.amount
FROM emp e, dept d, orders o
WHERE e.dept_no = d.dept_no
AND e.emp_no = 'E5'
AND o.customer_id = 5;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- LEADING 指定三表顺序：先扫 emp（1行），再关联 dept（1行），再关联 orders
EXPLAIN PLAN FOR
SELECT /*+LEADING(e d o)*/ e.emp_name, d.dept_name, o.amount
FROM emp e, dept d, orders o
WHERE e.dept_no = d.dept_no
AND e.emp_no = 'E5'
AND o.customer_id = 5;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
场景二：用户查自己的订单列表。 这比教科书里的 dept=3 行更贴近实际业务——用户点进"我的订单"，customer 过滤后只有 1 行，拿这 1 行去 NL probe orders 和 order_detail，秒出结果。
-- 用户 12345 查最近一个月的订单明细
SELECT /*+LEADING(c) USE_NL(o d)*/ o.order_id, d.product_name, d.quantity
FROM customer c, orders o, order_detail d
WHERE c.cust_id = 12345
AND c.cust_id = o.cust_id
AND o.order_id = d.order_id
AND o.order_date >= ADD_MONTHS(SYSDATE, -1);
不加 HINT 时，优化器可能拿 orders 做驱动表（5 万行），每行都去 probe customer（1 行），完全反了。
-- dept 过滤后只有几行，emp 有索引，NL 最快
EXPLAIN PLAN FOR
SELECT /*+LEADING(d) USE_NL(e)*/ d.dept_name, e.emp_name
FROM dept d, emp e
WHERE d.dept_no = e.dept_no
AND d.location = 'BEIJING';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
不加 HINT 时，优化器可能选 Hash Join（先把两张表都全扫一遍再在内存里匹配），对于这个小结果集场景，Hash Join 的全表扫描是浪费。
-- 两张大表等值连接，Hash Join 是正确选择
EXPLAIN PLAN FOR
SELECT /*+USE_HASH(e d)*/ d.dept_name, COUNT(*), AVG(e.salary)
FROM emp e, dept d
WHERE e.dept_no = d.dept_no
GROUP BY d.dept_name;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
什么时候加： 大表 JOIN 大表、等值连接且没有合适索引、批量处理和报表场景。
/*+PARALLEL(t 4)*/ — 加在表上，只让这张表并行扫描，其他表不受影响
/*+DOP(4)*/ — 加在语句上，整个 SQL 所有表都被强制并行，包括你没打算并行的小表
-- PARALLEL：只有 orders 并行，dept 不受影响
SELECT /*+PARALLEL(o 4)*/ d.dept_name, COUNT(*)
FROM dept d, orders o WHERE d.dept_no = o.dept_no GROUP BY d.dept_name;
-- DOP：dept 也被强制并行了，3 行的小表开并行纯粹浪费
SELECT /*+DOP(4)*/ d.dept_name, COUNT(*)
FROM dept d, orders o WHERE d.dept_no = o.dept_no GROUP BY d.dept_name;
生产环境建议在表级别设 DEGREE，而不是在每条 SQL 里写 HINT——更可控：
ALTER TABLE orders PARALLEL 4; -- 表级并行，所有查询自动生效
ALTER TABLE orders NOPARALLEL; -- 关掉并行
⚠️ 线上高危： OLTP 系统开并行 = 自杀。确认场景是批处理/报表后再开。