Case insensitive queries
JB
How can this be done globally(always) as well as per query and per
session? All three please!
Thanks!
Anna C. Dent
CAN'T.
CAN'T.
P.S. Stop cross posting!
Hans Forbrich
AFAIK, data is case sensitive. AFAIK, you can not force Oracle to set
case insensiivity using some instance/database/session poarameter.
Per query, you can force case-matching using the 'lower()' and
'uppper()' functions against both sides of the condition.
Opinion: I'm not sure that I'd want to be able to have a global- or
session- based case insensitivity. Seems to me there'd be too many
exceptions where I'd want the other way. (Seems to me the requirement
for case-insensitivity would be a generated by programmer laziness or
insufficient user training - not uncommon these days.)
Dave Hau
news:3FC13015...@yahoo.net...
This can be helpful in client apps where a user can specify in a "User
Preferences" section whether they want case sensitivity in their search or
not. Rather than coding if-then statements to concatenate upper() into
every string comparison predicate, if Oracle had this session parameter, the
app could just execute an ALTER SESSION statement at the start of the
session, based on the value of the case sensitivity setting in the User
Preferences section.
Regards,
Dave
Mark Townsend
FYI - case insensitivity, as a database/session/query parameter, is
supported in Oracle Database 10g, coming soon.
Thomas Kellerer
> JB wrote:
>
>>How can one set the Oracle database in case insensitive query mode?
>>How can this be done globally(always) as well as per query and per
>>session? All three please!
>>
>
> (Seems to me the requirement
> for case-insensitivity would be a generated by programmer laziness or
> insufficient user training - not uncommon these days.)
Let's assume a search application with searches for contacts, companies,
addresses, products,... You name it.
I think it's a valid requirement, that the user does not need to know how a
name, city, etc has been written, and I have been coding around this
shortcoming of Oracle for several years now.
It's good to hear that Oracle finally can do what the competitors could do
for years.
Just my .02€
Thomas
Hans Forbrich
>
> Hans Forbrich schrieb:
>
>
> Let's assume a search application with searches for contacts, companies,
> addresses, products,... You name it.
>
> I think it's a valid requirement, that the user does not need to know how a
> name, city, etc has been written, and I have been coding around this
> shortcoming of Oracle for several years now.
> It's good to hear that Oracle finally can do what the competitors could do
> for years.
'Twas but an opinion.
With Function-Based indexes, it is not unreasonable to code the query
against an UPPER(column) or LOWER(column) with virtually no performance
hit. Doesn't seem like a huge 'coding around' effort to me.
(Of course, that's assuming literate developers who have bothered to
read the docco and have found out about function based indexes. I'm
always amazed at the number of blanks stares I get from supposedly
competent Oracle developers when I mention these things.) (this
generalization not directed at Thomas!)
But it's interesting that Oracle will provide the database/session/query
capability in 10g.
Next, based on history, will be the whining about the difficulty
interspersing mixed-case and case-insensitive searches. <g>
Daniel Morgan
You can't with any version currently on the market.
The solution is to write your queries correctly and read the literature
before posting. I recommend http://tahiti.oracle.com.
And please do NOT cross-post. All you do is get people angry.
--
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damo...@x.washington.edu
(replace 'x' with a 'u' to reply)
Mark Townsend
> I think it's a valid requirement, that the user does not need to know
> how a name, city, etc has been written, and I have been coding around
> this shortcoming of Oracle for several years now.
> It's good to hear that Oracle finally can do what the competitors could
> do for years.
>
Well, this feature is provided primarily for migration purposes
(i.e for those customers/partners that want to migrate to Oracle from
SQLServer).
I'm sure that Oracle would still argue that the 'correct' default
behaviour is case sensitivity.
Daniel Morgan
I know I would. In fact I would argue that the case sensitivity should
be a switch so that it can be turned off in the interest of data integrity.
Other database products can do lots of things Oracle can't do. And it is
for that reason that many of us don't recommend them in many situations.
Keep up the good work but please please please do something about the
quality of support. Not just TAR and iTAR but also metalink and the web
posted documents. It is becoming more and more of an issue.
JB
>
> CAN'T.
> CAN'T.
> CAN'T.
>
> P.S. Stop cross posting!
>
Hmm.. I was under the impression that to maximize the chance of getting
the right answer you have to cross post to every possible NG on the planet.
Otherwise thanks for the completely useless follow-up.
Thanks for the rest of you who posted info about this case-insensitive
search capabilities in 10G without all the idealogical drivel.
Daniel Morgan
If you wish to alienate yourself from any help here just keep it up.
Most people don't know much about 10g because they are not Beta testers.
But everyone knows you shouldn't crosspost and everyone knows you should
say thank you and not insult people you may need to rely on later.
Rene Nyffenegger
> FYI - case insensitivity, as a database/session/query parameter, is
> supported in Oracle Database 10g, coming soon.
This is interesting, because I was asked that very question less than
three days ago and didn't know that it's going to be possible
with 10g. Does someone already know more details about it, such
as parameter name and the like. I'd think it'll be something like
alter session set sql_case_sensitivity=false
or so.
Thanks,
Rene
--
Rene Nyffenegger
http://www.adp-gmbh.ch
Daniel Morgan
Yes. But unlike Mark I'm not sure where my NDA starts and stops so
in the interest of not bashing my university on the head I don't
feel comfortable commenting. Perhaps Mark or someone less averse to
risk taking will weigh in.
Mark Townsend
New NLS_SORT and NLS_COMP parameters. Note that accent and case
insensitivity are supported.
ALTER SESSION SET NLS_SORT=generic_m_ai;
ALTER SESSION SET NLS_COMP=ansi;
SELECT cust_last_name
FROM customers
WHERE cust_last_name = 'De Niro';
CUST_LAST_NAME
-----------------------
de Niro
De Niro
dë nirõ
Rene Nyffenegger
Thanks a lot, Mark
Rene
Ed prochak
And I've programmed data cleanup that had to deal with source data
that was case insensitive. Addresses can be especially troubling.
Dupont and DuPont are NOT the same street in some locations. The
search issue is bigger than case sensitivity, just consider puncuation
like names with contractions. Removing case sensitivity doesn't help.
So it's not a limition of the ORACLE DB.
It's a limitation of some other databases that cannot deal with the
true nature of the data. (When all your data is in CAPS, there is no
case issue.) And sometimes a limit of application developers to deal
with it properly also.
That's my $.02!
Ed
JB
> New NLS_SORT and NLS_COMP parameters. Note that accent and case
> insensitivity are supported.
>
> ALTER SESSION SET NLS_SORT=generic_m_ai;
> ALTER SESSION SET NLS_COMP=ansi;
>
> SELECT cust_last_name
> FROM customers
> WHERE cust_last_name = 'De Niro';
>
> CUST_LAST_NAME
> -----------------------
> de Niro
> De Niro
> dë nirõ
>
Now that wasn't that hard. Thanks Mark. Note to the rest of you sexually
repressed, crusty DBA's ... Instead of bashing users for asking a
question and slipping in subjective ideological drivel, just answer the
bloody question, perhaps with some humor.
As they say: just the facts maam, just the facts!
Thanks again Mark!
Hans Forbrich
>
>
> Now that wasn't that hard. Thanks Mark. Note to the rest of you sexually
> repressed, crusty DBA's ... Instead of bashing users for asking a
> question and slipping in subjective ideological drivel, just answer the
> bloody question, perhaps with some humor.
>
> As they say: just the facts maam, just the facts!
>
> Thanks again Mark!
As long as you are aware that it ain't released yet - go for it!
Daniel Morgan
Catch a clue Mark. 10g is Beta and under NDA: NDA as in NON DISCLOSURE
AGREEMENT. Mark Townsend is a product manager and can do whatever he
wishes. We, on the other hand, can't say anything about it without
Oracle's permission.
For us to answer wouldn't be hard it would be easy ... it would also
subject us to legal action from all of Larry's attorneys.
I think an apology is in order.
Mark Townsend
> repressed, crusty DBA's ...
Well, to be fair to the crusty's, it's an as yet un-released product
subject to non disclosure agreements. So it's probably not a matter of
wouldn't answer, but couldn't.
As an aside, is there any relationship between degree of crustiness and
length of sexual repression ? Sort of a "DBA's inverse law of
gettingness vs average MTTR" ? Does it differ by database vendor ?
John Russell
prochak) wrote:
In a case like that, I'd use Oracle Text's CONTAINS() operator.
...WHERE CONTAINS(last_name, 'de la hoya') would match 'De La Hoya'
etc. You could also set it up so that punctuation characters (e.g.
dashes) are treated like a space, a significant character, or just
ignored so the words are run together into one. That's settable
independently for each punctuation character.
John
--
Photo gallery: http://www.pbase.com/john_russell/
Daniel Morgan
Well if you want to talk about the DB2 crowd I think so. ;-)
Galen Boyer
> (Seems to me the requirement for case-insensitivity would be a
> generated by programmer laziness or insufficient user training
> - not uncommon these days.)
Why is it that when Oracle has an obvious limitation, people
respond like this? The web is pretty much a case-insensitive
world. Oracle crows about being all "webified" but it doesn't
offer this simple feature. Sure, the data is case-sensitive, but
it would be damn nice for the database to allow the developer the
ability to turn it off or on based on query needs.
--
Galen Boyer
Daniel Morgan
I disagree completely. When 10g is released I will advise my students to
never use the option for case insensitive queries.
A properly designed application should force valid case for inserts and
updates. Covering up for a bad design or bad implementation is not a
good policy. It is only one step removed from making all searches using
the SOUNDEX function or making all columns VARCHAR2(4000).
So is it a limitation? Absolutely. Is it one I am glad Oracle has
remedied? No! Efforts put elsewhere would have been far more valuable.
mcstock
"Daniel Morgan" <damo...@x.washington.edu> wrote in message
news:1070045315.606163@yasure...
daniel
there are probably more than one or two steps between case-insensitive
queries and 'soundex(4000)' ;-)
the business requirements that i've had to fulfill have not been due to lazy
untrained personnel -- recently the good people at the state agency that i
developed a system for were able to get more work done with less effort,
while gradually being able to clean up legacy data, by having for the first
time case-insensitive queries. (reaction 'wow! you can do that? that'll let
us do thus and so and etc.etc.)
it would be nice if all data could be squeaky clean at all times, but in
developing systems used by real people, there is always a cost/benefit
trade-off between more extensive business rules and a reasonable
implementation of a usable system
so, systems will continue to include requirements for case-insensitive
queries (written by hard-working, well-trained analysts) and we will need to
implement them either programmatically or through database features -- the
point is to make it easier (i.e., more efficient and less frustrating) for
our customers to get their work done while improving the quality of the data
-- mark stock
Daniel Morgan
I agree with you with one small exception. In your case you are using it
as a step toward clean data. Something you could have also done with
WHERE UPPER(x) = UPPER(y).
But please appreciate in the hands of many, if not most developers, it
is a license to be sloppy. And they will use that license and then
complain that implicit data-type conversions should be standard too.
Hans Forbrich
Am I missing something obvious here?
Is there something here that can't be accomplished by creating a view on
the underlying case-sensitive table(s), having a 'to_upper()' for the
columns in question in the view, and encouraging the app developer to do
an implicit 'to_upper()' on the request?
Even for update situations, an inline view based on the above view
should manage to handle most situations, shouldn't it?
If I am missing something, I am perfectly willing to retract my
opinion. The opinion is based on many developers I have met not being
able or willing to learn the underlying capability of the RDBMS (not
just Oracle), and therefore doing things very stupidly - eg: creating a
PL/SQL or T-SQL loop when a simple, properly projected, select would do
the trick.
mcstock
not just a step toward cleaning up data -- there are still times when there
are legitimate reasons why the data of interest can be stored in different
case (i.e., case neither adds not detracts value from the data)
re: WHERE UPPER(x) = UPPER(y) -- make sure to add a function based index
when setting the Case Insensitive Query item property in Forms, it generates
something like:
(colname = 'the search value as typed in'
or colname like 'Th%'
or colname like 'TH%'
or colname like 'th%'
or colname like 'tH%'
)
(going from memory on syntax) which will allow the optimizer to use a normal
index
yeah, functionality gets abused -- just like cheap memory has gotten abused
by commercial software developers -- but don't take away my kodachrome just
'cause someone else might misuse the functionality!
btw: is there a missing grin after the 'implicit data-type conversion'
comment?
-- mcs
Daniel Morgan
>
> btw: is there a missing grin after the 'implicit data-type conversion'
> comment?
>
> -- mcs
Not missing by accident. I fully expect Oracle has received voluminous
complaints from people that want to store everything as VARCHARs.
Phil Kaufman
of whinny "competitor" database users cry the blues about having the
ability to have case insensitive data in their wonderful database (term
used loosely for those competitor products), and the likes of Mark and
the gang at Oracle have given in, rather then telling these people what
they are (idiots).
Just one thing though, the moment Oracle goes to case sensitive system
dictionary, I'm out of this field!
JB wrote:
> How can one set the Oracle database in case insensitive query mode?
> How can this be done globally(always) as well as per query and per
> session? All three please!
>
> Thanks!
>
Hans Forbrich
>
> Personally I think what has happened is Oracle has listened to a number
> of whinny "competitor" database users cry the blues about having the
> ability to have case insensitive data in their wonderful database (term
> used loosely for those competitor products), and the likes of Mark and
> the gang at Oracle have given in, rather then telling these people what
> they are (idiots).
Telling customers and potential customers they are idiots is generally
not conducive to sales.
Telling customers and potential customers 'me too' occasionally reduces
potential market share erosion.
Daniel Morgan
I agree. I've no complaint with Oracle implementing it. Lots of
companies implement lots of foolish things for purposes of marketing and
sales.
By problem is with people that use it. They need to clean up what they
do and achieve a higher standard in their IT development.
Serge Rielau
While functional indexes are neat in themselves they do not provide a
match for case-insensitive search. Reason being is that often
the user requires both the case insensitive (predicate) and the
sensitive (select list) part. So a clean index access quickly turns
either into a requirement for duplicate storing of the same data (index
includes both) or an index scan with subsequent data page fetch which is
quite literaly a drag...
Sometimes competitors do good stuff and one is ill-advised to ignore it.
Cheers
Serge
PS: I can claim being unbiased since DB2 (at present) also does not
support case-insensitivity.. :-|
PPS: I ain't a crusty DB2 DBA, so I don't need to feel offended by
Daniel :-)
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Galen Boyer
> Galen Boyer wrote:
>> Sure, the data is case-sensitive, but it would be damn nice
>> for the database to allow the developer the ability to turn it
>> off or on based on query needs.
>
> I disagree completely. When 10g is released I will advise my
> students to never use the option for case insensitive queries.
How then should they get a case-insensitive query to execute?
Would you want them to do it the old-fashioned way..., and how
old-fashioned should it be?
> A properly designed application should force valid case for
> inserts and updates.
I would ask you to read my posting again. I'm not asking the
database to somehow turn everything to a single case for storage
purposes or fix my very poorly designed DML strategies for me.
I'm talking about case-insensitive queries. I am forced, because
of a limitation in Oracle, to either store a single-cased version
of all queriable columns used in searches, or I must explicitly
ask Oracle to function index a particular single-cased version
and then use that function in my search queries.
I would rather just be able to tell Oracle, hey, in this query,
ignore case and, btw, don't lose performance.
> Covering up for a bad design or bad implementation is not a
> good policy.
I just wish you would stop your bully-pulpit answers.
> It is only one step removed from making all searches using the
> SOUNDEX function or making all columns VARCHAR2(4000).
>
> So is it a limitation? Absolutely. Is it one I am glad Oracle
> has remedied? No! Efforts put elsewhere would have been far
> more valuable.
What efforts are those and more valuable to whom?
--
Galen Boyer
Galen Boyer
> Galen Boyer wrote:
>>
>> On Sun, 23 Nov 2003, forb...@yahoo.net wrote:
>>
>> > (Seems to me the requirement for case-insensitivity would be
>> > a generated by programmer laziness or insufficient user
>> > training - not uncommon these days.)
>>
>> Why is it that when Oracle has an obvious limitation, people
>> respond like this? The web is pretty much a case-insensitive
>> world. Oracle crows about being all "webified" but it doesn't
>> offer this simple feature. Sure, the data is case-sensitive,
>> but it would be damn nice for the database to allow the
>> developer the ability to turn it off or on based on query
>> needs.
>
>
> Is there something here that can't be accomplished by creating
> a view on the underlying case-sensitive table(s), having a
> 'to_upper()' for the columns in question in the view, and
> encouraging the app developer to do an implicit 'to_upper()' on
> the request?
No, you aren't missing something obvious. Having to do that is a
workaround for the limitation in Oracle. You also ask all
maintenance/dba crews to add more unneeded care to their plate.
> Even for update situations, an inline view based on the above
> view should manage to handle most situations, shouldn't it?
Hm... Why should one have to ask/answer this question?
> If I am missing something, I am perfectly willing to retract my
> opinion. The opinion is based on many developers I have met
> not being able or willing to learn the underlying capability of
> the RDBMS (not just Oracle), and therefore doing things very
> stupidly - eg: creating a PL/SQL or T-SQL loop when a simple,
> properly projected, select would do the trick.
Well, then those developers need to find something else to do.
The workaround is well-known and is pretty cool from my
development point of view, but, whether I like to implement nifty
things under the covers or not doesn't get past the fact that
this is a limitation.
Why are people so damn ready to defend Oracle, even when it has
an obvious limitation? I love alot of things about Oracle, but
this is a shortcoming, no matter how it is sliced.
--
Galen Boyer
Hans Forbrich
>
> Why are people so damn ready to defend Oracle, even when it has
> an obvious limitation? I love alot of things about Oracle, but
> this is a shortcoming, no matter how it is sliced.
>
Last counter-question .... is case-insensitivity part of the SQL spec?
If so, then full-speed ahead.
If not, I'd rather see all vendors get closer to the spec rather than
have each march in it's own direction on some 'convenience' mechanisms.
(I find the deviation from spec that each vendor has to be totally
disgusting.)
I've never found this issue a short-coming. IMO, "limitations" with
acceptable(?) workarounds are not necessarily limitations, although they
might be irritations. I really don't find this an obvious limitation.
(Using your tone - Why are people so ready to whine about Oracle when
there are acceptable solutions available?)
Hans Forbrich
Serge Rielau wrote:
>
> Hmm, time for my monthly Oracle posting I s'pose.
>
> While functional indexes are neat in themselves they do not provide a
> match for case-insensitive search. Reason being is that often
> the user requires both the case insensitive (predicate) and the
> sensitive (select list) part. So a clean index access quickly turns
> either into a requirement for duplicate storing of the same data (index
> includes both) or an index scan with subsequent data page fetch which is
> quite literaly a drag...
I also see additional situations - I want case insensitivity on 'this
part' of the where clause, but not 'that part'.
I can just see someone asking for a wild-carded, case-insensitive query
on a 200M row, unindexed, unicode UTF-16 column in a warehouse, and then
being totally puzzled that it takes a while. Probably'ld end up
muttering "but it's real fast in a 20 row Access database". But I
s'pose in this day and age the trade of between storage, memory and CPU
cycles is no longer an issue.
And, who is responsible for de-sensitizing the user's input (eg: bind
var) - developer or query engine?
What about joins - what's the cost to join two or more columns that
contain mixed-case data? Does one respect, or ignore, sensitivity on
the join (remembering that in some locales, DuPont and Dupont are
different!) or is that yet another parameter?
And is there any impact due to I18N? Is there a linear
case-insensitivity across all code sets and locales? Or are we
wandering around yet-another "global, as long as it's US-centric"
situation?
The whole thing can be taken to ridiculous extremes. (Perhaps the
problem has not been thought out, and the solution for one ain't the
solution for all?)
>
> Sometimes competitors do good stuff and one is ill-advised to ignore it.
Yes, they do. Not saying this should be ignored. Just suggesting that
there are (IMO) acceptable workarounds that take this a long way back
from "Oracle is bad, let's panic" level.
Admittedly I haven't looked it square in the eye, so I wouldn't know if
the competitor has totally thunked it out. But I have seen too many
hair-brained half-implementations of stuff that look nice on the surface
and totally fall flat when serious stuff starts happening.
>
> Cheers
> Serge
>
> PS: I can claim being unbiased since DB2 (at present) also does not
> support case-insensitivity.. :-|
One wonders why?
> PPS: I ain't a crusty DB2 DBA, so I don't need to feel offended by
> Daniel :-)
>
I shouldn't really speak on his behalf, but I suspect you're welcome to
feel offended by Daniel (or me) any time you wish. Wouldn't want you
left out, just 'cause you're in the wrong camp <bg> ....
Galen Boyer
> Galen Boyer wrote:
>>
>> Why are people so damn ready to defend Oracle, even when it
>> has an obvious limitation? I love alot of things about
>> Oracle, but this is a shortcoming, no matter how it is sliced.
>>
>
> Last counter-question .... is case-insensitivity part of the
> SQL spec?
Okay, the semantics of my argument have no merit, because this
isn't a requirement by anybody but customers. But, the
development community is the customer most wanting this.
> If so, then full-speed ahead.
>
> If not, I'd rather see all vendors get closer to the spec
> rather than have each march in it's own direction on some
> 'convenience' mechanisms. (I find the deviation from spec that
> each vendor has to be totally disgusting.)
Oh well.
> I've never found this issue a short-coming. IMO, "limitations"
> with acceptable(?) workarounds are not necessarily limitations,
> although they might be irritations. I really don't find this
> an obvious limitation.
Oracle is positioning itself as the database for the web. On web
searches, I have the ability to toggle. Why make the database
developer do anything special to handle the client's wishes?
> (Using your tone - Why are people so ready to whine about
> Oracle when there are acceptable solutions available?)
To the web development world, this is almost unacceptable.
Everytime the web-developer needs to produce a new web-page with
the toggle he has to go ask a database guy if that guy can
implement that solution again for another column.
This limits the productivity of development teams trying to get
applications based on Oracle out to customers.
--
Galen Boyer
Galen Boyer
> Yes, they do. Not saying this should be ignored. Just
> suggesting that there are (IMO) acceptable workarounds that
> take this a long way back from "Oracle is bad, let's panic"
> level.
You are misreading the thread. Who is panicking or calling
Oracle bad? It has a limitation, so what? Admit it and move
on.
--
Galen Boyer
Hans Forbrich
You find it 'almost unacceptable'. You also mention the 'web-developer
vs database guy'. You also mention that you have to implement a
solution column by column. To me that suggests the issue may be
non-technical.
I find it a non-issue.
You think it's a limitation. So - it's a limitation.
Let's move on.
Daniel Morgan
> On Fri, 28 Nov 2003, damo...@x.washington.edu wrote:
>
>>Galen Boyer wrote:
>
>
>>>Sure, the data is case-sensitive, but it would be damn nice
>>>for the database to allow the developer the ability to turn it
>>>off or on based on query needs.
>>
>>I disagree completely. When 10g is released I will advise my
>>students to never use the option for case insensitive queries.
>
>
> How then should they get a case-insensitive query to execute?
> Would you want them to do it the old-fashioned way..., and how
> old-fashioned should it be?
And which part of UPPER(x) = UPPER(y) is to radical to consider?
What is it you think Oracle is doing under the covers to make case
insensitive queries possible? Give it some thought. There is no other
way to compare byte values than to alter them. A switch in the init.ora
does not change what is happening.
Hans Forbrich
>
> What is it you think Oracle is doing under the covers to make case
> insensitive queries possible? Give it some thought. There is no other
> way to compare byte values than to alter them. A switch in the init.ora
> does not change what is happening.
But if it's done at the DB level, the developers can correctly state
that the performance issue is not theirs. Thereby shedding their
responsibility in the name of doing it 'at internet speed'.
(Lousy design is never a possible source of problems. Similar to
'security? naw, no time now! we'll do it later, maybe in production.
shouldn't have an impact.')
Galen Boyer
Please tell me any of this discussion has anything to do with
lousy design? This "lousy design" mantra is something thrown out
by people to somehow let their argument have more merit. Where
is the lousy design in wanting Oracle to remove this limitation?
--
Galen Boyer
Galen Boyer
> Galen Boyer wrote:
>
>> On Fri, 28 Nov 2003, damo...@x.washington.edu wrote:
>>
>>>Galen Boyer wrote:
>>
>>>>Sure, the data is case-sensitive, but it would be damn nice
>>>>for the database to allow the developer the ability to turn
>>>>it off or on based on query needs.
>>>
>>>I disagree completely. When 10g is released I will advise my
>>>students to never use the option for case insensitive queries.
>> How then should they get a case-insensitive query to execute?
>> Would you want them to do it the old-fashioned way..., and how
>> old-fashioned should it be?
>
> And which part of UPPER(x) = UPPER(y) is to radical to
> consider?
None. But having to do this is a workaround. Simple. What
about that is hard to consider?
> What is it you think Oracle is doing under the covers to make
> case insensitive queries possible? Give it some thought. There
> is no other way to compare byte values than to alter them. A
> switch in the init.ora does not change what is happening.
There are loads of things that Oracle is doing to speed
development. This is another thing they should implement to be
the "Web database".
--
Galen Boyer
Galen Boyer
>>
>> What is it you think Oracle is doing under the covers to make
>> case insensitive queries possible? Give it some thought. There
>> is no other way to compare byte values than to alter them. A
>> switch in the init.ora does not change what is happening.
>
> But if it's done at the DB level, the developers can correctly
> state that the performance issue is not theirs. Thereby
> shedding their responsibility in the name of doing it 'at
> internet speed'.
I don't have to consider whether an index works. How come the
database gives me that? I guess Daniel Morgan has suggest to his
students since indexes arrived, to not employ them.
--
Galen Boyer
Galen Boyer
> Give it some thought.
Love the patronizing post there Daniel. All Oracle needs to do
is give the DBA a switch at the column level to allow for this
particular column to have this case insensitivity on queries.
Allow for it to be altered. This would be quite akin to
materialized views. I bet this is how 10g ends up implementing
it. Yes, the developers and dbas wouldn't be devoid of
maintenance of these objects, but the setup, creation, ongoing
insertions? All handled by the database.
I'm going to look back and see if I can find any post where you
got all uppity about materialized views when they came on the
scene. Wouldn't surprise me to find a few. I bet you got all up
in arms about select auditing. How bout VPD? Where you all
pissed off about java in the database? Lets turn the tables.
Did you get all pissed off at locally managed tablespaces?
--
Galen Boyer
Daniel Morgan
Its a trap go back go back. ;-)
By which I mean your question is open-ended so no matter what anyone
gives you as an example your response will be ... but I don't want to
use it that way. So it would be preferable if you gave an example of
somewhere that you think you need it and let us try to counter it. The
one thing I will say is that in 14+ years working with Oracle I have
never required a case insensitive string comparison within an
application except when dealing with data from upstream systems that had
not been cleaned up on import.
Daniel Morgan
>>And which part of UPPER(x) = UPPER(y) is to radical to
>>consider?
>
>
> None. But having to do this is a workaround. Simple. What
> about that is hard to consider?
But a switch in the init.ora that does the same thing isn't? The code
above will run on any version of Oracle going back to the stone age. The
code you write dependent on the switch will work only if on 10g.
>>What is it you think Oracle is doing under the covers to make
>>case insensitive queries possible? Give it some thought. There
>>is no other way to compare byte values than to alter them. A
>>switch in the init.ora does not change what is happening.
>
>
> There are loads of things that Oracle is doing to speed
> development. This is another thing they should implement to be
> the "Web database".
I fail to see what the web has to do with it. Please explain the issue.
Daniel Morgan
Indexes provide real value not achievable by any other means.
That is not in this case. We've been doing case insensitive queries in
Oracle since the 80s.
Daniel Morgan
> On Fri, 28 Nov 2003, damo...@x.washington.edu wrote:
>
>
>>Give it some thought.
>
>
> Love the patronizing post there Daniel.
Didn't mean to be.
All Oracle needs to do
> is give the DBA a switch at the column level to allow for this
> particular column to have this case insensitivity on queries.
> Allow for it to be altered. This would be quite akin to
> materialized views. I bet this is how 10g ends up implementing
> it.
I have 10g and I would bet you are incorrect.
Yes, the developers and dbas wouldn't be devoid of
> maintenance of these objects, but the setup, creation, ongoing
> insertions? All handled by the database.
>
> I'm going to look back and see if I can find any post where you
> got all uppity about materialized views when they came on the
> scene.
You'll fail. They, too, provide real value.
Wouldn't surprise me to find a few. I bet you got all up
> in arms about select auditing. How bout VPD? Where you all
> pissed off about java in the database? Lets turn the tables.
> Did you get all pissed off at locally managed tablespaces?
Only Java and only because it is slow compared to PL/SQL. I would have
prefered a built-in package to make identical functionality available
without the additional language. But none of them are, IMO, a kludge for
a data integrity related design problem. Simply stated if two things are
going to be compared they should be identical.
This isn't a big emotional issue with me and given the number of posts
to this thread I think this has gotten out of hand so if you wish to
respond to this please choose just one or lets communicate off-line.
Hans Forbrich
My comments are directly from experience and reflect my frustration with
developers in general who haven't bothered to learn how to develop
anything past rapid prototyping.
Don't take it personally. Unless the shoe fits.
Galen Boyer
As I suspected, you don't have an answer for using this as your
argument.
--
Galen Boyer
Hans Forbrich
Said it before - I don't see this as a limitation & thus I don't 'need
it removed'. You do.
In terms of lousy design, I've already indicated that a view should be
sufficient to manage the case difference. If you & your organization
needs a different view for each possible 'upper case' search, then -
based on apparent similarity with other projects I've seen - I'm not
convinced there is a design behind this as much as a growth.
Galen Boyer
> Galen Boyer wrote:
>
>> On Sat, 29 Nov 2003, forb...@tyahoo.net wrote:
>>
>>>Daniel Morgan wrote:
>>>
>>>>What is it you think Oracle is doing under the covers to make
>>>>case insensitive queries possible? Give it some
>>>>thought. There is no other way to compare byte values than to
>>>>alter them. A switch in the init.ora does not change what is
>>>>happening.
>>>
>>>But if it's done at the DB level, the developers can correctly
>>>state that the performance issue is not theirs. Thereby
>>>shedding their responsibility in the name of doing it 'at
>>>internet speed'.
>>>
>>>(Lousy design is never a possible source of problems. Similar
>>>to 'security? naw, no time now! we'll do it later, maybe in
>>>production. shouldn't have an impact.')
>> Please tell me any of this discussion has anything to do with
>> lousy design? This "lousy design" mantra is something thrown
>> out by people to somehow let their argument have more merit.
>> Where is the lousy design in wanting Oracle to remove this
>> limitation?
>>
>
> Its a trap go back go back. ;-)
>
> By which I mean your question is open-ended so no matter what
> anyone gives you as an example your response will be ...
I see. You can't answer my question. In other threads you yell
and scream about textbook designs, but you just gave a "textbook"
answer. You have seen something or read about something and you
apply that blindly to the next thing. You had nothing to back up
your claim of "bad design" but it was one of the strongest
statements in your answers.
> but I don't want to use it that way. So it would be preferable
> if you gave an example of somewhere that you think you need it
> and let us try to counter it.
(Wish you would have answered my earlier question to you)
A java developer came to me and said, no matter what the client
sends in, I need to use it in a case-insensitive query.
I said, I will need to create either a function based index or a hidden
single case version of that column. Would that work for you?
We are going to have lots of these and our application works
against multiple databases. Does Oracle have the ability to
toggle case-sensitivity before a query is executed?
Well, this is how I can solve it for you.
Yeah, I know, but this means I need to execute two different
queries based on the desires of the client.
> The one thing I will say is that in 14+ years working with
> Oracle I have never required a case insensitive string
> comparison within an application except when dealing with data
> from upstream systems that had not been cleaned up on import.
Have you built an application in the last, say 5 years? Look at
Oracle on Google. How do you think "daniEl MoRgan" brings back
7420 hits.
--
Galen Boyer
Galen Boyer
>>>And which part of UPPER(x) = UPPER(y) is to radical to
>>>consider?
>> None. But having to do this is a workaround. Simple. What
>> about that is hard to consider?
>
> But a switch in the init.ora that does the same thing isn't?
> The code above will run on any version of Oracle going back to
> the stone age. The code you write dependent on the switch will
> work only if on 10g.
But upper(x) = upper(y) only works when you want
case-insensitive. What if you now need case-sensitive? That
query won't work. So, it is two queries. With Oracle's help, we
could have just one query to solve both questions.
--
Galen Boyer
Galen Boyer
> This isn't a big emotional issue with me and
I don't quite care about it all that much either. But, when I
get the Daniel Morgan bully pulpit answer, I respond in kind.
When you stop with your knee-jerk, "You don't know what you are
doing, I am a professor, the ramifications of what you ask
are... and the best being, You have bad design..." I'll stop
challenging you to defend those all-encompassing statements.
> given the number of posts to this thread I think this has
> gotten out of hand so if you wish to respond to this please
> choose just one or lets communicate off-line.
I see. You are also the usenet professor. Can I have a
hall-pass?
--
Galen Boyer
Daniel Morgan
>
> Have you built an application in the last, say 5 years? Look at
> Oracle on Google. How do you think "daniEl MoRgan" brings back
> 7420 hits.
>
Have I built any apps in the last 5 years? Surely this is a joke. I
spend 90% of my time buiding applications and have been involved in the
design, development, and coding of four separate apps so far this year.
As well as the migration of one app from SQL Server to Oracle for a
local phone company. And I don't mean peripherally. I mean I have coded
not less than 75% of the entire application including defining data
types, tables, constraints, indexes, packages, and the front-end. And no
they are not simple apps with one or two tables.
Of what relevance is the fact that google searches are case irrelevant?
Do you really think a the google search engine isn't doing UPPER(x) =
UPPER(y)? If you do you'd better look for a local college or university
and apply for a CS degree.
It appears that you have gotten yourself emotionally involved in this
... and as I've stated I consider it a minor issue ... so I am
unilaterally terminating any further responses to this thread. If you
are looking for someone or something to get emotional about it please
find another target.
Galen Boyer
> I am unilaterally terminating
You author with such self-importance.
--
Galen Boyer
Peter J. Holzer
> On Sat, 29 Nov 2003, damo...@x.washington.edu wrote:
>> Galen Boyer wrote:
>>>>And which part of UPPER(x) = UPPER(y) is to radical to
>>>>consider?
>>> None. But having to do this is a workaround. Simple. What
>>> about that is hard to consider?
>>
>> But a switch in the init.ora that does the same thing isn't?
>> The code above will run on any version of Oracle going back to
>> the stone age. The code you write dependent on the switch will
>> work only if on 10g.
>
> But upper(x) = upper(y) only works when you want
> case-insensitive. What if you now need case-sensitive? That
> query won't work. So, it is two queries.
Yes, of course. They are two different questions, so I write them as two
different queries.
What are you going to do with an init.ora parameter? Bounce the database
each time someone wants to switch from case-sensitive to
case-insensitive queries and back? What if you need both at the same
time (e.g., case-sensitive comparison on message-ids and
case-insensitive comparison on domain names in the same query?).
Now a function for doing case-insensitive queries (or - more generally -
locale-specific comparisons) or maybe even a session parameter is a good
thing. It makes explicit what you are doing and saves you some coding
and design decisions. I don't see how they could work any better than
function-based indexes do now.
hp
--
_ | Peter J. Holzer | In this vale
|_|_) | Sysadmin WSR | Of toil and sin
| | | h...@hjp.at | Your head grows bald
__/ | http://www.hjp.at/ | But not your chin. -- Burma Shave
Peter J. Holzer
> To the web development world, this is almost unacceptable.
> Everytime the web-developer needs to produce a new web-page with
> the toggle he has to go ask a database guy if that guy can
> implement that solution again for another column.
What does he need the database guy for?
Peter J. Holzer
> Now a function for doing case-insensitive queries (or - more generally -
> locale-specific comparisons) or maybe even a session parameter is a good
> thing.
Or an attribute on the column, to state the obvious myself before anyone
else does.