The like statement is a killer for the qurey performance, if I remove the last line of the query ( ra.text_value like '%cccc%'), it runs fast.
ra is the 1.2M records and ra.text_value is defined as varchar2(4000). Any suggestions?

select /*+ FIRST_ROWS */

e.line_number as "nodeID",

e.name as "elementName",

'RESPONSE_ANSWER' as "type",

ra.response_answer_id as "id",

ra.text_value as "text_value"

from element e, top_level_element tle, response_bid rb, response_details rd,

response_answer ra

where

tle.responder_document_id = 58745 and

e.top_level_element_id = tle.element_id and

((tle.data_collection_type_id in (3, 4) and

element_state_id in (3, 4, 5, 8)) or

(tle.data_collection_type_id in (1, 2) and

rb.response_status_id in (1, 5) and

rb.response_details_id = rd.response_details_id and

rd.username_id = 153855 and

rd.top_level_element_id = tle.element_id)) and

ra.element_id = e.element_id and

ra.text_value is not null and

e.element_type_id != 2 and

ra.text_value like '%cccc%'