Re: Case insensitive searches

From: Mohamed Houri <>
Date: Tue, 18 Mar 2014 16:46:07 +0100
Message-ID: <>


I can confirm you that you can create a unique constraint and a unique index on a virtual column. Although function based index and index on a virtual column are both of "FUNCTION-BASED NORMAL" type 'there is a subtle difference in using an index on a virtual column because in this case the function that defines the virtual column is hidden within the column and can not ''disturb'' the CBO in contrast to the function based index where its definition should match the query where clause in order to be used by the CBO (eventually).

There is a further details on the advantage of using a virtual column in the following post

Best regards
Mohamed Houri

2014-03-18 16:28 GMT+01:00 Ryan January <>:

> I do have more info since my last email. I have confirmed it is a 'sort
> unique nosort' operation, however it's over an index range scan of a
> multi-column primary key index.
> Column 1 is integer, column 2 varchar. The predicates supplied to the
> query cover both columns. As Mohamed also indicated, hinting to change the
> execution confirms other plans work as expected. I'm very confident that
> this is the issue.
> I hadn't really considered multi-column indexes when I did my original
> assessment of the queries generated by the application. I'm in the process
> of doing that now while researching what changes we can make to solidly
> mask the issue on the database side. My expectation is that we'll end up
> with function based nlssort indexes. I'm getting ready to test out some
> edge cases now, such as whether or not the index can support a primary key.
> Speaking more generically, not to this particular case, virtual columns do
> make sense as a possibility for case insensitivity. While you're still
> incurring the CPU overhead of the function, it's at least controlled and
> centralized. I really like that it also allows for selective indexing
> where appropriate. I don't believe I've ever tried constraining a virtual
> column. I'll have to do some testing on that. If we do end up having to
> modify the queries this may end up being part of the proposed solution.
> On 03/18/2014 09:48 AM, Mark W. Farnham wrote:
>> Another take (not implying that previous suggestions will not work) on
>> this
>> is to add the virtual column of upper(<original column>). IF that column
>> becomes a frequent primary filter the pros and cons of indexing the
>> virtual
>> column (which will then de facto be a function based index) can be
>> considered, but the sorting semantics can be directly displayed side by
>> side
>> <original column> and upper(<original column>) to answer questions that
>> come
>> up, and it is effective as a secondary filter, group by, and order by
>> column
>> reference.
>> I have not tested constraints on virtual columns, but I certainly *hope*
>> they work the way it seems natural to presume, that a unique constraint on
>> upper(<original column>) would prevent the simultaneous commitment of both
>> 'Ryan' and 'ryan' in the <original> column.
>> Excusing the pun, whether this is a useful solution for a specific case
>> will
>> depend on the details of the case.
>> mwf
>> -----Original Message-----
>> From: [
>> ]
>> On Behalf Of Ryan January
>> Sent: Tuesday, March 18, 2014 9:12 AM
>> To: Martin Klier;;;
>> Cc: oracle-l
>> Subject: Re: Case insensitive searches
>> 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
>>> 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:
> --
> ------------------------------------------------------------------
> 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..
> ------------------------------------------------------------------
> --

Bien Respectueusement
Mohamed Houri

Received on Tue Mar 18 2014 - 16:46:07 CET

Original text of this message