Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2004
    Posts
    62

    Unanswered: Partitioning or More table

    Dear all dbas
    I have millions of records, my program will be simplified if i insert all records into 1 table.

    I have to query by month and year. My consultant said my query will be slow even if i index the month and year field because my table have millions of data.

    Therefore they suggest me to separate the data into 12 tables, anyhow this will make my code complex. For instant, if i want to search for specific serialnumbers, i have to browse all the 12 tables to find the specific serialnumbers instead of having 1 table.

    I have read about partitioning about a table , and i plan to partition it by month and year. this will simplify my code, furthermore i can have a single query instead of 12 union. With proper index on the partition, the performance can be better. Additionally, it is easier for housekeeping.

    Can anyone giving an opinion for pros and cons on above method.

    Thank you

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    First: fire your consultant, he doesn't seem to know anything about tuning Oracle.
    "Millions of rows" is not really big for Oracle. Definitely not a reason to de-normalize your data upfront.

    I would first run some performance test with the real size of the tables and using the indexes you think should be used. I would assume that with decent hardware the queries shouldn't be too slow. You will need to check the execution plan though.

    If your queries are not fast enough, partitioning is definitely the way to go.
    The only "con": you will need the enterprise (!) edition for that.

  3. #3
    Join Date
    Jun 2004
    Posts
    115
    Hi

    If majority of your queries are by month and year and this is mostly a read intensive application with ocassional bulk loads then yes go ahead and partition


    do test against a realistic workload before you roll out anything to production.

    If we had a concrete advices like partition or dont partition then it would have been in the docs :-).

    In 11g i presume there is a partition advisor which takes a look at your workload and suggests partitions.



    regards
    Hrishy

  4. #4
    Join Date
    Jul 2004
    Posts
    62
    Thanks all for the comments,

    Just to add on the partitioning,
    this database is accessed by their web portal applications, basically accessed our 1000-2000 customers 24 hours.
    Web users can also do CRUD transactions. Majoirty will query by month and years and only their data.

    My job is to load the bulk data to the database and can be viewed by the web users. My program will search the data and do update if there is changes , but this is done daily, so no worry, as long it can be done from 1:00AM to 7:00AM everyday

  5. #5
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by pakcik_kantin
    My job is to load the bulk data to the database
    Make sure you analyze the tables and indexes after the upload, so that the optimizer chooses the best execution plan

  6. #6
    Join Date
    Jun 2004
    Posts
    115
    Hi

    Are those updates based on month and year or serial numbers ?

    How many millions in the table ?

    Are those rows going to be archived or are those milions growing to billions ?

    regards
    Hrishy
    Last edited by db2hrishy; 11-15-07 at 09:58.

  7. #7
    Join Date
    Jul 2004
    Posts
    62
    Dear db2hrishy

    1. Updates for every specific records, example update table1 set a=? where serialnumber=?. My program will do bulk insert or update like above everyday.
    2. 1 day can cost up to 1 million data, but i plan to keep it for 1 year,or 365 millions
    3. yes my data is going to be archived

    { Make sure you analyze the tables and indexes after the upload, so that the optimizer chooses the best execution plan }
    I will hand it to our internal DBA.

  8. #8
    Join Date
    Jun 2004
    Posts
    115
    Hi

    If you do that

    update table1 set a=? where serialnumber=?.

    its going to be slow as you have partitioned by month and year but now you are not updating based on the partition keys.

    I suggest that you run your workload before and after doing the partitioning and then depending upon the results you can decide to partition or not.

    regards
    Hrishy

  9. #9
    Join Date
    Jul 2004
    Posts
    62
    Quote Originally Posted by db2hrishy
    Hi

    If you do that

    update table1 set a=? where serialnumber=?.

    its going to be slow as you have partitioned by month and year but now you are not updating based on the partition keys.

    I suggest that you run your workload before and after doing the partitioning and then depending upon the results you can decide to partition or not.

    regards
    Hrishy
    How about i index the serialnumbers field

  10. #10
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by pakcik_kantin
    How about i index the serialnumbers field
    Absolutely, otherwise your DB will grind to a halt

  11. #11
    Join Date
    Jun 2004
    Posts
    115
    Hi

    I would prefer something like

    Code:
    update table1 set a=? 
    where serialnumber=?
    and partitioned-column=?
    with a index on serial number .

    regards
    Hrishy

Posting Permissions

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