The AO_54C900_C_TEMPLATE_REF table does not have an index on the UUID column

Issue Summary

The AO_54C900_C_TEMPLATE_REF table can grow quite large, and can cause startup to take a long time. A query is run during startup that may need to be run more than a thousand times, and the lack of an index on the UUID column causes the queries to run slowly if there are hundreds of thousands of rows in the table. Here's the query:

SELECT "NAME","PLUGIN_CLONE","TEMPLATE_ID","ID","UUID","PLUGIN_MODULE_KEY" FROM "AO_54C900_C_TEMPLATE_REF" WHERE "UUID" = ?

Environment

Issue does not seem to be specific to a version of Confluence or database

Steps to Reproduce

1. On an instance of Confluence with several hundred thousands of rows in the AO_54C900_C_TEMPLATE_REF table, time how long startup takes.
2. Add an index to the UUID column in the AO_54C900_C_TEMPLATE_REF table, and then time to see how long startup takes.

Expected Results

Confluence should already have an index on this column

Actual Results

There is no index on the column.

Notes

After adding the index to the UUID column on an instance with over 500,000 rows, startup time went from about 20 minutes to about 3 minutes.

Workaround

You can add a column to this table with the following SQL statement, for Oracle:

CREATE INDEX uuid_idx ON AO_54C900_C_TEMPLATE_REF (UUID);