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" > _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are"
