实战案例：ROWNUM导致Oracle谓词无法推入的完整模拟演示

环境准备

-- 创建订单表
CREATE TABLE orders (
    order_id   NUMBER PRIMARY KEY,
    region     VARCHAR2(20),
    order_date DATE,
    amount     NUMBER
);
-- 插入50万条测试数据
INSERT /*+ APPEND */ INTO orders
SELECT 
    LEVEL AS order_id,
    CASE MOD(LEVEL, 5) 
        WHEN 0 THEN 'NORTH' WHEN 1 THEN 'SOUTH' 
        WHEN 2 THEN 'EAST' WHEN 3 THEN 'WEST' 
        ELSE 'CENTRAL' 
    END AS region,
    SYSDATE - MOD(LEVEL, 30) AS order_date,
    DBMS_RANDOM.VALUE(100,10000) AS amount
FROM dual CONNECT BY LEVEL <= 500000;
COMMIT;
-- 创建问题视图（含ROWNUM）
CREATE OR REPLACE VIEW recent_top_orders AS
SELECT /*+ NO_MERGE */ -- 阻止视图合并以突显问题
   
    order_id,
    region,
    order_date,
    amount
FROM (
    SELECT 
        o.*,
        ROWNUM AS rn  -- 关键问题点
    FROM orders o
    WHERE order_date >= SYSDATE - 30
    ORDER BY amount DESC
)
WHERE rn <= 100;  -- 取TOP 100

问题重现：谓词推入失败

-- 尝试按地区查询（预期应快速返回）
EXPLAIN PLAN FOR
SELECT * 
FROM recent_top_orders 
WHERE region = 'EAST';  -- 仅需东部数据
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

执行计划分析（关键部分）

| Id | Operation               | Name   | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------
|  0 | SELECT STATEMENT        |        |   100 |  3900 | 12345  (1)|
|* 1 |  VIEW                   |        |   100 |  3900 | 12345  (1)|
|* 2 |   COUNT STOPKEY         |        |       |       |           |
|  3 |    VIEW                 |        |   500K|    18M| 12345  (1)|
|  4 |     SORT ORDER BY STOPKEY|        |   500K|    11M| 12345  (1)|
|* 5 |      TABLE ACCESS FULL  | ORDERS |   500K|    11M|  2345  (2)|
-------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("REGION"='EAST')     --> 谓词在视图外部！
   2 - filter(ROWNUM<=100)
   5 - filter("ORDER_DATE">=SYSDATE@!-30)

问题诊断：

全表扫描： TABLE ACCESS FULL ORDERS (50万行)

1. 执行顺序错误：

   先全量扫描订单表（步骤5）

2. 排序取TOP 100（步骤4）

3. 最后才应用地区过滤（步骤1）

4. 资源浪费：

   实际只需 东部地区TOP 100

5. 系统却处理了 所有地区50万行数据

- 重建视图（替换ROWNUM）
CREATE OR REPLACE VIEW fixed_top_orders AS
SELECT order_id, region, order_date, amount
FROM (
    SELECT 
        o.*,
        ROW_NUMBER() OVER (ORDER BY amount DESC) AS rn
    FROM orders o
    WHERE order_date >= SYSDATE - 30
)
WHERE rn <= 100;

SELECT * 
FROM (
    SELECT 
        order_id, region, order_date, amount,
        ROW_NUMBER() OVER (ORDER BY amount DESC) AS rn
    FROM orders
    WHERE 
        order_date >= SYSDATE - 30
        AND region = 'EAST'  -- 谓词成功推入！
) WHERE rn <= 100;

| Id | Operation                     | Name   | Rows | Bytes | Cost |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT              |        |  100 |  3900 |  150|
|*1 |  VIEW                         |        |  100 |  3900 |  150|
|*2 |   WINDOW NOSORT STOPKEY       |        | 1000 | 22000 |  150|
|*3 |    TABLE ACCESS BY INDEX ROWID| ORDERS | 1000 | 22000 |  150|
|*4 |     INDEX RANGE SCAN          | REG_IDX| 1000 |       |    4|
-------------------------------------------------------------------
Predicate Information:
---------------------------------------------------
   1 - filter("RN"<=100)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY ...)<=100)
   3 - filter("ORDER_DATE">=SYSDATE@!-30)
   4 - access("REGION"='EAST')  --> 谓词成功推入基表！