Re: Case insensitive searches

The MOS note is saying this

If all of the following are true you may have hit this problem:

  NLS_COMP is set to LINGUISTIC

  A SORT .. NOSORT operation is used in the plan

   above an INDEX FULL SCAN

  The query fails with ORA-600 [qernsRowP]

I am wondering what happens if you hint your culprit query to use a hash_aggregation method as shown in the example below. I am not suggesting you to do it. It is just to know if by using hash_aggregation the ora-0600 is avoided

SQL> SELECT c1

  2 FROM t

  3 GROUP BY c1

  4 ORDER BY c1 ASC NULLS LAST;

SQL_ID 0gy317h433nkd, child number 3

SELECT c1 FROM t GROUP BY c1 ORDER BY c1 ASC NULLS LAST

Plan hash value: 2111031280

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | | | 209 (100)| |

| 1 | SORT GROUP BY NOSORT| | 3 | 9 | 209 (5)| 00:00:01 |

| 2 | INDEX FULL SCAN | T_PK | 200K| 585K| 209 (5)| 00:00:01 |

SQL> SELECT /*+ USE_HASH_AGGREGATION*/ c1

  2 FROM t

  3 GROUP BY c1

  4 ORDER BY c1 ASC NULLS LAST;

SQL_ID 6uddpp0y70xqy, child number 0

SELECT /*+ USE_HASH_AGGREGATION*/ c1 FROM t GROUP BY c1 ORDER BY c1 ASC

NULLS LAST Plan hash value: 2835688100

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | | | 209 (100)| | | 1 | SORT ORDER BY | | 3 | 9 | 209 (5)| 00:00:01 | | 2 | HASH GROUP BY | | 3 | 9 | 209 (5)| 00:00:01 | | 3 | INDEX FULL SCAN| T_PK | 200K| 585K| 209 (5)| 00:00:01 |

Best regards
Mohamed Houri
www.hourim.wordpress.com

2014-03-18 14:12 GMT+01:00 Ryan January <rjjanuary_at_multiservice.com>:

> Thank you for all the responses so far.
>
> Stéphane: Function based indexes were the first solution that came to
> mind. Especially considering that we'd possibly require them to support
> the NLS changes, similar to what Martin is suggesting. Unfortunately we
> felt it wasn't a good fit for a few reasons. Mainly, the varchar fields are
> a secondary search criteria. Table access would generally be through
> existing indexes on integer columns. You make a very good point about
> constraints and insuring uniqueness. I don't believe we'll run into issues
> here but it is something I'll have to keep in mind.
> Internationalization was our main reason to avoid storing varchar values
> case insensitive within the database. We do support multiple languages and
> want to avoid that situation if at all possible.
>
> Herald: We have considered Oracle text in the past, however decided
> against it due it's inability to perform partial word searches. The words
> 'App' and 'Apple' would bring back very different search results. I'll
> read back through the Oracle text docs today to see if we overlooked
> anything or if it's been extended since we last reviewed it. Even if it
> isn't a fit here, I can think of a few other areas it may be beneficial.
>
> Rajendra: We had not considered regex. The reason we were included within
> the discussion was to make a general sweeping change in an attempt to
> reduce the impact on the application code base. Ultimately that's why we
> began to consider the NLS settings. This is something we may look at if it
> comes to modifying the queries within the application.
>
> Norm: This is what I've been considering as a next step. The main
> complaint started out that a user felt the search results were incorrectly
> sorted, they hadn't yet realized the searches were case sensitive. After
> we explained it was working as expected they also brought up the need for
> case insensitive searching. (eg: Ryan = ryan) Given that most table
> access will be through existing indexes I'm thinking we propose this change
> as at least a temporary solution. My worry is that going down this road
> will cause us to eventually modify the queries to allow case insensitive
> compares as well.
>
> Martin: I'm glad to see someone who has had success with this approach.
> When we started seeing this issue so soon I felt we may be wondering down
> a path rarely traveled. I'm at least willing to put a little additional
> time in the NLS settings as a solution. As you've mentioned, we're setting
> these params at the session level and only for the few app users who
> require it. Unfortunately the query that surfaced this issue was of a
> moderately complex view which involves a few sys owned objects. We're still
> trying to narrow down the table access which is actually causing the
> problem. Since the bug referenced is specifically sort/nosort operations
> within the execution plan I expected this to be an issue with the index
> itself. If the issue ends up being with these sys object access methods I'm
> not sure where we'll go next. This is something I'll be digging into
> momentarily.
> Did you remove/replace all the existing varchar indexes or additionally
> supplement with the NLS specific indexes? Do any of your applications
> interact with any oracle owned tables/views? If so, how do you approach
> those situations?
>
>
>
> On 03/18/2014 07:28 AM, Martin Klier wrote:
>
>> Hi Ryan,
>>
>> we are running more than 50 projects on 11.2.x with NLS_SORT set to
>> BINARY_CI and NLS_COMP to LINGUISTIC.
>>
>> We do two things:
>> * both parameters are only set on session level for the app users and
>> NOT for SYSTEM or the dictionary owner.
>> * all VARCHAR field indexes are changed to function based:
>> NLSSORT(MY_TEXT_FIELD, 'NLS_SORT=BINARY_CI')
>> Because this function is what Oracle wraps around any string search when
>> the mentioned parameters are set.
>>
>> Hope this helps
>> Martin Klier
>>
>> Am 17.03.2014 23:02, schrieb Ryan January:
>>
>>> to set NLS_SORT to BINARY_CI and NLS_COMP to LINGUISTIC
>>>
>>
>
> --
>
>
> ------------------------------------------------------------------
> This email is intended solely for the use of the addressee and may
> contain information that is confidential, proprietary, or both.
> If you receive this email in error please immediately notify the
> sender and delete the email..
> ------------------------------------------------------------------
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Bien Respectueusement
Mohamed Houri

--
http://www.freelists.org/webpage/oracle-l