Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2004
    Posts
    113

    Unanswered: Partitioning Question

    We have a historical table that is 125+ million rows. The columns we would like to have partitioned is the month and year. However, we would like to partitioned it to whatever the current month's data is. Which type of partition would be best?

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    is it a date field or a character field?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Apr 2004
    Posts
    113
    Duck,

    It is a character field.
    Number(9), not null

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    well, that would make it a numeric field.

    are we talking two columns here?
    month number(9) and year number(9) ??

    maybe you should post some sample rows so I don't have to keep asking questions. I would assume valid month values would be 1-12.

    are you saying you only want to partition the current month's data and have all other data in another partition? that is normally how it would work.

    Also, have you considered a materialized view based on the current year and month? That would work too (would depend how much activity there is on that table which seems like a lot so maybe this is not a good idea).

    The main obstacle you have is matching the numeric field to a current date/month.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Apr 2004
    Posts
    113
    Duck,

    Yes, basically we are trying to partition the current month's data and have all other data in another partition or partitions.

    When we run our daily process to insert the data, it takes a long time. We thought partitioning would be a good way to distribute the data.

    Here is sample data:

    month_no year_no
    1 2003
    11 2002

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by newbie2004
    Duck,
    When we run our daily process to insert the data, it takes a long time. We thought partitioning would be a good way to distribute the data.
    I don't think that is going to speed up your INSERT process. It will speed up query performance by using partitioning.

    You don't have a great deal of options for inserting data other than direct load. You would need to set the table to NOLOGGING, then do a direct-load insert (sqlloader, append hint). After that you could backup the table.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Apr 2004
    Posts
    113
    Thank you Duck!!!
    You have always been quite helpful!

  8. #8
    Join Date
    Nov 2003
    Location
    North Carolina, USA
    Posts
    3
    How are you loading your data? And do you have multiple processors? I worked on a project last year in which we loaded a similar amount of data with SQL*Loader. We were on Unix with multiple processors. We first split the data up using various Unix commands and then ran multiple SQL*Loader processes in parallel. This dramatically increased our speed. Duck was correct to point out that the partitioning really only helps you once the data is in the database and you select, update or delete. You will also get more speed if your partitions are on different disks and your queries are parallel. Hope this helps...

    Chet

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •