Re: Wildcarded lookup

Paul Miller wrote:
>
> Hello!
>
> I have an interesting problem that I am wrestling with. I can make it
> work but it is not very efficient.
>
> I have a table (table B) with 8 columns making up the key. 6 of those
> columns can have either a specific value or a wildcard (I'm using a star,
> '*', if it matters).
>
> Those 8 columns also exist in another table (table A), with no wildcards.
> I need to find all of the rows in table B that match a single row in
> table A.
>
> Let me give an example:
>
> TABLE A
> =======
> Col_1 Col_2 Col_3 Col_4 Col_5 Col_6 Col_7 Col_8
> ----------------------------------------------------
> R1 A B C D E F G H
> R2 A B C Z Y X W V
>
> TABLE B
> =======
> Col_1 Col_2 Col_3 Col_4 Col_5 Col_6 Col_7 Col_8
> ----------------------------------------------------
> R3 A B * * * * * *
> R4 A C W * * F * *
> R5 A B C D * * * H
> R6 A B C D * * * R
> R7 A B C * * X * *
>
> In this example, R1 from Table A would match R3 and R5. R2 would match R3
> and R7. R4 and R6 would not match either row.
>
> I apologize if this is unclear, but it really does match the problem I'm
> trying to solve :)
>
> Anyway, right now I have a cursor where I say (A.Col_1 = B.Col_1 .... and
> (A.Col_5 = B.Col_5 OR B.Col_5 = '*') and ....). It gives me the right
> answers VERY slowly.
>
> I would appreciate any and all suggestions, even if you tell me I've set
> up the tables all wrong.
>
> thanks!
> paul

You could write the SQL Statement as:

A.col_1 = b.col_1 and
A.col_2 = b.col_2 and
A.col_3 = Decode(b.col_3,'*',a.col_3,b.col_3) and
A.col_4 = Decode(b.col_4,'*',a.col_4,b.col_4) and
A.col_5 = Decode(b.col_5,'*',a.col_5,b.col_5) and
A.col_6 = Decode(b.col_6,'*',a.col_6,b.col_6) and
A.col_7 = Decode(b.col_7,'*',a.col_7,b.col_7) and
A.col_8 = Decode(b.col_8,'*',a.col_8,b.col_8)

The performance is going to depend on a few things, first which optimizer you are using, second is table b indexed on col_1 and col_2. Third the size of table b. If you are using CBO have statistics been calculated. Have you run an explain plain?

Bob Morrison Received on Tue May 06 1997 - 00:00:00 CDT