Re: 10G Case Insensitive Searches.

From: William Robertson <william.robertson_at_bigfoot.com>
Date: 1 Jul 2005 08:14:00 -0700
Message-ID: <1120230840.704480.186090@g14g2000cwa.googlegroups.com>

LineVoltageHalogen wrote:
> If I understand correctly by setting the following:
>
> alter session set nls_comp=ansi;
>
> alter session set nls_sort=binary_ci;
>
> I can issue a query against the database that is case insensitive.
> My question is this: When I test this functionality the index I have in
> place is not longer used for the query? Do I need to create a special
> function based index to speed things up or should the existing index
> (which works if I don't specify the alter sessions) work?
>
> TFD
Yes AFAIK you will need a function based index.

The following test is in 9.2.0.5 and uses "ALTER SESSION SET nls_sort = generic_baseletter" because it works in 9i. I will do a test of binary_ci in 10g if I get a chance.

SQL> CREATE TABLE test_tab
  2 AS
  3 SELECT DISTINCT object_name AS col FROM all_objects;

Table created.

SQL> INSERT ALL

  2  INTO test_tab VALUES ('DATA')
  3  INTO test_tab VALUES ('Data')
  4  INTO test_tab VALUES ('data')

  5 SELECT * FROM dual;

3 rows created.

• Regular index: SQL> CREATE INDEX test_ix ON test_tab (col) COMPUTE STATISTICS;

Index created.

SQL> SELECT * FROM test_tab WHERE col = 'Data'   2
SQL> @xplan

---

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|

---

| 0 | SELECT STATEMENT | | 1 | 15 | 2 (50)|
|* 1 | INDEX RANGE SCAN | TEST_IX | 1 | 15 | 2 (50)|

---

Predicate Information (identified by operation id):

---

   1 - access("TEST_TAB"."COL"='Data')

SQL> ALTER SESSION SET nls_sort = generic_baseletter;

Session altered.

SQL> ALTER SESSION SET nls_comp = ansi;

Session altered.

• No index range scan after changing sort parameters: SQL> SELECT * FROM test_tab WHERE col = 'Data' 2 SQL> @xplan

  ---

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|

---

| 0 | SELECT STATEMENT | | 52 | 780 | 6 (17)|
|* 1 | INDEX FAST FULL SCAN| TEST_IX | 52 | 780 | 6 (17)|

---

Predicate Information (identified by operation id):

---

   1 - filter(NLSSORT("TEST_TAB"."COL")=NLSSORT('Data'))

• But notice the filter clause above. Index that expression: SQL> CREATE INDEX test_fbi ON test_tab (NLSSORT(col)) COMPUTE STATISTICS;

Index created.

SQL> ALTER SESSION SET query_rewrite_enabled = TRUE;

Session altered.

SQL> ALTER SESSION SET query_rewrite_integrity = TRUSTED;

Session altered.

SQL> SELECT * FROM test_tab WHERE col = 'Data'   2
SQL> @xplan

---

| Id  | Operation                   |  Name       | Rows  | Bytes |

Cost (%CPU)|

---

| 0 | SELECT STATEMENT | | 52 | 780 | 3 (34)|
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_TAB | 52 | 780 | 3 (34)|
|* 2 | INDEX RANGE SCAN | TEST_FBI | 21 | | 2 (50)|

---

Predicate Information (identified by operation id):

---

   2 - access(NLSSORT("TEST_TAB"."COL")=NLSSORT('Data')) Received on Fri Jul 01 2005 - 10:14:00 CDT