Database Version 12.1.0.2.0
I created a MULTI_COLUMN_DATASTORE ctxsys.context on a very large table, ie:
ctx_ddl.create_preference('multi_store','MULTI_COLUMN_DATASTORE');
ctx_ddl.set_attribute('multi_store',
'columns',
'c1,c2,c3,c4'
);
CREATE INDEX "TEST"."CTX_SRCH_IDX" ON "TEST"."BIGTABLE" ("FIRSTNAME")
INDEXTYPE IS "CTXSYS"."CONTEXT" PARAMETERS ('DATASTORE multi_store section group ctxsys.auto_section_group sync (on commit)') ;
When I search, the results are blazing fast, ie:
select *
from
TEST.BIGTABLE t
where contains (t.FIRSTNAME, 'kenneth and band') > 0
This query would search 50 million records in 1 second, and return 11 results as expected. But whenever i add another limiting factor on another column (that has normal btree index, the 'filtered' result takes about 20 seconds, ie:
select *
from
TEST.BIGTABLE t
where contains (t.FIRSTNAME, 'kenneth and band') > 0
and t.user = 'JOHNSMITH'
I understand that the btree index on 't.user' is slower, but why wouldn't oracle prioritize the CTXSYS index, then filter THOSE results? It seems to trying to use the slower index first. Any help?