Originally posted by engiri
May be genius dba's give explanation, which I dont know for
this behaviour.

But when same kind of situation when we faced, after long analysis,
I found some of these kind of things worked.

In SQLPLUS he is using hard coded values and may be its going for index scans on fullname, but if u notice in PL/SQL block its a variable and oracle is not choosing for index scans but for partition full scans. when you put a trunc on that column you are actually forcing oracle to use index scans on the col Fullname..

suggestion : Collect stats on columns full name and departure_date.. may be it will help.

Abhay.