Issue Summary

During the Bamboo upgrade process, the task com.atlassian.bamboo.upgrade.tasks.repeatable.AddIndicesToForeignKeys uses an Oracle database query that include the RULE hint. This hint is outdated and can negatively impact performance due to its reliance on the deprecated Rule-Based Optimizer (RBO) in Oracle databases.

Steps to Reproduce

1. Configure Bamboo to use an Oracle database.
2. Initiate a Bamboo upgrade.

Expected Results

The performance of the Bamboo upgrade, specifically during the task of retrieving database constraints while using Oracle, should be optimized. The use of deprecated query hints should be avoided to ensure efficient execution. Find more details below about RULE hint:

Oracle Docs:

Oracle Corporation strongly advises the use of cost-based optimization. Rule-based optimization will be deprecated in a future release.

Oracle Rule Based Optimizer:

The Oracle Rule-Based Optimizer (RBO) has been deprecated since Oracle 11g.

Actual Results

The database logs reveal the query executed during the upgrade process include the RULE hint. This outdated hint can lead to inefficient query execution and prolonged upgrade times. Performance testing shows a significant time difference when the hint is removed:

SELECT /*+ RULE */
    tc.constraint_name,
    ccu.column_name,
    tc.constraint_type
FROM all_constraints tc
JOIN all_cons_columns ccu ON tc.table_name = ccu.table_name
    AND tc.constraint_name = ccu.constraint_name
WHERE upper(tc.table_name) = 'AO_AC3877_USER_RL_SETTINGS'
  AND tc.owner = 'bamboo'

All Rows Fetched: 0 in 4.375 seconds

SELECT
    tc.constraint_name,
    ccu.column_name,
    tc.constraint_type
FROM all_constraints tc
JOIN all_cons_columns ccu ON tc.table_name = ccu.table_name
    AND tc.constraint_name = ccu.constraint_name
WHERE upper(tc.table_name) = 'AO_AC3877_USER_RL_SETTINGS'
  AND tc.owner = 'bamboo'

All Rows Fetched: 0 in 0.013 seconds

Workaround

Currently, there is no workaround available.