RE: Case insensitive searches
Date: Fri, 21 Mar 2014 15:29:13 -0400
Message-ID: <09b701cf453b$d80dbe00$88293a00$_at_rsiz.com>
IF this is your problem, a reasonable workaround (typos included for amusement) is to create a view of just your physical columns, and do any dance you have to regarding table, view, and synonyms so existing programs function correctly:
SQL> create view vcp as (select col1, col2 from vc);
View created.
SQL> insert intp vcp values (10,20);
insert intp vcp values (10,20)
*
ERROR at line 1:
ORA-00925: missing INTO keyword
SQL> c/intp/into
1* insert into vcp values (10,20)
SQL> r
1* insert into vcp values (10,20)
1 row created.
SQL> commit;
Commit complete.
SQL> select * from vc;
COL1 COL2 COL3
- ---------- ----------
10 20 30
I personally think this is a bug in the way virtual columns were defined to behave, so that ordered sets of values into the physical columns would still work without this workaround. But I am also not advocating that inserts be done that way, especially considering how making columns invisible and appear again works.
HTH, mwf
-----Original Message-----
From: Ric Van Dyke [mailto:ric.van.dyke_at_hotsos.com]
Sent: Friday, March 21, 2014 3:08 PM
To: mwf_at_rsiz.com; 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
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: <mailto:oracle-l-bounce_at_freelists.org> oracle-l-bounce_at_freelists.org [ <mailto: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: <mailto:rjjanuary_at_multiservice.com> rjjanuary_at_multiservice.com; 'Martin Klier'; <mailto:sfaroult_at_roughsea.com> sfaroult_at_roughsea.com;<mailto:rajendra.pande_at_ubs.com> rajendra.pande_at_ubs.com;
<mailto:Herald.ten.Dam_at_superconsult.nl> Herald.ten.Dam_at_superconsult.nl;
<mailto:oracle_at_dunbar-it.co.uk> 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: <mailto:oracle-l-bounce_at_freelists.org> oracle-l-bounce_at_freelists.org [ <mailto: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; <mailto:sfaroult_at_roughsea.com> sfaroult_at_roughsea.com;
<mailto:Herald.ten.Dam_at_superconsult.nl> Herald.ten.Dam_at_superconsult.nl;
<mailto:rajendra.pande_at_ubs.com> rajendra.pande_at_ubs.com;
<mailto:oracle_at_dunbar-it.co.uk> 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 -- http://www.freelists.org/webpage/oracle-lReceived on Fri Mar 21 2014 - 20:29:13 CET