Skip to Main Content


Question and Answer

Connor McDonald

Thanks for the question.

Asked: January 22, 2020 - 8:32 pm UTC

Last updated: March 02, 2021 - 2:03 am UTC

Version: 12.1

Viewed 1000+ times

You Asked

In one of the databases , we have list of courses , descriptions , outlines , ...
We would like to implement "google" like search

( ie)

a) If I type the keyword "data" , it look for databases , data mining , big data ...

b) If I type the keyword such as "Project Mgmt" , it should auto correct the criteria as "Project Management" ...
Kinda of knowledge search

It looks like , Oracle Text can't do this .
Please let me know what technology/algorithm I can use to achieve this , if possible .


and we said...

For (a), text search *can* do that. That's pretty much what we use on AskTOM here to provide search.

We want an index across "all" the text columns that make up a question, an answer and the reviews. So we have a procedure to do that:

CREATE OR REPLACE PROCEDURE  asktom.ate_question_idx
(rid in rowid, tlob in out nocopy clob)
for c1 in (select q.subject, q.question, q.answer,
             from ate_submitted_questions q,
                  ate_admins a
            where q.rowid = rid
            and   q.admin_id =
    tlob := tlob || c1.subject || ' ';

    if is not null then
        tlob := tlob || || ' ';
    end if;

    tlob := tlob || c1.question || ' ';

    if c1.answer is not null and length(c1.answer) > 0
        tlob := tlob || c1.answer || ' ';
    end if;

    for c2 in (select review_comments, followup_comments
               from ate_question_reviews
               where question_id =
               order by created_on)
        if c2.review_comments is not null
            tlob := tlob || c2.review_comments ||' ';
        end if;

        if c2.followup_comments is not null
            tlob := tlob || c2.followup_comments ||' ';
        end if;
    end loop;
  end if;

end loop;

and then we can define a text index that uses that output at the "column" for the text index to be based on


create index ATE_SEARCH_IDX 
  indextype is ctxsys.context
    datastore       ATE_SEARCH_IDX_DST
    sync (every SYSDATE + 4/24 memory 67108864)

So even though the index is defined for on the "ANSWER" column, the procedure above means that we'll go collect all the other information as well to put in the index.

For (b) ... no we don't do the "auto correct" but we do provide fuzzy search capability


  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment


What is the Fuzzy Search criteria that Ask Tom uses

A reader, March 01, 2021 - 10:41 pm UTC

Suppose I enter "Oracle Text" for the search. what is the parsed string? Is it just

and contains(answer, 'fuzzy({oracle text}, 30, 2000)') > 0

Connor McDonald
March 02, 2021 - 2:03 am UTC

We do a little bit of pre-processing on the inputs etc, but basically:

contains(info_col, :your_criteria, 99) > 0}

"info_col" being a consolidation of keywords in the question, answer, comments and other things

More to Explore


New to good database design? Check out Chris Saxon's full fundamentals class.