SQL & PL/SQL

Hi all,

I have a problem with the partitioning.

here is my table:

create table message_part
(
msg_id NUMBER(10) NOT NULL,
vehicle_id NUMBER(10) NOT NULL,
send_date DATE NOT NULL,
keep_data NUMBER(1)
);

Partition scope:
1. Easy delete of old data -> partition by range (send_date) -> one can delete a whole partition
2. Some messages must not be deleted -> data needs to be stored in different partition -> partition on multi-column (send_date AND keep_data)
3. additional performance boost by subpartition by hash (vehicle id)

I wanted to solve points 1 and 2 in the first step:


create table message_part
(
msg_id NUMBER(10) NOT NULL,
vehicle_id NUMBER(10) NOT NULL,
send_date DATE NOT NULL,
keep_data NUMBER(1)
)
PARTITION BY RANGE (send_date, keep_data)
(

PARTITION P_PRE_2013_DEL VALUES less than (to_date('01.01.2013 00:00:00','DD.MM.YYYY HH24:Mi:SS'), 0),
PARTITION P_PRE_2013_KEEP VALUES less than (to_date('01.01.2013 00:00:00','DD.MM.YYYY HH24:Mi:SS'), 2),
PARTITION P_2013_DEL1 VALUES less than (to_date('01.01.2014 00:00:00','DD.MM.YYYY HH24:Mi:SS'), 0),
PARTITION P_2013_KEEP1 VALUES less than (to_date('01.01.2014 00:00:00','DD.MM.YYYY HH24:Mi:SS'), 2)

);
insert into message_part values (1,200,sysdate-700,null);
insert into message_part values (2,200,sysdate-700,0);
insert into message_part values (3,200,sysdate-700,1);
insert into message_part values (4,200,sysdate-700,2);
commit;

After analyzing the table stats I see that the data is not stored as I expected:
select TABLE_NAME,PARTITION_NAME,NUM_ROWS from user_tab_partitions where TABLE_NAME='MESSAGE_PART';

TABLE_NAME PARTITION_NAME NUM_ROWS
MESSAGE_PART P_2013_DEL1 0
MESSAGE_PART P_2013_KEEP1 0
MESSAGE_PART P_PRE_2013_DEL 4
MESSAGE_PART P_PRE_2013_KEEP 0

I expected that at least 1 row in the partition P_PRE_2013_KEEP.
I expexted the range by clause works for both partition keys (send_date AND keep_data).

Can anyone tell me why (insert into message_part values (2,200,sysdate-700,0);) is not stored in P_PRE_2013_KEEP? (Sysdate is before 2013 and 0 is smaller than 2.

If i make this work I will try to add the sub partitioning for vehicle_id by hash.

Regards,
Andreas

Edited by: Andreas S. on 28.02.2013 05:31

Edited by: Andreas S. on 28.02.2013 05:36