How can i optimize a simple oracle query

Transact-SQL
1

This oracle query is taking quite long to run. Is there any way to optimize it and make it faster?

select t1.*, t2.*
from table1 t1, table2 t2
where t1.id = t2.id
and t1.key = t2.key
and t1.product like '%apple%'
2

Update: I have tried this approach but not making a big difference in performance (since I am working with a couple of hundred thousand records. Any other approach that I can use here for faster results?

select t1.*, t2.*
from table1 t1
INNER JOIN table2 t2 ON t1.id = t2.id
and t1.key = t2.key
WHERE t1.product like '%apple%'
3

because you are using like and using the beginning wildcard, it will do a table scan. You can try implementing Full Text indexes if you really need to search like this

1 Like
4

Hi @mike01 could you please elaborate on full text indexes? Are you referring to using exact names separated by comma instead of using a wildcard %?

In terms of join, is my approach accurate? anything can be done to optimize it?

5

your joins are fine, but full text indexing is a full implementation with special syntax. There's a lot of information out there, but you can start with this