This content has been marked as final. Show 38 replies
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.
is there an option to create multicolumn datastore in ctxcat index?
this was required to mark the question as unanswered
Edited by: 946207 on Dec 21, 2012 7:31 PM
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.
can i use definescore to return score how i want it to?
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
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
Definescore is an 11g feature.
the actual query is something like this
a little bigger than the one i wrote above
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;
how about having one column with prod_details starting with xxx and another column prod_details2 verbatim
and then having two contains clauses
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
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
Oracle® Text Referencewill be trying with this also
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.
where prod_details is a column with 'xxx' in the beginninng
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
and prod_details_act is the data as it is
this also works okay as far as accuracy is concerned
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
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