How about if you create another column in your table which always has the upper case of the name?
On Sat, Jun 11, 2011 at 10:04 AM, gayathri Dev <gd0...@gmail.com> wrote: > I tried creating ctxsys.context Index, but since query is using UPPER it > was still making full table scan. > And I was not let to create function based ctxsys.context Index...:( > > > > On Sat, Jun 11, 2011 at 8:14 AM, Michael Moore <michaeljmo...@gmail.com>wrote: > >> Would Oracle Text do it for you? >> http://download.oracle.com/docs/cd/B10500_01/text.920/a96517/cdefault.htm >> >> >> On Fri, Jun 10, 2011 at 9:08 PM, gayathri Dev <gd0...@gmail.com> wrote: >> >>> Thanks for the solution David. >>> >>> But this query is a part of Dynamic SQL that gets generated by PL/SQL >>> block. >>> and the string can be any thing else. ABC was just an example i gave. >>> >>> Please suggest. >>> >>> >>> Regards, >>> G. >>> >>> >>> >>> >>> On Fri, Jun 10, 2011 at 4:50 PM, ddf <orat...@msn.com> wrote: >>> >>>> >>>> >>>> On Jun 10, 4:28 pm, gayathri Dev <gd0...@gmail.com> wrote: >>>> > Hi All, >>>> > >>>> > I have following query : >>>> > >>>> > SELECT EMP_NAME FROM EMP >>>> > WHERE UPPER(EMP_NAME) LIKE '%ABC%'; >>>> > >>>> > CREATE INDEX IX_NAME ON >>>> > emp(UPPER(EMP_NAME)); >>>> > Looks like using the meta character (%) both leading and trailing >>>> would not >>>> > use the index and go for Full table scan. >>>> > >>>> > Pls advise how can i optimize above query. >>>> > >>>> > Thanks in advance, >>>> > G >>>> >>>> >>>> SQL> create index ix_name on >>>> 2 emp(instr(ename, 'ABC')); >>>> >>>> Index created. >>>> >>>> SQL> select * from emp >>>> 2 where instr(ename, 'ABC') > 0 >>>> 3 / >>>> >>>> EMPNO ENAME JOB MGR HIREDATE SAL >>>> COMM DEPTNO >>>> ---------- ---------- --------- ---------- --------- ---------- >>>> ---------- ---------- >>>> 7783 BLABCO MANAGER 7839 09-JUN-81 >>>> 2450 10 >>>> >>>> >>>> Execution Plan >>>> ---------------------------------------------------------- >>>> Plan hash value: 3587740764 >>>> >>>> >>>> --------------------------------------------------------------------------------------- >>>> | Id | Operation | Name | Rows | Bytes | Cost >>>> (%CPU)| Time | >>>> >>>> --------------------------------------------------------------------------------------- >>>> | 0 | SELECT STATEMENT | | 1 | 87 | >>>> 2 (0)| 00:00:01 | >>>> | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 87 | >>>> 2 (0)| 00:00:01 | >>>> |* 2 | INDEX RANGE SCAN | IX_NAME | 1 | | >>>> 1 (0)| 00:00:01 | >>>> >>>> --------------------------------------------------------------------------------------- >>>> >>>> Predicate Information (identified by operation id): >>>> --------------------------------------------------- >>>> >>>> 2 - access(INSTR("ENAME",'ABC')>0) >>>> >>>> Note >>>> ----- >>>> - dynamic sampling used for this statement >>>> >>>> >>>> Statistics >>>> ---------------------------------------------------------- >>>> 0 recursive calls >>>> 0 db block gets >>>> 2 consistent gets >>>> 0 physical reads >>>> 0 redo size >>>> 826 bytes sent via SQL*Net to client >>>> 385 bytes received via SQL*Net from client >>>> 2 SQL*Net roundtrips to/from client >>>> 0 sorts (memory) >>>> 0 sorts (disk) >>>> 1 rows processed >>>> >>>> SQL> >>>> >>>> >>>> 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 >>> >> >> -- >> 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 > -- 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
