[ https://issues.apache.org/jira/browse/OOZIE-1717?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16343484#comment-16343484 ]
Attila Sasvari commented on OOZIE-1717: --------------------------------------- OpenJPA 2.4.2 does not support JPA 2.1, so I had to add the following composite indices with {{java.sql.Statement's execUpdate()}}: {code:java} CREATE INDEX I_WF_JOBS_STATUS_CREATED_TIME ON WF_JOBS (status, created_time) CREATE INDEX I_COORD_ACTIONS_JOB_ID_STATUS ON COORD_ACTIONS (job_id, status) CREATE INDEX I_COORD_JOBS_STATUS_CREATED_TIME ON COORD_JOBS (status, created_time) CREATE INDEX I_COORD_JOBS_STATUS_LAST_MODIFIED_TIME ON COORD_JOBS (status, last_modified_time) CREATE INDEX I_COORD_JOBS_PENDING_DONE_MATERIALIZATION_LAST_MODIFIED_TIME ON COORD_JOBS (pending, done_materialization, last_modified_time) "CREATE INDEX I_COORD_JOBS_PENDING_LAST_MODIFIED_TIME ON COORD_JOBS (pending, last_modified_time) CREATE INDEX I_BUNLDE_JOBS_STATUS_CREATED_TIME ON BUNDLE_JOBS (status, created_time) CREATE INDEX I_BUNLDE_JOBS_STATUS_LAST_MODIFIED_TIME ON BUNDLE_JOBS (status, last_modified_time) CREATE INDEX I_BUNLDE_ACTIONS_PENDING_LAST_MODIFIED_TIME ON BUNDLE_ACTIONS (pending, last_modified_time){code} After the change, the following indices are present in the oozie database: {code:java} > SELECT DISTINCT TABLE_NAME, COLUMN_NAME, INDEX_NAME FROM > INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'oozie'; +------------------------+----------------------+--------------------------------------------------------------+ | TABLE_NAME | COLUMN_NAME | INDEX_NAME | +------------------------+----------------------+--------------------------------------------------------------+ | BUNDLE_ACTIONS | bundle_action_id | PRIMARY | | BUNDLE_ACTIONS | bundle_id | I_BNDLTNS_BUNDLE_ID | | BUNDLE_ACTIONS | pending | I_BUNLDE_ACTIONS_PENDING_LAST_MODIFIED_TIME | | BUNDLE_ACTIONS | last_modified_time | I_BUNLDE_ACTIONS_PENDING_LAST_MODIFIED_TIME | | BUNDLE_JOBS | id | PRIMARY | | BUNDLE_JOBS | created_time | I_BNDLJBS_CREATED_TIME | | BUNDLE_JOBS | end_time | I_BNDLJBS_END_TIME | | BUNDLE_JOBS | last_modified_time | I_BNDLJBS_LAST_MODIFIED_TIME | | BUNDLE_JOBS | status | I_BNDLJBS_STATUS | | BUNDLE_JOBS | suspended_time | I_BNDLJBS_SUSPENDED_TIME | | BUNDLE_JOBS | user_name | I_BNDLJBS_USER_NAME | | BUNDLE_JOBS | status | I_BUNLDE_JOBS_STATUS_CREATED_TIME | | BUNDLE_JOBS | created_time | I_BUNLDE_JOBS_STATUS_CREATED_TIME | | BUNDLE_JOBS | status | I_BUNLDE_JOBS_STATUS_LAST_MODIFIED_TIME | | BUNDLE_JOBS | last_modified_time | I_BUNLDE_JOBS_STATUS_LAST_MODIFIED_TIME | | COORD_ACTIONS | id | PRIMARY | | COORD_ACTIONS | created_time | I_CRD_TNS_CREATED_TIME | | COORD_ACTIONS | external_id | I_CRD_TNS_EXTERNAL_ID | | COORD_ACTIONS | job_id | I_CRD_TNS_JOB_ID | | COORD_ACTIONS | last_modified_time | I_CRD_TNS_LAST_MODIFIED_TIME | | COORD_ACTIONS | nominal_time | I_CRD_TNS_NOMINAL_TIME | | COORD_ACTIONS | rerun_time | I_CRD_TNS_RERUN_TIME | | COORD_ACTIONS | status | I_CRD_TNS_STATUS | | COORD_ACTIONS | job_id | I_COORD_ACTIONS_JOB_ID_STATUS | | COORD_ACTIONS | status | I_COORD_ACTIONS_JOB_ID_STATUS | | COORD_JOBS | id | PRIMARY | | COORD_JOBS | bundle_id | I_CRD_JBS_BUNDLE_ID | | COORD_JOBS | created_time | I_CRD_JBS_CREATED_TIME | | COORD_JOBS | end_time | I_CRD_JBS_END_TIME | | COORD_JOBS | last_modified_time | I_CRD_JBS_LAST_MODIFIED_TIME | | COORD_JOBS | next_matd_time | I_CRD_JBS_NEXT_MATD_TIME | | COORD_JOBS | status | I_CRD_JBS_STATUS | | COORD_JOBS | suspended_time | I_CRD_JBS_SUSPENDED_TIME | | COORD_JOBS | user_name | I_CRD_JBS_USER_NAME | | COORD_JOBS | status | I_COORD_JOBS_STATUS_CREATED_TIME | | COORD_JOBS | created_time | I_COORD_JOBS_STATUS_CREATED_TIME | | COORD_JOBS | status | I_COORD_JOBS_STATUS_LAST_MODIFIED_TIME | | COORD_JOBS | last_modified_time | I_COORD_JOBS_STATUS_LAST_MODIFIED_TIME | | COORD_JOBS | pending | I_COORD_JOBS_PENDING_DONE_MATERIALIZATION_LAST_MODIFIED_TIME | | COORD_JOBS | done_materialization | I_COORD_JOBS_PENDING_DONE_MATERIALIZATION_LAST_MODIFIED_TIME | | COORD_JOBS | last_modified_time | I_COORD_JOBS_PENDING_DONE_MATERIALIZATION_LAST_MODIFIED_TIME | | COORD_JOBS | pending | I_COORD_JOBS_PENDING_LAST_MODIFIED_TIME | | COORD_JOBS | last_modified_time | I_COORD_JOBS_PENDING_LAST_MODIFIED_TIME | | OPENJPA_SEQUENCE_TABLE | ID | PRIMARY | | SLA_EVENTS | event_id | PRIMARY | | SLA_EVENTS | bean_type | I_SL_VNTS_DTYPE | | SLA_REGISTRATION | job_id | PRIMARY | | SLA_REGISTRATION | nominal_time | I_SL_RRTN_NOMINAL_TIME | | SLA_SUMMARY | job_id | PRIMARY | | SLA_SUMMARY | app_name | I_SL_SMRY_APP_NAME | | SLA_SUMMARY | event_processed | I_SL_SMRY_EVENT_PROCESSED | | SLA_SUMMARY | last_modified | I_SL_SMRY_LAST_MODIFIED | | SLA_SUMMARY | nominal_time | I_SL_SMRY_NOMINAL_TIME | | SLA_SUMMARY | parent_id | I_SL_SMRY_PARENT_ID | | VALIDATE_CONN | id | PRIMARY | | WF_ACTIONS | id | PRIMARY | | WF_ACTIONS | pending_age | I_WF_CTNS_PENDING_AGE | | WF_ACTIONS | status | I_WF_CTNS_STATUS | | WF_ACTIONS | wf_id | I_WF_CTNS_WF_ID | | WF_JOBS | id | PRIMARY | | WF_JOBS | created_time | I_WF_JOBS_CREATED_TIME | | WF_JOBS | end_time | I_WF_JOBS_END_TIME | | WF_JOBS | external_id | I_WF_JOBS_EXTERNAL_ID | | WF_JOBS | last_modified_time | I_WF_JOBS_LAST_MODIFIED_TIME | | WF_JOBS | parent_id | I_WF_JOBS_PARENT_ID | | WF_JOBS | status | I_WF_JOBS_STATUS | | WF_JOBS | user_name | I_WF_JOBS_USER_NAME | | WF_JOBS | status | I_WF_JOBS_STATUS_CREATED_TIME | | WF_JOBS | created_time | I_WF_JOBS_STATUS_CREATED_TIME | +------------------------+----------------------+--------------------------------------------------------------+{code} Let me know what you think - Reviewboard link: https://reviews.apache.org/r/65385/ > Index user_name column > ---------------------- > > Key: OOZIE-1717 > URL: https://issues.apache.org/jira/browse/OOZIE-1717 > Project: Oozie > Issue Type: Bug > Reporter: Purshotam Shah > Assignee: Attila Sasvari > Priority: Minor > Attachments: OOZIE-1717-00.patch, OOZIE-1717-002.patch, > OOZIE-1717-003.patch, OOZIE-1717-01.patch > > > User_name is one of the frequently used filter. Adding index should avoid > full db scan. > Index need to be done for BUNDLE_JOBS,COORD_JOBS and WF_JOBS. -- This message was sent by Atlassian JIRA (v7.6.3#76005)