Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

how to speed up JOIN: s.Id = Coalesce(t.Id1, t.Id2)

WestDraytonSep 3 2010 — edited Sep 3 2010
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?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 1 2010
Added on Sep 3 2010
5 comments
1,916 views