|
|
|
|
| Re: How to avoid Full table scan for Like '%XYZ%' [message #414855 is a reply to message #410992] |
Thu, 23 July 2009 16:53  |
 |
Kevin Meade
Messages: 2103
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
