Home » RDBMS Server » Performance Tuning » How to avoid Full table scan for Like '%XYZ%' (Oracle
How to avoid Full table scan for Like '%XYZ%' [message #410988] Wed, 01 July 2009 07:26 Go to next message
Messages: 266
Registered: May 2009
Senior Member
How to avoid Full table scan for Like '%XYZ%'
Re: How to avoid Full table scan for Like '%XYZ%' [message #410992 is a reply to message #410988] Wed, 01 July 2009 07:53 Go to previous messageGo to next message
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Simple answer - you can't.

More complex answer - you might be able to do something with Oracle Text

Re: How to avoid Full table scan for Like '%XYZ%' [message #414855 is a reply to message #410992] Thu, 23 July 2009 16:53 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
OK, I am sure this is not what you want but what the hey...

If you are always searching for XYZ, then you could do this:

select *
from T
where sign(instr(C1,'XZY')) = 1

create index I1 on T(sign(instr(C1,'XYZ'))

As you can see this is near useless. It only works for one search value. And you must make sure that the query you are issuing matches the indexed expression; this can easily be achieved with a view. If you have only one search value, or a small number of search values (small being relative to your tollerance for proliferation of indexes) you could create several such indexes. Yes, it is a very special case solution and likely not what you want.

But as three wise men once showed us, if it is an Ana-Cana-Pana-Sana which you need to do your job, having one is the perfect solution.

In the old days (PRE-oracle CONTEXT INDEX days) we used to build our own "false" index so to speak.

create table search_term (token varchar2(30))

create table row_term (table_name varchar2(30),token varchar2(30),row_key number)

create index row_term_i1 on row_term(table_name,token,row_key)

select *
from   T
     , row_term
where row_term.table_name = 'T'
and row_term.token = '&&1'
and row_term.row_key = T.row_key

Of course YOU have to populate the table row_term and YOU must keep it up-to-date either in your code or via use of triggers. And this table can get lots of rows in it because it is based on the number or token hits per row. But for moderate size tables, it works well enough to be very useful.

It is my understanding that context indexes do something similar as this only better, and with less maintenance on your part (sometimes).

Someone else might have other solutions to offer, or comments on these ideas.

Good luck, Kevin
Previous Topic: Viewing Pinned Objects in KEEP cache
Next Topic: Index with OR predicates
Goto Forum:

Current Time: Thu Mar 30 15:37:30 CDT 2017

Total time taken to generate the page: 0.09241 seconds