Avoid the database calls when issue is null

Morning chaps,

This is not so much a support issue as an "FYI" which may come in useful to both you and your customers who are attempting a similar migration to ourselves.

Background:

We currently are running our "Live" JIRA instance with a MySQL backend. Most of our enterprise apps. use Oracle for the DB and so we have been working on a project to migrate JIRA data to Oracle. We carried out the requisite steps (XML export, repoint database at Oracle, XML import) and it seemed to work quite nicely - until, that is, we attempted to access GreenHopper. Clicking the GreenHopper link was incredibly slow - it took over 3 minutes from clicking the link to get to the Planning Board.

We carried out some investigation and determined that this delay was caused by Oracle executing a particular query over 40 times and each query was taking around 3 seconds to return. Actual query causing the problem:

SELECT ID, pkey, PROJECT, REPORTER, ASSIGNEE, issuetype, SUMMARY, DESCRIPTION, ENVIRONMENT, PRIORITY, RESOLUTION, issuestatus, CREATED, UPDATED, DUEDATE, VOTES, IMEORIGINALESTIMATE, TIMEESTIMATE, TIMESPENT, WORKFLOW_ID, SECURITY, FIXFOR, COMPONENT FROM jiraissue WHERE pkey IS NULL

Here's the analysis from our DBA:

"The slowdown on starting GreenHopper from the Oracle JIRA instance is being caused by a query that attempts to do an "IS NULL" comparison against the pkey column of the Jiraissue table (94k rows).

The problem with this is, Oracle is doing a full table scan for the query (and this is occurring multiple times ... presumably GreenHopper is executing the query for each project or something).

The full table scan is in turn causing cluster issues because each query has to ping the other node to ensure the blocks being referenced haven't changed. Hence the huge amounts of IO we saw this morning.

So why a full table scan and not an index scan ? (which would be faster).

Essentially relational database design stipulates that a NULL means no value or "unknown" - thus Oracle doesn't record it in the index.

Nulls are never stored in an index, and database design principles always recommend using a real value to indicate "unknown" instead of an actual NULL.

I've applied a slight "hack" on to the index however to see if it will be a suitable work-around.

Essentially I've recreated an index as a composite (with a constant arbitrary value as the second part of the key). Essentially forcing the NULL to be included in the index.

This seems to have worked, but we should keep an eye on the GreenHopper over the next couple of days just to be sure.

If this works, I'd suggest applying the same change to the jiraissue table in the iplay jira instance.

It may also be worth raising this with Atlassian / GreenHopper .... The database behaviour between Oracle and MySQL is different in this respect."

Note that this issue is exacerbated by running Oracle in a clustered environment (as ours is) although it would still manifest itself in a stand-alone configuration. Can you cast an eye over this analysis and let us know if it makes sense to you, please?

Regards,

Alan Ferrier