| NLS_COMP and NLS_SORT (merged) [message #514941] |
Thu, 07 July 2011 07:05  |
|
|
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 #514946 is a reply to message #514944] |
Thu, 07 July 2011 07:25  |
|
|
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: DIFFERENCE BETWEEN BINARY_AI and GENERIC_M_AI [message #515159 is a reply to message #515154] |
Fri, 08 July 2011 05:37 |
|
|
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: 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: 54167
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 #515807 is a reply to message #515746] |
Wed, 13 July 2011 06:18 |
|
|
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 #515876 is a reply to message #515845] |
Wed, 13 July 2011 23:49 |
|
|
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
|
|
|
|
|
|
|
|
|
|
] 
Members
Search
Help
Register
Login
Home
