T
TheMadDBA
Guest
Until Progress gets a cost based optimizer for the 4GL that will decide to do partition scans without indexes you are much better off partitioning for ease of maintenance and ignoring any performance gains. The physics of looking up a non partitioned record and a partitioned record are essentially the same at this point.... find the index block(s) for the rowid and then get the appropriate data block. Partitioning for performance works best when you are doing non indexed scans and you eliminate most of the data by partition pruning. It works even better when you are doing non indexed joins between tables that have the same partition strategy and the join is happening in bulk on the server. Unfortunately this is not the case for the vast majority of 4GL code and the server side hash joins don't exist for the 4GL at all. If you partition on a status code of some kind you can get improvements for keeping the "active" records in a smaller number of data blocks but you pay the penalty of moving those records around when the status changes. How much the net gain/loss is will depend on how often those status codes change and how many moves you do. A good place to start would be to look at OLTP partitioning strategies for Oracle. The concepts are similar even if the execution is different. You will probably get much better performance improvements by making sure you use the alternate buffer pool properly (for your base tables) and maybe look at splitting those TRANS areas into multiple areas for similar record sizes and usage patterns. And of course any code/index changes.
Continue reading...
Continue reading...