Thanks Jose, You've answered my questions pretty well.
Rod On 8 February 2012 20:51, Jose Huerta <[email protected]> wrote: > ** 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/#%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 Wed, Feb 8, 2012 at 13:30, Rod Harris <[email protected]> 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 >> >> _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are"
