sql - How do I go about optimizing an Oracle query? -

sql - How do I go about optimizing an Oracle query? -

i given sql query, saying have optimize query.

i came accross explain plan. so, in sql developer, ran explain plan ,

it divided query different parts , showed cost each of them.

how go optimizing query? for? elements high costs?

i bit new db, if need more information, please inquire me, , seek it.

i trying understand process rather posting query , getting answer.

the query in question:

select cr.client_app_id, cr.personal_flg, r.requestor_type_id credit_request cr, requestor r, evaluator e cr.evaluator_id = 96 , cr.request_id = r.request_id , cr.evaluator_id = e.evaluator_id , cr.request_id != 143462 , ((r.soc_sec_num_txt = 'xxxxxxxxx' , r.soc_sec_num_txt not null) or (lower(r.first_name_txt) = 'test' , lower(r.last_name_txt) = 'newprogram' , to_char(r.birth_dt, 'mm/dd/yyyy') = '01/02/1960' , r.last_name_txt not null , r.first_name_txt not null , r.birth_dt not null))

on running explain plan, trying upload screenshot.

operation object_name options cost select statement 15 nested loops nested loops 15 hash bring together 12 access predicates cr.evaluator_id=e.evaluator_id index evaluator_pk unique scan 0 access predicates e.evaluator_id=96 table access credit_request index rowid 11 index crdrq_done_eval_task_req_ndx skip scan 10 access predicates cr.evaluator_id=96 filter predicates , cr.evaluator_id=96 cr.request_id<>143462 index requestor_pk range scan 1 access predicates cr.request_id=r.request_id filter predicates r.request_id<>143462 table access requestor index rowid 3 filter predicates or r.soc_sec_num_txt='xxxxxxxx' , r.birth_dt not null r.last_name_txt not null r.first_name_txt not null lower(r.first_name_txt)='test' lower(r.last_name_txt)='newprogram' to_char(internal_function(r.birth_dt),'mm/dd/yyyy')='01/02/1960'

after refactoring query comes indexes, next on @eric's post:

credit_request: you're joining onto requestor on request_id, hope unique. in clause have status on evaluator_id , select client_app_id , personal_flg in query. so, need unique index, on credit_request of (request_id, evaulator_id, client_app_id, personal_flg.

by putting columns you're selecting index avoid by index rowid, means have selected values index re-entered table pick more information. if info in index there's no need.

you're joining onto evaluator on evaluator_id, included in first index.

requestor: beingness joined onto on request_id , clause include soc_sec_num_text, lower(first_name_txt), lower(last_name_txt) , birth_dt. so, need unique if possible, index on (request_id, soc_sec_num_text) because of or farther complicated because should have index on many of conditions possible. you're selecting requestor_type_iud.

in case avoid functional index, many columns, i'd index on (request_id, soc_sec_num_text, birth_dt ) if have space, time , inclination adding lower(first_name_txt)... etc may improve speed depending on how selective column is. means if there far more values in instance, first_name_txt birth_dt you'd improve of putting in front end of birth_dt in index query has less scan if it's non-unique index.

you notice haven't added selected column index you're going have go table gain nil adding it.

evaluator: beingness joined on evaluator_id need unique, if possible, index on column.

sql oracle oracle11g oracle-sqldeveloper