首页 » ORACLE 9i-23ai » 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)
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。