Home » Server Options » Text & interMedia » SUBSTRING_INDEX (Oracle 9.2)
SUBSTRING_INDEX [message #447630] |
Tue, 16 March 2010 21:18  |
ora22
Messages: 8 Registered: December 2009 Location: BOSTON
|
Junior Member |
|
|
I have an application that needs to support prefix and suffix wildcards
i have a preference set as follows:
begin
ctx_ddl.create_preference('t_x_wdl','BASIC_WORDLIST');
ctx_ddl.set_attribute('t_x_wdl','PREFIX_INDEX','TRUE');
ctx_ddl.set_attribute('t_x_wdl','PREFIX_MIN_LENGTH','3');
ctx_ddl.set_attribute('t_x_wdl','PREFIX_MAX_LENGTH','5');
ctx_ddl.set_attribute('t_x_wdl','SUBSTRING_INDEX', 'YES');
end;
that i use when creating the context index
CREATE INDEX GCA_IDX_ALLCOLS ON GCP_CCT_AGG
(CONCAT)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('DATASTORE ctxsys.gcp_multi_column_datastore
SECTION GROUP gcp_section_group
WORDLIST t_x_wdl
STORAGE CTX_STORE_TBS')
PARALLEL ( DEGREE 3 INSTANCES 1 );
this works well as long as there are atlease 3 characters after the wildcard, for example
SELECT *
FROM gcp_cct_agg agg
WHERE 1 = 1 and contains ( concat,' (( %mat ) within the_entity_duns_search_name) ',1 ) > 0
works fine and i results in < 5 secons
however if there are only two characters after the wildcard such as :
SELECT *
FROM gcp_cct_agg agg
WHERE 1 = 1 and contains ( concat,' (( %at ) within the_entity_duns_search_name) ',1 ) > 0
the query takes minutes !
As i was investigating i realized that in the $P table that is created to support SUBSTRING_INDEX, the PART2 column has a minimum length of 3.
My question is three part
1. can one control the minimum lenhth of PART2 in the $P Table. Something akin to 'PREFIX_MIN_LENGTH' and 'PREFIX_MAX_LENGTH' but for teh SUBSTRING_INDEX ?
2. If not, what other strategies can i explore.
3. Since it works so slow the users thing that the system is hanging and close the browser. If its not possible to make it perform faster I'd rather have it raise an exception - so that the user can be informed. Is that possible ?
Would greatly appreciate your inputs.
|
|
|
|
Goto Forum:
Current Time: Sat Feb 22 00:57:49 CST 2025
|