Partitioning by date of record

[grrickar]

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?

[marist89]

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.