How do I go about optimizing an Oracle query?

Question

I was given a SQL query, saying that I have to optimize this query.

I came accross explain plan. So, in SQL developer, I ran explain plan for ,

It divided the query into different parts and showed the cost for each of them.

How do I go about optimizing the query? What do I look for? Elements with high costs?

I am a bit new to DB, so if you need more information, please ask me, and I will try to get it.

I am trying to understand the process rather than just posting the query itself and getting the answer.

The query in question:

SELECT cr.client_app_id,
  cr.personal_flg,
  r.requestor_type_id 
FROM credit_request cr,
  requestor r,
  evaluator e 
WHERE cr.evaluator_id = 96 AND
  cr.request_id = r.request_id AND
  cr.evaluator_id = e.evaluator_id AND
  cr.request_id != 143462 AND
  ((r.soc_sec_num_txt = 'xxxxxxxxx' AND         
  r.soc_sec_num_txt IS NOT NULL) OR
  (lower(r.first_name_txt) = 'test' AND
  lower(r.last_name_txt) = 'newprogram' AND
  to_char(r.birth_dt, 'MM/DD/YYYY') = '01/02/1960' AND
  r.last_name_txt IS NOT NULL AND
  r.first_name_txt IS NOT NULL AND
  r.birth_dt IS NOT NULL))

On running explain plan, I am trying to upload the screenshot.

OPERATION    OBJECT_NAME     OPTIONS     COST 
 SELECT STATEMENT                        15 
 NESTED LOOPS              
 NESTED LOOPS                            15 
 HASH JOIN                               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    BY INDEX ROWID      11 
 INDEX  CRDRQ_DONE_EVAL_TASK_REQ_NDX     SKIP SCAN   10 
 Access Predicates 
 CR.EVALUATOR_ID=96 
 Filter Predicates 
 AND 
 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     BY INDEX ROWID      3 
 Filter Predicates 
 OR 
 R.SOC_SEC_NUM_TXT='XXXXXXXX' 
 AND 
 R.BIRTH_DT IS NOT NULL 
 R.LAST_NAME_TXT IS NOT NULL 
 R.FIRST_NAME_TXT IS 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' 

Answer 1

As a quick update to your query, you're going to want to refactor it to something like this:

SELECT
    cr.client_app_id,
    cr.personal_flg,
    r.requestor_type_id 
FROM 
    credit_request cr
    inner join requestor r on
        cr.request_id = r.request_id
    inner join evaluator e on
        cr.evaluator_id = e.evaluator_id
WHERE 
    cr.evaluator_id = 96
    and cr.request_id != 143462
    and (r.soc_sec_num_txt = 'xxxxxxxxx' 
        or (
            lower(r.first_name_txt) = 'test'
            and lower(r.last_name_txt) = 'newprogram'
            and r.birth_dt = date '1960-01-02'
        )
    )

Firstly, joining by commas creates a cross join, which you want to avoid. Luckily, Oracle's smart enough to do it as an inner join since you specified join conditions, but you want to be explicit so you don't accidentally miss something.

Secondly, your is not null checks are pointless--if a column is null, and = check you do will return false for that row. In fact, any comparison with a null column, even null = null returns false. You can try this with select 1 where null = null and select 1 where null is null. Only the second one returns.

Thirdly, Oracle's smart enough to compare dates with the ISO format (at least the last time I used it, it was). You can just do r.birth_dt = date '1960-01-02' and avoid doing a string format on that column.

That being said, your query isn't exactly poorly written in terms of egregious performance mistakes. What you want to look for are indices. Does evaluator have one on evaluator_id? Does credit_request? What types are they? Typically, evaluator will have a one on the PK evaluator_id, and credit_request will have one for that column, as well. The same for requestor and the request_id columns.

Other indices you may want to consider are all the fields you're using to filter. In this case, soc_sec_num_txt, first_name_txt, last_name_txt, birth_dt. Consider putting a multi-column index on the latter three, and a single column index on the soc_sec_num_txt column.