Thanks Michael. Appreciate your suggestions.
On Mon, Jun 13, 2011 at 11:51 AM, Michael Moore <michaeljmo...@gmail.com>wrote: > 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 > -- 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
