| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: 10G Case Insensitive Searches.
William Robertson wrote:
> 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'))
Similar behaviour in 10.1.0.3:
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production With the Partitioning, OLAP and Data Mining options
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')
3 rows created.
SQL> CREATE INDEX test_ix ON test_tab (col) COMPUTE STATISTICS;
Index created.
SQL> SELECT * FROM test_tab WHERE col = 'Data'
2
SQL> @xplan
Plan hash value: 4070774997
|
Predicate Information (identified by operation id):
1 - access("COL"='Data')
Note
SQL> ALTER SESSION SET nls_sort = binary_ci;
Session altered.
SQL> ALTER SESSION SET nls_comp = ansi;
Session altered.
SQL> SELECT * FROM test_tab WHERE col = 'Data'
2
SQL> @xplan
Plan hash value: 310319270
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 44 | 748 | 6 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| TEST_TAB | 44 | 748 | 6 (0)|00:00:01 |
Predicate Information (identified by operation id):
1 -
filter(NLSSORT("COL",'nls_sort=''BINARY_CI''')=HEXTORAW('64617461
00') )
Note
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
Plan hash value: 2573813933
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 44 | 748 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TEST_TAB | 44 | 748 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | TEST_FBI | 18 | | 1(0)| 00:00:01 |
Predicate Information (identified by operation id):
2 -
access(NLSSORT("COL",'nls_sort=''BINARY_CI''')=HEXTORAW('6461746100') )
Note
 |
 |