Trying to get Oracle to use an index
Oracle Database 19c Enterprise Edition Release 22.214.171.124.0 - Production Version 126.96.36.199.0
For formatting, I attached a document with details of the reports.
I have SQL that is generated by a product that I have no ability to change how it works. A very common query is to find all of the cases (TICKET) for a person using their employee ID (PRI). There’s an index on TICKET.PRI
select T1.dbid,T1.id,T2.name,T1.date_open,T3.name,T1.action_taken,T1.employment_record_select,T1.date_effective_from,T1.date_effective_to,T1.ticket_detail,T1.assignee_curr,T5.team_name,T1.classification,T4.code,T11.name,T1.verified_date,T1.name,T1.contact_info,T1.project_label,T1.pri,T1.lang,T1.pl,T1.status,T1.date_modified,T1.created_by from ticket T1,statedef T2,sub_type T3,enttable_1 T5,department T4,work_type T11 where T1.state = T2.id and T1.sub_type = T3.dbid and T1.assigned_group = T5.dbid and T1.department_ref = T4.dbid and T1.work_type = T11.dbid and (T1.dbid <> 0 and ((T1.pri like '%123456789%'))) order by T1.id DESC;