I tinkered with the small EMPLOYEES table available by default on most Oracle servers... by using the previous suggestion of an INSTR operation, I combined the approach using a WITH clause in my main query. The following code uses WITH clause sub-queries to defined the supporting on-demand views that would be feeding the output of the main search query.
In this case, I noticed my full table scans came from the unbounded search string (% wildcard in the front) and by the uppercase notation intended to create uniformly managed last name values for searching. The results were interesting... I tried this with a small table (20+ records) and a much larger one (10,000 records +) *with* * upper_names as (* * select employee_id, upper(last_name) as upper_last_name* * from hr.employees ),* * string_match as (* * select employee_id, upper_last_name,* * instr( upper_last_name, 'KIN', 1, 1 ) as instring_match* * from upper_names )* *select employee_id, upper_last_name* *from string_match* *where instring_match > 0* This approach results in the use of a fast-full-scan of the table's index on employee_name without any additional table modifications, save for the already existing non-unique index placed on employee last name. Rich Pascual -- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to Oracle-PLSQL@googlegroups.com To unsubscribe from this group, send email to oracle-plsql-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en
