Java versus OCI - same SQL , different performance
Here is the SQL
select col1, col2 from table where col2 between to_number(:1) and to_number(:2) and ( (col3 in ('3', '4') and col4 !=
'Z') or col5 = to_date(:3, 'YYYYMMDD') ) order by col1
According to the explain plan, Oracle runs a full scan on a unique index that exists on the table and includes col1 and then it reads the rest of the data from the table itself. I am not sure how efficient this plan is (I would rather do a full table scan against the table and save on the IOs), but this is the same explain plan that I have seen in production where an old code (OCI - based) is used and no performance issues have been reported.