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
