1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Table partition

Discussion in 'SQL PL/SQL' started by Vamsi Sanku, Jun 25, 2017.

Tags:
  1. Vamsi Sanku

    Vamsi Sanku Starter

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    Hyderabad
    I have a requirement of partitioning a table first on region_id column then after sub partition by year column and then by month column.How to do this three level partition on a table.
    I am thankful to any suggestions to achieve this requirement.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,616
    Likes Received:
    364
    Trophy Points:
    1,430
    Location:
    Aurora, CO
  3. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    774
    Likes Received:
    147
    Trophy Points:
    830
    Location:
    Russian Federation
    What is the amount of data per month and per year ?What queries will be run : selection by year/region month/region by region ?

    May be helpful:
    1) Partitioning
    2) Example of partitioning
     
  4. Vamsi Sanku

    Vamsi Sanku Starter

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    Hyderabad
    Thanks for your reply Krasnoslobodtsev

    Table contains 30 Million records (approx).From Oracle doc i can do only two level partition like partition on year and then sub-partition on month or partition on region and sub-partition on year or partition on region and sub partition on year_month column. But what i am trying to do is to create main partition on region and then i want to create two sub-partition levels by year and by month, simply i want to create two sub-partitions.Is it possible for creating two sub-partition levels ?

    Regards
    Vamsi
     
  5. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    774
    Likes Received:
    147
    Trophy Points:
    830
    Location:
    Russian Federation
    There are only two levels of partition : partition and sub-partition.If you select an incorrect partitioning keys, we will get inefficient model( for example - too many small segments). I believe that maybe in your case, to partition the table by month(interval) and it is possible to create a local index on the region.