1 2 3 Previous Next 38 Replies Latest reply: Dec 26, 2012 1:08 AM by 949210 Go to original post RSS
      • 30. Re: searching  in a materialized view
        Roger Ford-Oracle
        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
          is there an option to create multicolumn datastore in ctxcat index?
          probably not
          • 32. Re: searching  in a materialized view
            949210
            ---
            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
              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
                  
                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
                  Definescore is an 11g feature.
                  • 36. Re: searching  in a materialized view
                    949210
                    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
                      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
                        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