One of the query got slow after minor upgrade from 19.10 to 19.14. noticed that execution plan got changed. having full table scan and not using index. although, index are in valid state. we rebuild index, drop & re-create index but that didn't help. we have 19c Standard edition without tuning pack. can someone help me please ?
Note:- i have created backup before upgrade. so if anything required, i can bring them back.
SELECT /*+ FIRST_ROWS */ c.id FROM schema.table1 c WHERE ((contains(c.description, '12345' ) > 0 OR contains(c.key_label, '12345' ) > 0 OR EXISTS (SELECT 1 FROM schema.table2 m WHERE c.id = m.case_id AND m.deleted_flag = 0 AND contains(m.case_comment, '12345' ) > 0)) AND c.case_type = 'issue' );