I am working on a legacy system which uses Oracle database. Please dont ask me why data is stored the way it is :), there is a table ofsd_temp which has a varchar2 column entity_nums which has string of numbers separatedby pipe delimiter
e.g.

|432124|
|12678|762333|74774|

There are about 300K records in ofsd_temp table, there is another table ofdl which has 150K records but the column is a number instead of string and below is SQL which is taking a very long time to execute returns about 130K rows, I cant change the way data is stored but if you have any suggestions to make it faster.

select *
from ofsd_temp sdtemp,
     ofdl dl
where '|' || dl.entity_num || '|' = sdtemp.entity_nums;