StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I have reproduced your finding on my test DB (10.2.0.3). Upon investigation, it appears the <code>LIKE</code> operator cannot use the linguistic index -- from the <a href="http://download.oracle.com/docs/cd/B19306%5F01/server.102/b14225/ch5lingsort.htm#sthref656" rel="nofollow noreferrer" title="Requirements for Using Linguistic Indexes">10gR2 Documentation</a>:</p> <blockquote> <p>The SQL functions MAX( ) and MIN( ), and also the LIKE operator, cannot use linguistic indexes when NLS_COMP is set to LINGUISTIC.</p> </blockquote> <p>It seems the main purpose of linguistic indexes is to improve the SORT operation.</p> <p>If your goal is to search on this column in a case-insensitive way, I suggest you create an index on <code>UPPER(name)</code> and build your query with <code>UPPER(name) LIKE UPPER('x%')</code> instead.</p> <p>If you want to use another (more complex) linguistic setting, you might want to look at the <a href="http://download.oracle.com/docs/cd/B19306_01/text.102/b14218/csql.htm#CCREF0105" rel="nofollow noreferrer">Oracle Text indexes</a>.</p> <p><strong>Edit:</strong> There is another workaround: you can replace the <code>LIKE 'ABC%'</code> with:</p> <pre><code>SQL> select * from x where name >= 'ABC' and name < 'ABD'; Execution Plan ---------------------------------------------------------- Plan hash value: 708878862 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 24 | 4 (0)| 00:00: | 1 | TABLE ACCESS BY INDEX ROWID| X | 1 | 24 | 4 (0)| 00:00: |* 2 | INDEX RANGE SCAN | X_IC | 1 | | 3 (0)| 00:00: -------------------------------------------------------------------------------- </code></pre> <p>As you can see if you can translate the LIKE expression to an expression with the comparison operators (<code>></code> and <code><</code>) the linguistic index might be used.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload