| NLS_COMP and NLS_SORT (merged) [message #514941] |
Thu, 07 July 2011 07:05  |
 |
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
|
Senior Member |
|
|
Hi All,
I have created a test table and I am trying to find the use of index in linguistic sorting both in Oracle 10g and Oracle 11g.
I am sending you the completed SQL with the results setting values for the parameter
CASE 1: NLS_COMP='BINARY' and NLS_SORT='BINARY'
SQL> ALTER SESSION SET NLS_COMP=BINARY ;
Session altered.
SQL> ALTER SESSION SET NLS_SORT=BINARY;
Session altered. SQL> create table letter_tab1 (letter varchar2(10));
Table created.
SQL> create index letter_tab1_idx on letter_tab1 (letter);
Index created.
SQL> select * from letter_tab1;
LETTER
----------
ab
AB
AB!!
ab!!
!!ab
!!AB
!!AB__
!!ab__
!!ab\
!!AB\
!!AB\
LETTER
----------
!!ab\
!!AB__
!!ab__
!!AB
!!ab
ab!!
AB!!
AB
ab
20 rows selected.
SQL> set autotrace on
SQL> select * from letter_tab1 where letter='ab';
LETTER
----------
ab
ab
Execution Plan
----------------------------------------------------------
Plan hash value: 3453163378
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 14 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| LETTER_TAB1_IDX | 2 | 14 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("LETTER"='ab')
Note
-----
- dynamic sampling used for this statement
SQL> select * from letter_tab1 where letter LIKE 'ab';
LETTER
----------
ab
ab
Execution Plan
----------------------------------------------------------
Plan hash value: 3453163378
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 14 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| LETTER_TAB1_IDX | 2 | 14 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("LETTER" LIKE 'ab')
Note
-----
- dynamic sampling used for this statement
FOR ORACLE 11g
SQL> ALTER SESSION SET NLS_COMP=BINARY;
Session altered.
SQL> ALTER SESSION SET NLS_SORT=BINARY;
Session altered.
SQL> CREATE TABLE NEW1(NAME VARCHAR2(10));
Table created.
SQL> create index new1_idx on new1(name);
Index created.
SQL> select * from new1;
NAME
----------
ab
ab!!
ab\\
ab\
\ab\
!!ab
!!ab\\
ab
ab!!
ab\\
ab\
NAME
----------
\ab\
!!ab
!!ab\\
14 rows selected.
SQL> set autotrace on
SQL> select * from new1 where name = 'ab';
NAME
----------
ab
ab
Execution Plan
----------------------------------------------------------
Plan hash value: 2737796132
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 14 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| NEW1_IDX | 2 | 14 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("NAME"='ab')
Note
-----
- dynamic sampling used for this statement (level=2)
SQL> select * from new1 where name like 'ab';
NAME
----------
ab
ab
Execution Plan
----------------------------------------------------------
Plan hash value: 2737796132
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 14 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| NEW1_IDX | 2 | 14 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("NAME"='ab')
Note
-----
- dynamic sampling used for this statement (level=2)
CASE 2: NLS_COMP='LINGUISTIC' AND NLS_SORT='GENERIC_M_AI'
FOR ORACLE 10G
SQL> alter session set nls_comp='LINGUISTIC';
Session altered.
SQL> alter session set nls_SORT='GENERIC_M_AI';
Session altered.
SQL> select * from letter_tab1 where letter='ab';
LETTER
----------
ab
AB
AB!!
ab!!
!!ab
!!AB
!!AB__
!!ab__
!!ab\
!!AB\
!!AB\
LETTER
----------
!!ab\
!!AB__
!!ab__
!!AB
!!ab
ab!!
AB!!
AB
ab
20 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3455439176
------ - -----------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------ - -----------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LETTER_TAB1 | 1 | 7 | 2 (0)| 00:00:01 |
------ - -----------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NLSSORT("LETTER",'nls_sort=''GENERIC_M_AI''')=HEXTORAW('
01EA01EF') )
Note
-----
- dynamic sampling used for this statement
SQL> select * from letter_tab1 where letter LIKE 'ab';
LETTER
----------
!!AB
!!AB
!!AB\
!!AB\
!!ab
!!ab
!!ab\
!!ab\
AB
AB
ab
LETTER
----------
ab
12 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1044897230
------ - -----------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------ - -----------------------------------------
| 0 | SELECT STATEMENT | | 12 | 84 | 1 (0)| 00:00:01 |
|* 1 | INDEX FULL SCAN | LETTER_TAB1_IDX | 12 | 84 | 1 (0)| 00:00:01 |
------ - -----------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("LETTER" LIKE 'ab')
Note
-----
- dynamic sampling used for this statement
FOR ORACLE 11G
SQL> alter session set nls_comp='LINGUISTIC';
Session altered.
SQL> alter session set nls_SORT='GENERIC_M_AI';
Session altered.
SQL> select * from new1 where name = 'ab';
NAME
----------
ab
ab!!
ab\\
ab\
\ab\
!!ab
!!ab\\
ab
ab!!
ab\\
ab\
NAME
----------
\ab\
!!ab
!!ab\\
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2252656391
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| NEW1 | 1 | 7 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NLSSORT("NAME",'nls_sort=''GENERIC_M_AI''')=HEXTORAW('
01EA01EF') )
Note
-----
- dynamic sampling used for this statement (level=2)
SQL> select * from new1 where name LIKE 'ab';
NAME
----------
ab
ab!!
ab\\
ab\
\ab\
!!ab
!!ab\\
ab
ab!!
ab\\
ab\
NAME
----------
\ab\
!!ab
!!ab\\
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2252656391
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| NEW1 | 1 | 7 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NLSSORT("NAME",'nls_sort=''GENERIC_M_AI''')=HEXTORAW('
01EA01EF') )
Note
-----
- dynamic sampling used for this statement (level=2)
So If we see for Oracle 10g R2
In the case when "NLS_COMP" and "NLS_SORT" are set to "BINARY" , I have "index range scan" using "=" operator in where condition and same using "like" operator.
But in the case when "NLS_COMP" and "NLS_SORT" are set to "LINGUISTIC" AND "GENERIC_M_AI" respectively, I have "table access full" using "=" operator and "index full scan" using "like" operator.
If we talk about Oracle 11g R2
In the case when "NLS_COMP" and "NLS_SORT" are set to "BINARY" , I have "index range scan" using "=" operator in where condition and same using "like" operator.
But in the case when "NLS_COMP" and "NLS_SORT" are set to "LINGUISTIC" AND "GENERIC_M_AI" respectively, I have "table access full" using "=" operator and again same using "like" operator.
"My Question is Why in Oracle 11g R2, I am not getting INDEX RANGE SCAN while using the "LINGUISTIC" VALUE FOR NLS_COMP AND "GENERIC_M_AI" FOR NLS_SORT.
Thanks & Regards
Deepak
[Edit MC: remove irrelevant statistics part and useless empty SQL lines]
[Updated on: Thu, 07 July 2011 07:57] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: DIFFERENCE BETWEEN BINARY_AI and GENERIC_M_AI [message #515169 is a reply to message #515159] |
Fri, 08 July 2011 06:02  |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Quote:mentioned in last post
Do not start a topic for a question related to the previous ones, we should not have to search in all your posts to know what you are refering to.
And do NOT post your title in UPPER case.
Quote:If my seniors can tell me which area I can explore more so that I come across the difference in two versions of ORACLE.
Search in the list of bugs.
Regards
Michel
[Updated on: Fri, 08 July 2011 06:04] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
