On Jun 10, 4:28 pm, gayathri Dev <[email protected]> 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 [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
