SQL query takes 6 hours to execute

[mahajanakhil198]

Hi

Did it ever run in less then 5 minutes ?
(your users are very patient to wait for 5 minutes )

Have you thought of a MV

CREATE MATERIALIZED VIEW LOG ON MAM_ATTR_STRING_VALUES WITH ROWID

CREATE MATERIALIZED VIEW MAM_ATTR_STRING_VALUES_MV
CLUSTER m_lt_li (id)
REFRESH FAST ON COMMIT
ENABLE QUERY REWRITE
AS
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

There is no problem with this part. As I told earlier that the whole query executes in less than 2 seconds if I use select count(*) instead of select . I am not able to figure out why specifying the column list causes the query to take so long in fetching the results. Is it a known issue? I could understand that it has to bring column data, so more I/O is required but this more time could amount to 6 hours is not understandable.