Performance Issues after enabling "case insensitive search" per Oracle 10g documentation

View previous topic View next topic Go down

Performance Issues after enabling "case insensitive search" per Oracle 10g documentation

Post  giby.varghese@gmail.com on Sun Aug 08, 2010 1:02 pm

It should be noted that this is NOT a BMC/Remedy issue.
In Oracle 10g it is documented that you can set “case insensitivity” by setting the session
parameters:
NLS_SORT=BINARY_CI
and
NLS_COMP=LINGUISTIC
What we've found in various Oracle forums is that setting these renders all of your existing indexes useless to the DB until you
recreate them using the syntax:
create [unique] index index_name on
table_name (NLSSORT(column_name, 'NLS_SORT=BINARY_CI'));
Here’s an example of how this even affects the unique index on C1. Notice that without setting “case insensitivity” the DB is able
to do an INDEX UNIQUE SCAN returning the row very quickly with an overall cost of only 2:
SQL> select c1 from t22 where c1='000000000157138';
C1
---------------
000000000157138
Execution Plan
----------------------------------------------------------
Plan hash value: 2708875326
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 | 2 (0)| 00:00:01 |
|* 1 | INDEX UNIQUE SCAN| IT22 | 1 | 16 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("C1"='000000000157138')
Now we’ll set the parameters for “case insensitive searches”:
SQL> alter session set NLS_COMP=LINGUISTIC;
Session altered.
SQL> alter session set NLS_SORT=BINARY_CI;
Session altered.
Now the DB must scan the entire index (INDEX FAST FULL SCAN) with a cost of 163:
SQL> select c1 from t22 where c1='000000000157138';
C1
---------------
000000000157138
Execution Plan
----------------------------------------------------------
Plan hash value: 3989697481
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 | 163 (7)| 00:00:02 |
|* 1 | INDEX FAST FULL SCAN| IT22 | 1 | 16 | 163 (7)| 00:00:02 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NLSSORT("C1",'nls_sort=''BINARY_CI''')=HEXTORAW('303030303
03030303031353731333800')
Now we’ll recreate the index on C1:
SQL> drop index it22;
Index dropped.
SQL> create unique index it22 on
2 t22 (NLSSORT(c1, 'NLS_SORT=BINARY_CI'));
Index created.
Executing the same query we can see that the DB is now able to do an INDEX UNIQUE SCAN of the index and then access the
row by rowId with a cost of 3:
SQL> select c1 from t22 where c1='000000000157138';
C1
---------------
000000000157138
Execution Plan
----------------------------------------------------------
Plan hash value: 1855191866
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T22 | 1 | 16 | 3 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | IT22 | 1 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(NLSSORT("C1",'nls_sort=''BINARY_CI''')=HEXTORAW('3030303030303
0303031353731333800') )

giby.varghese@gmail.com

Posts : 107
Points : 222
Reputation : 3
Join date : 2009-11-11

View user profile

Back to top Go down

Re: Performance Issues after enabling "case insensitive search" per Oracle 10g documentation

Post  giby.varghese@gmail.com on Sun Aug 08, 2010 1:03 pm

BMC Remedy AR System Server (version 7.1.00)

AR System Server for Windows; Server: Windows Server 2003 Standard; Client: Windows XP Professional;
Language: English-USA; Database: Oracle 10g; Web: Apache Tomcat; Browser: Microsoft Internet Explorer 6.x;

Recreate your indexes using the syntax:
create [unique] index index_name on
table_name (NLSSORT(column_name, 'NLS_SORT=BINARY_CI'));

giby.varghese@gmail.com

Posts : 107
Points : 222
Reputation : 3
Join date : 2009-11-11

View user profile

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum