In Oracle, you can create a function-based index that stores precomputed results of a function or expression applied to the table columns.
Function-based indexes are used to increase the performance of queries that use functions in the WHERE clause.
Oracle:
-- Create a function-based index that stores names in upper case CREATE INDEX cities_fn_idx ON cities (UPPER(name)); -- Index range scan will be used instead of expensive full table scan SELECT name FROM cities WHERE UPPER(name) = 'HOUSTON';
SQL Server does not support function-based indexes, but you can use computed columns and indexes on computed columns to increase the performance of queries that use functions in the WHERE clause.
Note that for case-insensitive search in SQL Server, you do not need function-based indexes, you have to use case-insensitive collation instead (it is default).
SQL Server:
-- Search is case-insensitive in SQL Server by default, no need to use UPPER function SELECT name FROM cities WHERE name = 'HOUSTON';
In other cases, you can use a computed column and index on it:
SQL Server:
-- Define a computed column CREATE TABLE cities ( ... upper_name AS UPPER(name) ); CREATE INDEX cities_fn_idx ON cities (upper_name));
Summary information:
Last Update: Oracle 11g R2 and Microsoft SQL Server 2012
Assume that you want to perform a case-insensitive search by a city name:
Oracle:
-- Define a table CREATE TABLE cities ( name VARCHAR2(90), state CHAR(2), notes VARCHAR2(90) ); -- Insert some data INSERT INTO cities VALUES ('Houston', 'TX', '4th largest city in the US, and the largest city in Texas'); INSERT INTO cities VALUES ('Seattle', 'WA', 'the largest city in the Pacific Northwest'); -- Create index (non-function based) on name column CREATE INDEX cities_name_idx ON cities (name);
When there is a regular (non-function based) index define on a table, it is not used when you to perform a case-insensitive search (use a function in WHERE clause):
Oracle:
-- Enable trace in SQL*Plus SET AUTOTRACE ON SELECT name, state, notes FROM cities WHERE UPPER(name) = 'HOUSTON'; -- Fragment of SQL*Plus statistics # Execution Plan # 0 SELECT STATEMENT # 1 TABLE ACCESS FULL | CITIES
You can see that full table scan is used that can be very expensive when the table contain a lot of rows.
Now let's create a function-based index and run the query again:
-- Create a function-based index to index names in upper case CREATE INDEX cities_fn_idx ON cities (UPPER(name)); SELECT name, state, notes FROM cities WHERE UPPER(name) = 'HOUSTON'; -- Fragment of SQL*Plus statistics # Execution Plan # 0 SELECT STATEMENT # 1 TABLE ACCESS BY INDEX ROWID | CITIES # 2 INDEX RANGE SCAN | CITIES_FN_IDX
Now index range scan is used that can significantly increase the performance if the table contains a lot of rows and the index is selective (few rows meet the selection criteria).
In SQL Server, you can use a computed column and index defined on the computed column to increase the performance of a query that uses a function in WHERE condition.
SQL Server allows you to use a case-insensitive collation (and it is default) so you do not need to use UPPER function and function-based index to perform a case-insensitive search in SQL Server, you can use a regular index on column.
SQL Server:
-- Define a table CREATE TABLE cities ( name VARCHAR(90), state CHAR(2), notes VARCHAR(90) ); -- Insert some data INSERT INTO cities VALUES ('Houston', 'TX', '4th largest city in the US, and the largest city in Texas'); INSERT INTO cities VALUES ('Seattle', 'WA', 'the largest city in the Pacific Northwest'); -- Create index (non-function based) on name column CREATE INDEX cities_name_idx ON cities (name);
UPPER function is not required to return the correct results, so index on name column can be used:
SELECT name, state, notes FROM cities WHERE name = 'HOUSTON';
Output:
name | state | notes |
Houston | TX | 4th largest city in the US, and the largest city in Texas |
But let's use UPPER function as an example how to implement a function-based index using a computed column and index on it in SQL Server:
SQL Server:
-- Define a table with a computed column CREATE TABLE cities ( name VARCHAR(90), state CHAR(2), notes VARCHAR(90), upper_name AS UPPER(name) ); -- Insert some data INSERT INTO cities VALUES ('Houston', 'TX', '4th largest city in the US, and the largest city in Texas'); INSERT INTO cities VALUES ('Seattle', 'WA', 'the largest city in the Pacific Northwest'); -- Create an index on computed column that acts as the function-based index CREATE UNIQUE INDEX cities_fn_idx ON cities (upper_name);
Note that SQL Server allows you to not specify columns list (name, state, notes) although INSERT omits the value for the computed column.
Now SQL Server uses the index on the computed column even if you still use UPPER(name) function in the query, not only upper_name column:
SQL Server:
SELECT name, state, notes FROM cities WHERE UPPER(name) = 'HOUSTON';
Execution plan:
There are just 2 rows in the table, so we used UNIQUE index to force SQL Server to use it, and demonstrate that a index on a computed column can be used even if the query still uses a function in WHERE.
Oracle 11g R2 Database Concepts
Oracle 11g R2 Database Administrator's Guide
Microsoft SQL Server 2012 - Books Online
Microsoft SQL Server 2008 R2 - Books Online
SQLines offers services to migrate Oracle databases and applications to Microsoft SQL Server. For more information, please Contact Us.