Oracle Database Discussions

Hi,
My query is going for full table scan even we are using INDEXES .. is there is any cause we need to check for full table scan.

select D1.c2 as c1,
D1.c1 as c2,
D1.c3 as c3
from
(select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3
from
(select count(distinct case when T73739.DELETE_FLG = 'N' and upper(T85181.EXEC_STATUS) = 'LAUNCHED' and T73739.ROW_WID > 0 and upper(T66707.X_ARC_FUTURE_1_FLG) = 'N' and T66707.LAUNCH_DT_WID > 0 then concat(T73739.INTEGRATION_ID, cast(T73739.DATASOURCE_NUM_ID as CHARACTER ( 30 ) )) end ) as c1,
case when upper(T68292.SEGMENT_TYPE) = 'SEGMENT' then T68292.SGMT_NAME end as c2,
T68292.INTEGRATION_ID as c3,
ROW_NUMBER() OVER (PARTITION BY T68292.INTEGRATION_ID ORDER BY T68292.INTEGRATION_ID ASC) as c4
from
TABLE1 T66707 /* Fact_TABLE1*/ ,
TABLE2 T68292 /* Dim_TABLE2*/ ,
TABLE3 T73739,
TABLE4 T85181 /* Dim_TABLE4 */
where ( T66707.CONTACT_WID = T73739.ROW_WID and T66707.SEGMENT_WID = T68292.ROW_WID and T66707.LD_WAVE_WID = T85181.ROW_WID and case when upper(T68292.SEGMENT_TYPE) = 'SEGMENT' then T68292.SGMT_NAME end is not null )
group by T68292.INTEGRATION_ID, case when upper(T68292.SEGMENT_TYPE) = 'SEGMENT' then T68292.SGMT_NAME end
) D1
where ( D1.c4 = 1 )
) D1
order by c3


Thank you,