Author: jeff.yuchang Date: 2010-01-13 03:20:46 -0500 (Wed, 13 Jan 2010) New Revision: 457 Modified: trunk/runtime/engine-assembly/src/main/resources/bpel-sql/mysql.sql trunk/runtime/engine-assembly/src/main/resources/bpel-sql/oracle.sql trunk/runtime/engine-assembly/src/main/resources/bpel-sql/postgres.sql trunk/runtime/engine-assembly/src/main/resources/bpel-sql/sqlserver.sql Log: * added indexes for oracle and sqlserver. Modified: trunk/runtime/engine-assembly/src/main/resources/bpel-sql/mysql.sql =================================================================== --- trunk/runtime/engine-assembly/src/main/resources/bpel-sql/mysql.sql 2010-01-12 06:00:35 UTC (rev 456) +++ trunk/runtime/engine-assembly/src/main/resources/bpel-sql/mysql.sql 2010-01-13 08:20:46 UTC (rev 457) @@ -42,6 +42,8 @@ create table LARGE_DATA (ID bigint not null auto_increment, BIN_DATA blob, INSERT_TIME datetime, MLOCK integer not null, primary key (ID)); create table VAR_PROPERTY (ID bigint not null auto_increment, XML_DATA_ID bigint, PROP_VALUE varchar(255), PROP_NAME varchar(255) not null, INSERT_TIME datetime, MLOCK integer not null, primary key (ID)); +--create indexes + create index IDX_CORRELATOR_CID on BPEL_CORRELATOR (CID); create index IDX_BPEL_CORRELATOR_MESSAGE_CKEY on BPEL_CORRELATOR_MESSAGE_CKEY (CKEY); create index IDX_SELECTOR_CORRELATOR on BPEL_SELECTORS (CORRELATOR); Modified: trunk/runtime/engine-assembly/src/main/resources/bpel-sql/oracle.sql =================================================================== --- trunk/runtime/engine-assembly/src/main/resources/bpel-sql/oracle.sql 2010-01-12 06:00:35 UTC (rev 456) +++ trunk/runtime/engine-assembly/src/main/resources/bpel-sql/oracle.sql 2010-01-13 08:20:46 UTC (rev 457) @@ -47,3 +47,46 @@ create index IDX_UNMATCHED_CKEY on BPEL_UNMATCHED (CORRELATION_KEY); create index IDX_UNMATCHED_CORRELATOR on BPEL_UNMATCHED (CORRELATOR); create sequence hibernate_sequence; + +--create indexes + +create index IDX_XMLDATA_IID on BPEL_XML_DATA (PIID) ; +create index IDX_XMLDATA_SID on BPEL_XML_DATA (SCOPE_ID) ; +create index IDX_XMLDATA_NAME on BPEL_XML_DATA (NAME) ; +create index IDX_XMLDATA_NAME_SID on BPEL_XML_DATA (NAME, SCOPE_ID) ; + +create index IDX_EVENT_IID on BPEL_EVENT (IID) ; +create index IDX_EVENT_PID on BPEL_EVENT (PID) ; + + +create index IDX_CORR_SET_NAME on BPEL_CORRELATION_SET (CORR_SET_NAME) ; +create index IDX_CORR_SET_SCOPE_ID on BPEL_CORRELATION_SET (SCOPE_ID) ; +create index IDX_BPEL_INSTANCE_PROCESS_ID on BPEL_INSTANCE (PROCESS_ID) ; +create index IDX_BPEL_INSTANCE_STATE on BPEL_INSTANCE (STATE) ; +create index IDX_BPEL_PROCESS_TYPE_NAME on BPEL_PROCESS (type_name) ; +create index IDX_BPEL_PROCESS_TYPE_NS on BPEL_PROCESS (type_ns) ; +create index IDX_BPEL_CORRELATOR_PROCESS_ID on BPEL_CORRELATOR (PROCESS_ID) ; +create index IDX_UNMATCHED_CORRELATOR_CKEY on BPEL_UNMATCHED (CORRELATOR,CORRELATION_KEY) ; +create index IDX_RECOVERY_LDATAID on BPEL_ACTIVITY_RECOVERY (LDATA_ID) ; +create index IDX_EVENT_LDATAID on BPEL_EVENT (LDATA_ID) ; +create index IDX_FAULT_LDATAID on BPEL_FAULT (LDATA_ID) ; +create index IDX_MESSAGE_LDATAID on BPEL_MESSAGE (DATA) ; +create index IDX_INSTANCE_LDATAID on BPEL_INSTANCE (JACOB_STATE) ; +create index IDX_MEXCEPR_LDATAID on BPEL_MESSAGE_EXCHANGE (LDATA_CEPR_ID) ; +create index IDX_MEXEPR_LDATAID on BPEL_MESSAGE_EXCHANGE (LDATA_EPR_ID) ; +create index IDX_PLINK_VAL_PROCESS_IDX on BPEL_PLINK_VAL (PROCESS) ; +create index IDX_PLINK_VAL_SCOPE on BPEL_PLINK_VAL (SCOPE) ; +create index IDX_PLINKMYR_LDATAID on BPEL_PLINK_VAL (MYROLE_EPR) ; +create index IDX_PLINKPR_LDATAID on BPEL_PLINK_VAL (PARTNERROLE_EPR) ; +create index IDX_PLINK_VAL_MODELID on BPEL_PLINK_VAL (MODELID) ; +create index IDX_SELECTOR_INSTANCE on BPEL_SELECTORS (PIID) ; +create index IDX_VARPROP_XMLDATA on VAR_PROPERTY (XML_DATA_ID) ; +create index IDX_VARPROP_NAME on VAR_PROPERTY (PROP_NAME) ; +create index IDX_VARPROP_VALUE on VAR_PROPERTY (PROP_VALUE) ; +create index IDX_UNMATCHED_MEX on BPEL_UNMATCHED (MEX) ; + +--Indexes for instance deletion +create index IDX_MESSAGE_MEX on BPEL_MESSAGE(MEX) ; +create index IDX_MESSAGE_EXCHANGE_PIID on BPEL_MESSAGE_EXCHANGE(PIID) ; +create index IDX_SCOPE_PIID on BPEL_SCOPE(PIID) ; + Modified: trunk/runtime/engine-assembly/src/main/resources/bpel-sql/postgres.sql =================================================================== --- trunk/runtime/engine-assembly/src/main/resources/bpel-sql/postgres.sql 2010-01-12 06:00:35 UTC (rev 456) +++ trunk/runtime/engine-assembly/src/main/resources/bpel-sql/postgres.sql 2010-01-13 08:20:46 UTC (rev 457) @@ -48,6 +48,9 @@ create index IDX_UNMATCHED_CORRELATOR on BPEL_UNMATCHED (CORRELATOR); create sequence hibernate_sequence; + +--create indexes + create index IDX_XMLDATA_IID on BPEL_XML_DATA (PIID) ; create index IDX_XMLDATA_LDATA_ID on BPEL_XML_DATA (LDATA_ID) ; create index IDX_XMLDATA_SID on BPEL_XML_DATA (SCOPE_ID) ; Modified: trunk/runtime/engine-assembly/src/main/resources/bpel-sql/sqlserver.sql =================================================================== --- trunk/runtime/engine-assembly/src/main/resources/bpel-sql/sqlserver.sql 2010-01-12 06:00:35 UTC (rev 456) +++ trunk/runtime/engine-assembly/src/main/resources/bpel-sql/sqlserver.sql 2010-01-13 08:20:46 UTC (rev 457) @@ -45,3 +45,45 @@ create index IDX_SELECTOR_SELGRPID on BPEL_SELECTORS (SELGRPID); create index IDX_UNMATCHED_CKEY on BPEL_UNMATCHED (CORRELATION_KEY); create index IDX_UNMATCHED_CORRELATOR on BPEL_UNMATCHED (CORRELATOR); + +-- create indexes. + +create index IDX_XMLDATA_IID on BPEL_XML_DATA (PIID) ; +create index IDX_XMLDATA_SID on BPEL_XML_DATA (SCOPE_ID) ; +create index IDX_XMLDATA_NAME on BPEL_XML_DATA (NAME) ; +create index IDX_XMLDATA_NAME_SID on BPEL_XML_DATA (NAME, SCOPE_ID) ; + +create index IDX_EVENT_IID on BPEL_EVENT (IID) ; +create index IDX_EVENT_PID on BPEL_EVENT (PID) ; + + +create index IDX_CORR_SET_NAME on BPEL_CORRELATION_SET (CORR_SET_NAME) ; +create index IDX_CORR_SET_SCOPE_ID on BPEL_CORRELATION_SET (SCOPE_ID) ; +create index IDX_BPEL_INSTANCE_PROCESS_ID on BPEL_INSTANCE (PROCESS_ID) ; +create index IDX_BPEL_INSTANCE_STATE on BPEL_INSTANCE (STATE) ; +create index IDX_BPEL_PROCESS_TYPE_NAME on BPEL_PROCESS (type_name) ; +create index IDX_BPEL_PROCESS_TYPE_NS on BPEL_PROCESS (type_ns) ; +create index IDX_BPEL_CORRELATOR_PROCESS_ID on BPEL_CORRELATOR (PROCESS_ID) ; +create index IDX_UNMATCHED_CORRELATOR_CKEY on BPEL_UNMATCHED (CORRELATOR,CORRELATION_KEY) ; +create index IDX_RECOVERY_LDATAID on BPEL_ACTIVITY_RECOVERY (LDATA_ID) ; +create index IDX_EVENT_LDATAID on BPEL_EVENT (LDATA_ID) ; +create index IDX_FAULT_LDATAID on BPEL_FAULT (LDATA_ID) ; +create index IDX_MESSAGE_LDATAID on BPEL_MESSAGE (DATA) ; +create index IDX_INSTANCE_LDATAID on BPEL_INSTANCE (JACOB_STATE) ; +create index IDX_MEXCEPR_LDATAID on BPEL_MESSAGE_EXCHANGE (LDATA_CEPR_ID) ; +create index IDX_MEXEPR_LDATAID on BPEL_MESSAGE_EXCHANGE (LDATA_EPR_ID) ; +create index IDX_PLINK_VAL_PROCESS_IDX on BPEL_PLINK_VAL (PROCESS) ; +create index IDX_PLINK_VAL_SCOPE on BPEL_PLINK_VAL (SCOPE) ; +create index IDX_PLINKMYR_LDATAID on BPEL_PLINK_VAL (MYROLE_EPR) ; +create index IDX_PLINKPR_LDATAID on BPEL_PLINK_VAL (PARTNERROLE_EPR) ; +create index IDX_PLINK_VAL_MODELID on BPEL_PLINK_VAL (MODELID) ; +create index IDX_SELECTOR_INSTANCE on BPEL_SELECTORS (PIID) ; +create index IDX_VARPROP_XMLDATA on VAR_PROPERTY (XML_DATA_ID) ; +create index IDX_VARPROP_NAME on VAR_PROPERTY (PROP_NAME) ; +create index IDX_VARPROP_VALUE on VAR_PROPERTY (PROP_VALUE) ; +create index IDX_UNMATCHED_MEX on BPEL_UNMATCHED (MEX) ; + +--Indexes for instance deletion +create index IDX_MESSAGE_MEX on BPEL_MESSAGE(MEX) ; +create index IDX_MESSAGE_EXCHANGE_PIID on BPEL_MESSAGE_EXCHANGE(PIID) ; +create index IDX_SCOPE_PIID on BPEL_SCOPE(PIID) ;