- by nw
The documentation for Oracle Text gives this example of a prefix/substring preference setting for context and catalog indexes:
begin
ctx_ddl.create_preference('mywordlist', 'BASIC_WORDLIST');
ctx_ddl.set_attribute('mywordlist','PREFIX_INDEX','TRUE');
ctx_ddl.set_attribute('mywordlist','PREFIX_MIN_LENGTH', '3');
ctx_ddl.set_attribute('mywordlist','PREFIX_MAX_LENGTH', '4');
ctx_ddl.set_attribute('mywordlist','SUBSTRING_INDEX', 'YES');
end;
What I need to know is whether the substring_index attribute is necessary if I only ever issue prefix searches, such as:
SELECT title FROM auction WHERE CATSEARCH(title, 'cam*', '') > 0;
TITLE
---------------
CANON CAMERA
FUJI CAMERA
NIKON CAMERA
OLYMPUS CAMERA
PENTAX CAMERA
SONY CAMERA
6 rows selected