I have a table called TB_STUDENT which has two fields called STUDENT_NAME,STUDENT_ID,STUDENT_NUMBER and have at least 100000 rows of data. Now I have created two indexes and I want to search the student according to student_name. I have use hint, but it seems that it still is TABLE ACCESS FULL.How to change my SQL?
CREATE index IDX_STUDENT_ID ON CUST (STUDENT_ID);
CREATE index IDX_STUDENT_NAME ON CUST (STUDENT_NAME);
SELECT /*+ INDEX(IDX_STUDENT_ID ,IDX_STUDENT_NAME ) */STUDENT_ID,STUDENT_NAME FROM TB_STUDENT S WHERE STUDENT_NAME LIKE '%LUSY%';
An index stores the data in a sorted tree, making searching it easier. For varchar columns, this means the data is sorted lexicographically. It would make sense to use an index if you had a wildcard at the end (or even the middle) of the string, but here, you have a wildcard at the beginning of the string, meaning the value can be anywhere in the index. In such a case where using an index gives no benefit over scanning the entire table, the optimizer will choose not to use the index. Hints are just what their name suggests - hints - and cannot force the optimizer to use an index in an invalid usecase.