SQL Performance (MOSC)

MOSC Banner

Discussions

Trying to get Oracle to use an index

edited Jul 4, 2022 8:35PM in SQL Performance (MOSC) 18 commentsAnswered ✓

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.15.0.0.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

Original SQL

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;

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center