how to optimize sqls with Like expression

Let's say we have an sql like this:

select * from table_andrei
where name like '%Andrei%' ;


We could rewrite it as it follows:


select * from table_andrei
where instr(name ,'Andrei')>0 ;



  An index on instr(name,’Andrei’) can be created only if it is absolutely necessary.