Thanks Rich, I tried to handle the string as per your suggestion, but I still see a full table scan.
On Mon, Jun 13, 2011 at 4:01 PM, ddf <orat...@msn.com> wrote: > > > 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 > -- 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
