Skip to Main Content

composite index

544237Mar 31 2010 — edited Apr 2 2010
Hi gurus,
I wantto create a composite index just like whta I do in regular queries. In Oracle TEXT is multi-datastore the only way to do this. But it is not exactly what I want. Because have merged a few columns then I cannot distinguish the indivdual fields so I got more rows returned.
Any help will be appreciated.

This was what I did :

BEGIN
ctx_ddl.create_preference('my_multi', 'MULTI_COLUMN_DATASTORE');
ctx_ddl.set_attribute('my_multi', 'columns', 'referrer, urlfield');
CTX_DDL.CREATE_PREFERENCE ('my_lexer', 'BASIC_LEXER');
CTX_DDL.SET_ATTRIBUTE ('my_lexer', 'PRINTJOINS', '%');
CTX_DDL.CREATE_PREFERENCE ('my_wordlist', 'BASIC_WORDLIST');
CTX_DDL.SET_ATTRIBUTE ('my_wordlist', 'WILDCARD_MAXTERMS', '15000');
END;


CREATE INDEX my_idx on cfm.cfm_tealeaf_msgs_t2 (url) indextype is ctxsys.context
PARAMETERS
('LEXER my_lexer
WORDLIST my_wordlist
datastore my_multi')
/


And my query before using TEXT:
select
XXXXX, XXXX
from a
where a.pagets > 1250200613000 and a.pagets < 1268244540000
and a.url like '%2fwi%2fcontroller%'
and (a.referrer like '%InitiateDom%' or a.referrer like '%InitiateInt%' ) and urlfield not like '%password%'
;

For TEXT:

select
XXXXXX , XXXX, score(0)
from a
where
a.pagets > 1250200613000 and a.pagets < 1268244540000 and
contains(a.url , '%2fwi% and %2fcontroller%',1) >0 or
contains(a.url, '%InitiateDom% | %InitiateInt%',0 )> 0
and contains (url , '%password%', 2)=0
;

and it is a lot (7 times) slower.
Comments
Post Details
Added on Mar 31 2010
4 comments
1,535 views