【SQL】Oracle交換分割槽中引數without validation引發的症狀（一）

xysoul_雲龍發表於2015-08-07
原文網址 : http://blog.itpub.net/29487349/viewspace-1764438/
SQLOracle
在一次刷微博中偶然看到這麼一個例子，下面是執行的語句資訊：

點選(此處)摺疊或開啟

  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部落格 ” ，連結：http://blog.itpub.net/29487349/viewspace-1764438/，如需轉載，請註明出處，否則將追究法律責任。

相關文章