Home » RDBMS Server » Performance Tuning » How can I improve the performance of LOWER (col) LIKE ‘%name%’
How can I improve the performance of LOWER (col) LIKE ‘%name%’ [message #168336] Wed, 19 April 2006 16:39 Go to next message
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
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 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
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%')

Re: How can I improve the performance of LOWER (col) LIKE ‘%name%’ [message #168357 is a reply to message #168339] Thu, 20 April 2006 00:29 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If you're very brave, you can look into Oracle Text. It's a bit like hunting rabbits with a machine-gun, but it can find words in strings.


Ross Leishman
Re: How can I improve the performance of LOWER (col) LIKE ‘%name%’ [message #168382 is a reply to message #168357] Thu, 20 April 2006 02:40 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Quote:

It's a bit like hunting rabbits with a machine-gun,
Nice image to kick off the day Laughing
Re: How can I improve the performance of LOWER (col) LIKE ‘%name%’ [message #168457 is a reply to message #168357] Thu, 20 April 2006 09:28 Go to previous message
Art Metzer
Messages: 2480
Registered: December 2002
Senior Member
As a complement to Ross's link:
Previous Topic: Multiple index on Multiple tables
Next Topic: URGENT-Query not performing
Goto Forum:
  


Current Time: Fri Mar 29 06:12:47 CDT 2024