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 thoughts on “A new extended partition syntax in 11g”

  1. 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. 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.

  3. 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.

  4. 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.

  5. 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.

  6. 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.

  7. 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!

  8. 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 to Sheila Cancel Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to Top