前面我们知道无法查出数据原因了，详情请参考http://blog.itpub.net/29487349/viewspace-1764438/ 【SQL】Oracle交换分区中参数without validation引发的症状（一）

这次是通过实验测试，下面呈现环境部署：

创建一个分区表(SCOTT用户）

点击(此处)折叠或打开

create table emp_p(EMPNO number(4),ENAME varchar2(10),job varchar2(9),mgr number(4),hiredate date,sal number(7,2),comm number(7,2),deptno number(2))
partition by range(empno)
(partition p1 values less than(7000),
partition p2 values less than (8000)
partition p3 values less than (9000));

该分区表与表EMP进行数据交换：

点击(此处)折叠或打开

alter table emp_p exchange partition p1 with table emp without validation; --这里我添加了不验证数据

这也就造成了数据存放于P1，而根据where条件，Oracle会查找P2，无法获取数据。希望在运维过程中我们会注意到一些细节的东西。

分析表，并查看该表无效数据：

点击(此处)折叠或打开

SQL> @?/rdbms/admin/utlvalid.sql
SQL> analyze table scott.emp_p partition(p1) validate structure
-----
SQL> col owner_name for a10
SQL> select * from invalid_rows;

OWNER_NAME TABLE_NAME PARTITION_NAME SUBPARTITION_NAME HEAD_ROWID ANALYZE_TIMESTAMP
---------- --------------- ------------------------------ ------------------------------ ------------------ -------------------
SCOTT EMP_P P1 N/A AAAVRiAAEAAAACXAAA 2015-08-06 15:40:50
SCOTT EMP_P P1 N/A AAAVRiAAEAAAACXAAB 2015-08-06 15:40:50
SCOTT EMP_P P1 N/A AAAVRiAAEAAAACXAAC 2015-08-06 15:40:50
SCOTT EMP_P P1 N/A AAAVRiAAEAAAACXAAD 2015-08-06 15:40:50
SCOTT EMP_P P1 N/A AAAVRiAAEAAAACXAAE 2015-08-06 15:40:50
SCOTT EMP_P P1 N/A AAAVRiAAEAAAACXAAF 2015-08-06 15:40:50
SCOTT EMP_P P1 N/A AAAVRiAAEAAAACXAAG 2015-08-06 15:40:50
SCOTT EMP_P P1 N/A AAAVRiAAEAAAACXAAH 2015-08-06 15:40:50
SCOTT EMP_P P1 N/A AAAVRiAAEAAAACXAAI 2015-08-06 15:40:50
SCOTT EMP_P P1 N/A AAAVRiAAEAAAACXAAJ 2015-08-06 15:40:50
SCOTT EMP_P P1 N/A AAAVRiAAEAAAACXAAK 2015-08-06 15:40:50
SCOTT EMP_P P1 N/A AAAVRiAAEAAAACXAAL 2015-08-06 15:40:50
SCOTT EMP_P P1 N/A AAAVRiAAEAAAACXAAM 2015-08-06 15:40:50
SCOTT EMP_P P1 N/A AAAVRiAAEAAAACXAAN 2015-08-06 15:40:50

其实通过分析，Oracle已给出，改分区无效数据。
这是还原问题，再诊断轻松、容易太多，当真出现该问题时，也许需要耗费更多东西，也就促使我们不断学习，掌握更多方法。此次试验过程如有其他问题，请联系，一起进步。

下面是通过另一种方式呈现该问题：

点击(此处)折叠或打开

SQL> create table test (id int,name varchar2(30));
Table created.
SQL> alter table test add primary key(id);
Table altered.
SQL> exec sys.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE('test_emp_rewrite','select * from test where id=1','select * from test where 1=2');
PL/SQL procedure successfully completed.
SQL> insert into test values(1,'firsoul');
1 row created.
SQL> commit;
SQL> alter session set query_rewrite_integrity=trusted;
Session altered.
SQL> select * from test where id=1;
no rows selected
SQL> select * from test where to_char(id)=1;
ID NAME
---------- ------------------------------
1 firsoul
SQL>

我们看看该两条语句的执行计划：

点击(此处)折叠或打开

SQL> explain plan for select * from test where id=1;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 766971379
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 0 (0)| |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| TEST | 1 | 30 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NULL IS NOT NULL)
Note
-----
- dynamic sampling used for this statement (level=2)
18 rows selected.
SQL> explain plan for select * from test where to_char(id)=1;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 30 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER(TO_CHAR("ID"))=1)
Note
-----
- dynamic sampling used for this statement (level=2)
17 rows selected.
SQL>

目前还有一些小知识点不太理解，大家有什么好的方法，建议可以告知我。

附，Oracle大牛老杨说的几种实现的方法，有兴趣的可以看看：
构建表和索引数据冲突；构建不满足约束条件的表记录；构建不满足分区条件的数据；高级查询重写；DB FIREWALL；方法应该还有很多。

文盲筱烨 2015年8月7日

ITPub博客

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

最新文章