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

xysoul_雲龍發表於2015-08-07
SQLOracle

前面我們知道無法查出資料原因了，詳情請參考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));

  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');

  8. PL/SQL procedure successfully completed.

  10. SQL> insert into test values(1,'firsoul');

  12. 1 row created.

  14. SQL> commit;
  15. SQL> alter session set query_rewrite_integrity=trusted;

  17. Session altered.

  19. SQL> select * from test where id=1;

  21. no rows selected

  23. SQL> select * from test where to_char(id)=1;

  25.         ID NAME
  26. ---------- ------------------------------
  27.          1 firsoul

  29. SQL>

我們看看該兩條語句的執行計劃：

點選(此處)摺疊或開啟

  1. SQL> explain plan for select * from test where id=1;

  3. Explained.

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

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

  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. ---------------------------------------------------------------------------

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

  22.    1 - filter(NULL IS NOT NULL)

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

  28. 18 rows selected.

  30. SQL> explain plan for select * from test where to_char(id)=1;

  32. Explained.

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

  36. PLAN_TABLE_OUTPUT
  37. ------------------------------------------------------------------------------------------------------------------------
  38. Plan hash value: 1357081020

  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. --------------------------------------------------------------------------

  47. Predicate Information (identified by operation id):
  48. ---------------------------------------------------

  50.    1 - filter(TO_NUMBER(TO_CHAR("ID"))=1)

  52. Note
  53. -----
  54.    - dynamic sampling used for this statement (level=2)

  56. 17 rows selected.

  58. SQL>
目前還有一些小知識點不太理解，大家有什麼好的方法，建議 可以告知我。

附，Oracle大牛老楊說的幾種實現的方法，有興趣的可以看看：
構建表和索引資料衝突；構建不滿足約束條件的表記錄；構建不滿足分割槽條件的資料；高階查詢重寫；DB FIREWALL；方法應該還有很多。


文盲筱燁 2015年8月7日

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29487349/viewspace-1764509/，如需轉載，請註明出處，否則將追究法律責任。

相關文章