I have tables S and T in Oracle 10g database, and i often join those tables like following way:
WITH s AS
(SELECT 1 Id FROM Dual),
t AS
(SELECT 1 Id1, NULL Id2
FROM Dual
UNION ALL
SELECT 1, 1
FROM Dual
UNION ALL
SELECT NULL, 1 FROM Dual)
SELECT s.Id, t.* FROM t, s WHERE s.Id = Coalesce(t.Id1, t.Id2);
/*
1 1
1 1 1
1 1
*/
Is there any index i could create that would make such join faster to perform?