eagle’s home

在oracle 9205版本composite partition key partition上做partition split时有一个莫名其妙的bug

SQL> create table test(x int, y int)
2 partition by range(x,y) (
3 partition p01 values less than (maxvalue,maxvalue));

Table created.

SQL> create index test_idx on test(x,y) local;

Index created.

SQL> begin
2 for i in 1..10 loop
3 for j in 1..100 loop
4 insert into test values(i,j);
5 end loop;
6 end loop;
7 end;
8 /

PL/SQL procedure successfully completed.

SQL> select segment_name,partition_name,header_file,header_block from dba_segments
2 where segment_name in (‘TEST’,'TEST_IDX’) and owner=’XFAN’;

SEGMENT_NAME PARTITION_NAME HEADER_FILE HEADER_BLOCK
—————————————- —————————— ———– ————
TEST P01 13 1801
TEST_IDX P01 13 1825

SQL> alter table test split partition p01 at (10,101)
2 into (partition p01,partition p02);

Table altered.

SQL> select segment_name,partition_name,header_file,header_block from dba_segments
2 where segment_name in (‘TEST’,'TEST_IDX’) and owner=’XFAN’;

SEGMENT_NAME PARTITION_NAME HEADER_FILE HEADER_BLOCK
—————————————- —————————— ———– ————
TEST P02 13 1841
TEST P01 13 1833
TEST_IDX P01 13 1849 — 索引被重建了！！
TEST_IDX P02 14 729

SQL> select count(8) from test partition(P01);

COUNT(8)
———-
1000

SQL> select count(*) from test partition(p02);

COUNT(*)
———-
0

明明是分出来一个空的partition，竟然将旧的partition的索引重建了。虽然数据是放对位置了，但是当P01很大的时候，这项操作需要耗费非常长的时间，更本无法接受。

打开SQL Trace，竟然看到这样的SQL:

select /*+ FIRST_ROWS PARALLEL(TEST,1) */ 1 from “XFAN”.”TEST” PARTITION (“P01″) where ((“X” >= 10 OR “X” is null) OR “X” = 10 AND (“Y” >= 101 OR “Y” is null)) and rownum < 2

很明显，这个SQL是不对的，在10g和9207下做同样的试验，看的的SQL就是正确的：

select /*+ FIRST_ROWS PARALLEL(“TEST”, 1) */ 1 from “SYS”.”TEST” PARTITION (“P01″) where ( ( ( ( “X” > 10 OR “X” IS NULL ) ) OR ( “X” = 10 AND ( “Y” >= 101 OR “Y” IS NULL ) ) ) ) and rownum < 2

一字之差害死人，估计当时也不知道是哪儿写程序的粗心大意一把

metalink上bug report轻描淡写的：

解决办法倒是有，比较的tricky

SQL> create table test(x int, y int)
2 partition by range(x,y) (
3 partition p01 values less than (maxvalue,maxvalue));

Table created.

SQL> create index test_idx on test(x,y) local;

Index created.

SQL> begin
2 for i in 1..10 loop
3 for j in 1..100 loop
4 insert into test values(i,j);
5 end loop;
6 end loop;
7 end;
8 /

PL/SQL procedure successfully completed.

SQL> select segment_name,partition_name,header_file,header_block from dba_segments
2 where segment_name in (‘TEST’,'TEST_IDX’) and owner=’XFAN’;

SEGMENT_NAME PARTITION_NAME HEADER_FILE HEADER_BLOCK
—————————————- —————————— ———– ————
TEST P01 13 1801
TEST_IDX P01 13 1825

SQL> alter table test split partition p01 at (11,101)
2 into (partition p01,partition p02);

Table altered.

SQL> select segment_name,partition_name,header_file,header_block from dba_segments
2 where segment_name in (‘TEST’,'TEST_IDX’) and owner=’XFAN’;

SEGMENT_NAME PARTITION_NAME HEADER_FILE HEADER_BLOCK
—————————————- —————————— ———– ————
TEST P02 13 1833
TEST P01 13 1801
TEST_IDX P01 13 1825
TEST_IDX P02 13 1841

SQL> create table temp(x int,y int);

Table created.

SQL> create index temp_idx on temp(x,y);

Index created.

SQL> alter table test exchange partition p01 with table temp including indexes without validation;

Table altered.

SQL> alter table test split partition P01 at (10,101)
2 into (partition P01,partition P_temp);

Table altered.

SQL> alter table test exchange partition p01 with table temp including indexes without validation;

Table altered.

SQL> select count(8) from test partition(p01);

COUNT(8)
———-
1000

SQL> select count(*) from test partition(p02);

COUNT(*)
———-
0

SQL> select count(*) from test partition(P_TEMP);

COUNT(*)
———-
0

SQL> alter table test merge partitions P_TEMP,P02 into partition P02;

Table altered.

SQL> select segment_name,partition_name,header_file,header_block from dba_segments
2 where segment_name in (‘TEST’,'TEST_IDX’) and owner=’XFAN’;

SEGMENT_NAME PARTITION_NAME HEADER_FILE HEADER_BLOCK
—————– —————————— ———– ————
TEST P02 13 1849
TEST P01 13 1801
TEST_IDX P01 13 1825
TEST_IDX P02 14 729

是不是感觉有点眼花缭乱？其实根本的目的就是split在正确的点上并且避免index rebuild

Eagle Fan oracle