SQL query takes 6 hours to execute

[mahajanakhil198]

Hi

Check if this gets you the same result as your original query

SELECT TO_CHAR(A.ID) A118,A.ASSET_GROUP_XID A2,A.ACL_ID A106,A.OWNER A4,CONVERT(A.NAME,'WE8MACROMAN8') A1,
A.FIRST_VERSION_CREATED_BY A15,LPAD(LTRIM(TO_CHAR(A.FIRST_VERSION_DATE_CREATED,'SYYYYMMDDHH24MISS')),15,'+') A16,
LPAD(LTRIM(TO_CHAR(A.DATE_CREATED,'SYYYYMMDDHH24MISS')),15,'+') A18,CONVERT(A.CHECKOUT_LOCATION,'WE8MACROMAN8') A24,
TO_CHAR(A.ASSET_SIZE) A7,A.CHECKED_OUT_BY A23,A.ARCHIVE_STATUS A27,A.STORAGE_CHANGE_REQUEST A110,A.HAS_HISTORY A74,
TO_CHAR(A.CONTAINED_IN_COUNT) A73,TO_CHAR(A.CONTAINER_OF_COUNT) A72,TO_CHAR(A.RELATED_WITH_COUNT) A71,
CONVERT(A.FILE_TYPE,'WE8MACROMAN8') A12,CONVERT(A.FILE_CREATOR_APPLICATION,'WE8MACROMAN8') A13,
CONVERT(A.FILE_EXTENSION,'WE8MACROMAN8') A9,TO_CHAR(A.REVISION) A21,A.IS_CURRENT_VERSION A109,
TO_CHAR(A.FAMILY_ID) A112,LPAD(LTRIM(TO_CHAR(A.FILE_DATE_CREATED,'SYYYYMMDDHH24MISS')),15,'+') A11,
LPAD(LTRIM(TO_CHAR(A.FILE_DATE_LAST_MODIFIED,'SYYYYMMDDHH24MISS')),15,'+') A10
FROM MAM_ASSETS A
WHERE ((A.ACL_ID=65536) AND
(A.ASSET_GROUP_XID=120) AND
(A.IS_CURRENT_VERSION=1) AND
EXISTS
(SELECT SV65539.ID FROM MAM_ATTR_STRING_VALUES SV65539
WHERE (SV65539.LOWER_CASE_STRING_VALUE LIKE 
LOWER(CONVERT('%813982%','WE8MSWIN1252','WE8MACROMAN8')))
AND   (SV65539.ATTRIBUTE_XID=65539)
AND  A.ID=SV65539.ID)
ORDER BY A.ASSET_SIZE

Also see if you can or already have a index on

MAM_ATTR_STRING_VALUES (ID,ATTRIBUTE_XID,LOWER_CASE_STRING_VALUE )

I cannot change the query. It is generated by the application by the UI of user session. Moreover, the development on application has stopped and it is only under support plan. Thanks for your effort anyways.