Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2012

    Unanswered: FullTextSearch not returning results after re-building Full text index

    we have a table that is Full Text Search index enabled on one column.This table has 200 lakhs of rows(20000000) . ContainsTable() function is searching data with in these 200 lakhs of rows(20000000), if any new rows are inserted then the ContainsTable is not going to search in these recent inserted rows.

    we observed when we try for a data to search. it is returning the rows till the rows that are inserted date is less than 30th of march 2012. but not searching in the records that are created after April month , if even the data we are searching is available .
    TableFulltextItemCount is around 2.2 crores.
    Then we done rebuilt the FT catalog Index. then the TableFulltextItemCount became 0.Again we run the containstable query ,but still it is not getting results.

    As the no of rows are very more . so i am not able to show the actual rows from which the data is not coming.

    the below query gives 2 results that are from actual base table
    HTML Code:
    select * from g_case_action_log where cas_details like '%235355%' and  product_id = 38810
    To search for the same above word using FTS,I have used the query as below
    HTML Code:
    SELECT Distinct top 50 cal.case_id,cal.cas_details From g_case_action_log cal (READUNCOMMITTED)inner join containstable(es.g_case_action_log, cas_details, ' ("235355" OR "<br>235355" OR "235355<br> ") ') as key_tbl on cal.log_id = key_tbl.[key]    Where cal.product_id = 38810 ORDER By cal.case_id DESC
    I have attached one sql script file for your ref that contains create logic and index schema properties

    Can anyone suggest any idea why it is not returning results all the time.
    Attached Files Attached Files

  2. #2
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    This is pure "air code", but give it a try and let me know if it works:
    SELECT DISTINCT TOP 50 cal.case_id, cal.cas_details
       FROM g_case_action_log cal (READUNCOMMITTED)
       INNER JOIN ContainsTable (es.g_case_action_log
    ,     cas_details, '(235355)')
          as key_tbl on cal.log_id = key_tbl.[key]
       WHERE cal.product_id = 38810
       ORDER BY cal.case_id DESC
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts