We have a problem with FoxPro 9 and Oracle 10g that I hope someone can help me with. We have all of the indexes for character data created using syntax like the following:

create index <index_name> on <table>
(NLSSORT(<field>,'nls_sort=BINARY_CI'))

Then, we have the following trigger on login:

ALTER SESSION SET NLS_SORT=BINARY_CI
ALTER SESSION SET NLS_COMP=LINGUISTIC

I am connecting to the database using a DSNless ODBC connection and executing a query with SPT. We also tested it with a remote view using a DSN connection.

The performance is terrible when referencing these character fields in the WHERE clause. If we alter the ALTER SESSION SET NLS_COMP=LINGUISTIC to be ALTER SESSION SET NLS_COMP=BINARY, performance is great again but our case insensitive WHERE clauses no longer return all of the data. We profiled the SQL statements and when NLS_COMP = LINGUISTIC, Oracle is not using the index but it does us the index when NLS_COMP = BINARY.

We ran the same tests in other platforms (including Access) and the results were fast regardless of the SET NLS_COMP setting. We also checked and the indexes were always being used in the other platforms regardless of the setting of NLS_COMP.

Does anyone know what FoxPro is doing with the connection that might induce Oracle to do a table scan rather than use the index? FWIW, we know that this is not a parameter issue because it occurs on JOIN statements as well.

Toni-