Wrong estimation of Rows in explain

[Rathi Rao]

Hi,

I have the following query,

select ai.activityid,ai.farmercropid,fca.ActivityID,fca.farmercropid
from activityinformation ai
inner join FarmerCropActivity fca
on ai.farmercropid = fca.farmercropid
and fca.activityid = ai.activityid

select count(*) from FarmerCropActivity; -- 2806792
select count(*) from activityinformation; -- 3269567

Indexes are

create index idx_ai_activityid on activityinformation(activityid)
create index idx_ai_farmercropid on activityinformation(farmercropid)

create index idx_fca_activityid on FarmerCropActivity(activityid)
create index idx_fca_farmercropid on FarmerCropActivity(farmercropid)

The activityinformation table is partitioned on a column called isactive. And isactive has only 2 values 0 and 1

The problem is the query takes forever to run. And when I look into the explain plan, the rows for FarmerCropActivity shows 2, which is wrong

Any thoughts on this would be greatly appreciated

Thanks in advance
Rathi