ORACLE LIKE with multiple values

from or_log_all_proc, or_proc, or_log, patient

Your SELECT contains no join criteria so it's going to generate a product of all the records from three of those tables and the result set of whichever table is filtered by the LIKE clauses. That is the root of your performance issue.

The solution is quite simple. Join the tables correctly using the appropriate key columns.

Incidentally this is a benefit of using the ANSI 92 join syntax. By making the type of join explicit we have to actually write CROSS JOIN if we really do want to generate a product, and prevents us from doing it accidentally.

Expecting LIKE '%anything%' to be quick is also futile

Up to a point. Most likely it's a Full Table Scan, but it's an indexed column then we might get a Full Fast Scan. Even if it is just an FTS that's not necessarily catastrophic, especially if the table is small or narroe. As with most tuning tasks, it really depends on the details.

Instead of LIKE, use regular expressions. But more importantly, you are missing JOIN conditions:

select pat_mrn_id,surgery_date, proc_name  
from or_log_all_proc join
     or_proc
     on ???? join
     or_log
     on ????
     patient
     on ????
where regexp_like(upper(proc_name), '(CRANIOTOMY ANEURYSM)|(CLIPPING.*ANEURYSM)');

It is much more likely that the performance issue is due to the lack of JOIN conditions than the LIKE.

LIKE will be slow anyway, won't it? Though, if you're sure that it is OR that causes problems, try UNION, e.g.

select pat_mrn_id,surgery_date, proc_name  
from or_log_all_proc, or_proc, or_log, patient
where upper(proc_name) like ('%CRANIOTOMY ANEURYSM%')
union all
select pat_mrn_id,surgery_date, proc_name  
from or_log_all_proc, or_proc, or_log, patient
where upper(proc_name) like ('CLIPPING%ANEURYSM')

Also, I guess that this isn't query you really use - there are 4 tables in the FROM clause, without any joins - a Cartesian product isn't the best choice.

Try union all instead of or for better performance

 select pat_mrn_id,surgery_date, proc_name  
 from or_log_all_proc, or_proc, or_log, patient
 where 
 upper(proc_name) like ('CLIPPING%ANEURYSM')
 union all     
 select pat_mrn_id,surgery_date, proc_name  
 from or_log_all_proc, or_proc, or_log, patient
 where upper(proc_name) like ('%CRANIOTOMY ANEURYSM%')

Also I think you forgot to connect the tables using unique IDs