sql - 不需要的查询在 Oracle 10g 中合并

Question

我正在研究 Oracle 数据库 10g 10.2.0.5.0 版。我有这样的看法:

CREATE OR REPLACE VIEW some_view
(
    A,
    B
)
AS
    SELECT A, B
    FROM table_a
    WHERE condition_a
UNION ALL
    SELECT A, B
    FROM table_b
    WHERE condition_b;

和一些数据库函数some_db_package.foo()。我的问题是当我执行查询时:

SELECT A, some_db_package.foo(B) val
FROM some_view
WHERE some_db_package.foo(B) = 0;

Oracle 正在合并来自查询和 some_view 的条件，所以我得到类似的东西:

SELECT A, some_db_package.foo(B) val
FROM table_a
WHERE some_db_package.foo(B) = 0 AND condition_a
UNION ALL
SELECT A, some_db_package.foo(B) val
FROM table_b
WHERE some_db_package.foo(B) = 0 AND condition_b;

some_db_package.foo() 在 table_a 和 table_b 的所有行上执行，我想执行 some_db_package.foo( ) 仅在过滤(通过 condition_a 和 condition_b)行。假设我不能在查询中使用优化器提示，有什么方法可以做到这一点(即通过更改 sql 查询或 some_view 定义)？

---

问题解决了。总结一下:

1. some_db_package.foo() - 对于给定的事件和日期范围，计算日期之间发生的事件错误(foo() 访问表)，因此只有当 sysdate 时它才是确定的> 日期到。

2. select * from ( SELECT A, some_db_package.foo(B) val FROM some_view ) 没有区别。

3. 实际上我不需要 UNION ALL 并且我用 UNION 进行了测试，但结果还是一样。

4. with some_view_set as (select A, B from some_view) select * from ( select A, some_db_package.foo(B) val from some_view_set ) 其中 val = 0 没有区别。

5. 我确实使用优化器提示进行了测试，不幸的是 Oracle 忽略了它们。

6. 在 some_view 中使用 ROWNUM >= 1 是我的问题的解决方案。

谢谢你的帮助，我真的很感激。

Answer 1

ROWNUM通常是停止优化器转换的最佳方式。提示很难正确 - 语法很奇怪且有错误，并且有许多潜在的转换需要停止。还有其他方法可以重写查询，但 ROWNUM 通常是最好的方法，因为它被记录为以这种方式工作。 ROWNUM 必须最后评估才能用于前 N 个查询，您始终可以依靠它来防止查询 block 被合并。

示例架构

drop table table_a;
drop table table_b;

create table table_a(a number, b number);
create table table_b(a number, b number);

insert into table_a select level, level from dual connect by level <= 10;
insert into table_b select level, level from dual connect by level <= 10;

begin
    dbms_stats.gather_table_stats(user, 'table_a');
    dbms_stats.gather_table_stats(user, 'table_b');
end;
/

--FOO takes 1 second each time it is executed.
create or replace function foo(p_value number) return number is
begin
    dbms_lock.sleep(1);
    return 0;
end;
/

--BAR is fast, but the optimizer doesn't know it.
create or replace function bar(p_value number) return number is
begin
    return p_value;
end;
/

--This view returns 2 rows.
CREATE OR REPLACE VIEW some_view AS
    SELECT A, B
    FROM table_a
    WHERE a = bar(1)
UNION ALL
    SELECT A, B
    FROM table_b
    WHERE a = bar(2);

慢查询

此查询需要 20 秒才能运行，这意味着该函数被评估了 20 次。

SELECT A, foo(B) val
FROM some_view
WHERE foo(B) = 0;

解释计划显示条件已合并，条件似乎是从左到右评估的(但不要依赖它总是为真!)。

explain plan for
SELECT A, foo(B) val
FROM some_view
WHERE foo(B) = 0;

select * from table(dbms_xplan.display);


Plan hash value: 4139878329

---------------------------------------------------------------------------------
| Id  | Operation           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |     1 |     6 |     5   (0)| 00:00:01 |
|   1 |  VIEW               | SOME_VIEW |     1 |     6 |     5   (0)| 00:00:01 |
|   2 |   UNION-ALL         |           |       |       |            |          |
|*  3 |    TABLE ACCESS FULL| TABLE_A   |     1 |     6 |     3   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| TABLE_B   |     1 |     6 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("FOO"("B")=0 AND "A"="BAR"(1))
   4 - filter("FOO"("B")=0 AND "A"="BAR"(2))

Note
-----
   - automatic DOP: skipped because of IO calibrate statistics are missing

快速查询

添加一个看似多余的 ROWNUM 谓词，它除了阻止转换外什么都不做。

CREATE OR REPLACE VIEW some_view2 AS
    SELECT A, B
    FROM table_a
    WHERE a = bar(1)
    AND ROWNUM >= 1 --Prevent optimizer transformations, for performance.
UNION ALL
    SELECT A, B
    FROM table_b
    WHERE a = bar(2)
    AND ROWNUM >= 1 --Prevent optimizer transformations, for performance.
;

现在查询只需要 4 秒，函数只运行了 4 次。

SELECT A, foo(B) val
FROM some_view2
WHERE foo(B) = 0;

在新的解释计划中，很明显 FOO 函数是最后计算的，在大部分过滤完成之后。

explain plan for
SELECT A, foo(B) val
FROM some_view2
WHERE foo(B) = 0;

select * from table(dbms_xplan.display);


Plan hash value: 4228269064

------------------------------------------------------------------------------------
| Id  | Operation             | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |            |     2 |    52 |     6   (0)| 00:00:01 |
|*  1 |  VIEW                 | SOME_VIEW2 |     2 |    52 |     6   (0)| 00:00:01 |
|   2 |   UNION-ALL           |            |       |       |            |          |
|   3 |    COUNT              |            |       |       |            |          |
|*  4 |     FILTER            |            |       |       |            |          |
|*  5 |      TABLE ACCESS FULL| TABLE_A    |     1 |     6 |     3   (0)| 00:00:01 |
|   6 |    COUNT              |            |       |       |            |          |
|*  7 |     FILTER            |            |       |       |            |          |
|*  8 |      TABLE ACCESS FULL| TABLE_B    |     1 |     6 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("FOO"("B")=0)
   4 - filter(ROWNUM>=1)
   5 - filter("A"="BAR"(1))
   7 - filter(ROWNUM>=1)
   8 - filter("A"="BAR"(2))

Note
-----
   - automatic DOP: skipped because of IO calibrate statistics are missing

Ben 使函数 DETERMINISTIC 的想法也可能有助于减少函数调用。