I ran into issues with one query that used to work fine in the past, but now it is so slow so it basically never finishes (perhaps in some hours, beyond any timeouts).
The query is like
select ky.issuer_id from T_MASTER_ISSUER_KEYS ky
inner join T_ISSUER_SECURITY_LIST lst on lst.ISSUER_ID_SIX = ky.ISSUER_ID_SIX AND lst.ISSUER_ID_BBG = ky.ISSUER_ID_BBG
WHERE ky.ISSUER_ID_RDE is not null
And its live execution plan looks like that
The live execution still stuck at the Hash Match join step.
However, if I force a nested loops join instead
select ky.issuer_id from T_MASTER_ISSUER_KEYS ky
inner loop join T_ISSUER_SECURITY_LIST lst on lst.ISSUER_ID_SIX = ky.ISSUER_ID_SIX AND lst.ISSUER_ID_BBG = ky.ISSUER_ID_BBG
WHERE ky.ISSUER_ID_RDE is not null
Then it finishes in a second, as expected:
I already tried create some covering indices to include all the join and selection attributes, but nothing helped there. To me it looks like a hash join of 100k and 300k rows should be a piece of cake for the server.
Moreover, if I remove any of the two join conditions, the query works fine:
select ky.issuer_id from T_MASTER_ISSUER_KEYS ky
inner join T_ISSUER_SECURITY_LIST lst on /*lst.ISSUER_ID_SIX = ky.ISSUER_ID_SIX AND*/ lst.ISSUER_ID_BBG = ky.ISSUER_ID_BBG
WHERE ky.ISSUER_ID_RDE is not null
The joining columns are VARCHAR(20), could it have any impact on the join?
4
Rewrite as :
select ky.issuer_id
from T_MASTER_ISSUER_KEYS AS ky
WHERE EXISTS(SELECT 1/0
FROM T_ISSUER_SECURITY_LIST AS lst
WHERE lst.ISSUER_ID_SIX = ky.ISSUER_ID_SIX AND
lst.ISSUER_ID_BBG = ky.ISSUER_ID_BBG AND
ky.ISSUER_ID_RDE is not null);
Create theses index if not exists :
-- index for ky :
CREATE INDEX X_Jan_Drozen_T_MASTER_ISSUER_KEYS_001
ON T_MASTER_ISSUER_KEYS (ISSUER_ID_RDE, ISSUER_ID_SIX, ISSUER_ID_BBG) INCLUDE (issuer_id);
-- index for lst :
CREATE INDEX X_Jan_Drozen_T_ISSUER_SECURITY_LIST_001
ON T_ISSUER_SECURITY_LIST (ISSUER_ID_SIX, ISSUER_ID_BBG) ;