Oracle, not using a simple Index in a multilingual db

So Oracle's Cost Based Optimizer can often decide not to use an index. We had a case this week where it decided not to use an index on a single column varchar2 using a string bind variable?

After eliminating statistics as an issue, we discovered the problem was only affecting linux from either sqlplus, or jdbc using a type 4 thin driver, windows clients were ok.

So not sounding like a driver issue, we hunted further and foud that after select * from nls_session_parameters it was obvious that some we had some very important nls settings that were incorrect. For Oracle on *nix you have to ensure that an NLS_LANG environment variable is setup, like "ENGLISH_AUSTRALIA.UTF8"

Since we have been porting from sqlserver (case-insensitive) to oracle (case sensitive) there were certain nls settings that need to be set.

In Particular nls_comp=ansi and nls_sort=binary_ai. There are some articles around for doing this, just ask tom.

Warning

It should be stated however that this will only allow case insensitive searching for relational operators like '=', '<', and '>'. LIKE searches will still be sensitive, so for many cases you will still need to do standard uppercasing of literal search value and creation of function based index.

Back to our problem, why was asp working and the good guys not, well there was an existing after logon trigger that was setting up a single nls_sort=binary_ai.

Now we were getting this setting on connection, but asp were not, other regional settings must have been interfering and the nls_sort value was the standard "binary". So asp was still working fast due to the use of a standard NOT CASE INSENSITIVE index.

Meanwhile the cost based optimizer had no index to choose for us, because the creation of a function based index with that nls_sort setting had been missed. If you had a need to create one yourself it would be something like this:
create index blah on tbl (nlssort(columnName, 'NLS_SORT=BINARY_AI'))

BTW, when adjusting some of these parameters you may need to spend time on checking the order, as there are implicit dependencies with no apparent documentation on which order you should override them.

So if you are working in a multilingual db, make sure you check out nls_session_parameters to confirm your changes. The other tables nls_instance_parameters or nls_database_parameters are only relevant for checking the character set(s) of your database since that isn't session specific.

The Test

So you may be wondering how this was tested, well there was a test, it was very simple, using the explain plan feature of oracle asserting that there was an index scan present. Here are the interesting bits:

    public void testOracleWillUseIndexForQuery() throws SQLException {
        String problemQuery = "select * from test where name = '2423423'";
        assertQueryUsesIndex(problemQuery);
    }

    private void assertQueryUsesIndex(String problemQuery) throws SQLException {
        assertTrue(explainPlan(problemQuery).indexOf("INDEX (RANGE SCAN") > 0);
    }

    private String explainPlan(final String query) throws SQLException {
        String queryPlan = "explain plan for ";
        getConnection().createStatement().execute(queryPlan + query);
        return explainPlan;
    }

    private String getExplainPlan() throws SQLException {
        String queryPlan = "select n" +
        "  substr (lpad(' ', level-1) || operation || ' (' || options ||')',1,30 ) "Operation", n" +
           "  object_name "Object"n" +
           "from n" +
           "  plan_table n" +
           "start with id = 0 n" +
           "connect by prior id=parent_id";
        ResultSet rs = getConnection().createStatement().executeQuery(queryPlan);
        return accumulateColumn1(rs);
    }

    private String accumulateColumn1(ResultSet rs) throws SQLException {
        StringBuffer result = new StringBuffer();
        while (rs.next()) {
            result.append(rs.getString(1));
            result.append("n");
        }
        return result.toString();
    }