[JBoss jBPM] - Re: Maintenance and Repair Tools
by michaelholtzman
anonymous wrote : Are 1,2 and 3 valid for all DBMS you use? Would be great if you could confirm that.
Definitely Oracle and Sybase, don't have much experience with MSSql yet.
anonymous wrote : And maybe also what the default locking mechanisms are on specific database.
Defaults:
Oracle - Row locking (as long as it has the indexes it needs to group the rows)
Sybase - Table locking (row locking must be specified on a table)
MSSql - Version 7+ defaults to row locking
anonymous wrote : Surprises me that there is no slowdown btw. And I'm also curious what additional indexes you created that are not already on jBPM. Since I was under the impression that FK relations had an index.
Surprised us too :)
I think that certain DB's automatically index the foreign key columns, but apparently Oracle, Sybase, and SqlServer do not. We added these indices:
| /* OLF - Add indices on foreign keys */
| create index JBPM_ACTION_EVENT_IDX on JBPM_ACTION (EVENT_);
| create index JBPM_ACTION_EXCEPT_IDX on JBPM_ACTION (EXCEPTIONHANDLER_);
| create index JBPM_ACTION_PROCDEFN_IDX on JBPM_ACTION (PROCESSDEFINITION_);
| create index JBPM_ACTION_TIMERACTION_IDX on JBPM_ACTION (TIMERACTION_);
| create index JBPM_ACTION_ACTIONDEL_IDX on JBPM_ACTION (ACTIONDELEGATION_);
| create index JBPM_ACTION_REFACTION_IDX on JBPM_ACTION (REFERENCEDACTION_);
|
| create index JBPM_BYTEARRAY_FILEDEFN_IDX on JBPM_BYTEARRAY (FILEDEFINITION_);
| create index JBPM_BYTEBLOCK_PROCFILE_IDX on JBPM_BYTEBLOCK (PROCESSFILE_);
|
| create index JBPM_COMMENT_TOKEN_IDX on JBPM_COMMENT (TOKEN_);
| create index JBPM_COMMENT_TASKINST_IDX on JBPM_COMMENT (TASKINSTANCE_);
|
| create index JBPM_DECISIONCOND_DEC_IDX on JBPM_DECISIONCONDITIONS (DECISION_);
|
| create index JBPM_DELEGATION_PROCDEFN_IDX on JBPM_DELEGATION (PROCESSDEFINITION_);
|
| create index JBPM_EVENT_PROCDEFN_IDX on JBPM_EVENT (PROCESSDEFINITION_);
| create index JBPM_EVENT_NODE_IDX on JBPM_EVENT (NODE_);
| create index JBPM_EVENT_TRANSITION_IDX on JBPM_EVENT (TRANSITION_);
| create index JBPM_EVENT_TASK_IDX on JBPM_EVENT (TASK_);
|
| create index JBPM_LOG_SRCENODE_IDX on JBPM_LOG (SOURCENODE_);
| create index JBPM_LOG_TOKEN_IDX on JBPM_LOG (TOKEN_);
| create index JBPM_LOG_OLDBYTE_IDX on JBPM_LOG (OLDBYTEARRAY_);
| create index JBPM_LOG_NEWBYTE_IDX on JBPM_LOG (NEWBYTEARRAY_);
| create index JBPM_LOG_CHILD_IDX on JBPM_LOG (CHILD_);
| create index JBPM_LOG_DESTNODE_IDX on JBPM_LOG (DESTINATIONNODE_);
| create index JBPM_LOG_TASKINST_IDX on JBPM_LOG (TASKINSTANCE_);
| create index JBPM_LOG_SWIMLANEINSTANCE_IDX on JBPM_LOG (SWIMLANEINSTANCE_);
| create index JBPM_LOG_PARENT_IDX on JBPM_LOG (PARENT_);
| create index JBPM_LOG_NODE_IDX on JBPM_LOG (NODE_);
| create index JBPM_LOG_ACTION_IDX on JBPM_LOG (ACTION_);
| create index JBPM_LOG_VARINST_IDX on JBPM_LOG (VARIABLEINSTANCE_);
| create index JBPM_LOG_TRANSITION_IDX on JBPM_LOG (TRANSITION_);
|
| create index JBPM_MODULEDEFN_STRTTASK_IDX on JBPM_MODULEDEFINITION (STARTTASK_);
| create index JBPM_MODULEDEFN_PROCDEFN_IDX on JBPM_MODULEDEFINITION (PROCESSDEFINITION_);
|
| create index JBPM_MODULEINST_TASKMGMT_IDX on JBPM_MODULEINSTANCE (TASKMGMTDEFINITION_);
| create index JBPM_MODULEINST_PROCINST_IDX on JBPM_MODULEINSTANCE (PROCESSINSTANCE_);
|
| create index JBPM_NODE_SUBPDEFN_IDX on JBPM_NODE (SUBPROCESSDEFINITION_);
| create index JBPM_NODE_PROCDEFN_IDX on JBPM_NODE (PROCESSDEFINITION_);
| create index JBPM_NODE_ACTION_IDX on JBPM_NODE (ACTION_);
| create index JBPM_NODE_DECISIONDEL_IDX on JBPM_NODE (DECISIONDELEGATION);
| create index JBPM_NODE_SUPERSTATE_IDX on JBPM_NODE (SUPERSTATE_);
|
| create index JBPM_PROCDEFN_STARTSTATE_IDX on JBPM_PROCESSDEFINITION (STARTSTATE_);
|
| create index JBPM_PROCINST_PROCDEFN_IDX on JBPM_PROCESSINSTANCE (PROCESSDEFINITION_);
| create index JBPM_PROCINST_ROOTTOKEN_IDX on JBPM_PROCESSINSTANCE (ROOTTOKEN_);
| create index JBPM_PROCINST_SUPERTOKEN_IDX on JBPM_PROCESSINSTANCE (SUPERPROCESSTOKEN_);
|
| create index JBPM_RTACTION_PROCINST_IDX on JBPM_RUNTIMEACTION (PROCESSINSTANCE_);
| create index JBPM_RTACTION_ACTION_IDX on JBPM_RUNTIMEACTION (ACTION_);
|
| create index JBPM_TASK_TASKCTRL_IDX on JBPM_TASK (TASKCONTROLLER_);
| create index JBPM_TASK_ASSIGNDEL_IDX on JBPM_TASK (ASSIGNMENTDELEGATION_);
| create index JBPM_TASK_TASKNODE_IDX on JBPM_TASK (TASKNODE_);
| create index JBPM_TASK_PROCDEFN_IDX on JBPM_TASK (PROCESSDEFINITION_);
| create index JBPM_TASK_STARTSTATE_IDX on JBPM_TASK (STARTSTATE_);
| create index JBPM_TASK_TASKMGMT_IDX on JBPM_TASK (TASKMGMTDEFINITION_);
| create index JBPM_TASK_SWIMLANE_IDX on JBPM_TASK (SWIMLANE_);
|
| create index JBPM_TACTRPOOL_POOLACTR_IDX on JBPM_TASKACTORPOOL (POOLEDACTOR_);
| create index JBPM_TACTRPOOL_TASKINST_IDX on JBPM_TASKACTORPOOL (TASKINSTANCE_);
|
| create index JBPM_TASKCTRL_TSKCNTDEL_IDX on JBPM_TASKCONTROLLER (TASKCONTROLLERDELEGATION_);
|
| create index JBPM_TASKINST_TASKMGMT_IDX on JBPM_TASKINSTANCE (TASKMGMTINSTANCE_);
| create index JBPM_TASKINST_TOKEN_IDX on JBPM_TASKINSTANCE (TOKEN_);
| create index JBPM_TASKINST_SWIMINST_IDX on JBPM_TASKINSTANCE (SWIMLANINSTANCE_);
| create index JBPM_TASKINST_TASK_IDX on JBPM_TASKINSTANCE (TASK_);
|
| create index JBPM_TIMER_TOKEN_IDX on JBPM_TIMER (TOKEN_);
| create index JBPM_TIMER_PROCINST_IDX on JBPM_TIMER (PROCESSINSTANCE_);
| create index JBPM_TIMER_ACTION_IDX on JBPM_TIMER (ACTION_);
| create index JBPM_TIMER_TASKINST_IDX on JBPM_TIMER (TASKINSTANCE_);
|
| create index JBPM_TOKEN_PARENT_IDX on JBPM_TOKEN (PARENT_);
| create index JBPM_TOKEN_NODE_IDX on JBPM_TOKEN (NODE_);
| create index JBPM_TOKEN_PROCINST_IDX on JBPM_TOKEN (PROCESSINSTANCE_);
| create index JBPM_TOKEN_SUBPROCINST_IDX on JBPM_TOKEN (SUBPROCESSINSTANCE_);
|
| create index JBPM_TOKVARMAP_CNTXINST_IDX on JBPM_TOKENVARIABLEMAP (CONTEXTINSTANCE_);
| create index JBPM_TOKVARMAP_TOKEN_IDX on JBPM_TOKENVARIABLEMAP (TOKEN_);
|
| create index JBPM_TRANSITION_NODETO_IDX on JBPM_TRANSITION (TO_);
| create index JBPM_TRANSITION_PROCDEFN_IDX on JBPM_TRANSITION (PROCESSDEFINITION_);
| create index JBPM_TRANSITION_NODEFROM_IDX on JBPM_TRANSITION (FROM_);
|
| create index JBPM_VARACC_TASKCTRL_IDX on JBPM_VARIABLEACCESS (TASKCONTROLLER_);
| create index JBPM_VARACC_SCRIPT_IDX on JBPM_VARIABLEACCESS (SCRIPT_);
| create index JBPM_VARACC_PROCSTATE_IDX on JBPM_VARIABLEACCESS (PROCESSSTATE_);
|
| create index JBPM_VARINST_TOKEN_IDX on JBPM_VARIABLEINSTANCE (TOKEN_);
| create index JBPM_VARINST_TOKVARMAP_IDX on JBPM_VARIABLEINSTANCE (TOKENVARIABLEMAP_);
| create index JBPM_VARINST_PROCINST_IDX on JBPM_VARIABLEINSTANCE (PROCESSINSTANCE_);
| create index JBPM_VARINST_TASKINST_IDX on JBPM_VARIABLEINSTANCE (TASKINSTANCE_);
| create index JBPM_VARINST_BYTEVALUE_IDX on JBPM_VARIABLEINSTANCE (BYTEARRAYVALUE_);
| commit;
|
anonymous wrote : DBMS have tools to identify some low level issues like partially corrupt tables etc, but I get the impression that is not what you mean.
We've enslaved -- I mean, enlisted our DBA's to do this for us, I was hoping for something that worked at a higher level.
View the original post : http://www.jboss.org/index.html?module=bb&op=viewtopic&p=4250250#4250250
Reply to the post : http://www.jboss.org/index.html?module=bb&op=posting&mode=reply&p=4250250
17 years, 2 months