This discussion is archived
5 Replies Latest reply: Apr 12, 2012 3:38 AM by 32685 RSS

Doubt in Table Partitioning

874471 Newbie
Currently Being Moderated
Hi All,
I have a doubt in partitioning a table.
Our database version is 11.2.0.1.0.

I have created a table with below details.
CREATE TABLE XXtest_TAB
(
SCENARIO VARCHAR2(50 CHAR),
VERSION NUMBER,
BUS_UNIT VARCHAR2(100 CHAR),
RUN_DATE DATE,
PRODUCT_SKU VARCHAR2(100 CHAR),
CUSTOMER VARCHAR2(100 CHAR),
ORGANISATION VARCHAR2(100 CHAR),
TIME_BUCKET DATE )
partition by range (time_bucket)
interval (numtoyminterval(1,'MONTH'))
subpartition by list (bus_unit)
(partition p1 values less than (to_date('01-04-2010', 'dd-mm-yyyy'))
(
subpartition "A" values ('A'),
subpartition "B" values ('B'),
subpartition "C" values ('C'),
subpartition "D" values ('D')
)
);


Here, we will be using this table to store the forecasted data. The program will be run every week.
So everytime I run the program, my program will delete the old data and insert the new data for next 1 year.

My questions here are, how oracle will behave...
1) Will it create new partitions when the data for new month will come in?
2) Will it drop the old partitions for which there is no data as I am storing the future data (data greater than SYSDATE only) everytime I run the program.

Please help.

Thanks,
S

Edited by: 871468 on 11-Apr-2012 07:32
  • 1. Re: Doubt in Table Partitioning
    874471 Newbie
    Currently Being Moderated
    please help
  • 2. Re: Doubt in Table Partitioning
    rp0428 Guru
    Currently Being Moderated
    You created the table so why don't you insert some records into it and see what happens?
    After the inserts you can check the partitions and subpartitions that were created using
    -- partition query
    SELECT PARTITION_NAME, SUBPARTITION_COUNT
    FROM ALL_TAB_PARTITIONS
    WHERE TABLE_OWNER = 'SCOTT'
      AND TABLE_NAME = 'MY_PARTITIONED_TABLE'
     
    -- subpartition query
    SELECT PARTITION_NAME, SUBPARTITION_NAME
    FROM ALL_TAB_SUBPARTITIONS
    WHERE TABLE_OWNER = 'SCOTT'
      AND TABLE_NAME = 'MY_PARTITIONED_TABLE'
    1) Will it create new partitions when the data for new month will come in?
    2) Will it drop the old partitions for which there is no data as I am storing the future data (data greater than SYSDATE only) everytime I run the
    >
    1 - Yes it will as you will see if you insert some data and run the queries I provided.
    2 - No Oracle does not drop partitions or tables on its own. The user has to do that.

    If you are really generating completely new data for all future months (that is the old July data will be replace by new July data) then you should just create a new table for each forecast and drop the old table.

    Otherwise you will need to drop the partitions you don't want and then load data for partitions you want new.
    Or you could just use TRUNCATE TABLE REUSE STORAGE to truncate all partitions and then do a reload.
  • 3. Re: Doubt in Table Partitioning
    32685 Expert
    Currently Being Moderated
    rp0428 wrote:
    If you are really generating completely new data for all future months (that is the old July data will be replace by new July data) then you should just create a new table for each forecast and drop the old table.
    Why would you want to do that rather than use partitioning? Surely creating new tables and dropping old ones means you either have to use dynamic code to determine the names of the segments or put synonyms or views on top of them...I'm not sure what advantage this brings.
  • 4. Re: Doubt in Table Partitioning
    rp0428 Guru
    Currently Being Moderated
    >
    Why would you want to do that rather than use partitioning? Surely creating new tables and dropping old ones means you either have to use dynamic code to determine the names of the segments or put synonyms or views on top of them...I'm not sure what advantage this brings.
    >
    As I said in the next paragraph
    >
    Otherwise you will need to drop the partitions you don't want and then load data for partitions you want new.
    Or you could just use TRUNCATE TABLE REUSE STORAGE to truncate all partitions and then do a reload.
    >
    OP is using partitioning by month and I never said anything about not using partitioning.

    But OP also said
    >
    The program will be run every week.
    So everytime I run the program, my program will delete the old data and insert the new data for next 1 year.
    >
    If ALL of the old data is being deleted then why is the table partitioned to begin with? And why not just truncate the table, partitioned or not, before you regenerate the new data.

    So if 'delete the old data' means the data generated for next June will be deleted and regenerated because it might change (even if it doesn't change) then just truncate the data. The only 'waste' will be old weeks whose partition you don't need anymore that may need to be dropped.

    But you don't need to use dynamic code or views/synonyms: drop the table and recreate it using the same DDL that was used to begin with.

    There are too many unknowns to try to guess at what an optimal solution might be.

    1. What is the rationale for using partitioning to begin with?
    2. How will the data be queried during the week?
    3. How much data are we talking about?
    4. Is there a 'window' where data isn't needed that the truncate/reload can be performed in?
    5. Why not use weekly partitioning if data is being generated by week?

    Unless we know the specifics the range of solutions is pretty wide open.
  • 5. Re: Doubt in Table Partitioning
    32685 Expert
    Currently Being Moderated
    rp0428 wrote:
    >
    Why would you want to do that rather than use partitioning? Surely creating new tables and dropping old ones means you either have to use dynamic code to determine the names of the segments or put synonyms or views on top of them...I'm not sure what advantage this brings.
    >
    As I said in the next paragraph
    >
    Otherwise you will need to drop the partitions you don't want and then load data for partitions you want new.
    Or you could just use TRUNCATE TABLE REUSE STORAGE to truncate all partitions and then do a reload.
    >
    OP is using partitioning by month and I never said anything about not using partitioning.
    Hmm, I must have completely misunderstood what you meant when you said
    rp0428 wrote:
    If you are really generating completely new data for all future months (that is the old July data will be replace by new July data) then you should just create a new table for each forecast and drop the old table.
    I did read the paragraph where you suggested the possibility of truncating which would of course be a viable option as are some of the others you suggested. The point I was making is that dropping and creating tables on the fly as per your suggestion is a messy implementation with quite a number of drawbacks.
    Unless we know the specifics the range of solutions is pretty wide open.
    Absolutely agreed.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points