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

首页 > 数据库 > Oracle > 【SQL】Oracle交换分区中参数without validation引发的症状（一）

【SQL】Oracle交换分区中参数without validation引发的症状（一）

原创 Oracle 作者：xysoul_云龙 时间：2015-08-07 09:57:07 0 删除 编辑
在一次刷微博中偶然看到这么一个例子，下面是执行的语句信息：

点击(此处)折叠或打开

  1. SQL> select * from emp_p;

  3.      EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
  4. ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
  5.       7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
  6.       7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
  7.       7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
  8.       7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
  9.       7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
  10.       7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
  11.       7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
  12.       7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
  13.       7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
  14.       7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
  15.       7870 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
  16.       7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
  17.       7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
  18.       7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10

  20. 14 rows selected.

  22. SQL> select * from emp_p where empno=7870;

  24. no rows selected

  26. SQL>

加入where条件后查不出结果，而转换一下却可以：

点击(此处)折叠或打开

  1. SQL> select * from emp_p where to_char(empno)=7870;

  3.      EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
  4. ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
  5.       7870 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20

  7. SQL>

这是什么情况呢， 隐式转换？
下面我们看一下 表结构信息：

点击(此处)折叠或打开

  1. SQL> desc emp_p
  2.  Name Null? Type
  3.  ----------------------------------------------------------------------------------- -------- --------------------------------------------------------
  4.  EMPNO NOT NULL NUMBER(4) --类型
  5.  ENAME VARCHAR2(10)
  6.  JOB VARCHAR2(9)
  7.  MGR NUMBER(4)
  8.  HIREDATE DATE
  9.  SAL NUMBER(7,2)
  10.  COMM NUMBER(7,2)
  11.  DEPTNO NUMBER(2)
再看一下select * from emp_p where empno=7870的执行计划：

点击(此处)折叠或打开

  1. SQL> select * from table(dbms_xplan.display);

  3. PLAN_TABLE_OUTPUT
  4. ------------------------------------------------------------------------------------------------------------------------
  5. Plan hash value: 1248329429

  7. -------------------------------------------------------------------------------------------------------------------
  8. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
  9. -------------------------------------------------------------------------------------------------------------------
  10. | 0 | SELECT STATEMENT | | 1 | 87 | 1 (0)| 00:00:01 | | |
  11. | 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| EMP_P | 1 | 87 | 1 (0)| 00:00:01 | 2 | 2 |
  12. |* 2 | INDEX UNIQUE SCAN | SYS_C0014288 | 1 | | 0 (0)| 00:00:01 | | |
  13. -------------------------------------------------------------------------------------------------------------------

  15. Predicate Information (identified by operation id):
  16. ---------------------------------------------------

  18.    2 - access("EMPNO"=7870)

  20. 14 rows selected.

我们可以看到，该SQL语句查找的是表EMP_P中的分区P2， 好，下面我们看看该分区数据：

点击(此处)折叠或打开

  1. SQL> select * from emp_p partition(p2);

  3. no rows selected

再次查看该表几个分区

点击(此处)折叠或打开

  1. SQL> set lines 150
  2. SQL> select TABLE_OWNER,table_name,partition_name,PARTITION_POSITION from dba_tab_partitions where table_name='EMP_P';

  4. TABLE_OWNER TABLE_NAME PARTITION_NAME PARTITION_POSITION
  5. ------------------------------ ------------------------------ ------------------------------ ------------------
  6. SCOTT EMP_P P1 1
  7. SCOTT EMP_P P2 2
  8. SCOTT EMP_P P3 3

前面我们知道，分区表中有数据，下面我们看看其他分区是否有需要的数据

点击(此处)折叠或打开

  1. SQL> select * from emp_p partition(p1);

  3.      EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
  4. ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
  5.       7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
  6.       7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
  7.       7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
  8.       7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
  9.       7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
  10.       7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
  11.       7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
  12.       7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
  13.       7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
  14.       7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
  15.       7870 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
  16.       7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
  17.       7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
  18.       7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10

  20. 14 rows selected.

  22. SQL> select * from emp_p partition(p3);

  24. no rows selected

  26. SQL>

从上面内容可以看到，我们需要的数据在分区P1中，而执行查询语句的时候Oracle找的却是P2，那么下面就看看该分区表的分区机制(建表信息）：


点击(此处)折叠或打开

  1. CREATE TABLE "SCOTT"."EMP_P"
  2.    ( "EMPNO" NUMBER(4,0),
  3.         "ENAME" VARCHAR2(10),
  4.         "JOB" VARCHAR2(9),
  5.         "MGR" NUMBER(4,0),
  6.         "HIREDATE" DATE,
  7.         "SAL" NUMBER(7,2),
  8.         "COMM" NUMBER(7,2),
  9.         "DEPTNO" NUMBER(2,0),
  10.          PRIMARY KEY ("EMPNO")
  11.   USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  12.   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  13.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  14.   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  15.   TABLESPACE "USERS" ENABLE
  16.    ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  17.   STORAGE(
  18.   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  19.   TABLESPACE "USERS"
  20.   PARTITION BY RANGE ("EMPNO")
  21.  (PARTITION "P1" VALUES LESS THAN (7000) SEGMENT CREATION IMMEDIATE
  22.   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  23.  NOCOMPRESS LOGGING
  24.   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  25.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  26.   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  27.   TABLESPACE "USERS" ,
  28.  PARTITION "P2" VALUES LESS THAN (8000) SEGMENT CREATION DEFERRED
  29.   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  30.  NOCOMPRESS LOGGING
  31.   STORAGE(
  32.   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  33.   TABLESPACE "USERS" ,
  34.  PARTITION "P3" VALUES LESS THAN (9000) SEGMENT CREATION DEFERRED
  35.   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  36.  NOCOMPRESS LOGGING
  37.   STORAGE(
  38.   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  39.   TABLESPACE "USERS" )
从上面的信息我们可以看到，表EMP_P 根据列empno 分区，每个分区的限制如黄色部分。好，我们再看看刚查询语句：
select * from emp_p where empno=7870;  正常情况下该数据应该存放于分区P2，但却存放到分区p1上了。
上面的执行计划中也说明了，根据分区规则及where条件，Oracle会只会查找分区P2，下面我们通过类型转换再次查看。

点击(此处)折叠或打开

  1. SQL> select * from emp_p where to_char(empno)=7870;

  3.      EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
  4. ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
  5.       7870 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20

  7. SQL>

查看该语句执行计划：

点击(此处)折叠或打开

  1. SQL> explain plan for select * from emp_p where to_char(empno)=7870;

  3. Explained.

  5. SQL> select * from table(dbms_xplan.display);

  7. PLAN_TABLE_OUTPUT
  8. ------------------------------------------------------------------------------------------------------------------------
  9. Plan hash value: 2733612900

  11. ---------------------------------------------------------------------------------------------
  12. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
  13. ---------------------------------------------------------------------------------------------
  14. | 0 | SELECT STATEMENT | | 1 | 87 | 3 (0)| 00:00:01 | | |
  15. | 1 | PARTITION RANGE ALL| | 1 | 87 | 3 (0)| 00:00:01 | 1 | 3 |
  16. |* 2 | TABLE ACCESS FULL | EMP_P | 1 | 87 | 3 (0)| 00:00:01 | 1 | 3 |
  17. ---------------------------------------------------------------------------------------------

  19. Predicate Information (identified by operation id):
  20. ---------------------------------------------------

  22.    2 - filter(TO_NUMBER(TO_CHAR("EMPNO"))=7870)

  24. Note
  25. -----
  26.    - dynamic sampling used for this statement (level=2)

  28. 18 rows selected.
  Oracle做了一个隐式类型转换，TO_NUMBER，但该语句查找的是全部分区。

where条件无法检索的原因我们知道了，在做数据转换的时候添加了without validation，致使数据导入了P1分区，应该导入到P2，算是工作中失误。有哪些方法会造成该语句查不出结果呢，后续会再做一个简单的测试。



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

1
0
上一篇： 【BUG】Oracle12c CDB:EM EXPRESS::ORA-07445
下一篇： 【SQL】Oracle交换分区中参数without validation引发的症状（二）
请登录后发表评论 登录
全部评论
xysoul_云龙
专家姓名：杨云龙
主要从事Oracle数据库 DBA相关工作，其他操作系统、中间件等也有涉及，热衷分享。岁月老将至，如有幸在江湖中留点踪迹，也是欣慰。 【文盲筱烨】好读书爱运动的IT技术爱好者 微博：文盲筱烨 微信公众号：筱烨视点

注册时间：2014-02-15

  • 博文量
    412
  • 访问量
    1104587

最新文章

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

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

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

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