Optimizing String search in oracle

Question

I have a view that contain all data related to employee. it had about 350k records. I have to make a name search functionality. That will retrieve all the data that matches the keyword entered.

The query performance is very slow it takes 15-20 seconds to retrieve data. Cost-15000

My query:

 SELECT            H.PERSON_ID,
                   B.EMPLOYEE_ID,
                   INITCAP(B.FIRST_NAME) EMP_FNAME,
                   INITCAP(B.MIDDLE_NAME) EMP_MNAME,
                   INITCAP(B.LAST_NAME) EMP_LNAME,
                   B.EMPLOYEE_TYPE PERSON_DESC,
                   B.EMPLOYMENT_STATUS STATUS_TYPE,
                   EA.BASE_BRANCH

              FROM EMPLOYEE_BASIC_DTLS   B,
                   EMP_ASSIGNMENT_DTLS_MV EA,
                   EMPLOYEE_HIS_DEPNDENT_TBL  H
             WHERE B.PERSON_ID = EA.PERSON_ID
               AND B.PERSON_ID = H.PERSON_ID
               AND ((UPPER(B.FIRST_NAME) LIKE
                   ('%' || V_SEARCH_PARAM1 || '%')) OR
                   (UPPER(B.MIDDLE_NAME) LIKE
                   ('%' || V_SEARCH_PARAM1 || '%')) OR
                   (UPPER(B.LAST_NAME) LIKE
                   ('%' || V_SEARCH_PARAM1 || '%')))
               AND TRUNC(SYSDATE) BETWEEN EA.EFFECTIVE_START_DATE AND
                   EA.EFFECTIVE_END_DATE
               AND UPPER(H.RELATIONSHIP_CODE) = 'A';

Since EMPLOYEE_BASIC_DTLS is a view I cant use indexing.

Answer 1

As one may look for any name or any part of a name there is no way to create an index containing the values to be searched beforehand. So that won't help you here. Oracle will do a full table scan to check every single string for a match.

What you can do though is to speed up that scan.

You can speed up a full table scan by parallelizing it via /*+parallel(EMPLOYEE_BASIC_TABLE,4)*/ for instance. (This would be my advice here.)

Or you can avoid a full table scan by having one index per column, well knowing that there are many repeatedly used names, so that every name is scanned just once. Then you would use function based keys on the underlying table as Bob Jarvis suggests, because you are using the upper function on any name. Fastest would be a combined index:

create bitmap index idx_name_search on EMPLOYEE_BASIC_TABLE (upper(first_name || '|' || middle_name || '|' || last_name))

so there is just one index to look up. (You would have to use exactly this expression in your query of course: WHERE upper(first_name || '|' || middle_name || '|' || last_name) like '%JOHN%'.) But still, you don't know what will be searched for in advance, and as '%JOHN%' may effect only 2% of your table data, '%E%' may affect 80%. The optimizer would never know. You could at least guess and have to different select statements, one with a full table hint you'd use when the search string contains at least three letters and one with an index hint you'd use otherwise, for instance.

You see, that gets quite complicated the more you think about it. I suggest to try the parallel hint first. Maybe this already speeds things up sufficiently.

Answer 2

While it's true you can't put an index on a view, you can certainly put indexes on the underlying tables. However, as noted by @JustinCave even if you do add indexes to the appropriate tables this query still won't use them because of the use of LIKE. Additionally, because the UPPER function is being applied to the FIRST_NAME, MIDDLE_NAME, and LAST_NAME columns you'd need to define your indexes as function-based indexes. For example, if the 'real' table accessed by the EMPLOYEE_BASIC_TABLE view is called EMPLOYEES you could define a function-based index on the FIRST_NAME column as

CREATE INDEX EMPLOYEES_UPPER_FIRST_NAME ON EMPLOYEES (UPPER(FIRST_NAME));

I suggest you consider whether the LIKE comparisons are really needed, as working around those to get better performance is going to be difficult.

If you'd like to investigate Oracle Text indexes you can find the documentation here. I think you'll find it's more suited to document or document fragment indexes, but perhaps it would give you some ideas.

Share and enjoy.