Dec 07 2008

A new extended partition syntax in 11g

Category: 11g,Partitionittichai @ 5:30 am

A new extended partition syntax can be used to designate a partition without knowing its name. The syntax must refer to a possible value for the partition. This syntax works for all cases when you have to reference a partition, whether it be range, list, interval, or hash. It supports all operations such as drop, merge, split, and so on.

Some samples are shown below -


create table SALES (
  id              number,
  order_date      date
)
partition by range (order_date)
(
  partition p1 values less than
     (to_date('01/01/2008','mm/dd/yyyy')),
  partition p2 values less than
     (to_date('02/01/2008','mm/dd/yyyy')),
  partition p3 values less than
     (to_date('03/01/2008','mm/dd/yyyy'))
);

Generally when you want to merge partitions, the syntax will have to refer to the partition names.


alter table SALES merge partitions p2, p3
into partition p2_3;

However, in 11g, the same can be accomplished by referring to a possible value for the partition with use of “for” syntax.


alter table SALES merge partitions
   for(to_date('01/12/2008','mm/dd/yyyy')),
   for(to_date('02/15/2008','mm/dd/yyyy'))
into partition p2_3;

SQL> select table_name, partition_name, high_value
from user_tab_partitions
where table_name = 'SALES';

TABLE_NAME PARTITION_ HIGH_VALUE
---------- ---------- ------------------------------
SALES      P1         TO_DATE(' 2008-01-01 00:00:00'
                      , 'SYYYY-MM-DD HH24:MI:SS', 'N
                      LS_CALENDAR=GREGORIA

SALES      P2_3       TO_DATE(' 2008-03-01 00:00:00'
                      , 'SYYYY-MM-DD HH24:MI:SS', 'N
                      LS_CALENDAR=GREGORIA

Or with dropping a partition -


SQL> alter table SALES drop partition
for(to_date('02/15/2008','mm/dd/yyyy'));

Table altered.

SQL> select table_name, partition_name, high_value
from user_tab_partitions
where table_name = 'SALES';

TABLE_NAME PARTITION_ HIGH_VALUE
---------- ---------- ------------------------------
SALES      P1         TO_DATE(' 2008-01-01 00:00:00'
                      , 'SYYYY-MM-DD HH24:MI:SS', 'N
                      LS_CALENDAR=GREGORIA

Tags: ,

15 Responses to “A new extended partition syntax in 11g”

  1. Romeo says:

    A new cool feature is the interval partitioning as well. With the interval partitioning you don’t have other options but the new ‘for’ clause.

    Nice article !

  2. IC says:

    Romeo,
    Yes I agree.

    Thanks for stopping by.

    IC

  3. Murali says:

    Nice doc’s

  4. Amy says:

    Além disso, não é possível para os indivíduos para passar o tempo em academias e clubes de saúde por causa de seus compromissos profissionais. Logo, é a sua preferência em primeiro lugar para conseguir uma solução aberto e conveniente que é benévolo o suficiente para cumprir o seu finalidade sem afetar suas programações. Em tais condições, zero poderia ser melhor do que Fort Max Diet.

  5. Victorina says:

    I really like what you guys are up too. This type of clever work and reporting! Keep up the superb works guys I’ve incorporated you guys to our blogroll.

  6. Karl says:

    Nice post. I used to be checking continuously this weblog and I’m inspired! Very helpful information specially the closing part :) I maintain such information much. I was looking for this particular info for a very long time. Thanks and best of luck.

  7. Cheryl says:

    Good post. I learn something totally new and challenging on blogs I stumbleupon on a daily basis. It will always be helpful to read through articles from other writers and use something from other web sites.

  8. Carl says:

    Nice post. I learn something new and challenging on sites I stumbleupon every day. It’s always interesting to read through articles from other writers and practice something from their websites.

  9. Cierra says:

    Thanks for some other wonderful post. Where else could anybody get that kind of information in such a perfect approach of writing? I’ve a presentation next week, and I’m at the look for such info.

  10. Sheila says:

    Very nice article. I definitely love this site. Thanks!

  11. Armand says:

    I just like the helpful information you provide on your articles. I will bookmark your blog and take a look at once more right here regularly. I am rather sure I will be told a lot of new stuff right here! Best of luck for the next!

  12. Juli says:

    Good article. I absolutely appreciate this website. Stick with it!

  13. Marlys says:

    I would like this site significantly a lot great info.

  14. Douglas says:

    After I originally left a comment I seem to have clicked the -Notify me when new comments are added- checkbox and now whenever a comment is added I recieve four emails with the same comment. Perhaps there is an easy method you are able to remove me from that service? Thanks!

Leave a Reply