[
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)
