Axton, you just gave me a great idea for a post! Frederick, I know that the index rebuild topic is a black nebula where it is easy to get lost. This is the reason because I didn't speak as a fact but I cited my DBA. I do not share this. But the fact is that dropping and building them again, solved the issue.
Also, the UPPER function is not a solution. For english speakers may be it is, but for the rest of the world not. It is not only about comparing, it's also about sorting. Some examples for Spanish. Angel and ángel must be equal The correct sort order for the next words is the next: Ala Ángel avión nada Ñora ñu OPERA Oracle has one function to solve it: NLSSORT() that allows to select an specific sorting. Also I prefer to drop and create because it also updates the list of indexes regarding the ARS info. I some ARS index has been deleted, it won't be created after dropping. Also If some index is changed or some index is created, the new version will be created according to them. The alter index sentence will be very complicated and I think that it doesn't worth the effort. 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 17:56, Axton <[email protected]> wrote: > ** Remedy has a long standing history of using on SQL (DML and DDL) that > works for all supported databases. They have avoided implementing > functions that are database specific. The rationale behind this I imagine > is along the lines of one of these: > - one code line for dynamic sql generation for all databases (cheaper to > maintain) > - less complexity (cheaper to support) > - does not give one database platform a competitive advantage over another > (easier to market) > - reduces database interoperability issues for end consumers (does not > matter what version of database platform the customer uses) > > The downside to this position is that Remedy performs sub-optimally on all > databases. Is this the best position to take? Probably depends on who you > ask. BMC marketing, BMC support, BMC R&D, end consumers, users of Remedy > at end consumer points, etc. will all have different answers. The question > that will drive the decision, at the end of the day, is, "Which position is > most profitable?" Asking that question, the status quo is probably the > most logical choice. > > Axton Grams > > > On Wed, Feb 8, 2012 at 9:53 AM, Grooms, Frederick W < > [email protected]> wrote: > >> The whole delete and recreate indexes is one of those issues like "Which >> type of computer is better?". Each person you ask will have a different >> opinion. Here is a great link on the issue: >> >> http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6601312252730 >> >> On the index "corruption" issue. Are you using a DICTIONARY managed >> tablespace and are you regularly analyzing and gathering statistics? As >> long as statistics are gathered regularly then the database should have no >> problems using the correct index. >> >> If you do wish to rebuild an index instead of deleting them you could >> just alter them to recreate >> SELECT 'ALTER INDEX ' || INDEX_NAME || ' REBUILD;' FROM USER_INDEXES >> WHERE INDEX_NAME LIKE 'AI%'; >> >> One idea for BMC to eliminate the issue is if they provide a flag to mark >> a field in an index as case insensitive. They could make the field use a >> function based index (i.e. CREATE INDEX xxxx ON T1 (UPPER(C8)); ) all >> queries would then use the same function to access the data (i.e. SELECT C1 >> FROM T1 WHERE UPPER(C8) = UPPER('value to search for'); ). The data would >> still be stored as the user entered it, but searches would be case >> insensitive. >> >> Fred >> >> >> -----Original Message----- >> From: Action Request System discussion list(ARSList) [mailto: >> [email protected]] On Behalf Of Jose Huerta >> Sent: Wednesday, February 08, 2012 6:52 AM >> To: [email protected] >> Subject: Re: Case sensitive In ITSM problem management >> >> ** 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 >> because 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 >> >> SM2 Baleares S.A. >> C/Rita Levi >> Edificio SM2 Parc Bit >> 07121 Palma de Mallorca >> >> >> -----Original Message----- >> From: Action Request System discussion list(ARSList) [mailto: >> [email protected]] On Behalf Of Rod Harris >> Sent: Wednesday, February 08, 2012 6:30 AM >> To: [email protected] >> Subject: Re: Case sensitive In ITSM problem management >> >> ** 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 >> >> -----Original Message----- >> From: Action Request System discussion list(ARSList) [mailto: >> [email protected]] On Behalf Of Jose Huerta >> Sent: Wednesday, February 08, 2012 5:34 AM >> To: [email protected] >> Subject: Re: Case sensitive In ITSM problem management >> >> ** 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 >> Error! Filename not specified. >> SM2 Baleares S.A. >> C/Rita Levi >> Edificio SM2 Parc Bit >> 07121 Palma de Mallorca >> >> >> >> _______________________________________________________________________________ >> UNSUBSCRIBE or access ARSlist Archives at www.arslist.org >> attend wwrug12 www.wwrug12.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"
<<image001.jpg>>
<<image003.jpg>>
<<image002.jpg>>
<<image004.jpg>>
