首页 » ORACLE » The filter pushed in subquery issue in oracle 11G (filter推进子查询)

The filter pushed in subquery issue in oracle 11G (filter推进子查询)

the filter pushed in subquery issue in oracle 11g

今天开发的同事来问我一个SQL问题, 在一个字符字段to_number时提示有无效数值, 肉眼看全部为数字格式, 数据库版本 11.2.0.3 当然开始怀疑有不可显示字符(如char(10)), 可以创建个function来验证一下, 脚本如下:

CREATE OR REPLACE function IS_NUMBER(str in varchar2)
 return number IS
dummy number;
begin
dummy := TO_NUMBER(str);
return  1;
Exception WHEN OTHERS then
return 0;
end;
/

下面是当时的问题SQL

SELECT *
   FROM (SELECT s.score AS count_num
           FROM em_examinee e, em_exam_course_score s
          WHERE     e.exam_id = 444
                AND e.id = s.examinee_id
                AND s.score <> '缺考'    AND s.score <> '不需要考'
                AND s.score <> '其他'    AND s.score <> '违纪') a
  WHERE TO_NUMBER (a.count_num) < 60;

em_exam_course_score.score字段是varchar2,值有数字和汉字(先不讨论为什么不用负数来代替中文),这个简单的SQL一看也可以猜出意图是过滤掉无法转换数值的记录,再从子查询的结果中找出小于60分的记录。 首先我们把子查询单独拿出来执行并加上刚才创建的function 来验证is_number=0,无返回结果。 也许你已经想到了”谓词推进”. 下一步让我们来看一下执行计划。

SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
Plan hash value: 2955730142
-----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                      |     3 |    54 |    81   (2)| 00:00:01 |
|*  1 |  HASH JOIN                   |                      |     3 |    54 |    81   (2)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EM_EXAMINEE          |     3 |    27 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | EM_EXAMINEE_UK1      |     3 |       |     2   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS FULL          | EM_EXAM_COURSE_SCORE |   968 |  8712 |    78   (2)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("E"."ID"="S"."EXAMINEE_ID")
   3 - access("EXAM_ID"=444)
   4 - filter(TO_NUMBER("S"."SCORE")<60 AND "S"."SCORE"<>'缺考' AND
              "S"."SCORE"<>'不需要考' AND "S"."SCORE"<>'其他' AND "S"."SCORE"<>'违纪')

Notice:
注意上面的4# 计划 的fileter 部分, 这就是问题的根源。了解SQL的执行步骤应该清楚在sql parse阶段CBO会帮我们做sql查询转换生成高效的执行计划, 在本案例我们想把TO_NUMBER(“S”.”SCORE”)<60 的条件放到子查询返回的结果集后再过滤, 结果CBO错误把此条件提前和该字段的其它条件一并执行,找到了问题,我开始尝试阻止score <60 条件推进到子查询中去(to prevent the filter is pushed into the inline view),使用Hint来引导CBO.

解决方法1,materialize hint.

with c as (
SELECT /*+materialize*/s.score AS count_num
FROM em_examinee e, em_exam_course_score s
WHERE     exam_id = 444
--and s.em_exam_course_id=482 --or s.em_exam_course_id=4841
AND e.id = s.examinee_id
AND s.score <> '缺考'
AND s.score <> '不需要考'
AND s.score <> '其他'
AND s.score <> '违纪'
)
select * from c where  to_number(count_num)<60;

执行计划
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                            |    12 |  1224 |    84   (2)| 00:00:02 |
|   1 |  TEMP TABLE TRANSFORMATION     |                            |       |       |            |       |
|   2 |   LOAD AS SELECT               | SYS_TEMP_0FD9D95AD_72C40D1 |       |       |            |       |
|*  3 |    HASH JOIN                   |                            |    12 |   216 |    82   (2)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| EM_EXAMINEE                |     6 |    54 |     4   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | EM_EXAMINEE_UK1            |     6 |       |     2   (0)| 00:00:01 |
|*  6 |     TABLE ACCESS FULL          | EM_EXAM_COURSE_SCORE       | 22305 |   196K|    78   (2)| 00:00:01 |
|*  7 |   VIEW                         |                            |    12 |  1224 |     2   (0)| 00:00:01 |
|   8 |    TABLE ACCESS FULL           | SYS_TEMP_0FD9D95AD_72C40D1 |    12 |    48 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("E"."ID"="S"."EXAMINEE_ID")
5 - access("EXAM_ID"=444)
6 - filter("S"."SCORE"<>'缺考' AND "S"."SCORE"<>'不需要考' AND "S"."SCORE"<>'其他' AND "S"."SCORE"<>'违纪')
7 - filter(TO_NUMBER("COUNT_NUM")<60)

解决方法2 “_optimizer_filter_pushdown” parameter

SQL> explain plan for SELECT /*+ NO_MERGE(a) no_push_pred(a) */*
2    FROM (SELECT s.score  AS count_num
3            FROM em_examinee e, em_exam_course_score s
4           WHERE     exam_id = 444
5                 AND e.id = s.examinee_id
6                 AND s.score <> '缺考'    AND s.score <> '不需要考'
7                 AND s.score <> '其他'    AND s.score <> '违纪') a
8   WHERE TO_NUMBER (a.count_num) < 60;

SQL> select * from table(dbms_xplan.display);
------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                      |     3 |   306 |    81   (2)| 00:00:01 |
|   1 |  VIEW                         |                      |     3 |   306 |    81   (2)| 00:00:01 |
|*  2 |   HASH JOIN                   |                      |     3 |    54 |    81   (2)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| EM_EXAMINEE          |     3 |    27 |     3   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | EM_EXAMINEE_UK1      |     3 |       |     2   (0)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL          | EM_EXAM_COURSE_SCORE |   968 |  8712 |    78   (2)| 00:00:01 |

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("E"."ID"="S"."EXAMINEE_ID")
4 - access("EXAM_ID"=444)
5 - filter(TO_NUMBER("S"."SCORE")<60 AND "S"."SCORE"<>'缺考' AND
"S"."SCORE"<>'不需要考' AND "S"."SCORE"<>'其他' AND "S"."SCORE"<>'违纪')

SQL> explain plan for SELECT /*+ NO_MERGE(a) no_push_subq(@subq1) */*
2    FROM (SELECT /*+ qb_name(subq1) */  s.score  AS count_num
3            FROM em_examinee e, em_exam_course_score s
4           WHERE     exam_id = 444
5                 AND e.id = s.examinee_id
6                 AND s.score <> '缺考'    AND s.score <> '不需要考'
7                 AND s.score <> '其他'    AND s.score <> '违纪') a
8   WHERE TO_NUMBER (a.count_num) < 60;

SQL> select * from table(dbms_xplan.display);
------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                      |     3 |   306 |    81   (2)| 00:00:01 |
|   1 |  VIEW                         |                      |     3 |   306 |    81   (2)| 00:00:01 |
|*  2 |   HASH JOIN                   |                      |     3 |    54 |    81   (2)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| EM_EXAMINEE          |     3 |    27 |     3   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | EM_EXAMINEE_UK1      |     3 |       |     2   (0)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL          | EM_EXAM_COURSE_SCORE |   968 |  8712 |    77   (0)| 00:00:01 |

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("E"."ID"="S"."EXAMINEE_ID")
4 - access("EXAM_ID"=444)
5 - filter(TO_NUMBER("S"."SCORE")<60 AND "S"."SCORE"<>'缺考' AND "S"."SCORE"<>'不需要考' AND
"S"."SCORE"<>'其他' AND "S"."SCORE"<>'违纪')

Note:
#注意上面的Hint 居然都未启作用, 下在在会话级turn off “_optimizer_filter_pushdown”

SQL> alter session set "_optimizer_filter_pushdown"=false;

SQL> explain plan for SELECT /*+ NO_MERGE(t) NO_PUSH_PRED(t)*/*
2    FROM (SELECT  s.score
3            FROM em_examinee e, em_exam_course_score s
4           WHERE     exam_id = 444
5                 AND e.id = s.examinee_id
6                 AND s.score <> '缺考'    AND s.score <> '不需要考'
7                 AND s.score <> '其他'    AND s.score <> '违纪') t
8   WHERE to_number(score) < 60;

SQL> select * from table(dbms_xplan.display);
------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                      |     5 |   510 |    81   (2)| 00:00:01 |
|*  1 |  VIEW                         |                      |     5 |   510 |    81   (2)| 00:00:01 |
|*  2 |   HASH JOIN                   |                      |     5 |    90 |    81   (2)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| EM_EXAMINEE          |     3 |    27 |     3   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | EM_EXAMINEE_UK1      |     3 |       |     2   (0)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL          | EM_EXAM_COURSE_SCORE | 19369 |   170K|    77   (0)| 00:00:01 |

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

1 - filter(TO_NUMBER("SCORE")<60)
2 - access("E"."ID"="S"."EXAMINEE_ID")
4 - access("EXAM_ID"=444)
5 - filter("S"."SCORE"<>'缺考' AND "S"."SCORE"<>'不需要考' AND "S"."SCORE"<>'其他' AND
"S"."SCORE"<>'违纪')

或者在SQL级

SELECT /*+ no_merge(t) OPT_PARAM('_OPTIMIZER_FILTER_PUSHDOWN' 'FALSE') */*
FROM (SELECT  s.score
FROM em_examinee e, em_exam_course_score s
WHERE     exam_id = 444
AND e.id = s.examinee_id
AND s.score <> '缺考'    AND s.score <> '不需要考'
AND s.score <> '其他'    AND s.score <> '违纪') t
WHERE to_number(score) < 60;

 

解决方法3 put rownum in subquery

SQL> explain plan for SELECT *
  2    FROM (SELECT  s.score ,rownum rn
  3            FROM em_examinee e, em_exam_course_score s
  4           WHERE     exam_id = 444
  5                 AND e.id = s.examinee_id
  6                 AND s.score <> '缺考'    AND s.score <> '不需要考'
  7                 AND s.score <> '其他'    AND s.score <> '违纪') t
  8   WHERE to_number(score) < 60; 已解释。 SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
Plan hash value: 129358830
-------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                      |     5 |   575 |    81   (2)| 00:00:01 |
|*  1 |  VIEW                          |                      |     5 |   575 |    81   (2)| 00:00:01 |
|   2 |   COUNT                        |                      |       |       |            |          |
|*  3 |    HASH JOIN                   |                      |     5 |    90 |    81   (2)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| EM_EXAMINEE          |     3 |    27 |     3   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | EM_EXAMINEE_UK1      |     3 |       |     2   (0)| 00:00:01 |
|*  6 |     TABLE ACCESS FULL          | EM_EXAM_COURSE_SCORE | 19369 |   170K|    77   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TO_NUMBER("SCORE")<60)
   3 - access("E"."ID"="S"."EXAMINEE_ID")
   5 - access("EXAM_ID"=444)
   6 - filter("S"."SCORE"<>'缺考' AND "S"."SCORE"<>'不需要考' AND "S"."SCORE"<>'其他' AND
              "S"."SCORE"<>'违纪')

ORACLE在引入一些新特性的同时, 往往会加入一些隐藏参数来关掉这些特性, 我们可以通过下面的V$ 或才X$的视图查询相关的优化器参数
v$ses_optimizer_env;
v$sys_optimizer_env;
或对应的X$(比v$更加全面)
x$qksceses, x$qkscesys

set linesize 180
set trimspool on
set pagesize 1000

column name     format a40
column feature      format a24
column value        format a15
column def_value    format a15

break on feature skip 1

select
--  FID_QKSCESYROW          feature,    -- vc64
    PNAME_QKSCESYROW    name,       -- vc40
    PVALUE_QKSCESYROW   value,      -- vc25
    DEFPVALUE_QKSCESYROW    def_value,  -- vc25
    KSPNUM_QKSCESYROW   parameter,  -- number
    PNUM_QKSCESYROW     opt_param  -- number
--  ,FLAGS_QKSCESYROW    flags       -- number
from
    X$QKSCESYS
order by
--  feature,
    name;

Summary :
阻止filter推进subquery这里提到了三种方法
with + materialize hint;
rownum column in subquery;
Setting the parameter _optimizer_filter_pushdown to FALSE.

关于”_optimizer_filter_pushdown” 参数,我建议在11G 版本中设为false, 因为相关的bug 实在太多。

Bug 17397506 SORT MERGE JOIN incorrectly chosen over HASH JOIN IN 11.2.0.3
Bug 17645326 Wrong Results with outer joins of subquery factoring having subqueries in 11.2.0.3
Bug 10100244 Wrong results from view with RESULT_CACHE, UNION and filter predicates version>11.1
Bug 17033499 ORA-7445 [kkqfppRelFilter] from SQL with PLSQL function
Bug 16220085 ORA-22905 using UNION in 11.2 with TABLE() function
Bug 17893931 ORA-7445 [kkobok] during filter pushdown in CONNECT BY .. PRIOR SQL
Bug 13245379 Hang/spin during query parse / optimization processing transitive predicates
Bug 12695062 Wrong results doing join factorization on OUTER joins with constants inside views – superceded
Wrong Results from Queries Selecting from USER|ALL|DBA_OBJECTS Views with NVL(with bind) On the Left Side of the Join Condition (文档 ID 1624690.1)
Insert as Select Fails with ORA-22905 in PL/SQL Block (文档 ID 1528251.1)

打赏

,

对不起,这篇文章暂时关闭评论。