On Jun 13, 2:55 pm, rich p <richg...@gmail.com> wrote: > 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
Please post the plan; I have a difficult time accepting that in index on employee_name will be used in that query since it's not present in the where clause. David Fitzjarrell -- 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
