Database Software

Hi there!

I'm trying to develop a "good enough" "search engine" doing queries like Yellow pages would expect. The important goal is to retrieve 100% matches when you search for combination of words. The companies must have all words searched for (the Oracle Text score, could still be low)

I have a table like:

COMPANY
name varchar2(100)
keywords1 varchar2(1500)
keywords2 varchar2(1500)

So far, I'm using CONTEXT index in Oracle 9.2.

---

Companies can buy words. The words in keywords1 should have higher rank then keywords2 = better score = first in the list when you have match in keywords1 compared to match in keywords2 for another company.

So far so good, so if you search for:

ford garage

I want companies that rapair ford cars, and only those.

So if you have rows like

"company1", "ford volvo", "garage motor ...."
"company2", "volvo nissan", "garage ......."
"company3", null, "ford garage motor ....."

--

I will have match with company1 and company3. Company2 don't do ford cars. Company3 haven't paid for keywords1, but they still doing garage stuff with Ford cars, but should be after company1 in the result list.

Before I discover MULTI_COLUMN_DATASTORE I searched
with:

where....
contains(keywords1, 'ford & garage', 1) > 0 OR
conatins(keywords2, 'ford & garage', 2) > 0

order by score(1)*3 + score(2) ....

keywords1 have 3x higher rank than keywords2.

That works just fine when both the words you search for
are in one column, but what happens when one of the word is in the first column, and the second word is in the second column ? = my query don't works of course :-)

Do you see my problem ?

Then I read about MULTI_COLUMN_DATASTORE that solve my first problem, able to have 100% match for words (of course lower score), in different columns, but I got another problem, the rank between keywords1 vs keywords2.

Do you see what I'm heading to ???

So how can I put more weight/higer rank when I have match in one column (keywords1), compare to match in another column (keywords2) ?? Is that possible ? Do I have to do something with the data ? my table ?

Will 10g helps ??? will CTXCAT index helps ? but what about score then ?? I still want to keep the score, because if you have match in several words, higher up in the result list.

Let me know you have any clues of what I can do about this !!

Thanks!!!

Cheers,
Erlend Bjørge