Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> "LIKE" and REVERSE index Burleson tip is not working
mohamed_hfm
post Mar 30 2009, 02:23 AM
Post #1


Member
**

Group: Members
Posts: 14
Joined: 21-December 08
Member No.: 39,215



Hello,

Oracle Tips by Burleson Consulting about like clause and reverse index

http://www.dba-oracle.com/oracle_tips_like_sql_index.htm

is not working with me ,as my test case oracle still use full scan with table when using LIKE '%dfs%' even i when create reverse index on the column in the where condition , please can you tell me what is the wrong

thanks
Mohamed
Go to the top of the page
 
+Quote Post
Laurent Schneide...
post Mar 30 2009, 04:06 AM
Post #2


Advanced Member
***

Group: Members
Posts: 243
Joined: 24-June 07
From: Switzerland
Member No.: 9,590



QUOTE (mohamed_hfm @ Mar 30 2009, 08:24 AM) *
Hello,

Oracle Tips by Burleson Consulting about like clause and reverse index

http://www.dba-oracle.com/oracle_tips_like_sql_index.htm

is not working with me ,as my test case oracle still use full scan with table when using LIKE '%dfs%' even i when create reverse index on the column in the where condition , please can you tell me what is the wrong

thanks
Mohamed


That's right Mohammed, you cannot use a REVERSE index with a like '%XXX', unfortunately.

You could create a function based index. But this of course would have some overhead.


--------------------
Go to the top of the page
 
+Quote Post
mohamed_hfm
post Mar 30 2009, 04:58 AM
Post #3


Member
**

Group: Members
Posts: 14
Joined: 21-December 08
Member No.: 39,215



Thanks laurent for quick reply
Go to the top of the page
 
+Quote Post
burleson
post Mar 30 2009, 06:42 AM
Post #4


Advanced Member
***

Group: Members
Posts: 11,335
Joined: 26-January 04
Member No.: 13



Hi Mo,

>> oracle still use full scan with table when using LIKE '%dfs%'

Yeah, the tip never said that it would index with TWO wildcards, one on each end . . .

However, the reverse index should work if you reverse the key too

If you had '%dfs' . . .

You could use a reverse index if you did 'sfd%' . . .

QUOTE
One obscure trick for indexing queries with a leading wildcard character (like '%SON') is to create a REVERSE index and them programmatically reverse the SQL like clause to read "like 'NOS%'", effectively indexing on the other side of the text, clumsy, yet effective.


I'm going to have a consultant look into this, thanks for the note . . .


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 1st August 2014 - 08:52 AM