Interesting problem. Are you doing this for some sort of educational
exercise? It's hard to imagine a real world scenario for this
requirement.

There are 10 two letter combinations [5!/(3!*2!)] of letters in a five
letter word. If you create 24 bitmapped indexes- one for each letter of
the alphabet. You should be able to get good response time.


create or replace function A( p_word varchar2 ) return number is
begin
if instr(upper(p_word),'A') > 0 then return 1;
else return 0;
end if;
end;

create or replace function B( p_word varchar2 ) return number is
begin
if instr(upper(p_word),'B') > 0 then return 1;
else return 0;
end if;
end;


create bitmap index a-idx on mytable ( A(col1) ) ;
create bitmap index b-idx on mytable ( B(col1) ) ;



select * from my_table
where
( A(col1) = 1 and H(col1) = 1 )
or ( A(col1) = 1 and M(col1) = 1 )
or ( A(col1) = 1 and E(col1) = 1 )
or ( A(col1) = 1 and D(col1) = 1 )
or ( H(col1) = 1 and M(col1) = 1 )
or ( H(col1) = 1 and E(col1) = 1 )
or ( H(col1) = 1 and D(col1) = 1 )
or ( M(col1) = 1 and E(col1) = 1 )
or ( M(col1) = 1 and D(col1) = 1 )
or ( E(col1) = 1 and D(col1) = 1 )

if the above does not work you can do it this way

select * from my_table
where ( A(col1) = 1 and H(col1) = 1 )
union all
select * from my_table
where ( A(col1) = 1 and M(col1) = 1 )
union all
(etc)



-----Original Message-----
From: Agarwal
[mailto:oracledba-ezmlmshield-x62615980.[Email address protected]
Sent: Thursday, August 31, 2006 1:10 PM
To: LazyDBA Discussion
Subject: Performance hit.

Hi Guys,



I have scenario where I need to make "Like" type of search based on
certain type of combinations on multiple million record table. Like for
full name "Ahmed" I need to find all records which have any two
character of this name as "AM/MD" etc. in any order. As per requirement,
I need to build this query dynamically based on type of input. Due to
various combinations included in search I have getting major performance
hit.



Will anybody share his/her experience for this issue. Thanx in advance.



Regards,

Sanjeev



---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



----------------------------------------------------------------------------------
CareAnyware Confidentiality Notice: This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the CareAnyware Helpdesk ([Email address protected] or 919-678-0222). This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail.

Oracle LazyDBA home page