看了 全面学习分区表及分区索引(10)--交换分区 这篇,最后是关于使用了without validation子句后,不会再验证数据的有效性。究竟使用不当会有什么结果,试下。
(所用的表是 全面学习分区表及分区索引(10)--交换分区 的表)。
SQL> select * from v$version; BANNER |
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 SQL> select * from t_partition_range_tmp; ID NAME
--和分区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
--和分区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
------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): 2 - filter("ID">10) Note
按照分区表的定义,其实这个分区不该有大于10的数据。当然刚刚制造了些。Oracle在这里以及之前的查询都有一个 filter("ID">10),或者这样更可靠些?毕竟存在着不该出现的数据的可能。 不加 without validation子时,会检验 数据的有效性 ,是做了全表扫描么(如果对应id如有索引呢?)?如果需要做全表扫描,还很快么? |
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23650854/viewspace-693878/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/23650854/viewspace-693878/