看了全面學習分割槽表及分割槽索引(10)--交換分割槽這篇，最後是關於使用了without validation子句後，不會再驗證資料的有效性。究竟使用不當會有什麼結果，試下。

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

create table t_partition_range (id number,name varchar2(50))

partition by range(id)(

partition t_range_p1 values less than (10) ,

partition t_range_p2 values less than (20),

partition t_range_p3 values less than (30) ,

partition t_range_pmax values less than (maxvalue)

);

insert into t_partition_range values (11,'a');

insert into t_partition_range values (12,'b');

insert into t_partition_range values (13,'c');

commit;

insert into t_partition_range_tmp values (15,'d');

insert into t_partition_range_tmp values (16,'e');

insert into t_partition_range_tmp values (17,'d');

commit;

SQL> select * from t_partition_range partition(t_range_p2);

        ID NAME
---------- --------------------------------------------------
        11 a
        12 b
        13 c

SQL> select * from t_partition_range_tmp;

        ID NAME
---------- --------------------------------------------------
        15 d
        16 e
        17 d
         8 g

--和分割槽1交換
SQL> alter table t_partition_range exchange partition t_range_p1
2 with table t_partition_range_tmp ;
with table t_partition_range_tmp
*
ERROR at line 2:
ORA-14099: all rows in table do not qualify for specified partition

$ oerr ora 14099
14099, 00000, "all rows in table do not qualify for specified partition"
// *Cause: There is at least one row in the non partitioned table which
//          does not qualify for the partition specified in the ALTER TABLE
//          EXCHANGE PARTITION
// *Action: Ensure that all the rows in the segment qualify for the partition.
//          Perform. the alter table operation with the NO CHECKING option.
//          Run ANALYZE table VALIDATE on that partition to find out the
//          invalid rows and delete them.

--和分割槽1交換，加上without validation

SQL> alter table t_partition_range exchange partition t_range_p1
2 with table t_partition_range_tmp without validation;

Table altered.

SQL> select * from t_partition_range partition(t_range_p1);

        ID NAME
---------- --------------------------------------------------
        15 d
        16 e
        17 d
         8 g

SQL> set autotrace on
SQL> select * from t_partition_range where id>10;

        ID NAME
---------- --------------------------------------------------
        11 a
        12 b
        13 c

Execution Plan
----------------------------------------------------------
Plan hash value: 955328034

--------------------------------------------------------------------------------------------------------------
| Id | Operation                | Name              | Rows | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                   |     3 |   120 |     3   (0)| 00:00:01 |       |       |
|   1 | PARTITION RANGE ITERATOR|                   |     3 |   120 |     3   (0)| 00:00:01 |     2 |     4 |
|* 2 |   TABLE ACCESS FULL      | T_PARTITION_RANGE |     3 |   120 |     3   (0)| 00:00:01 |     2 |     4 |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("ID">10)

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          5 recursive calls
          0 db block gets
         30 consistent gets
          0 physical reads
          0 redo size
        639 bytes sent via SQL*Net to client
        488 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          3 rows processed

SQL> select * from t_partition_range;

        ID NAME
---------- --------------------------------------------------
        15 d
        16 e
        17 d
         8 g
        11 a
        12 b
        13 c

7 rows selected.

可以看到，當查詢where id>10時，使用分割槽特性過濾了分割槽1，掃描了2-4。

一個分割槽裡存在著不屬於這個分割槽的資料，查詢查不到一些本該查到的資料。

這就是without validation使用不慎的結果吧。

最後，再看這個

SQL> select * from t_partition_range partition(t_range_p1) where id>10;

        ID NAME
---------- --------------------------------------------------
        15 d
        16 e
        17 d

Execution Plan
----------------------------------------------------------
Plan hash value: 3894260365

------------------------------------------------------------------------------------------------------------
| Id | Operation              | Name              | Rows | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                   |     3 |   120 |     3   (0)| 00:00:01 |       |       |
|   1 | PARTITION RANGE SINGLE|                   |     3 |   120 |     3   (0)| 00:00:01 |     1 |     1 |
|* 2 |   TABLE ACCESS FULL    | T_PARTITION_RANGE |     3 |   120 |     3   (0)| 00:00:01 |     1 |     1 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("ID">10)

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          4 recursive calls
          0 db block gets
         16 consistent gets
          0 physical reads
          0 redo size
        639 bytes sent via SQL*Net to client
        488 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          3 rows processed

按照分割槽表的定義，其實這個分割槽不該有大於10的資料。當然剛剛製造了些。Oracle在這裡以及之前的查詢都有一個 filter("ID">10)，或者這樣更可靠些？畢竟存在著不該出現的資料的可能。

不加 without validation子時，會檢驗資料的有效性，是做了全表掃描麼（如果對應id如有索引呢？）？如果需要做全表掃描，還很快麼？

交換分割槽之without validation

相關文章