Hey Joe, It is my understanding that MS-SQL outperforms any oracle implementation on any OS when it comes to case-insensitive searches (provided the hardware was similar). Oracle does not support it as an option you turn on with 10g or earlier (10GR2 is where you can build indexes that use full text search). Before R2 you have to rebuild all of your searches to use “UPPER(FIELD1) = UPPER(FIELD2)” which involves a full table scan because “UPPER” is a function.
I found this from a remedy message board: “It should be noted that this is NOT a BMC/Remedy issue. In Oracle 10g it is documented that you can set “case insensitivity” by setting the session parameters: NLS_SORT=BINARY_CI and NLS_COMP=LINGUISTIC What we've found in various Oracle forums is that setting these renders all of your existing indexes useless to the DB until you recreate them using the syntax: create [unique] index index_name on table_name (NLSSORT(column_name, 'NLS_SORT=BINARY_CI')); Here’s an example of how this even affects the unique index on C1. Notice that without setting “case insensitivity” the DB is able to do an INDEX UNIQUE SCAN returning the row very quickly with an overall cost of only 2:” It should be noted that Remedy does not build indexes this way out of the box. You would have to configure ardb.conf to build them this way (and I’m not sure if it could do it). Which would be a major pain. Again … this could be accomplished via a different means in Remedy by using Full Text Search. Your crunching is not done at the DB level but at the app level. Not every field needs to be case insensitive. You gain performance at the db level because of the case-sensitive search and you gain the functionality of case-insensitive search via Full Text Search. – this would be realized if you went the Oracle route. All I am saying is that there are other considerations when choosing your Hardware/OS. Thanks, Sean From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Joe Martin D'Souza Sent: Wednesday, October 05, 2011 3:49 PM To: arslist@ARSLIST.ORG Subject: Re: BMC Performance Matrix on ARS 7.6.04 on Different OS ** I heard the same thing about Oracle case insensitivity too.. But what I would like to know is compared to what? Are we comparing an Oracle case sensitive database against another Oracle case insensitive database? If so yes its obvious there would be an expected performance hit.. Or is that comparison between a MS-SQL case insensitive database vs an Oracle on Unix case insensitive database of the same size and same type of data? Or is that comparison between a MS-SQL case insensitive database vs an Oracle on Windows case insensitive database of the same size and same type of data? The second and third comparison would be more the kind one would ideally want to look at if you want to really make a selection between two databases/platforms offering the same feature.. Joe From: Garrison, Sean (Norcross)<mailto:sean.garri...@fiserv.com> Sent: Wednesday, October 05, 2011 3:36 PM Newsgroups: public.remedy.arsystem.general To: arslist@ARSLIST.ORG<mailto:arslist@ARSLIST.ORG> Subject: Re: BMC Performance Matrix on ARS 7.6.04 on Different OS ** Hi Bhupesh, One thing that no one has mentioned on this thread is the consideration of case sensitivity. If you want case-insensitive searches then MS-SQL would be a better bet. However db performance would be reduced compared to Oracle. Since it is MS-SQL your choice of OS becomes more clear … Oracle Provides Case-Insensitive searching but it is my understanding that the performance of the DB is reduced dramatically when it is turned on (I’m sure someone on the list may disagree). Another option is to use Oracle and utilize “Full Text Search” cabability within Remedy. This requires some customization in that you must choose the fields that belong to the “Full Text Search” capability but also requires more resources for your app server. The advantage of this approach is that you get the faster Oracle performance (case-sensitive) with the advantages of case-insensitive searches. In addition I would recommend separating your app server from your DB server if you haven’t decided that already. 7.6 has new search capabilities which may make this an irrelevant discussion. I have been burned before because I did not bring up the case insensitive issue. Just speaking from experience. Thanks, Sean From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Bhupesh Gupta Sent: Wednesday, October 05, 2011 5:19 AM To: arslist@ARSLIST.ORG Subject: BMC Performance Matrix on ARS 7.6.04 on Different OS ** Hi - Does anybody has any input or any matrix on Performace og BMC ARS on different OS, so that We can take a decision that Which OS is more suitable. I have option of both Windows 2008 or Unix , but on Which OS BMC Product's performace is better. Please advise -- Regards, Bhupesh Gupta _attend WWRUG11 www.wwrug.com ARSlist: "Where the Answers Are"_ _attend WWRUG11 www.wwrug.com ARSlist: "Where the Answers Are"_ _attend WWRUG11 www.wwrug.com ARSlist: "Where the Answers Are"_ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: "Where the Answers Are"
