Can we optimize this further?

[pandeyji]

Dear All, I am looking for help to optimize below query. This query is to look into single table and see if data was changed. There are 9-10 unions of similar query to get change in other data. I am posting query below. Please let me know if it can be optimize further.
=============================SQL QUERY=============
SELECT A.Facility,
A.Pat,
A.NAME,
A.data_entry_date,
A.data_entry_time,
A.data_entry_by,
B.data_entry_by PREVIOUS_DATA_ENTRY_BY,
'Change to Last Name' Type_of_Change,
A. freetxt_40_2 Change_To,
B. freetxt_40_2 Change_from,
SUBSTRING(A.MPI_uniqueid, 4, 50) MPI_uniqueid
FROM pat_his A,
pat_his B
WHERE A.facility = B.facility
AND A.pat = B.pat
AND (UPPER(TRIM(A.freetxt_40_2)) <> UPPER(TRIM(B.freetxt_40_2)) or (B.freetxt_40_2 is null and A.freetxt_40_2 is not null))
AND SUBSTRING(A.MPI_uniqueid, 4, 50) > SUBSTRING(B.MPI_uniqueid, 4, 50)
AND A.data_entry_date >= {?From Date}
AND A.data_entry_date <= {?Thru Date}

AND SUBSTRING(B.MPI_uniqueid, 4, 50) = (SELECT MAX(SUBSTRING(C.MPI_uniqueid, 4, 50))
FROM pat_his C
WHERE A.facility = C.facility
AND A.pat = C.pat
AND SUBSTRING(A.MPI_uniqueid, 4, 50) > SUBSTRING(C.MPI_uniqueid, 4, 50))


=====================================================

[stecal]

Any function applied to an indexed column practically guarantees the index will not be used. Test for not equals practically guarantees a full table scan (have to look at everything to see what is not present). Optimize further is more like not optimized at all to begin with.

[Flyby]

Mayhaps with some function-based indexes.

[PAVB]

Mayhaps with some function-based indexes.

How is that going to help when there is a condition calling for a full table scan?