There are two problems with a scalar subselect:
1. As mentioned, the costs are not properly accounted for and
2. The scalar subselect gives the optimizer no chance to unnest the subquery so for the FULL table scan, this could be really problematic.

But it’s only after a change from INSTR to a regular expression that we see:
1. Just how expensive this lack of an unnest might be and
2. Just how cpu hungry regular expressions can be.

Using real time sql monitoring to demonstrate… (I’ve had to brutally cut out the extra columns to keep the width within the boundaries of this layour)