Indexes in remedy are mainly text indexes. Normally other applications index keys (primary and foreign) and search fields. Since in Remedy keys are text based, almost all indexes are text based. With my script I duplicate only text based indexes (no date or number). But this means almost all indexes.
I recommend to not delete case sensitive indexes by three reasons: 1.- Indexes are maintained by ARS. I always try to not touch the ARS managed objects, since it is unsupported. 2.- I maintain an easy rollback by turning off the login trigger. It is unsupported, so it can come the day when something goes wrong and it is beacuse of the case insensitive mode. 3.- Some apps (like SQL Developer, BI, or others) can connect to the database and force a sort-where mode. If they force the case sensitive mode, all case insensitive indexes will be useless. Since we have a lot of indexes in Remedy and almost all are duplicated this means an increase in database size. Non indexed queries run slower. I think that this is because the system must apply the conversion function to all registers at the table to perform the comparison. And this is an extra load. The fact is that if a search cost 10 seconds, with case insensitive costs 12 seconds. But this applies only to non-indexed. Our DBA scans non-indexes queries to look for a pattern and propose new indexes. Finally, the oracle index corruption statement: I don't know if index are corrupted or not. But we experience a problem: After some months, Oracle don't use case insensitive indexes and starts performing full text scans. Don't know why. The recommendation from one DBA was to drop and create all indexes. He talked about "index corruption". I don't know if indexes where corrupted or not, but dropping and creating them again solved the problem. So our DBA has a programmed maintenance task of dropping and creating all indexes each two months. Regards, Jose M. Huerta Project Manager** Movil: 661 665 088 Telf.: 971 75 03 24**** Fax: 971 75 07 94**** <http://www.sm2baleares.es/>**** SM2 Baleares S.A. C/Rita Levi **** Edificio SM2 Parc Bit**** 07121 Palma de Mallorca**** <http://es-es.facebook.com/pages/SM2-Baleares/158608627954> <http://twitter.com/#!/SM2Baleares> <http://www.linkedin.com/company/sm2-baleares> La información contenida en este mensaje de correo electrónico es confidencial. La misma, es enviada con la intención de que únicamente sea leída por la persona(s) a la(s) que va dirigida. El acceso a este mensaje por otras personas no está autorizado, por lo que en tal caso, le rogamos que nos lo comunique por la misma vía, se abstenga de realizar copias del mensaje o remitirlo o entregarlo a otra persona y proceda a borrarlo de inmediato.**** P Por favor, no imprima este mensaje ni sus documentos adjuntos si no es necesario. On Wed, Feb 8, 2012 at 13:30, Rod Harris <r...@smapps.com.au> wrote: > ** Hi Jose, > > You are right in saying that Remedy defaults to case sensitive indexes for > Oracle and to avoid performance issues when case insensitive you must > maintain case insensitive indexes outside of Dev Studio/Remedy. > > Likewise I agree that performance on indexed queries will be > indistinguishable between the two sensitive/insensitive if the index is > used. > > I'm interested in the reasons why you're quoting a large increase in > database size and decrease in performance on non indexed queries. Is it > because in your technique you are recommending duplicating indexes, one for > each mode? > > I'm also interested in your statement that the insensitive indexes become > corrupt over time. > > For Remedy it only does inserts/deletes one database row at a time so it > is difficult to see any performance differences in these operations so it > should rarely be a consideration. > > Rod Harris > > > > On 8 February 2012 19:34, Jose Huerta <jose.hue...@sm2baleares.es> wrote: > >> ** In this post I cover how to turn the oracle database to case >> insensitive recreating all indexes. >> >> http://theremedyforit.com/2011/12/case-insensitive-in-remedy/ >> >> The result in our case, in performance term is: >> >> - Searches involving an indexed field -> Same performance as case >> sensitive. >> - Searches involving a non indexed field -> Performance degraded 10 % - >> 40 %. >> - Database size increase by 10 % - 20 % >> - Insert performance degraded theoretically (we are sure that it is >> degraded, but the response time of our Oracle server remains equal). >> >> Regards, >> >> Jose M. Huerta >> Project Manager** >> >> Movil: 661 665 088 >> >> Telf.: 971 75 03 24**** >> >> Fax: 971 75 07 94**** >> >> <http://www.sm2baleares.es/>**** >> >> SM2 Baleares S.A. >> C/Rita Levi **** >> >> Edificio SM2 Parc Bit**** >> >> 07121 Palma de Mallorca**** >> >> <http://es-es.facebook.com/pages/SM2-Baleares/158608627954> >> <http://twitter.com/#%21/SM2Baleares> >> <http://www.linkedin.com/company/sm2-baleares> >> >> La información contenida en este mensaje de correo electrónico es >> confidencial. La misma, es enviada con la intención de que únicamente sea >> leída por la persona(s) a la(s) que va dirigida. El acceso a este mensaje >> por otras personas no está autorizado, por lo que en tal caso, le rogamos >> que nos lo comunique por la misma vía, se abstenga de realizar copias del >> mensaje o remitirlo o entregarlo a otra persona y proceda a borrarlo de >> inmediato.**** >> >> P Por favor, no imprima este mensaje ni sus documentos adjuntos si no es >> necesario. >> >> >> >> On Tue, Feb 7, 2012 at 17:09, patchsk <vamsi...@gmail.com> wrote: >> >>> ** >>> Yes checking with the BMC account manager is the preferred method. >>> Thanks Nathan for correcting it. >>> _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ >>> >> >> _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ >> > > _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ > _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are"
<<image004.jpg>>
<<image001.jpg>>
<<image002.jpg>>
<<image003.jpg>>
