Home » RDBMS Server » Server Administration » NLS_COMP and NLS_SORT (merged) (Oracle 11g 11.2.0.1.0)
NLS_COMP and NLS_SORT (merged) [message #514941] Thu, 07 July 2011 07:05 Go to next message
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: NLS_COMP and NLS_SORT [message #514942 is a reply to message #514941] Thu, 07 July 2011 07:08 Go to previous messageGo to next message
Michel Cadot
Messages: 59425
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
set trimout on

Take care your lines does not exceed 80 character width and ESPECIALLY
there is no useless trailing spaces.

Regards
Michel
Re: NLS_COMP and NLS_SORT [message #514943 is a reply to message #514942] Thu, 07 July 2011 07:11 Go to previous messageGo to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member

I am sorry Sir was not aware of this, keep in mind from my next post.

I will be very thankful, If you can help me and solve my problem.

Thanks
Deepak
Re: NLS_COMP and NLS_SORT [message #514944 is a reply to message #514943] Thu, 07 July 2011 07:20 Go to previous messageGo to next message
Michel Cadot
Messages: 59425
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I can't read the long lines as I don't want to scroll left to right and back and forth.
Will help you in your next post.

Regards
Michel
Re: NLS_COMP and NLS_SORT [message #514946 is a reply to message #514944] Thu, 07 July 2011 07:25 Go to previous messageGo to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member

Sir Its only the lines which I manually written are long for the I am posting them again.

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
Re: NLS_COMP and NLS_SORT [message #514948 is a reply to message #514946] Thu, 07 July 2011 08:02 Go to previous messageGo to next message
Michel Cadot
Messages: 59425
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ BINARY is the natural order for index
2/ = and LIKE is language dependent so if it is not BINARY you can't use the index
3/ 10.2 has a bug

Take to post ONLY the relevant part, all the statistics were not for this question
as well as all useless empty lines.
Make your post the shorter as possible for a complete description of your question.

Regards
Michel
DIFFERENCE BETWEEN BINARY_AI and GENERIC_M_AI [message #515154 is a reply to message #514941] Fri, 08 July 2011 05:05 Go to previous messageGo to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member

Hi All,

I want to know what is the difference between the values "BINARY_AI" AND "GENERIC_M_AI" which are used for NLS_SORT parameter.

Thanks
Deepak
Re: DIFFERENCE BETWEEN BINARY_AI and GENERIC_M_AI [message #515159 is a reply to message #515154] Fri, 08 July 2011 05:37 Go to previous messageGo to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member

Actually I am working on Linguistic Sorting now days and try to find what are the major difference on
using NLS_COMP='LINGUISTIC' AND NLS_SORT='GENERIC_M_AI' IN ORACLE 10gR2 AND ORACLE 11gR2.
Till now what I have came across are the two major points:
Case 1: "Effect on using the special symbols in the column and how they are ignored while sorting."
Case 2: "On using Linguistic Sorting the effect on use of indexes." [mentioned in last post.]

If my seniors can tell me which area I can explore more so that I come across the difference in two versions of ORACLE.

Thanks
Deepak
Re: NLS_COMP and NLS_SORT [message #515161 is a reply to message #514948] Fri, 08 July 2011 05:41 Go to previous messageGo to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member

Thanks a lot for you help Sir. Thanks Everyone.

Regards
Deepak
Re: DIFFERENCE BETWEEN BINARY_AI and GENERIC_M_AI [message #515169 is a reply to message #515159] Fri, 08 July 2011 06:02 Go to previous messageGo to next message
Michel Cadot
Messages: 59425
Registered: March 2007
Location: Nanterre, France, http://...
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

Re: DIFFERENCE BETWEEN BINARY_AI and GENERIC_M_AI [message #515174 is a reply to message #515169] Fri, 08 July 2011 06:18 Go to previous messageGo to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member

Can you provide me the link for the same Sir?

Regards
Deepak
Re: DIFFERENCE BETWEEN BINARY_AI and GENERIC_M_AI [message #515179 is a reply to message #515174] Fri, 08 July 2011 07:19 Go to previous messageGo to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member

Can anyone tell me difference between "BINARY_AI" and "GENERIC_M_AI".

Regards
Deepak
Re: DIFFERENCE BETWEEN BINARY_AI and GENERIC_M_AI [message #515693 is a reply to message #515169] Tue, 12 July 2011 23:31 Go to previous messageGo to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member

My Question was not answered but still thanks everyone for your participation and helping me.

Regards
Deepak
Re: DIFFERENCE BETWEEN BINARY_AI and GENERIC_M_AI [message #515730 is a reply to message #515693] Wed, 13 July 2011 02:29 Go to previous messageGo to next message
Michel Cadot
Messages: 59425
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you get an answer elsewhere?

Regards
Michel
Re: DIFFERENCE BETWEEN BINARY_AI and GENERIC_M_AI [message #515741 is a reply to message #515730] Wed, 13 July 2011 03:03 Go to previous messageGo to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member

I read somewhere that if we use BINARY_AI or GENERIC_M_AI, its one and the same thing.. but I was confused if its really true Sir.

Thanks
Deepak
Re: DIFFERENCE BETWEEN BINARY_AI and GENERIC_M_AI [message #515746 is a reply to message #515741] Wed, 13 July 2011 03:08 Go to previous messageGo to next message
Michel Cadot
Messages: 59425
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Where did you read it? Maybe we can estimate the reliability of the source.

Regards
Michel

[Updated on: Wed, 13 July 2011 03:10]

Report message to a moderator

Re: DIFFERENCE BETWEEN BINARY_AI and GENERIC_M_AI [message #515804 is a reply to message #515746] Wed, 13 July 2011 05:38 Go to previous messageGo to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member

But what you think Sir, are the two values for the parameter NLS_SORT same or they mean different.

Regards
Deepak
Re: DIFFERENCE BETWEEN BINARY_AI and GENERIC_M_AI [message #515807 is a reply to message #515746] Wed, 13 July 2011 06:18 Go to previous messageGo to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member

I have doubt on this because if both the value are same then have a look at this Sir:


SQL> select * from nls_session_parameters where parameter in ('NLS_SORT','NLS_COMP');

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_SORT                       BINARY
NLS_COMP                       BINARY

SQL> select * from bug692 where word like '%a_b_c%';

WORD
--------------------
a_b_c_d

SQL> alter session set nls_comp='LINGUISTIC';

Session altered.

SQL> alter session set nls_SORT='BINARY_AI';

Session altered.

SQL> select * from bug692 where word like '%a_b_c%';

WORD
--------------------
qa_bug_cal

SQL> alter session set nls_SORT='GENERIC_M_AI';

Session altered.

SQL>  select * from bug692 where word like '%a_b_c%';

no rows selected

SQL>


For one [nls_SORT='BINARY_AI'] I am getting result and for the other [nls_SORT='GENERIC_M_AI'] I am not getting any result.

Please throw some light on this.

Regards
Deepak
Re: DIFFERENCE BETWEEN BINARY_AI and GENERIC_M_AI [message #515845 is a reply to message #515807] Wed, 13 July 2011 09:32 Go to previous messageGo to next message
Michel Cadot
Messages: 59425
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a test case we can reproduce.

Regards
Michel
Re: DIFFERENCE BETWEEN BINARY_AI and GENERIC_M_AI [message #515876 is a reply to message #515845] Wed, 13 July 2011 23:49 Go to previous messageGo to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member

Here is the test table i have create with the results:

SQL> select * from bug692;

WORD
--------------------
a_b_c_d
qa_bug_cal

SQL> select * from bug692 where word like '%a_b_c%';

WORD
--------------------
qa_bug_cal

SQL> alter session set nls_sort=generic_m_ai;

Session altered.

SQL> select * from bug692 where word like '%a_b_c%';

no rows selected



Thanks
Deepak
Re: DIFFERENCE BETWEEN BINARY_AI and GENERIC_M_AI [message #515903 is a reply to message #515876] Thu, 14 July 2011 02:48 Go to previous messageGo to next message
Michel Cadot
Messages: 59425
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Test case (I thought you know what it is now).

Regards
Michel
Re: DIFFERENCE BETWEEN BINARY_AI and GENERIC_M_AI [message #515994 is a reply to message #515903] Thu, 14 July 2011 07:28 Go to previous messageGo to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member

I'll understand if you can make me understand that Sir.

Regards
Deepak
Re: DIFFERENCE BETWEEN BINARY_AI and GENERIC_M_AI [message #516004 is a reply to message #515994] Thu, 14 July 2011 08:01 Go to previous message
cookiemonster
Messages: 11076
Registered: September 2008
Location: Rainy Manchester
Senior Member
If there's something in that link that you don't understand, say so and we'll explain.
Looks very straight forward to me.
Previous Topic: Access Logs via SQL
Next Topic: oracle 9i question
Goto Forum:
  


Current Time: Fri Oct 24 07:31:47 CDT 2014

Total time taken to generate the page: 0.11393 seconds