Partitioning by date of record
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Partitioning by date of record

  1. #1
    Join Date
    May 2000
    Location
    Huntsville, AL. USA
    Posts
    52
    If I have a database with 3+ million rows in a hybrid environment (neither completely OLTP or DSS) and I partition the tables say by month which the records are timestamped with (one of the columns in each table is the timestamp), will this significantly improve select statements such as select * from tablex where date between xxx and yyy; ? I presume this would involve only the partitions that contain data rows which are referenced by the where clause, correct? This being the case, would this not be more efficient than just having an index on the tables? Since the query may involve several tables, several indexes would come into play. Partitioning at a glance seems like the way to go, am I correct in this line of thinking?

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    For only 3M rows, it depends on what the values of x and y are. If the difference between x and y is small, then you would probably be better off in with an index. The index will perform a range scan between x and y.

    If the difference between x and y is great (1 month) then a full partition scan would take approximately the same time as a index range scan + table accesses. You would get similar performance either way.

    That being said, there are other advantages to partitioning, especially in your case. If you are going to periodically purge this data based on the month it was created, the ease of management would definitely be a reason to choose partitioning.
    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."

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