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"
