Shilen Patel @ Duke:
"So we tried the following.
drop index MEMBERSHIP_FIELD_IDX;
drop index MEMBERSHIP_OWNER_IDX;
drop index MEMBERSHIP_TYPE_IDX;
CREATE INDEX IMS.GROUPER_MEMBERSHIPS_IDX1 ON IMS.GROUPER_MEMBERSHIPS
(OWNER_ID, LIST_NAME, LIST_TYPE, MSHIP_TYPE);
CREATE INDEX IMS.GROUPER_MEMBERSHIPS_IDX2 ON IMS.GROUPER_MEMBERSHIPS
(MEMBER_ID, LIST_NAME, LIST_TYPE);
I've tried these index changes on two database servers and have seen
similar results on both.
The time to delete a group drops from 2.18 seconds to .18 seconds.
The time to delete a stem drops from .40 seconds to .08 seconds.
The time to run Member.getMemberships() for a person with 7 memberships
drops from .55 seconds to .10 seconds."