ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > 【Oracle】查询条件中出现to_number报错，用Hint之后改变运行结果

【Oracle】查询条件中出现to_number报错，用Hint之后改变运行结果

原创 Oracle 作者：PiscesCanon 时间：2017-12-12 14:05:42 0 删除 编辑
前言：
以前一直觉得Hint加了不会影响sql执行状态或者结果，然后其实是会的。
因为涉及到很多的知识点，研究了还是挺有意义的。在这里探讨一下。

先看现象：
  1. --11.2.0.4.0
  2. SYS@proc> select name from v$parameter where name='db_block_size' and to_number(value)=8192;
  3. select name from v$parameter where name='db_block_size' and to_number(value)=8192
  4.                                                             *
  5. ERROR at line 1:
  6. ORA-01722: invalid number


  9. SYS@proc> explain plan for
  10.   2 select name from v$parameter where name='db_block_size' and to_number(value)=8192;

  12. Explained.

  14. SYS@proc> select * from table(dbms_xplan.display());

  16. PLAN_TABLE_OUTPUT
  17. --------------------------------------------------------------------------------
  18. Plan hash value: 1128103955

  20. -------------------------------------------------------------------------------
  21. | Id | Operation         | Name     | Rows  | Bytes   | Cost (%CPU)| Time     |
  22. -------------------------------------------------------------------------------
  23. |  0 | SELECT STATEMENT  |          |     1 |    2115 |     0   (0)| 00:00:01 |
  24. |* 1 |  HASH JOIN        |          |     1 |    2115 |     0   (0)| 00:00:01 |
  25. |* 2 |   FIXED TABLE FULL| X$KSPPI  |     1 |      81 |     0   (0)| 00:00:01 |
  26. |* 3 |   FIXED TABLE FULL| X$KSPPCV |     1 |    2034 |     0   (0)| 00:00:01 |
  27. -------------------------------------------------------------------------------


  30. PLAN_TABLE_OUTPUT
  31. --------------------------------------------------------------------------------
  32. Predicate Information (identified by operation id):
  33. ---------------------------------------------------

  35.    1 - access("X"."INDX"="Y"."INDX")
  36.        filter(TRANSLATE("KSPPINM",'_','#') NOT LIKE '#%' OR
  37.      "KSPPSTDF"='FALSE' OR BITAND("KSPPSTVF",5)>0)
  38.    2 - filter("KSPPINM"='db_block_size' AND
  39.      "X"."INST_ID"=USERENV('INSTANCE') AND BITAND("KSPPIFLG",268435456)=0
  40.      AND TRANSLATE("KSPPINM",'_','#') NOT LIKE '##%')
  41.    3 - filter(TO_NUMBER("KSPPSTVL")=8192)

  43. 21 rows selected.
  1. ---12C---
  2. SQL> select name from v$parameter where name='db_block_size' and to_number(value)=8192;

  4. NAME
  5. --------------------------------
  6. db_block_size
  7. SQL> set autotrace traceonly
  8. SQL> select name from v$parameter where name='db_block_size' and to_number(value)=8192;

  10. 执行计划
  11. ------------------------------------------------------------
  12. Plan hash value: 185276389

  14. ------------------------------------------------------------------------------------------------
  15. | Id    | Operation                | Name             | Rows  | Bytes  | Cost (%CPU)| Time     |
  16. ------------------------------------------------------------------------------------------------
  17. |     0 | SELECT STATEMENT         |                  |     1 |     55 |     0   (0)| 00:00:01 |
  18. |     1 |  NESTED LOOPS            |                  |     1 |     55 |     0   (0)| 00:00:01 |
  19. |*    2 |   FIXED TABLE FIXED INDEX| X$KSPPI (ind:1)  |     1 |     37 |     0   (0)| 00:00:01 |
  20. |*    3 |   FIXED TABLE FIXED INDEX| X$KSPPCV (ind:1) |     1 |     18 |     0   (0)| 00:00:01 |
  21. ------------------------------------------------------------------------------------------------

  23. Predicate Information (identified by operation id):
  24. ---------------------------------------------------

  26.     2 - filter("KSPPIM"='db_block_size' AND TRANSLATE("KSPPINM",'_','$') NOT LIKE
  27.              '$$%' AND "X"."INST_ID"=USERENV('INSTANCE') AND BITAND("KSPPIFLG",268435456)=0)
  28.     3 - filter("X"."INDX"="Y"."INDX" AND TO_NUMBER("KSPPSTVL")=8192 AND
  29.              (TRANSLATE("KSPPINM",'_','$') NOT LIKE '$%' OR "KSPPSTDF"='FALSE' OR
  30.              BITAND("KSPPSTVF",5>0))
12c可以正常运行不报错，但是11g里边却是报了错误。
原因是因为11g的执行计划id为3的谓词条件为"filter(TO_NUMBER("KSPPSTVL")=8192)"，是在X$KSPPCV上过滤的，自然含有不能to_number的值，比如含有字母的字符串。
12C做了优化可以运行，这条语句在11G运行不了，需要构造子查询/视图与外部查询，并加一个连接条件，才能尝试不用谓词推入。

实验过程：
这里将sql改写：
  1. SYS@proc> select value from (select value from v$parameter p where p.name='db_block_size') a where to_number(a.value)=8192;
  2. select value from (select value from v$parameter p where p.name='db_block_size') a where to_number(a.value)=8192
  3.                                                                                          *
  4. ERROR at line 1:
  5. ORA-01722: invalid number


  8. SYS@proc> select value from v$parameter p where p.name='db_block_size';

  10. VALUE
  11. --------------------------------------------------------------------------------
  12. 8192
从结果看，运行还是出现错误，但是"select value from v$parameter p where p.name='db_block_size';"执行结果只有一个"8192"，不应该出现ora1722错误。
看一眼执行计划就明白了：
  1. SYS@proc> explain plan for select value from (select value from v$parameter p where p.name='db_block_size') a where to_number(a.value)=8192;

  3. Explained.

  5. SYS@proc> set linesize 200
  6. SYS@proc> select * from table(dbms_xplan.display());

  8. PLAN_TABLE_OUTPUT
  9. ------------------------------------------------------------------------------------------
  10. Plan hash value: 1128103955

  12. -------------------------------------------------------------------------------
  13. | Id | Operation         | Name     | Rows  | Bytes   | Cost (%CPU)| Time     |
  14. -------------------------------------------------------------------------------
  15. | 0  | SELECT STATEMENT  |          |     1 |    2115 |     0   (0)| 00:00:01 |
  16. |* 1 |  HASH JOIN        |          |     1 |    2115 |     0   (0)| 00:00:01 |
  17. |* 2 |   FIXED TABLE FULL| X$KSPPI  |     1 |      81 |     0   (0)| 00:00:01 |
  18. |* 3 |   FIXED TABLE FULL| X$KSPPCV |     1 |    2034 |     0   (0)| 00:00:01 |
  19. -------------------------------------------------------------------------------


  22. PLAN_TABLE_OUTPUT
  23. ------------------------------------------------------------------------------------------
  24. Predicate Information (identified by operation id):
  25. ---------------------------------------------------

  27.    1 - access("X"."INDX"="Y"."INDX")
  28.        filter(TRANSLATE("KSPPINM",'_','#') NOT LIKE '#%' OR
  29.      "KSPPSTDF"='FALSE' OR BITAND("KSPPSTVF",5)>0)
  30.    2 - filter("KSPPINM"='db_block_size' AND
  31.      "X"."INST_ID"=USERENV('INSTANCE') AND BITAND("KSPPIFLG",268435456)=0
  32.      AND TRANSLATE("KSPPINM",'_','#') NOT LIKE '##%')
  33.    3 - filter(TO_NUMBER("KSPPSTVL")=8192)

  35. 21 rows selected.
尝试加Hint不做谓词推入，不过前提是不做视图合并：
  1. SYS@proc> select /*+ no_merge(a) no_push_pred(a) */value from (select value from v$parameter p where p.name='db_block_size') a where to_number(a.value)=8192;
  2. select /*+ no_merge(a) */value from (select value from v$parameter p where p.name='db_block_size') a where to_number(a.value)=8192
  3.                                                                                                            *
  4. ERROR at line 1:
  5. ORA-01722: invalid number


  8. SYS@proc> explain plan for select /*+ no_merge(a) no_push_pred(a) */value from (select value from v$parameter p where p.name='db_block_size') a where to_number(a.value)=8192;

  10. Explained.

  12. SYS@proc> select * from table(dbms_xplain.display());
  13. select * from table(dbms_xplain.display())
  14.                     *
  15. ERROR at line 1:
  16. ORA-00904: "DBMS_XPLAIN"."DISPLAY": invalid identifier


  19. SYS@proc> select * from table(dbms_xplan.display());

  21. PLAN_TABLE_OUTPUT
  22. -------------------------------------------------------------------------------------------
  23. Plan hash value: 3035854849

  25. -------------------------------------------------------------------------------
  26. | Id | Operation          | Name     | Rows | Bytes | Cost (%CPU)|  Time     |
  27. -------------------------------------------------------------------------------
  28. |  0 | SELECT STATEMENT   |          |    1 |  2002 |    0    (0)|  00:00:01 |
  29. |  1 |  VIEW              |          |    1 |  2002 |    0    (0)|  00:00:01 |
  30. |* 2 |   HASH JOIN        |          |    1 |  2115 |    0    (0)|  00:00:01 |
  31. |* 3 |    FIXED TABLE FULL| X$KSPPI  |    1 |    81 |    0    (0)|  00:00:01 |
  32. |* 4 |    FIXED TABLE FULL| X$KSPPCV |    1 |  2034 |    0    (0)|  00:00:01 |
  33. -------------------------------------------------------------------------------

  35. PLAN_TABLE_OUTPUT
  36. ------------------------------------------------------------------------------------------

  38. Predicate Information (identified by operation id):
  39. ---------------------------------------------------

  41.    2 - access("X"."INDX"="Y"."INDX")
  42.        filter(TRANSLATE("KSPPINM",'_','#') NOT LIKE '#%' OR
  43.      "KSPPSTDF"='FALSE' OR BITAND("KSPPSTVF",5)>0)
  44.    3 - filter("KSPPINM"='db_block_size' AND
  45.      "X"."INST_ID"=USERENV('INSTANCE') AND BITAND("KSPPIFLG",268435456)=0
  46.      AND TRANSLATE("KSPPINM",'_','#') NOT LIKE '##%')
  47.    4 - filter(TO_NUMBER("KSPPSTVL")=8192)

  49. 22 rows selected.
这里的no_merge的hint是生效了，不过no_push_pred却没有。
原因是这个sql的改写也有问题，push_pred/no_push_pred的应用场景是视图和外部查询之间有连接条件，对于push_pred，优化器会把原本处于该视图外部查询中和该视图之间的连接条件推入到该视图定义的SQL语句内部，这样做是为了能使用上该视图内部相关基表的索引，进而走出基于索引的嵌套循环连接。
需要连接条件的话，要有构造另外一张辅助表：
  1. SYS@proc> create table test(id int);

  3. Table created.

  5. SYS@proc> insert into test values(null);

  7. 1 row created.

  9. SYS@proc> commit;

  11. Commit complete.
sql改写：select a.value from (select value from v$parameter p where p.name='db_block_size') a,test b where to_number(id||a.value)=8192;
该sql的执行计划以及执行结果如下：
  1. SYS@proc> select a.value from (select value from v$parameter p where p.name='db_block_size') a,test b where to_number(id||a.value)=8192;
  2. select a.value from (select value from v$parameter p where p.name='db_block_size') a,test b where to_number(id||a.value)=8192
  3.                                                                                                                *
  4. ERROR at line 1:
  5. ORA-01722: invalid number


  8. SYS@proc> explain plan for select a.value from (select value from v$parameter p where p.name='db_block_size') a,test b where to_number(id||a.value)=8192;

  10. Explained.

  12. SYS@proc> select * from table(dbms_xplan.display());

  14. PLAN_TABLE_OUTPUT
  15. -------------------------------------------------------------------------------------------
  16. Plan hash value: 662541633

  18. --------------------------------------------------------------------------------
  19. | Id | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
  20. --------------------------------------------------------------------------------
  21. |  0 | SELECT STATEMENT    |          |     1 |  2128 |   2     (0)| 00:00:01 |
  22. |* 1 |  HASH JOIN          |          |     1 |  2128 |   2     (0)| 00:00:01 |
  23. |  2 |   NESTED LOOPS      |          |     1 |  2047 |   2     (0)| 00:00:01 |
  24. |  3 |    TABLE ACCESS FULL| TEST     |     1 |    13 |   2     (0)| 00:00:01 |
  25. |* 4 |    FIXED TABLE FULL | X$KSPPCV |     1 |  2034 |   0     (0)| 00:00:01 |
  26. |* 5 |   FIXED TABLE FULL  | X$KSPPI  |     1 |    81 |   0     (0)| 00:00:01 |
  27. --------------------------------------------------------------------------------

  29. Predicate Information (identified by operation id):
  30. ---------------------------------------------------

  32.    1 - access("X"."INDX"="Y"."INDX")
  33.        filter(TRANSLATE("KSPPINM",'_','#') NOT LIKE '#%' OR
  34.      "KSPPSTDF"='FALSE' OR BITAND("KSPPSTVF",5)>0)
  35.    4 - filter(TO_NUMBER(TO_CHAR("ID")||"KSPPSTVL")=8192)
  36.    5 - filter("KSPPINM"='db_block_size' AND
  37.      "X"."INST_ID"=USERENV('INSTANCE') AND BITAND("KSPPIFLG",268435456)=0
  38.      AND TRANSLATE("KSPPINM",'_','#') NOT LIKE '##%')

  40. Note
  41. -----
  42.    - dynamic sampling used for this statement (level=2)

  44. 27 rows selected.
从执行计划上看，id为4的那步，在X$KSPPCV过滤to_number(id||a.value)=8192的数据，自然是有问题的。
添加Hint强制不做谓词推入，如下：
  1. SYS@proc> select /*+ no_merge(a) no_push_pred(a)*/ a.value from (select value from v$parameter p where p.name='db_block_size') a,test b where to_number(id||a.value)=8192;

  3. VALUE
  4. -----------------------------------------------------------------------------------------------
  5. 8192

  7. SYS@proc> explain plan for select /*+ no_merge(a) no_push_pred(a)*/ a.value from (select value from v$parameter p where p.name='db_block_size') a,test b where to_number(id||a.value)=8192;

  9. Explained.

  11. SYS@proc> select * from table(dbms_xplan.display());

  13. PLAN_TABLE_OUTPUT
  14. ------------------------------------------------------------------------------------------------
  15. Plan hash value: 742769369

  17. ------------------------------------------------------------------------------
  18. | Id | Operation           | Name     | Rows | Bytes | Cost (%CPU)| Time     |
  19. ------------------------------------------------------------------------------
  20. |  0 | SELECT STATEMENT    |          |    1 |  2015 |   2     (0)| 00:00:01 |
  21. |  1 |  NESTED LOOPS       |          |    1 |  2015 |   2     (0)| 00:00:01 |
  22. |  2 |   VIEW              |          |    1 |  2002 |   0     (0)| 00:00:01 |
  23. |* 3 |    HASH JOIN        |          |    1 |  2115 |   0     (0)| 00:00:01 |
  24. |* 4 |     FIXED TABLE FULL| X$KSPPI  |    1 |    81 |   0     (0)| 00:00:01 |
  25. |  5 |     FIXED TABLE FULL| X$KSPPCV |  100 |   198K|   0     (0)| 00:00:01 |
  26. |* 6 |   TABLE ACCESS FULL | TEST     |    1 |    13 |   2     (0)| 00:00:01 |
  27. ------------------------------------------------------------------------------

  29. Predicate Information (identified by operation id):
  30. ---------------------------------------------------

  32.    3 - access("X"."INDX"="Y"."INDX")
  33.        filter(TRANSLATE("KSPPINM",'_','#') NOT LIKE '#%' OR
  34.      "KSPPSTDF"='FALSE' OR BITAND("KSPPSTVF",5)>0)
  35.    4 - filter("KSPPINM"='db_block_size' AND
  36.      "X"."INST_ID"=USERENV('INSTANCE') AND BITAND("KSPPIFLG",268435456)=0
  37.      AND TRANSLATE("KSPPINM",'_','#') NOT LIKE '##%')
  38.    6 - filter(TO_NUMBER(TO_CHAR("ID")||"A"."VALUE")=8192)

  40. Note
  41. -----
  42.    - dynamic sampling used for this statement (level=2)

  44. 28 rows selected.
从执行计划上看，做to_number转换的对象是在test表上，自然就是可以运行出结果了。
PS：
该语句也是一样的：select a.value from (select /*+ no_merge no_push_pred*/ value from v$parameter p where p.name='db_block_size') a,test b where to_number(id||a.value)=8192;

问题延伸：
  1. SYS@proc> create table t as select * from v$parameter;

  3. Table created.

  5. SYS@proc> select value from t where name='db_block_size' and to_number(value)=8192;

  7. VALUE
  8. --------------------------------------------------------------------------------
  9. 8192

  11. SYS@proc> select value from v$parameter where name='db_block_size' and to_number(value)=8192;
  12. select value from v$parameter where name='db_block_size' and to_number(value)=8192
  13.                                                              *
  14. ERROR at line 1:
  15. ORA-01722: invalid number
为什么语句“select value from t where name='db_block_size' and to_number(value)=8192;”执行成功，换成v$parameter却报错。
该问题在另外一篇做研究。
这里附上链接http://blog.itpub.net/30174570/viewspace-2148607/

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30174570/viewspace-2148592/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 偶然的实验，查询两条记录的全表很久，10046真好用...
下一篇： 【Oracle】where条件执行顺序(上篇的问题延伸)
请登录后发表评论 登录
全部评论
PiscesCanon

注册时间：2015-03-23

  • 博文量
    29
  • 访问量
    57487

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593