RSS Feed

Method R Discussion Group

Background:
We have performance issue in our database for queries using partitioned tables. Our database is 1.5Tb in size and some of the tables are very huge in size (upto 40GB) and are partitioned. Most of these tables store health claims related data. Each of these tables has atleast 3 common columns i.e. SID column (which is a sequence number and primary key of the table) and other are from_service_date and to_service_date of date datatypes. Our range partitions are based on SID (sequence) column which are done yearly as we reset sequence every year and the record for new sequence number goes into new year partition created to accommodate range of sequence numbers.

Current Scenario:
Regarding the partition of the table is concerned, our application uses 70% queries(batch queries) based on sequences (joins involving SID columns)partition and 30% OLTP queries are based on from_service_date, to_service_date input columns. Our OLTP queries don’t use SID columns in predicates but they use from_service_date and to_service_date columns for claims screens. The queries involving these predicates don’t have advantage of partition pruning as partitioned column is not present in the queries. These queries are very slow and we have increasing number of user complaints. We think if we partition table on SID (range) and sub-partition on dates (hash) then we will have partition pruning benefit for both OLTP and batch jobs.

Question:
In this case, do you see any benefit if we range partition table on SID(Sequence columns) and then hash subpartition on from_service_date column? Can we achieve better response in both batch and online queries?