I have a nested query and I am not sure if the index I've chosen fits it's needs. Currently I am not happy with it's performance. The table contains > 2 Mio rows counting. I am working with Oracle.
SELECT * FROM mytable where groupid IN ( SELECT groupid FROM mytable where contractid IN (:contractids:) or predecessorContractId in (:contractIds:) );
contractId: lots of different exist
groupId: connected rows (predecessorContractId matches contractId) share the same groupId to be able to select contract chains more efficient. Also lots exist.
CREATE INDEX "MyIndex" ON "MyTable" ("contractId", "predecessorContractId", "groupId");
Is there a way to improve my index? Maybe also my query?