Forum Stats

  • 3,876,074 Users
  • 2,267,056 Discussions
  • 7,912,419 Comments

Discussions

Query Optimization - Text Search

Hi,

I'm working on a project with some tables which will contains a lot of records.

I'm looking for a solution to avoid a full-table-scan on those table when the end-users will do some search.

This is a simplify version of my DB Model (in blue the big tables)

  • One Post can have one or more products
  • Each product can be linked to one or more tags

When the user will search, he will enter some keyword, for example: "red big car"

My actual strategy is to tokenize the search string into words (space delimited) and build the query dynamically.

For each word, I'll find the corresponding tags into a comma separated list and use it in the main query. This is ok, the tags tables are small.

The issue is on the text attributes (NOTES / OTHER_DESC1 / OTHER_DESC2 / PROD_CODE_ORGINAL / PROD_TITLE_ORIGINAL)

The instr(...) will results to a FULL-TABLE-SCAN

select ...
from my_posts t1
where 
    ( 
        instr(upper(t1.notes),upper('...word1...')) > 0 or          ---> FULL TABLE SCAN !!!
        instr(upper(t2.other_desc1),upper('...word1...')) > 0 or    ---> FULL TABLE SCAN !!!
        instr(upper(t2.other_desc2),upper('...word1...')) > 0 or    ---> FULL TABLE SCAN !!!
        exists (select null
                  from my_posts_products t2, my_post_products_tags t3
                  where t2.post_id = t1.post_id
                  and t2.post_prod_id = t3.post_prod_id(+)
                  and ( 
                            t3.tag_id in (' ...tags found for word1... ') or
                            instr(upper(t2.prod_code_original),upper('...word1...')) > 0 or ---> FULL TABLE SCAN !!!
                            instr(upper(t2.prod_title_original),upper('...word1...')) > 0   ---> FULL TABLE SCAN !!!
                       )
                 )
    )
and ( 
        instr(upper(t1.notes),upper('...word2...')) > 0 or          ---> FULL TABLE SCAN !!!
        instr(upper(t2.other_desc1),upper('...word2...')) > 0 or    ---> FULL TABLE SCAN !!!
        instr(upper(t2.other_desc2),upper('...word2...')) > 0 or    ---> FULL TABLE SCAN !!!
        exists (select null
                  from my_posts_products t2, my_post_products_tags t3
                  where t2.post_id = t1.post_id
                  and t2.post_prod_id = t3.post_prod_id(+)
                  and ( 
                            t3.tag_id in (' ...tags found for word2... ') or
                            instr(upper(t2.prod_code_original),upper('...word2...')) > 0 or ---> FULL TABLE SCAN !!!
                            instr(upper(t2.prod_title_original),upper('...word2...')) > 0   ---> FULL TABLE SCAN !!!
                       )
                 )
    )
and ... word3 ...


I saw that ORACLE TEXT solves this issue by indexing all words etc...

But I've never used it and the documentation is quite complex and in my case I'm not sure which options should I use ? CTXCAT / CONTEXT ?

Ideally the search should have theses options

  • stemming
  • case insensitive
  • accent insensitive
  • optional : multi-language (the main language will be English)

I would be very grateful if someone can help me on this.

With Thanks & Regards

Nic

Answers