Sometime the requirement is to search a column of a table using like operator with both side %, as we may need to search employees whose names have 'AR'. so that table can be searched by query with ENAME LIKE '%AR%' condition or INSTR(ENAME,'AR')>0 condition. But this requirement may lead Oracle to avoid the use of index. Problem: SQL> CREATE INDEX MYEMP_ENAME_INDX ON MYEMP(ENAME); Index created. SQL> SELECT * FROM MYEMP WHERE ENAME LIKE '%AR%'; Execution Plan ---------------------------------------------------------- Plan hash value: 2208738075 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 546 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| MYEMP | 14 | 546 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- Here we can provide an Index Hint to query to suggest Oracle Database Engine to use index, Oracle may use index after providing index hint in query. Solution: SQL> SELECT /*+ INDEX (MYEMP MYEMP_ENAME_INDX) */ * FROM MYEMP WHERE ENAME LIKE '%AR%'; Execution Plan ---------------------------------------------------------- Plan hash value: 2208738075 ------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 39 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| MYEMP | 1 | 39 | 2 (0)| 00:00:01 | |* 2 | INDEX FULL SCAN | MYEMP_ENAME_INDX | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------ Same can be used when we write this requirement using INSTR. Solution: SQL> SELECT /*+ INDEX (MYEMP MYEMP_ENAME_INDX) */ * FROM MYEMP WHERE INSTR(ENAME,'AR')>0; Execution Plan ---------------------------------------------------------- Plan hash value: 2418123361 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 39 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| MYEMP | 1 | 39 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- Get More Query Optimization Tips: http://nimishgarg.blogspot.in/2012/10/query-optimization-tips-for-oracle.html
Index Hints and Like Clause
Subscribe to:
Post Comments (Atom)
Hmm, 11.2 seems to not need this:
ReplyDeleteSCOTT@ORCL> SELECT * FROM EMP WHERE ENAME LIKE '%AR%';
Execution Plan
----------------------------------------------------------
Plan hash value: 3381260881
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 2 (0)| 00:00:01 |
|* 2 | INDEX FULL SCAN | ENAME_IX | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ENAME" LIKE '%AR%' AND "ENAME" IS NOT NULL)
On this case Optimizer may or may not use the index, so use of hint is suggested...
Deletesir..
ReplyDeleteThis is very nice article.I have few queries..
Can u please provide some simple examples of the below ones in Oracle.
1.Partitioning (Table, Query), External tables.
2. Hints
3. TKPROF
4. How will we reduce the cost of the sql query statement..?
Thanks,
Rajkumar
read following articles:
Deletefor optimization: http://nimishgarg.blogspot.in/2012/10/query-optimization-tips-for-oracle.html
Tkprof: http://nimishgarg.blogspot.in/2010/10/oracle-tkprof-simple-steps.html
PL/SQL Profiler: http://nimishgarg.blogspot.in/2013/03/dbmsprofiler-overview-and-how-to-install.html
External Table: http://nimishgarg.blogspot.in/2012/06/create-external-table-from-csv-file.html
Thanks....!!! This is really very helpful
ReplyDeleteSir,
ReplyDeleteCan you please explain about step by step Oracle EXTERNAL TABLES..?
http://nimishgarg.blogspot.in/2012/06/create-external-table-from-csv-file.html
DeleteThis is really very helpful. Thanks !!!
DeleteI have one question why is the optimizer is ignoring index when the cost is lower then full table access? And why we need to use hint to make optimizer force the index use?
ReplyDeleteOracle Database does not use index with like operator, because oracle can not estimate the cardinality with like operator. So oracle has given us a way to use index with like operator if we know that cost would be low
DeleteHi Nimish,
ReplyDeleteThe last output (Index Hint with INSTR) is wrong.
In 11g, 12c if we use Index Hint along with INSTR then it will use Index.
it may or may not use. it depends
Delete