I am trying to debug a query which is taking a lot of time.
Following objects are involved in the query :
person_mst table :
person_id , first_name , last_name , age , address_id , contact_id , version_nbr , create_dt
PI : person_id
PPI : create_dt (weekly)
JI on person_mst :
first_name , last_name , age , version_nbr
NUSI on person_mst :
first_name , last_name
select a.person_id , b.first_name , a.last_name , a.age , a.create_dt , a.address_id , a.contact_id
from person_mst a inner join names_list_vt b on
a.first_name = b.first_name
and a.create_dt between '2015-01-01' and '2015-02-02';
The names_list_vt contains a list of distinct first names.
In the above query, the PE decides to hit the JI first and retrieve the row_id based on the condition of fisrt_name.
It then hits the base table doing a rowid join to fetch the results leveraging the PPI as well.
However, the rowid join is very slow and consumes a lot of time and the paramater AMP CPU is very high as well.
I am looking for a better way to execute this query. Looks like hitting the JI is not the best option here.
Note: I cannot share the exact the structure and other details due to compliance concerns.
Appreciate your help in this regard.