[Date Prev] [Date Next] [Prev in Thread] [Next in Thread] [Date Index] [Thread Index]

Re: SV: Oracle case-sensitivity



**

Jarl,

I must admit, my statement regarding function based indexes, comes from the
fact that we do many SQL queries in active links to replicate menus, and for
those you can use a function based index, because the database will
recognize it.  Also, as a mention of warning, using Upper(fieldValue) =
Upper(databaseValue) will cause a table scan.  An important issue if it is a
large table.

I apologize for failing to point out that in order to use function based
indexes require actions outside of the remedy environment.

Brian Goralczyk
Verizon Wireless

-----Original Message-----
From: Jarl Grøneng [mailto:jarl.groneng@TELENOR.COM]
Sent: Friday, October 04, 2002 11:17 AM
To: ARSLIST@LISTSERV.QMXS.COM
Subject: [ARSLIST] SV: Oracle case-sensitivity


**

ARS does not support function based indexes... At least not in v452, and
cant
find anything in the 5.x documentation.

We'r using fumction based query from RAS to an external custoemr database.
..
Jarl

>===== Original Message From arslist@ARSLIST.ORG =====
>**
>
>This has to be one of the most discussed items when it comes to Oracle
>databases.  you would think that after all these years, they would put a
>switch in the system.  Anyways, I have thought about this issue, and there
>are two ways to address this that I can think of.
>
>1. create hidden fields for each field you need to be insensitive where you
>put the value in a upper or lower state.
>
>2. create function indexes in Oracle.  These are a bit more difficult to
>do(not much) and you must have cost based rules set, and update your
>statistics somewhat regularly.
>
>I believe 1 is easier to set up, but harder to use.  While 2 is harder to
>set up but easier to use.  The choice, and challenge, is yours.
>
>Brian Goralczyk
>Verizon Wireless
>
>
>-----Original Message-----
>From: David Yearsley [mailto:DYEARSLEY@UTAH.GOV]
>Sent: Friday, October 04, 2002 9:31 AM
>To: ARSLIST@LISTSERV.QMXS.COM
>Subject: Re: [ARSLIST] Oracle case-sensitivity
>
>
>**
>
>I agree, but if you don't want to do this at the database level you can
>create and escalation to run once or nightly to make sure all data is
>the same format. Either all upper or lower is the best choice, but if
>you want to be adventurous you can also do proper case but you will have
>to program that.
>
>David Yearsley
>
>>> vholipa@NGCO.COM 10/03/02 10:30PM >>>
>**
>
>Hi guys, I really need some advice from anyone about Oracle's case-
>sensitive feature.
>
>We just migrated from ARS 4.5 and SQL Server on NT to 5.0.1/NT and
>Oracle/AIX. Below is our case scenario.
>
>We have tickets assigned to one group in the following different
>formats:
>techserv = 354 tickets, TECHSERV = 49, Techserv = 28.
>
>In SQL Server, when we search on 'Group+' = "techserv", we get all 431
>tickets for all occurences of "techserv" regardless of what case it is
>saved in the database.
>
>However, in Oracle, we get only the tickets for "techserv" which is
>354
>because of the case-sensitive nature of Oracle.
>
>How can we get the total number without having to resort to several OR
>conditions in the search criteria? We can use an active link to
>activate
>on search to look for UPPER('Group+') = "TECHSERV" but that would only
>be
>for searches on Group+. How can we get the same effect for whatever
>field
>or combination of fields the user wants to search on?
>
>Better yet, is there a way to instruct Oracle to turn off
>case-senitivity?
>
>___________________________________________________________________________
_
>_____________
>You may control your subscription options, including UNSUBSCRIBE,and
>ACCESS THE ARCHIVES at http://www.ARSLIST.org
>
>___________________________________________________________________________
_
>_____________
>You may control your subscription options, including UNSUBSCRIBE,and ACCESS
>THE ARCHIVES at http://www.ARSLIST.org
>
>___________________________________________________________________________
__
____________
>You may control your subscription options, including UNSUBSCRIBE,and ACCESS
THE ARCHIVES at http://www.ARSLIST.org

____________________________________________________________________________
_____________
You may control your subscription options, including UNSUBSCRIBE,and ACCESS
THE ARCHIVES at http://www.ARSLIST.org

_________________________________________________________________________________________
You may control your subscription options, including UNSUBSCRIBE,and ACCESS THE ARCHIVES at http://www.ARSLIST.org