All Forums Database
taruntrehan 43 posts Joined 10/12
16 Feb 2015
Performance : ROWID join after JI hit

Hi All,

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.

Regards, Tarun Trehan
dnoeth 4628 posts Joined 11/04
16 Feb 2015

Hi Tarun,
did you check statistics?
Any missing stats according to DIAGNOSTIC HELPSTATS ON FOR SESSION?


taruntrehan 43 posts Joined 10/12
19 Feb 2015

Hi ,
The statistics are updated on participating objects.

Tarun Trehan

taruntrehan 43 posts Joined 10/12
19 Feb 2015

Just FYI that Similar behaviour is observed if i hit a SI.
The rowid join takes a long time to complete.

Tarun Trehan

You must sign in to leave a comment.