A new extended partition syntax in 11g

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

,

15 Responses to A new extended partition syntax in 11g

  1. Romeo December 10, 2008 at 4:27 am #

    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 December 10, 2008 at 2:58 pm #

    Romeo,
    Yes I agree.

    Thanks for stopping by.

    IC

  3. Murali February 28, 2012 at 4:36 am #

    Nice doc’s

  4. Amy February 11, 2013 at 2:36 am #

    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 February 12, 2013 at 2:27 am #

    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 February 13, 2013 at 9:08 pm #

    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 February 17, 2013 at 8:26 am #

    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 February 17, 2013 at 8:32 am #

    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 February 18, 2013 at 7:06 am #

    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 April 23, 2013 at 9:30 pm #

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

  11. Armand April 24, 2013 at 9:18 am #

    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 April 26, 2013 at 3:28 pm #

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

  13. Katrice May 31, 2013 at 3:07 pm #

    Great post.

  14. Marlys June 11, 2013 at 10:47 pm #

    I would like this site significantly a lot great info.

  15. Douglas June 22, 2013 at 4:11 am #

    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

Powered by WordPress. Designed by WooThemes