Re: Wildcarded lookup

In article <336F295E.7843_at_cahners.com>, rmorrison_at_cahners.com says...
>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.

[ long example snipped; if anyone wants a copy let me know ]

>>
>> 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

Thanks Bob and one other person who replied (with the same suggestion).

The table with wildcards has about 110,000 rows and will soon go up to 500,000 or more :(

Explain Plan says it will do an index range scan (CBO)

I got pulled off for an (even more) urgent problem, but I'll try this suggestion Friday and see how it works.

Thanks again--
paul Received on Fri May 09 1997 - 00:00:00 CDT