-- COMMENT: Drop unnecessary indexes, add new ones for dbmail queries

DROP INDEX DBMAIL_HEADERVALUE_IDX1;
DROP INDEX DBMAIL_HEADERVALUE_IDX3;
DROP INDEX DBMAIL_HEADERVALUE_IDX4;
CREATE INDEX DBMAIL_HASH_HEADERVALUE_IDX1 ON DBMAIL_HEADERVALUE ( NLSSORT("HASH",'nls_sort=''BINARY_CI'''),NLSSORT("HEADERVALUE",'nls_sort=''BINARY_CI''') ) ONLINE PARALLEL 8 NOLOGGING TABLESPACE DBMAIL_TS_IDX;
ALTER INDEX DBMAIL_HASH_HEADERVALUE_IDX1 NOPARALLEL LOGGING;

DROP INDEX DBMAIL_MIMEPARTS_HASH_IDX;
CREATE INDEX DBMAIL_MIMEPARTS_HASH_IDX ON DBMAIL_MIMEPARTS ( NLSSORT("HASH",'nls_sort=''BINARY_CI''') ) ONLINE PARALLEL 8 NOLOGGING TABLESPACE DBMAIL_TS_IDX;
ALTER INDEX DBMAIL_MIMEPARTS_HASH_IDX NOPARALLEL LOGGING;

DROP INDEX DBMAIL_PBSP_IPNUMBER_IDX;
CREATE UNIQUE INDEX DBMAIL_PBSP_IPNUMBER_IDX ON DBMAIL_PBSP ( NLSSORT("IPNUMBER",'nls_sort=''BINARY_CI''') ) ONLINE PARALLEL 8 NOLOGGING TABLESPACE DBMAIL_TS_IDX;
ALTER INDEX DBMAIL_PBSP_IPNUMBER_IDX NOPARALLEL LOGGING;

DROP INDEX DBMAIL_REFERENCESFIELD_MSG_IDX;
CREATE UNIQUE INDEX DBMAIL_REFERENCESFIELD_MSG_IDX ON DBMAIL_REFERENCESFIELD ( PHYSMESSAGE_ID,NLSSORT("REFERENCESFIELD",'nls_sort=''BINARY_CI''') ) ONLINE PARALLEL 8 NOLOGGING TABLESPACE DBMAIL_TS_IDX;
ALTER INDEX DBMAIL_REFERENCESFIELD_MSG_IDX NOPARALLEL LOGGING;

DROP INDEX DBMAIL_REPLYCACHE_IDX;
CREATE UNIQUE INDEX DBMAIL_REPLYCACHE_IDX ON DBMAIL_REPLYCACHE ( NLSSORT("TO_ADDR",'nls_sort=''BINARY_CI'''),NLSSORT("FROM_ADDR",'nls_sort=''BINARY_CI'''),NLSSORT("HANDLE",'nls_sort=''BINARY_CI''') ) ONLINE PARALLEL 8 NOLOGGING TABLESPACE DBMAIL_TS_IDX;
ALTER INDEX DBMAIL_REPLYCACHE_IDX NOPARALLEL LOGGING;

DROP INDEX DBMAIL_USERMAP_IDX;
CREATE UNIQUE INDEX DBMAIL_USERMAP_IDX ON DBMAIL_USERMAP ( NLSSORT("LOGIN",'nls_sort=''BINARY_CI'''),NLSSORT("SOCK_ALLOW",'nls_sort=''BINARY_CI'''),NLSSORT("USERID",'nls_sort=''BINARY_CI''') ) ONLINE PARALLEL 8 NOLOGGING TABLESPACE DBMAIL_TS_IDX;
ALTER INDEX DBMAIL_USERMAP_IDX NOPARALLEL LOGGING;

DROP INDEX DBMAIL_ENVELOPE_IDX2;

DROP INDEX DBMAIL_HEADER_IDX3;
DROP INDEX DBMAIL_HEADER_IDX5;
DROP INDEX DBMAIL_HEADER_IDX5;

DROP INDEX DBMAIL_MAILBOXES_NAME_IDX;
DROP INDEX DBMAIL_MAILBOXES_SEQ_IDX;

DROP INDEX DBMAIL_MESSAGES_MBOX_IDNR_IDX;
DROP INDEX DBMAIL_MESSAGES_MBOX_STAT_IDX;
DROP INDEX DBMAIL_MESSAGES_PHSMSG_ID_IDX;
DROP INDEX DBMAIL_MESSAGES_SEEN_FLAG_IDX;
DROP INDEX DBMAIL_MESSAGES_STATUS_IDX;
DROP INDEX DBMAIL_MESSAGES_UNIQUE_ID_IDX;
CREATE INDEX DBMAIL_MESSAGES_SEEN_FLAG_IDX ON DBMAIL_MESSAGES ( PHYSMESSAGE_ID,SEEN_FLAG,NLSSORT("UNIQUE_ID",'nls_sort=''BINARY_CI''') ) ONLINE PARALLEL 8 NOLOGGING TABLESPACE DBMAIL_TS_IDX;
ALTER INDEX DBMAIL_MESSAGES_SEEN_FLAG_IDX NOPARALLEL LOGGING;
CREATE INDEX DBMAIL_MESSAGES_MBOX_STAT_IDX ON DBMAIL_MESSAGES ( PHYSMESSAGE_ID,MAILBOX_IDNR,STATUS ) ONLINE PARALLEL 8 NOLOGGING TABLESPACE DBMAIL_TS_IDX;
ALTER INDEX DBMAIL_MESSAGES_MBOX_STAT_IDX NOPARALLEL LOGGING;

DROP INDEX DBMAIL_PARTLISTS_PHMSG_ID_IDX;

DROP INDEX DBMAIL_SIEVESCRIPTS_IDX;
DROP INDEX DBMAIL_SIEVESCRIPTS_NAME_IDX;
CREATE UNIQUE INDEX DBMAIL_SIEVESCRIPTS_IDX ON DBMAIL_SIEVESCRIPTS ( OWNER_IDNR,NLSSORT("NAME",'nls_sort=''BINARY_CI''') ) ONLINE PARALLEL 8 NOLOGGING TABLESPACE DBMAIL_TS_IDX;
ALTER INDEX DBMAIL_SIEVESCRIPTS_IDX NOPARALLEL LOGGING;

DROP INDEX DBMAIL_SUBSCRIPTION_MBOX_IDX;

DROP INDEX DBMAIL_ACL_MAILBOX_ID_IDX;

DROP INDEX DBMAIL_ENVELOPE_IDX2;

