Re: Case insensitive searches

On 3/21/14, 3:07 PM, "Ric Van Dyke" <ric.van.dyke_at_hotsos.com> wrote:

>There is one (at least) major drawback of using virtual columns.
>
>Inserts must be done using column reference not position (see below). So
>adding this on COULD be a serious problem IF inserts are done without the
>column names.
>
>SQL> CREATE TABLE vc
> 2 ( col1 NUMBER
> 3 , col2 NUMBER
> 4 , col3 NUMBER GENERATED ALWAYS AS (col1 + col2) VIRTUAL
> 5 );
>SQL> INSERT INTO vc VALUES (10, 20);
>INSERT INTO vc VALUES (10, 20)
> *
>ERROR at line 1:
>ORA-00947: not enough values
>
>
>SQL> INSERT INTO vc VALUES (10, 20, 30);
>INSERT INTO vc VALUES (10, 20, 30)
>*
>ERROR at line 1:
>ORA-54013: INSERT operation disallowed on virtual columns
>
>
>SQL> INSERT INTO vc (col1, col2) VALUES (10, 20);
>SQL>
>
>-----Original Message-----
>From: oracle-l-bounce_at_freelists.org
>[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mark W. Farnham
>Sent: Tuesday, March 18, 2014 10:49 AM
>To: rjjanuary_at_multiservice.com; 'Martin Klier'; sfaroult_at_roughsea.com;
>Herald.ten.Dam_at_superconsult.nl; rajendra.pande_at_ubs.com;
>oracle_at_dunbar-it.co.uk
>Cc: 'oracle-l'
>Subject: RE: Case insensitive searches
>
>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: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
>On Behalf Of Ryan January
>Sent: Tuesday, March 18, 2014 9:12 AM
>To: Martin Klier; sfaroult_at_roughsea.com; Herald.ten.Dam_at_superconsult.nl;
>rajendra.pande_at_ubs.com; oracle_at_dunbar-it.co.uk
>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
>> 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
>
>
>--
>http://www.freelists.org/webpage/oracle-l
>
>
>--
>http://www.freelists.org/webpage/oracle-l
>
>

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