Database Reference
In-Depth Information
In version 10.2, another limitation is that in order to apply a
LIKE
operator, the database engine can't take
advantage of linguistic indexes. In other words, a full index scan or full table scan can't be avoided. This limitation is
no longer present from version 11.1 onward.
Even though the examples in this section are based on B-tree indexes, linguistic bitmap indexes are
supported as well.
The following example shows that linguistic indexes can also be used to avoid
ORDER BY
operations:
SQL> SELECT /*+ index(t) */ * FROM t WHERE c1 BETWEEN 'L' AND 'M' ORDER BY c1;
-------------------------------------------------------
| Id | Operation | Name |
-------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| T |
|* 2 | INDEX RANGE SCAN | I_C1_LINGUISTIC |
-------------------------------------------------------
2 - access(NLSSORT("C1",'nls_sort=''GENERIC_M_AI''')>=HEXTORAW('0226') AND NLSSORT(
"C1",'nls_sort=''GENERIC_M_AI''')<=HEXTORAW('0230') )
In summary, linguistic comparison is a powerful feature that's transparent for SQL statements. However, the
database engine can apply them efficiently only when a set of adapted indexes is available. Because the setting at the
client level might impact the utilization of the indexes, it's essential to plan its use carefully.
Composite Indexes
So far, with one exception, I've discussed only indexes that have an index key consisting of a single column. Index
keys, however, can consist of many columns (the limit is 32 for B-tree indexes and 30 for bitmap indexes). Indexes
with multiple columns are called
composite indexes
(sometimes the terms
concatenated indexes
or
multicolumn
indexes
are used). In this regard, B-tree indexes and bitmap indexes have completely different behaviors, so I discuss
them separately. Note that all examples in this section are based on the
composite_index.sql
script.
B-tree Indexes
The purpose of composite indexes is twofold. First, they can be used to implement a primary key or unique key
constraint composed of several columns. Second, they can be used to apply a predicate composed of several SQL
conditions combined with
AND
. Be careful, because when several SQL conditions are combined with
OR
, composite
indexes can't be used efficiently!
Naturally, it's important to discuss how to use composite indexes for applying restrictions. The following query is
used for this:
SELECT * FROM t WHERE n1 = 6 AND n2 = 42 AND n3 = 11
Let's begin by looking at what happens when a single column index is used. With an index built on the
n1
column, 527 rowids are returned from the index scan. Because the index stores only the data related to the
n1
column,
only the
n1 = 6
predicate can be applied through the index by operation 2. The other two predicates are applied as a
