Partitioning strategy Help
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Partitioning strategy Help

Hybrid View

  1. #1
    Join Date
    Dec 1999
    Location
    San Ramon, CA, USA
    Posts
    21

    Partitioning strategy Help

    I will have several tables with 6,000,000 rows in my database. As an example, this is one of the tables:
    TABLE_BIG
    FIELD1_PK NUMBER(10)
    FIELD2 NUMBER(10)
    FIELD3 VARCHAR2(256)
    CERATEDATE DATE

    Our queries are going to be over the field FIELD1_PK on the where clause and that number is generated using a sequence. That table will start with 5M rows, and will increase with 3M rows/month.
    My question is the type of partition that I should use considering that if I have 2 partitions, all of The new values will go to the right side of the partition.
    Should I use hash partition over that field? not partition at all?

    Thanks,
    Diego

  2. #2
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    As you said your quries will be mostly using FIELD1_PK in WHERE clause. I range partition by FIELD1_PK will be a good option.
    Last edited by SANJAY_G; 09-16-2003 at 10:55 PM.
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    The first question to answer is "what do you expect to gain from partitioning?"

    If you are just looking for a way of load balancing across multiple devices, you canachieve that very easily, and without the complexities of partitioning, with locally managed TS's with uniform extents.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    ===================================
    Our queries are going to be over the field FIELD1_PK on the where clause and that number is generated using a sequence.
    ===================================

    Seq Number is not a good candidate for partition key, because seq number does not have any attribute value. It is just used to enforce PK.

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by tamilselvan
    ===================================
    Our queries are going to be over the field FIELD1_PK on the where clause and that number is generated using a sequence.
    ===================================

    Seq Number is not a good candidate for partition key, because seq number does not have any attribute value. It is just used to enforce PK.
    If it's used as a predicate, then there's nothing wrong with using it as the partitioning key. Assuming that there is some benefit to partitioning in this case.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    What kind of info will you derive from seq number?

    Tamil

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    I am unclear in the reason you want to partition the artificial PK. If it is for administrative reasons then it might be worthwhile. However, I don't think you will be gaining any performance.

    Being the PK, most of your index hits (if not all) will not be range scans, therefore partitioning will be of little benefit. As others have mentioned, If you are looking to distribute your I/O, there are easier ways to do it.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  8. #8
    Join Date
    Mar 2002
    Posts
    534
    Could you be a bit more specific about the queries executed.
    If your queries are going to be over the the field FIELD1_PK do you query only 1 row at the time? If not how many?

Posting Permissions

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



Click Here to Expand Forum to Full Width