CODEWITHSUNDEEP
sqlperformanceoracle-database
Pravin Satav
Pravin Satav

Reputation: 702

optimizer refuses to use the index

SELECT /*+ PARALLEL(cfe, 6) */
      dpd.f_p_descr,
      ef.t_a_code,
      pd.p_name,
      ef.t_q
 FROM e_fact ef
INNER JOIN d_dim dd
   ON ef.t_d_key = dd.d_key
INNER JOIN f_e cfe
   ON ef.ref_id = cfe.t_id
  AND ef.r_version = cfe.t_version
INNER JOIN d_dim dpd
   ON dpd.d_key = ef.d_key
INNER JOIN p_dim pd
   ON pd.p_key = ef.b_p_key
INNER JOIN r_dim rd
   ON rd.r_key = ef.t_r_key
INNER JOIN f_t_dim ftd
   ON ftd.t_key = cfe.t_key
WHERE dd.d_value = '19-OCT-2012'
  AND dpd.f_d = 'XYZ'
  AND ftd.s_id IN (201, 209)
  AND rd.r_n = 'ABC'

I got this query from production, problem is optimizer refuses to use the index on f_e even when the hint is added (/*+ index(e.c_fact_idx12) */. What should be my approach and what all things I need to check for this. Is there any other way to tune this query? New to query tuning so help would be appreciated.

Upvotes: 1

Views: 57

Answers (1)

Sunil Khatri
Sunil Khatri

Reputation: 46

You are using e.c_fact_idx12, but the table alias e is not defined anywhere in the query !

Upvotes: 2

Related Questions