you might try reposting the question to ORACLE-L More people will read your question there.
http://www.orafaq.com/wiki/Oracle-l On Mon, Jun 13, 2011 at 11:37 AM, gayathri Dev <gd0...@gmail.com> wrote: > 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 > -- 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
