Atlassian uses cookies to improve your browsing experience, perform analytics and research, and conduct advertising. Accept all cookies to indicate that you agree to our use of cookies on your device. Atlassian cookies and tracking notice, (opens new window)
One DBA from FH suggested to improve this very frequent query:
SELECT AD_Table_ID, AccessLevel, TableName, IsView,
(SELECT ColumnName
FROM AD_COLUMN
WHERE AD_COLUMN.AD_TABLE_ID = AD_TABLE.AD_TABLE_ID
AND AD_COLUMN.COLUMNNAME = AD_TABLE.TABLENAME || '_ID')
FROM AD_Table WHERE IsActive='Y'
Adding UPPER to use the index, like this:
SELECT AD_Table_ID, AccessLevel, TableName, IsView,
(SELECT ColumnName
FROM AD_COLUMN
WHERE AD_COLUMN.AD_TABLE_ID = AD_TABLE.AD_TABLE_ID
AND upper(AD_COLUMN.COLUMNNAME) = upper(AD_TABLE.TABLENAME || '_ID'))
FROM AD_Table WHERE IsActive='Y';
One DBA from FH suggested to improve this very frequent query:
SELECT AD_Table_ID, AccessLevel, TableName, IsView, (SELECT ColumnName FROM AD_COLUMN WHERE AD_COLUMN.AD_TABLE_ID = AD_TABLE.AD_TABLE_ID AND AD_COLUMN.COLUMNNAME = AD_TABLE.TABLENAME || '_ID') FROM AD_Table WHERE IsActive='Y'Adding UPPER to use the index, like this:
SELECT AD_Table_ID, AccessLevel, TableName, IsView, (SELECT ColumnName FROM AD_COLUMN WHERE AD_COLUMN.AD_TABLE_ID = AD_TABLE.AD_TABLE_ID AND upper(AD_COLUMN.COLUMNNAME) = upper(AD_TABLE.TABLENAME || '_ID')) FROM AD_Table WHERE IsActive='Y';