good one. But not sure if adding another column would be accepted, as it might have to under go lot of pre-processing to already existing transactional records.
Is there any other work around? On Sat, Jun 11, 2011 at 12:54 PM, Michael Moore <michaeljmo...@gmail.com>wrote: > 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 > -- 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
