This discussion is archived
1 2 3 Previous Next 38 Replies Latest reply: Dec 25, 2012 11:08 PM by 949210 Go to original post RSS
  • 30. Re: searching  in a materialized view
    Roger Ford Expert
    Currently Being Moderated
    The bug you are hitting appears to be bug 5060137: PROGESSIVE RELAXATION RETURNS NO HITS ALTHOUGH IT SHOULD

    Please contact Oracle Support to see if there is a patch available for your platform and version.
  • 31. Re: searching  in a materialized view
    949210 Newbie
    Currently Being Moderated
    is there an option to create multicolumn datastore in ctxcat index?
    probably not
  • 32. Re: searching  in a materialized view
    949210 Newbie
    Currently Being Moderated
    ---
    this was required to mark the question as unanswered

    Edited by: 946207 on Dec 21, 2012 7:31 PM
  • 33. Re: searching  in a materialized view
    Barbara Boehmer Oracle ACE
    Currently Being Moderated
    No, you cannot create a multi_column_datastore on a ctxcat index and there are other reasons why you should not use a ctxcat index.

    Bug # 5060137 regarding progressive relaxation was fixed in Oracle version 10.2.0.3, so the obvious solution is to patch or upgrade to that or something higher.

    If for some strange reason you need to continue in your current version, you can apply weights to different results by multiplying the score, in order to achieve something similar to progressive relaxation.
  • 34. Re: searching  in a materialized view
    949210 Newbie
    Currently Being Moderated
      
    select prod_details,sign,sign2,score(1) from drop_it_5 
    where contains( prod_details, '(xxxaerosol within S)*10 or (xxxaerosol within K)*5 or (xxxaerosol within C)*0.1',1 ) > 0
    order by score(1) desc;
    XXXAerosol Bunk              S-357     S     100
    XXXAerosol Manufacturers     K-21941     K     81
    XXXAerosol Spray Dealers     K-6892     K     81
    XXXAerosol Tankers             C-255     C       2
    can i use definescore to return score how i want it to?
    For prod_details=search term and sign2=S,score=100
    For prod_details=search term and sign2=K,score=50
    For prod_details=search term and sign2=C,score=25
  • 35. Re: searching  in a materialized view
    Barbara Boehmer Oracle ACE
    Currently Being Moderated
    Definescore is an 11g feature.
  • 36. Re: searching  in a materialized view
    949210 Newbie
    Currently Being Moderated
    the actual query is something like this
    select prod_details,sign,sign2,score(1) from drop_it_5 
    where contains( prod_details, '(xxxaerosol within S)*0+100 or (xxxaerosol within K)*0+80 or (xxxaerosol within C)*0+60
     or (aerosol within S)*0+40 or (aerosol within K)*0+20 or (aerosol within C)*0+10',1 ) > 0
    order by score(1) desc;
    a little bigger than the one i wrote above
    how about having one column with prod_details starting with xxx and another column prod_details2 verbatim
    and then having two contains clauses
    or
    doing a union all??
    the searches for 'S' can be made 100 and 'C' can be multiplied by 0.1 so that K will hover somewhere between S and C
    same can be done to prod_details2
    :
    :
    ??comments??

    Edited : dont understand why the second line of the contains clause does not show itself blue in colour

    Edited by: 946207 on Dec 23, 2012 1:48 AM
  • 37. Re: searching  in a materialized view
    949210 Newbie
    Currently Being Moderated
    Oracle® Text Reference
    10g Release 2 (10.2)
    Part Number B14218-01
    Scores are divided into ranges. In a two-term ACCUM, hits that match both terms will always score between 51 and 100, whereas hits matching only one of the terms >will score between 1 and 50. Likewise, for a three-term ACCUM, a hit matching one term will score between 1 and 33; a hit matching two terms will score between 34 >and 66, and a hit matching all three terms will score between 67 and 100. Within these ranges, normal scoring algorithms apply.
    will be trying with this also
  • 38. Re: searching  in a materialized view
    949210 Newbie
    Currently Being Moderated
    select prod_details,prod_details_act,sign,sign2,input,(
    CASE 
    WHEN LOWER(INPUT)=LOWER(SUBSTR(PROD_DETAILS_ACT,1,LENGTH(INPUT)))  THEN CASE
                                                                            WHEN SIGN2='S' THEN '1'
                                                                            WHEN SIGN2='K' THEN '2'
                                                                            ELSE '3'
                                                                            END
    when sign2='S' then '4'
    when sign2='K' then '5'
    ELSE '6' 
    END                                                                        
    ) as ordinal
    from
    (
    SELECT PROD_DETAILS,PROD_DETAILS_ACT,SIGN,SIGN2,'"+ prefixText +"' AS INPUT FROM DROP_IT_5 WHERE CONTAINS( PROD_DETAILS, '(xxx"+ prefixText +"% within S) or (xxx"+ prefixText +"% within K)
    or (xxx"+ prefixText +"% within C) or ("+ prefixText +"% within S) or ("+ prefixText +"% within K) or 
    ("+ prefixText +"% within C)',1 ) > 0 
    )order by ordinal
    where prod_details is a column with 'xxx' in the beginninng
    and prod_details_act is the data as it is

    one more
     SELECT * FROM 
    (
    SELECT * FROM 
    ( 
    select PROD_DETAILS_ACT,SIGN,SIGN2,SCORE(1) from DROP_IT_5 
    WHERE CONTAINS( PROD_DETAILS, '(xxxco% within S)*10*10 or (xxxco% within K)*5 
    or (xxxco% within C)*0.1',1 ) > 0 ORDER BY SCORE(1) DESC ) 
    UNION ALL 
    SELECT * FROM 
    (
    SELECT PROD_DETAILS_ACT,SIGN,SIGN2,SCORE(1) FROM DROP_IT_5 
    WHERE CONTAINS( PROD_DETAILS, '(co% within S)*10*10 or (co% within K)*5 or 
    (co% within C)*0.1',1 ) > 0 ORDER BY SCORE(1) DESC 
    ) 
    )where rownum<16
    this also works okay as far as accuracy is concerned
    not as good as progressive relaxation though(the two above are a few milliseconds slower than progressive relaxation-when tested for conditions in which a row with similar beginning exists for each S,K and C )

    Edited by: 946207 on Dec 26, 2012 12:38 PM
1 2 3 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points