oracle分区键使用大于小于会失效吗_Oracle通过分区键值发现性能问题

在很多应用中如果数据量少有规模，都会有大量的分区表存在，使用比较多的是range partition.

一般的range partition都一时间为键值，或者根据业务绑定的关键id值。

虽然已经做了一些大数据量的数据迁移，但是不管是按照分区抽取，还是根据数据条数抽取，发现有一个表比较奇怪，一个100G左右的分区表，80%以上的数据都分布在一个分区里面，而这个大分区表却有180多个分区表。

如下所示，对于表charge，如果分区的大小在200M以内，就标记为1，如果大于200M，则按照200M为单位进行统计，可以看到，如下的分区  P120_C10占用了大量的空间，其他的分区却小的可怜。很明显从业务规划的角度存在一定的问题。

CHARGE                              P120_C100                            1

CHARGE                              P120_C10                            438

CHARGE                              P120_C20                              1

CHARGE                              P120_C30                              1

CHARGE                              P120_C40                              1

CHARGE                              P120_C50                              1

CHARGE                              P120_C60                              1

CHARGE                              P120_C70                              1

CHARGE                              P120_C80                              1

CHARGE                              P120_C90                              1

CHARGE                              P25_C100                              1

CHARGE                              P25_C10                              2

CHARGE                              P25_C20                              1

CHARGE                              P25_C30                              1

CHARGE                              P25_C40                              1

CHARGE                              P25_C50                              1

CHARGE                              P25_C60                              1

CHARGE                              P25_C70                              1

CHARGE                              P25_C80                              1

CHARGE                              P25_C90                              1

CHARGE                              P26_C100                              1

CHARGE                              P26_C10                              1

CHARGE                              P26_C20                              1

CHARGE                              P26_C30                              1

CHARGE                              P26_C40                              1

CHARGE                              P26_C50                              1

CHARGE                              P26_C60                              1

CHARGE                              P26_C70                              1

CHARGE                              P26_C80                              1

CHARGE                              P26_C90                              1

CHARGE                              P27_C100                              1

CHARGE                              P27_C10                              1

CHARGE                              P27_C20                              1

CHARGE                              P27_C30                              1

CHARGE                              P27_C40                              1

CHARGE                              P27_C50                              1

带着这个疑问，和对应的开发人员进行了沟通，因为这个表已经使用很长时间了，他们想让我们提供一些关键的信息，比如分区的逻辑等，简单抽取了一些信息如下，

对于最大的分区P120_C10来说，High_value是120,10 直接看也看不出来什么问题。

PARTITION_NAME            HIGH_VALUE      TS_NAME    INI_TRANS LOGGING COMPRESS GLO LAST_ANAL

------------------------- --------------- ---------- ---------- ------- -------- --- ---------

.......

P41_C90                  41, 90          DATAH01            8 NO      DISABLED YES 15-AUG-14

P41_C100                  41, 100        DATAH01            8 NO      DISABLED YES 15-AUG-14

P120_C10                  120, 10        DATAH01            8 NO      DISABLED YES 15-AUG-14

P120_C20                  120, 20        DATAH01            8 NO      DISABLED YES 12-AUG-14

P120_C30                  120, 30        DATAH01            8 NO      DISABLED YES 12-AUG-14

P120_C40                  120, 40        DATAH01            8 NO      DISABLED YES 12-AUG-14

P120_C50                  120, 50        DATAH01            8 NO      DISABLED YES 12-AUG-14

P120_C60                  120, 60        DATAH01            8 NO      DISABLED YES 12-AUG-14

P120_C70                  120, 70        DATAH01            8 NO      DISABLED YES 12-AUG-14

P120_C80                  120, 80        DATAH01            8 NO      DISABLED YES 12-AUG-14

P120_C90                  120, 90        DATAH01            8 NO      DISABLED YES 12-AUG-14

P120_C100                120, 100        DATAH01            8 NO      DISABLED YES 12-AUG-14

根据最初的需求，是希望对于键值#1<120 键值#2<10的值，都能够插入到P120_C10 这个分区里面。

根据他们的期望，我对分区的数据进行了简单的分析，发现对于分区的键值在满足第一个分区的条件下，对于第二个键值的条件就直接忽略了。

select period_key,CUSTOMER_KEY from charge partition(P120_C10) group by  period_key,CUSTOMER_KEY order by period_key,customer_key

SQL> /

42            0

42            1

42            2

....

42          14

42          15

42          16

42          17

...

42          99

43            0

...

44          99

45            0

45            1

45            2

45            3

45            4

...

45          98

45          99

46            0

46            1

46            2

46            3

46            4

46            5

46            6

46            7

46            8

46            9

46          10

46          11

46          12

...

57          88

57          89

57          90

57          91

57          92

57          93

57          94

57          95

57          96

57          97

57          98

57          99

如果这样看，似乎有些不太合理了，是什么原因导致这些数据进入p120_c10了呢。

来做个简单的测试模拟一下，发现对于这个多键值的分区表,分区的情况和单键值还是有很大的差别，比较容易混淆和误导。当第一个键值的条件满足时，就忽略了第二个键值的条件，(比如(55,70)，55已经小于第一个键值了，就直接插入p120_c10了，忽略了后面的一个条件)

如果键值等于120的时候，就开始校验第二个条件了(比如(120,5), (120,15)都校验了后面的键值，数据分别进入了p120_c10,p120_c20这两个分区)

如果键值大于120的时候，如果没有默认的分区，就直接报错了，因为Oracle根据这种匹配还找不到对应的分区。

create table test (period_key number,customer_key number)

partition by range(period_key,customer_key)

(

partition p120_c10 values less than (120,10),

partition p120_c20 values less than (120,20),

partition p120_c30 values less than (120,30)

);

SQL> insert into test values(57,99);

1 row created.

SQL> insert into test values(57,150);

1 row created.

SQL> insert into test values(120,5);

1 row created.

SQL> insert into test values(119,50);

1 row created.

SQL> insert into test values(120,5);

1 row created.

SQL> insert into test values(120,15);

1 row created.

SQL> insert into test values(120,25);

1 row created.

SQL> insert into test values(120,30);

insert into test values(120,30)

*

ERROR at line 1:

ORA-14400: inserted partition key does not map to any partition

SQL> insert into test values(121,1);

insert into test values(121,1)

*

ERROR at line 1:

ORA-14400: inserted partition key does not map to any partition

SQL> select *from test partition(p120_c10);

PERIOD_KEY CUSTOMER_KEY

---------- ------------

57          99

57          150

120            5

119          50

120            5

SQL> select *from test partition(p120_c20);

PERIOD_KEY CUSTOMER_KEY

---------- ------------

120          15

SQL> select *from test partition(p120_c30);

PERIOD_KEY CUSTOMER_KEY

---------- ------------

120          25

对于这个问题，只能根据业务的角度进行重新规划来把数据进一步balance了。