6 Replies Latest reply: Mar 14, 2013 10:40 AM by Bunty RSS

    Search + Optimized fields

    Bunty
      Webcenter version: 11.1.1.6
      DB : 11.2
      Search Engine: ORACLETEXTSEARCH

      for some users search is very slow and often times out in production environment. I did some analysis and found the root cause to be the dDocAccount getting added to the search query.

      The following results are from development Instance
      I ran the following query on DB directly and it took 3 secs
      SELECT * FROM idctext1 WHERE CONTAINS(dDocName, '
      (
           (((WCC\-acc1%) WITHIN dDocAccount)) 
           or (((WCC\-pre1\-acc2%) WITHIN dDocAccount)) 
           or (((WCC\-acc3%) WITHIN dDocAccount)) 
           or (((WCC\-pre2\-acc4%) WITHIN dDocAccount)) 
           or (((WCC\-pre3\-acc5%) WITHIN dDocAccount)) 
           or (((idcnull) WITHIN dDocAccount))
      )
      ') > 0; 
      Accounts field is not optimized by default and the account is not a zone search like zdSecurityGroup. So, I optimized the dDocAccount field and did a Index Rebuild (sddDocAccount is added to OTSMETA).
      I ran the following query on DB and it took .6ms
      SELECT * FROM idctext1 WHERE CONTAINS(dDocName, '(
      SDATA(sddDocAccount LIKE "WCC\-acc1%") 
      OR SDATA(sddDocAccount LIKE "WCC\-pre1\-acc2%") 
      OR SDATA(sddDocAccount LIKE "WCC\-acc3%") 
      OR SDATA(sddDocAccount LIKE "WCC\-pre2\-acc4%") 
      OR SDATA(sddDocAccount LIKE "WCC\-pre3\-acc5%") 
      )')>0
      Now I performed a search and could see that dDocAccount optimization is not considered for the query (The query was not using SDATA for account). Any help would be appreciated.

      Note: idctext table has around 20k records
      I removed idcnull from first query and it made no difference.

      I did a search on dDocAccount filed and the funny part is, it added the rest of the accounts to which the user had access to in the final query (libe).

      Edited by: Bunty on Mar 11, 2013 1:46 PM

      Edited by: Bunty on Mar 11, 2013 2:04 PM