ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 交换分区之without validation

交换分区之without validation

原创 Linux操作系统 作者:zecaro 时间:2011-04-26 21:59:04 0 删除 编辑

       看了 全面学习分区表及分区索引(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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: lisp入门
请登录后发表评论 登录
全部评论

注册时间:2010-10-28

  • 博文量
    182
  • 访问量
    348796