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
