【SQL】Oracle交換分割槽中引數without validation引發的症狀(一)
在一次刷微博中偶然看到這麼一個例子,下面是執行的語句資訊:
加入where條件後查不出結果,而轉換一下卻可以:
這是什麼情況呢, 隱式轉換?
下面我們看一下 表結構資訊:
再看一下select * from emp_p where empno=7870的執行計劃:
我們可以看到,該SQL語句查詢的是表EMP_P中的分割槽P2, 好,下面我們看看該分割槽資料:
再次檢視該表幾個分割槽
前面我們知道,分割槽表中有資料,下面我們看看其他分割槽是否有需要的資料
從上面內容可以看到,我們需要的資料在分割槽P1中,而執行查詢語句的時候Oracle找的卻是P2,那麼下面就看看該分割槽表的分割槽機制(建表資訊):
從上面的資訊我們可以看到,表EMP_P 根據列empno 分割槽,每個分割槽的限制如黃色部分。好,我們再看看剛查詢語句:
select * from emp_p where empno=7870; 正常情況下該資料應該存放於分割槽P2,但卻存放到分割槽p1上了。
上面的執行計劃中也說明了,根據分割槽規則及where條件,Oracle會只會查詢分割槽P2,下面我們透過型別轉換再次檢視。
檢視該語句執行計劃:
Oracle做了一個隱式型別轉換,TO_NUMBER,但該語句查詢的是全部分割槽。
where條件無法檢索的原因我們知道了,在做資料轉換的時候新增了without validation,致使資料匯入了P1分割槽,應該匯入到P2,算是工作中失誤。有哪些方法會造成該語句查不出結果呢,後續會再做一個簡單的測試。
點選(此處)摺疊或開啟
-
SQL> select * from emp_p;
-
-
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
-
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
-
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
-
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
-
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
-
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
-
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
-
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
-
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
-
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
-
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
-
7870 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
-
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
-
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
-
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
-
-
14 rows selected.
-
-
SQL> select * from emp_p where empno=7870;
-
-
no rows selected
-
- SQL>
加入where條件後查不出結果,而轉換一下卻可以:
點選(此處)摺疊或開啟
-
SQL> select * from emp_p where to_char(empno)=7870;
-
-
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
-
7870 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
-
- SQL>
這是什麼情況呢, 隱式轉換?
下面我們看一下 表結構資訊:
點選(此處)摺疊或開啟
-
SQL> desc emp_p
-
Name Null? Type
-
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
-
EMPNO NOT NULL NUMBER(4) --型別
-
ENAME VARCHAR2(10)
-
JOB VARCHAR2(9)
-
MGR NUMBER(4)
-
HIREDATE DATE
-
SAL NUMBER(7,2)
-
COMM NUMBER(7,2)
- DEPTNO NUMBER(2)
點選(此處)摺疊或開啟
-
SQL> select * from table(dbms_xplan.display);
-
-
PLAN_TABLE_OUTPUT
-
------------------------------------------------------------------------------------------------------------------------
-
Plan hash value: 1248329429
-
-
-------------------------------------------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-
-------------------------------------------------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 1 | 87 | 1 (0)| 00:00:01 | | |
-
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| EMP_P | 1 | 87 | 1 (0)| 00:00:01 | 2 | 2 |
-
|* 2 | INDEX UNIQUE SCAN | SYS_C0014288 | 1 | | 0 (0)| 00:00:01 | | |
-
-------------------------------------------------------------------------------------------------------------------
-
-
Predicate Information (identified by operation id):
-
---------------------------------------------------
-
-
2 - access("EMPNO"=7870)
-
- 14 rows selected.
我們可以看到,該SQL語句查詢的是表EMP_P中的分割槽P2, 好,下面我們看看該分割槽資料:
點選(此處)摺疊或開啟
-
SQL> select * from emp_p partition(p2);
-
- no rows selected
再次檢視該表幾個分割槽
點選(此處)摺疊或開啟
-
SQL> set lines 150
-
SQL> select TABLE_OWNER,table_name,partition_name,PARTITION_POSITION from dba_tab_partitions where table_name='EMP_P';
-
-
TABLE_OWNER TABLE_NAME PARTITION_NAME PARTITION_POSITION
-
------------------------------ ------------------------------ ------------------------------ ------------------
-
SCOTT EMP_P P1 1
-
SCOTT EMP_P P2 2
- SCOTT EMP_P P3 3
前面我們知道,分割槽表中有資料,下面我們看看其他分割槽是否有需要的資料
點選(此處)摺疊或開啟
-
SQL> select * from emp_p partition(p1);
-
-
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
-
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
-
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
-
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
-
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
-
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
-
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
-
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
-
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
-
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
-
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
-
7870 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
-
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
-
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
-
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
-
-
14 rows selected.
-
-
SQL> select * from emp_p partition(p3);
-
-
no rows selected
-
- SQL>
從上面內容可以看到,我們需要的資料在分割槽P1中,而執行查詢語句的時候Oracle找的卻是P2,那麼下面就看看該分割槽表的分割槽機制(建表資訊):
點選(此處)摺疊或開啟
-
CREATE TABLE "SCOTT"."EMP_P"
-
( "EMPNO" NUMBER(4,0),
-
"ENAME" VARCHAR2(10),
-
"JOB" VARCHAR2(9),
-
"MGR" NUMBER(4,0),
-
"HIREDATE" DATE,
-
"SAL" NUMBER(7,2),
-
"COMM" NUMBER(7,2),
-
"DEPTNO" NUMBER(2,0),
-
PRIMARY KEY ("EMPNO")
-
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
-
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
-
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
-
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
-
TABLESPACE "USERS" ENABLE
-
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
-
STORAGE(
-
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
-
TABLESPACE "USERS"
-
PARTITION BY RANGE ("EMPNO")
-
(PARTITION "P1" VALUES LESS THAN (7000) SEGMENT CREATION IMMEDIATE
-
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
-
NOCOMPRESS LOGGING
-
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
-
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
-
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
-
TABLESPACE "USERS" ,
-
PARTITION "P2" VALUES LESS THAN (8000) SEGMENT CREATION DEFERRED
-
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
-
NOCOMPRESS LOGGING
-
STORAGE(
-
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
-
TABLESPACE "USERS" ,
-
PARTITION "P3" VALUES LESS THAN (9000) SEGMENT CREATION DEFERRED
-
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
-
NOCOMPRESS LOGGING
-
STORAGE(
-
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
- TABLESPACE "USERS" )
select * from emp_p where empno=7870; 正常情況下該資料應該存放於分割槽P2,但卻存放到分割槽p1上了。
上面的執行計劃中也說明了,根據分割槽規則及where條件,Oracle會只會查詢分割槽P2,下面我們透過型別轉換再次檢視。
點選(此處)摺疊或開啟
-
SQL> select * from emp_p where to_char(empno)=7870;
-
-
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
-
7870 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
-
- SQL>
檢視該語句執行計劃:
點選(此處)摺疊或開啟
-
SQL> explain plan for select * from emp_p where to_char(empno)=7870;
-
-
Explained.
-
-
SQL> select * from table(dbms_xplan.display);
-
-
PLAN_TABLE_OUTPUT
-
------------------------------------------------------------------------------------------------------------------------
-
Plan hash value: 2733612900
-
-
---------------------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-
---------------------------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 1 | 87 | 3 (0)| 00:00:01 | | |
-
| 1 | PARTITION RANGE ALL| | 1 | 87 | 3 (0)| 00:00:01 | 1 | 3 |
-
|* 2 | TABLE ACCESS FULL | EMP_P | 1 | 87 | 3 (0)| 00:00:01 | 1 | 3 |
-
---------------------------------------------------------------------------------------------
-
-
Predicate Information (identified by operation id):
-
---------------------------------------------------
-
-
2 - filter(TO_NUMBER(TO_CHAR("EMPNO"))=7870)
-
-
Note
-
-----
-
- dynamic sampling used for this statement (level=2)
-
- 18 rows selected.
where條件無法檢索的原因我們知道了,在做資料轉換的時候新增了without validation,致使資料匯入了P1分割槽,應該匯入到P2,算是工作中失誤。有哪些方法會造成該語句查不出結果呢,後續會再做一個簡單的測試。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29487349/viewspace-1764438/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 【SQL】Oracle交換分割槽中引數without validation引發的症狀(二)SQLOracle
- 交換分割槽之without validation
- WITH VALIDATION 與WITHOUT VALIDATION對分割槽交換的影響
- 分割槽表中的maxvalue引數設定
- oracle之分割槽交換Oracle
- oracle儲存過程將引數字串分割sqlOracle儲存過程字串SQL
- 一個bug引發的Android分割槽儲存的思考Android
- 交換2個整形數引發的思考
- oracle分割槽交換(exchange)技術Oracle
- Oracle Vs MsSQL 之交換分割槽OracleSQL
- linux系統swappiness引數在記憶體與交換分割槽間優化LinuxAPP記憶體優化
- 使用預計算分割槽優化引數化優化
- pl/sql中的引數模式SQL模式
- linux系統swappiness引數在記憶體與交換分割槽間最佳化LinuxAPP記憶體
- 11g中的"_memory_imm_mode_without_autosga"引數
- 網路分割槽引發的oplog亂序問題
- linux交換分割槽Linux
- Qt 訊號槽如何傳遞引數(或帶引數的訊號槽)QT
- 全面學習分割槽表及分割槽索引(10)--交換分割槽索引
- oracle 之全文索引表的分割槽交換案例Oracle索引
- 檢視Oracle隱藏引數的SQLOracleSQL
- Oracle引數-隱藏引數Oracle
- oracle 交換分割槽歷史資料歸檔Oracle
- oracle實用sql(14)--查詢分割槽表的分割槽列和子分割槽列OracleSQL
- WebGeeker-Validation: 一個強大的 PHP 引數驗證器WebPHP
- oracle中的processes,session,transaction引數OracleSession
- Ubuntu 啟用交換分割槽Ubuntu
- Oracle中INITRANS和MAXTRANS引數Oracle
- oracle的一個隱含引數Oracle
- oracle 引數Oracle
- 選購交換機的引數依據和主要的引數指標詳解指標
- C#中的值引數,引用引數及輸出引數C#
- oracle分割槽索引(一)Oracle索引
- 使用PARTITION_OPTIONS引數控制資料泵分割槽表匯入
- 查詢oracle中的隱形引數Oracle
- Oracle升級中的引數補充Oracle
- js中如何在引號中巢狀引號JS巢狀
- Oracle 是分割槽表,但條件不帶分割槽條件的SQLOracleSQL