OraFAQ Forum: Performance Tuning » How can I improve the performance of LOWER (col) LIKE ‘%name%’

Home » RDBMS Server » Performance Tuning » How can I improve the performance of LOWER (col) LIKE ‘%name%’

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How can I improve the performance of LOWER (col) LIKE ‘%name%’ [message #168336]

Wed, 19 April 2006 16:39

moeen49
Messages: 12
Registered: September 2005

Junior Member

Hi,

I need to search for this condition:

WHERE LOWER (col) LIKE ‘%name%’

In a table with 6,000,000 rows. I understand that when LIKE pattern begins with %, it cannot take the advantage of indexes.

Is there any technique to improve my query’s performance?

Thank you,
Alan

Report message to a moderator

Re: How can I improve the performance of LOWER (col) LIKE ‘%name%’ [message #168339 is a reply to message #168336]

Wed, 19 April 2006 19:45

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

As usual, it depends on many factors.
Depends on version (9i and 10g ***may*** do an FFS), depends on the cardinality and your collected statistics.

SQL>  select ename from emp where lower(ename) like ('%mit%');

32768 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2374684868

----------------------------------------------------------------------------------
| Id  | Operation            | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           | 22923 |   134K|   283  (16)| 00:00:04 |
|*  1 |  INDEX FAST FULL SCAN| EMP_ENAME | 22923 |   134K|   283  (16)| 00:00:04 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(LOWER("ENAME") LIKE '%mit%')

SQL>  select empno,ename from emp where lower(ename) like ('%mit%');

32768 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2872589290

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 22346 |   414K|   644   (9)| 00:00:08 |
|*  1 |  TABLE ACCESS FULL| EMP  | 22346 |   414K|   644   (9)| 00:00:08 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(LOWER("ENAME") LIKE '%mit%')

SQL> drop index EMP_ENAME;

Index dropped.

SQL>  Create index EMP_ENAME_EMPNO on EMP(ENAME,EMPNO);

Index created.

SQL> set autotrace traceonly exp
SQL> @gather_table_stats;
Enter value for table_name: EMP

PL/SQL procedure successfully completed.

SQL>  select empno,ename from emp where lower(ename) like ('%mit%');

Execution Plan
----------------------------------------------------------
Plan hash value: 3818123871

----------------------------------------------------------------------------------------
| Id  | Operation            | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                 | 23123 |   225K|   340  (14)| 00:00:05 |
|*  1 |  INDEX FAST FULL SCAN| EMP_ENAME_EMPNO | 23123 |   225K|   340  (14)| 00:00:05 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(LOWER("ENAME") LIKE '%mit%')

Report message to a moderator