The Globalization of Language in Oracle - Index Requirements
May 19, 2005
What are the performance implications for the globalization of your Oracle database? This article takes a look at indexing requirements when using NLS_SORT <> BINARY and using an ORDER BY clause.
First, I would like to put to bed a myth that I am sure will sprout up as more and more individuals venture down the implementation of maintaining multiple languages within their Oracle databases. There is a statement made that states it is a requirement to create an index for columns that require linguistic sorting capability. This statement in no way requires an index on those columns that contain multi lingual data. We all know that indexes are optional and are not required for any form of data to be inserted into our databases. This statement only emphasizes that if you want to have linguistic data in your database and you want to sort the data efficiently then you should consider adding a linguistic index on those columns. As always, when we tune SQL statements, it is dependent on the type and amount of data we are trying to sort. In addition, as Oracle will perform a sort in the background for the linguistic sort you require, depending on the predicates in the WHERE clause, it may still be more efficient to NOT have a linguistic index on the columns.
By default, Oracle databases are setup with an NLS_SORT set to BINARY. When we do a sort on a column, the sort is done in BINARY order, which equals the order of the ASCII characters. This is the order all of us in the English-speaking world have grown to love. You can see sample data output on this in the second article of this series called The Globalization of Language in Oracle - The NLS_COMP and NLS_SORT variables. Look at Table 2 of that article and the column where NLS_COMP=ANSI and NLS_SORT=BINARY are. This sort was done on the xGraphic character and as you can see, the order of the Binary value represents the order of graphic character stored. As soon as we begin to introduce case insensitivity or accent insensitivity, the order of the binary value is all over the place in the next two columns in that table. This is because we have introduced a new sort order defined through the NLS_SORT parameter that takes precedence over the default BINARY order.
When we design databases we have been taught to put indexes on those columns that are involved in predicate comparisons of the WHERE clause or involved in sort functionality of SQL statements, such as the ORDER BY clause. For instance, Table A executes a simple SQL statement that queries the char_table we have been working with and ORDERs BY the xgraphic character. Following is the explain plan for just such a query. I have added the setting of the NLS_SORT parameter just for clarification that in fact we are running in "default" mode for binary sorting. As you can see from the explain output, we in fact used the binary index that was created on the xgraphic column.
The question now comes into play, since we are talking about multi linguistic databases, what happens when we are required by our clients to present the data in the sort order of a particular locale? For this example, we assume our client is in Germany. We need to change the NLS_SORT session variable to GERMAN. This in itself will provide a sort order to the client with a linguistic sort order accustomed to by the German language. The problem comes into play on how Oracle must get and sort the data to be presented. Table B shows the exact same SQL statement as in Table A, but please notice the explain plan output. As you can see we set the NLS_SORT to GERMAN and then produced the explain output. The access path now does a full table scan on the char_table and then performs a sort. This sort is actually a German Linguistic Sort. I think it would be nice for Oracle to indicate that in the explain plan but am sure it will be in a future release. Anyway, you can see that now the cost of the SELECT statement also went up considerably. Therefore, the question becomes 'how can we now provide acceptable performance for our German clients.'
The solution is really quite simple. We need only place an index (linguistic index) on the column that is required for the sort to be performed on. This is where the myth I mentioned in the opening paragraph will come from. Table C gives the session SQL statements executed along with the explain output on the SELECT statement. For creating the index, you need only specify the NLSSORT function around the column you would like to put a linguistic index on. Also, include the language for the function-based index. Now when we set the NLS_SORT session variable to GERMAN and execute the SELECT statement you can see that the linguistic index is used. Moreover, access to the table data is by ROWID just as in the Table A example. Also, note that the cost of executing this SQL statement also returned to the original value.
The one other caveat you need to be aware of is that in order for the linguistic index to be used the column in the table must be NOT NULLable. Alternatively, you can add the WHERE <column_name> IS NOT NULL to your SQL statement.
Providing adequate performance for multi-lingual data is not difficult. You only need to add linguistic indexes where they make sense on your tables. Are these indexes required by Oracle? No. Will these indexes always be used when you issue an ORDER BY clause? No. Remember performance tuning, especially for SQL statements is dependent on a variety of variables. You need to be concerned with well formed SQL statements, row count, cardinality, and selectivity before just placing indexes on any and every column involved in a SQL statement.