I have an comment and user_details table . The comment table contains the comments entered by the user and user_details contains the users first name and last name of the user who created the comment.
Comment_id comment_desc comment_title created_by
1 sam sam_com1 102
2 jam jam_com1 103
3 sam_com3 sam_com2 102
user_id first_name last_name
1 sam daniel
2 jam gopal
I have defined the function based index on comment_desc --> comments_desc_idx and comment_title --> comments_title_idx . I also have an index on last_name user_details_ln_idx in user_details table.
I have the below query to fetch the comments matching the test sam or first_name or last_name matching sam. The issue iam facing is , the comments table has almost 2345678 k rows. The user_details table also has rows upto 120049K rows
Select c.comment_id , c.comment_desc , c.comment_title , ut.first_name , ut.last_name
from comments c left outer join user_details ut
(c.comment_desc like '%sam%'
or c.comment_title like '%sam%'
or ut.first_name like '%sam%'
or ut.last_name like '%sam%'
or ut.first_name ||ut.last_name like '%sam%'
or ut.last_name||ut.first_name like '%sam%')
The query is taking an long time , as the indexes on the columns defined in comments table is not used. I cannot define anymore indexes on the user_details table as it is another user schema. We have an synonym for user_details table in out schema. Is there way to request oracle to use the indexes for the columns comment_Desc and comment_title.