JIRA: Additional indexes for performance

Some indices for purrformance, that piled up during the years:

-- Fix Kepler idiocies:
ALTER INDEX PK_KISSUESTATE RENAME TO ISSUE_STATE_IDX;
ALTER INDEX PK_KPLUGINSCFG RENAME TO PLUGIN_CFG_IDX;
ALTER INDEX PK_KSTATEVALUES RENAME TO STATE_VALUES_IDX;

-- Standard additions
CREATE INDEX IX_NOTIF_INST_MSGID ON NOTIFICATIONINSTANCE (MESSAGEID ASC);
CREATE INDEX IX_CHANGEGROUP_AUTHOR ON CHANGEGROUP (AUTHOR ASC); -- can be big
CREATE INDEX IX_CHANGEGROUP_AUTHOR_CREATED ON CHANGEGROUP (AUTHOR ASC, CREATED ASC); -- can be big
CREATE INDEX IX_CHANGEGROUP_AUTH_CREA_ISSUE ON CHANGEGROUP (AUTHOR ASC, CREATED DESC, ISSUEID ASC);
CREATE INDEX IX_FILEATTACHMENT_FILENAME ON FILEATTACHMENT (FILENAME ASC, '1'); -- See Note
CREATE INDEX IX_JIRAACTION_AUTHOR ON JIRAACTION (AUTHOR ASC);
CREATE INDEX IX_JIRAACTION_AUTHOR_CREATED ON JIRAACTION (AUTHOR ASC, CREATED ASC); 
CREATE INDEX IX_JIRAACTION_AUTH_CREAT_ISSUE ON JIRAACTION (AUTHOR ASC, CREATED DESC, ISSUEID ASC);
CREATE INDEX IX_JIRAACTION_TYPE_LEVEL ON JIRAACTION (ID ASC, ACTIONTYPE ASC, ACTIONLEVEL ASC); 
CREATE INDEX IX_JIRAISSUE_ISSUESTATUS ON JIRAISSUE (ISSUESTATUS ASC, '1');
CREATE INDEX IX_JIRAISSUE_PROJ_ASSIGNEE ON JIRAISSUE (PROJECT ASC, ASSIGNEE ASC);
CREATE INDEX IX_JIRAISSUE_PROJECT ON JIRAISSUE (PROJECT ASC, '1');
CREATE INDEX IX_JIRAISSUE_PROJECT_UPDATED ON JIRAISSUE (PROJECT ASC, UPDATED ASC);
CREATE INDEX issue_reporter ON JIRAISSUE (REPORTER ASC); -- lately introduced by Atlassian in JIRA 6.3
CREATE INDEX IX_JIRAISSUE_SUMMARY ON JIRAISSUE (SUMMARY ASC, '1'); -- See Note
CREATE INDEX IX_JIRAISSUE_UPDATED_PROJ_ID ON JIRAISSUE (UPDATED DESC, PROJECT ASC, ID ASC); -- for idiotic pagination queries
CREATE INDEX IX_JIRAISSUE_WF_PKEY_1 ON JIRAISSUE (WORKFLOW_ID ASC, PKEY ASC, '1');
CREATE INDEX IX_OS_WFENTRY_ID_NAME ON OS_WFENTRY (ID ASC, NAME ASC);

-- customfieldvalue is large. we need it to be created quickly, then turn off parallelism.
CREATE INDEX IX_CUSTOMFIELDVALUE_ISSUE ON CUSTOMFIELDVALUE (ISSUE ASC) PARALLEL 12; ALTER INDEX IX_CUSTOMFIELDVALUE_ISSUE NOPARALLEL; -- can be _really_ big depending on your CF usage

-- bitmap indexes:
CREATE BITMAP INDEX IX_OS_WFENTRY_STATE ON OS_WFENTRY (STATE ASC); -- degree of cardinality is typically below 1 ppm.

-- recreate these indexes:
DROP INDEX PRMSSN_SCHEME; CREATE BITMAP INDEX PRMSSN_SCHEME ON SCHEMEPERMISSIONS(SCHEME ASC);
DROP INDEX WF_ENTRYID; CREATE INDEX WF_ENTRYID ON OS_CURRENTSTEP (ENTRY_ID, STEP_ID);
DROP INDEX ISSUE_WORKFLOW; CREATE INDEX ISSUE_WORKFLOW ON JIRAISSUE (WORKFLOW_ID, ISSUESTATUS);

Note: On multi column indices containing a '1': The '1' here is actually a static char(1) column, always containing '1', and even if the first (the real) column's value is null, it will be stored in the index, and thus will be searchable.