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

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

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

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

点击(此处)折叠或打开

1. 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))
   
2. partition by range(empno)
   
3. (partition p1 values less than(7000),
   
4. partition p2 values less than (8000)
   
5. partition p3 values less than (9000));

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

点击(此处)折叠或打开

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

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

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

点击(此处)折叠或打开

1. SQL> @?/rdbms/admin/utlvalid.sql
   
2. SQL> analyze table scott.emp_p partition(p1) validate structure
3. -----
4. 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已给出，改分区无效数据。
这是还原问题，再诊断轻松、容易太多，当真出现该问题时，也许需要耗费更多东西，也就促使我们不断学习，掌握更多方法。此次试验过程如有其他问题，请联系，一起进步。

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

点击(此处)折叠或打开

1. SQL> create table test (id int,name varchar2(30));
   
2. 
   
3. Table created.
   
4. SQL> alter table test add primary key(id);
5. 
   Table altered.
   
   
6. SQL> exec sys.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE('test_emp_rewrite','select * from test where id=1','select * from test where 1=2');
   
7. 
   
8. PL/SQL procedure successfully completed.
   
9. 
   
10. SQL> insert into test values(1,'firsoul');
    
11. 
    
12. 1 row created.
    
13. 
    
14. SQL> commit;
    
15. SQL> alter session set query_rewrite_integrity=trusted;
    
16. 
    
17. Session altered.
    
18. 
    
19. SQL> select * from test where id=1;
    
20. 
    
21. no rows selected
    
22. 
    
23. SQL> select * from test where to_char(id)=1;
    
24. 
    
25.         ID NAME
    
26. ---------- ------------------------------
    
27.          1 firsoul
    
28. 
    
29. SQL>

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

点击(此处)折叠或打开

1. SQL> explain plan for select * from test where id=1;
   
2. 
   
3. Explained.
   
4. 
   
5. SQL> select * from table(dbms_xplan.display);
   
6. 
   
7. PLAN_TABLE_OUTPUT
   
8. ------------------------------------------------------------------------------------------------------------------------
   
9. Plan hash value: 766971379
   
10. 
    
11. ---------------------------------------------------------------------------
    
12. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    
13. ---------------------------------------------------------------------------
    
14. | 0 | SELECT STATEMENT | | 1 | 30 | 0 (0)| |
    
15. |* 1 | FILTER | | | | | |
    
16. | 2 | TABLE ACCESS FULL| TEST | 1 | 30 | 3 (0)| 00:00:01 |
    
17. ---------------------------------------------------------------------------
    
18. 
    
19. Predicate Information (identified by operation id):
    
20. ---------------------------------------------------
    
21. 
    
22.    1 - filter(NULL IS NOT NULL)
    
23. 
    
24. Note
    
25. -----
    
26.    - dynamic sampling used for this statement (level=2)
    
27. 
    
28. 18 rows selected.
    
29. 
    
30. SQL> explain plan for select * from test where to_char(id)=1;
    
31. 
    
32. Explained.
    
33. 
    
34. SQL> select * from table(dbms_xplan.display);
    
35. 
    
36. PLAN_TABLE_OUTPUT
    
37. ------------------------------------------------------------------------------------------------------------------------
    
38. Plan hash value: 1357081020
    
39. 
    
40. --------------------------------------------------------------------------
    
41. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    
42. --------------------------------------------------------------------------
    
43. | 0 | SELECT STATEMENT | | 1 | 30 | 3 (0)| 00:00:01 |
    
44. |* 1 | TABLE ACCESS FULL| TEST | 1 | 30 | 3 (0)| 00:00:01 |
    
45. --------------------------------------------------------------------------
    
46. 
    
47. Predicate Information (identified by operation id):
    
48. ---------------------------------------------------
    
49. 
    
50.    1 - filter(TO_NUMBER(TO_CHAR("ID"))=1)
    
51. 
    
52. Note
    
53. -----
    
54.    - dynamic sampling used for this statement (level=2)
    
55. 
    
56. 17 rows selected.
    
57. 
    
58. SQL>

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

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


文盲筱烨 2015年8月7日

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

转载于:http://blog.itpub.net/29487349/viewspace-1764509/