Home » Server Options » Text & interMedia » SUBSTRING_INDEX (Oracle 9.2)
SUBSTRING_INDEX [message #447630] Tue, 16 March 2010 21:18 Go to next message
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.
Re: SUBSTRING_INDEX [message #447634 is a reply to message #447630] Tue, 16 March 2010 23:40 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Quote:

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 ?


No, not that I know of.

Quote:

2. If not, what other strategies can i explore.


Require your users to input strings of at least three characters.

Quote:

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 ?


You can check the length of the parameter input by the user and, if it is less than three characters, then treat is as an exception and advise the user that each string of characters must be at least three characters long.

Previous Topic: Text Index Privileges
Next Topic: ultra search
Goto Forum:
  


Current Time: Thu Mar 28 16:55:45 CDT 2024