Ok, it seems there are no takers. I don't understand the lack of interest - maybe that's because I'm missing some really important point. Let me define where I'm coming from:
I'm going to run on average N*100000 workflows per day. That amounts to about 10*N*100000 variable instances and goodly quantity of tokens and such. So it stands to the reason that after about 10 days my db will start loosing performance even if the SQL it just perfect - no bad joins, no search by non-indexed fields etc. So I'd really love to partition all the tables by some field (say, date), add that field to the queries and just drop the partitions older that 2 days. All of this obviously is relevant to the DBMS with partitioning ability, like Oracle.
Then I'd love to build the proper indices in the db. Obviously, they'll have to be local, because I can not afford index-rebuild downtime following a partition drop.
So where could I find some information in respect to the queries and the update process? These issues have got to be common for all people with high load systems. Am I to understand that nobody runs jBPM for more than a few hundreds/thousands workflows per day?
I think you should look to the file where queries are defined :
And you can customize hibernate mapping file, see the persistence chapter :