交换分区之without validation

       看了 全面学习分区表及分区索引(10)--交换分区 这篇，最后是关于使用了without validation子句后，不会再验证数据的有效性。究竟使用不当会有什么结果，试下。

（所用的表是 全面学习分区表及分区索引(10)--交换分区 的表）。

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如有索引呢？）？如果需要做全表扫描，还很快么？

      

 

 

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

转载于:http://blog.itpub.net/23650854/viewspace-693878/