Fix indexes on columns in CTA Catalogue which use LOWER()

Problem

In order to enforce case-insensitive unique names in the CTA Catalogue, we created the following indexes:

CREATE UNIQUE INDEX ADMIN_USER_AUN_UN_IDX ON ADMIN_USER(LOWER(ADMIN_USER_NAME));
CREATE UNIQUE INDEX DISK_INSTANCE_DIN_UN_IDX ON DISK_INSTANCE(LOWER(DISK_INSTANCE_NAME));
CREATE UNIQUE INDEX DISK_INSTNCE_SPCE_DISN_UN_IDX ON DISK_INSTANCE_SPACE(LOWER(DISK_INSTANCE_SPACE_NAME));
CREATE UNIQUE INDEX DISK_SYSTEM_DSN_UN_IDX ON DISK_SYSTEM(LOWER(DISK_SYSTEM_NAME));
CREATE UNIQUE INDEX VIRTUAL_ORG_VON_UN_IDX ON VIRTUAL_ORGANIZATION(LOWER(VIRTUAL_ORGANIZATION_NAME));
CREATE UNIQUE INDEX STORAGE_CLASS_SCN_UN_IDX ON STORAGE_CLASS(LOWER(STORAGE_CLASS_NAME));
CREATE UNIQUE INDEX TAPE_POOL_TPN_UN_IDX ON TAPE_POOL(LOWER(TAPE_POOL_NAME));
CREATE UNIQUE INDEX MEDIA_TYPE_MTN_UN_IDX ON MEDIA_TYPE(LOWER(MEDIA_TYPE_NAME));
CREATE UNIQUE INDEX LOGICAL_LIBRARY_LLN_UN_IDX ON LOGICAL_LIBRARY(LOWER(LOGICAL_LIBRARY_NAME));
CREATE UNIQUE INDEX TAPE_VID_UN_IDX ON TAPE(LOWER(VID));
CREATE UNIQUE INDEX MOUNT_POLICY_MPN_UN_IDX ON MOUNT_POLICY(LOWER(MOUNT_POLICY_NAME));
CREATE UNIQUE INDEX DRIVE_STATE_DN_UN_IDX ON DRIVE_STATE(LOWER(DRIVE_NAME));

These indexes have the function of constraints on the table, but are not used when doing normal lookups. For example, WHERE ..... LOWER(LOGICAL_LIBRARY_NAME ) = :b1 does not use the undex, but WHERE ..... LOWER(LOGICAL_LIBRARY_NAME) = LOWER(:b1) would use it.

Proposed solution 1

The simplest solution is to add a second index to each of these tables, so we have one index to enforce the constraint and one index for lookups. The indexes above should be renamed to indicate that they are CASE INSENSITIVE UNIQUE INDEXES and the following new indexes should be added:

CREATE UNIQUE INDEX ADMIN_USER_AUN_UN_IDX ON ADMIN_USER(ADMIN_USER_NAME);
CREATE UNIQUE INDEX DISK_INSTANCE_DIN_UN_IDX ON DISK_INSTANCE(DISK_INSTANCE_NAME);
CREATE UNIQUE INDEX DISK_INSTNCE_SPCE_DISN_UN_IDX ON DISK_INSTANCE_SPACE(DISK_INSTANCE_SPACE_NAME);
CREATE UNIQUE INDEX DISK_SYSTEM_DSN_UN_IDX ON DISK_SYSTEM(DISK_SYSTEM_NAME);
CREATE UNIQUE INDEX VIRTUAL_ORG_VON_UN_IDX ON VIRTUAL_ORGANIZATION(VIRTUAL_ORGANIZATION_NAME);
CREATE UNIQUE INDEX STORAGE_CLASS_SCN_UN_IDX ON STORAGE_CLASS(STORAGE_CLASS_NAME);
CREATE UNIQUE INDEX TAPE_POOL_TPN_UN_IDX ON TAPE_POOL(TAPE_POOL_NAME);
CREATE UNIQUE INDEX MEDIA_TYPE_MTN_UN_IDX ON MEDIA_TYPE(MEDIA_TYPE_NAME);
CREATE UNIQUE INDEX LOGICAL_LIBRARY_LLN_UN_IDX ON LOGICAL_LIBRARY(LOGICAL_LIBRARY_NAME);
CREATE UNIQUE INDEX TAPE_VID_UN_IDX ON TAPE(VID);
CREATE UNIQUE INDEX MOUNT_POLICY_MPN_UN_IDX ON MOUNT_POLICY(MOUNT_POLICY_NAME);
CREATE UNIQUE INDEX DRIVE_STATE_DN_UN_IDX ON DRIVE_STATE(DRIVE_NAME);

Proposed solution 2

Another possible solution is to leave the indexes as they are, but modify the code to ensure that all lookups are forced to LOWER. This solution would be harder to maintain.