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';