熊仔阳光站

DBA’s can finally use the Automatic Interval Partitioning option.

Test steps following the Oracle By Example (OBE) scripts available at
http://otnbeta.us.oracle.com/db11gr1/trng/obes/index.htm:

– create interval partitioned table

create table newsales
( prod_id number(6) not null
, cust_id number not null
, time_id date not null
, channel_id char(1) not null
, promo_id number(6) not null
, quantity_sold number(3) not null
, amount_sold number(10,2) not null
)
partition by range (time_id)
interval (numtodsinterval(1,’DAY’))
( partition p_before_1_jan_2005 values
less than (to_date(’01-01-2005′,’dd-mm-yyyy’)))
/

Table is created.

Show partitions and partition boundaries for the interval partitioned table

select partition_name, high_value
from user_tab_partitions
where table_name = ‘NEWSALES’
order by partition_position
/

PARTITION_NAME HIGH_VALUE
————————- ————————————————————————————-
P_BEFORE_1_JAN_2005 TO_DATE(’ 2005-01-01 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’)

Insert some data after January 1 2005 that forces the creation of a new partition (segment)

insert into newsales values (11160,17450,to_date(’01-jan-2005′,’dd-mon-yyyy’),’I’,9999,19,798) ;
insert into newsales values (1340,33710,to_date(’02-jan-2005′,’dd-mon-yyyy’),’S’,9999,16,1264) ;
insert into newsales values (25270,65880,to_date(’05-jan-2005′,’dd-mon-yyyy’),’I’,9999,5,210) ;
insert into newsales values (1615,73480,to_date(’05-jan-2005′,’dd-mon-yyyy’),’I’,9999,8,96) ;
insert into newsales values (1900,84910,to_date(’06-jan-2005′,’dd-mon-yyyy’),’I’,9999,42,378) ;
insert into newsales values (8085,37900,to_date(’09-jan-2005′,’dd-mon-yyyy’),’S’,9999,1,68) ;
insert into newsales values (755,26590,to_date(’09-jan-2005′,’dd-mon-yyyy’),’I’,9999,11,132) ;
insert into newsales values (10,68060,to_date(’09-jan-2005′,’dd-mon-yyyy’),’P’,9999,28,4900) ;
insert into newsales values (13425,109310,to_date(’10-jan-2005′,’dd-mon-yyyy’),’I’,9999,1,68) ;
insert into newsales values (1955,65190,to_date(’10-jan-2005′,’dd-mon-yyyy’),’S’,9999,28,1512) ;

commit
/

Check again:

select partition_name, high_value
from user_tab_partitions
where table_name = ‘NEWSALES’
order by partition_position
/

PARTITION_NAME HIGH_VALUE
————————- ————————————————————————————-
P_BEFORE_1_JAN_2005 TO_DATE(’ 2005-01-01 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’)
SYS_P41 TO_DATE(’ 2005-01-02 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’)
SYS_P42 TO_DATE(’ 2005-01-03 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’)
SYS_P43 TO_DATE(’ 2005-01-06 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’)
SYS_P44 TO_DATE(’ 2005-01-07 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’)
SYS_P45 TO_DATE(’ 2005-01-10 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’)
SYS_P46 TO_DATE(’ 2005-01-11 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’)

7 rows selected.

Wow ==> Automatic Interval Partitioning Works Fine.

But when people insert strange values you might end up with a lot of partitions you don’t really want to have.
So does this really help the lazydba?

#######
Conclusion: In 11g finally Automatic Interval Partitioning is available.
No more full overflow partitions, and DBA’s who forgot to create a new partition on time.

Popularity: 13%