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
