Hi Tom,
Can you explain the true function for the parameters PREFIX_MIN_LENGTH & PREFIX_MAX_LENGTH when creating sub-string & prefix TEXT indexes?

It seems that no matter what values I set for these parameters, the same entries and number of entries appear in the corresponding $P table for both columns PAT_PART1 & PAT_PART2. Based on the Oracle docs, I would have expected tokens between 3 and 4 characters assuming I set PREFIX_MIN_LENGTH to 3 and PREFIX_MAX_LENGTH to 4. Maybe it's my confusion on the intent of these parameters or how they are applied?

I'm hoping to allow a user to search on at least 3 characters and minimize the size of the resulting index.

SQL> select * from v$version;

BANNER
-----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL>
SQL> begin
  2    ctx_ddl.create_preference('mywordlist', 'BASIC_WORDLIST');
  3    ctx_ddl.set_attribute('mywordlist','PREFIX_INDEX','TRUE');
  4    ctx_ddl.set_attribute('mywordlist','PREFIX_MIN_LENGTH', '3');
  5    ctx_ddl.set_attribute('mywordlist','PREFIX_MAX_LENGTH', '4');
  6    ctx_ddl.set_attribute('mywordlist','SUBSTRING_INDEX', 'YES');
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL>
SQL> create table test1 (a1 number, a2 varchar2(200));

Table created.

SQL> insert into test1 values (1, 'warehousing');

1 row created.

SQL>
SQL> CREATE INDEX myindex on test1(a2)
  2  indextype is ctxsys.context
  3  parameters('wordlist mywordlist');

Index created.

SQL> select * from test1
  2  where contains(a2, '%hou%') > 0;

        A1 A2
---------- -------------------------
         1 warehousing

SQL> select * from dr$myindex$p order by length(pat_part1);

PAT_PART1    PAT_PART2
------------ ------------
W            AREHOUSING
             WAREHOUSING
WA           REHOUSING
WAR          EHOUSING
WARE         HOUSING
WAREH        OUSING
WAREHO       USING
WAREHOU      SING
WAREHOUS     ING

9 rows selected.

SQL>
SQL> begin
  2    ctx_ddl.drop_preference('mywordlist');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> begin
  2    ctx_ddl.create_preference('mywordlist', 'BASIC_WORDLIST');
  3    ctx_ddl.set_attribute('mywordlist','PREFIX_INDEX','TRUE');
  4    ctx_ddl.set_attribute('mywordlist','PREFIX_MIN_LENGTH', '2');
  5    ctx_ddl.set_attribute('mywordlist','PREFIX_MAX_LENGTH', '5');
  6    ctx_ddl.set_attribute('mywordlist','SUBSTRING_INDEX', 'YES');
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> drop index myindex;

Index dropped.

SQL> CREATE INDEX myindex on test1(a2)
  2  indextype is ctxsys.context
  3  parameters('wordlist mywordlist');

Index created.

SQL> select * from dr$myindex$p order by length(pat_part1);

PAT_PART1    PAT_PART2
------------ ------------
W            AREHOUSING
             WAREHOUSING
WA           REHOUSING
WAR          EHOUSING
WARE         HOUSING
WAREH        OUSING
WAREHO       USING
WAREHOU      SING
WAREHOUS     ING

9 rows selected.

Thanks.

ops$tkyte%ORA11GR2> drop table test1 purge;

Table dropped.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> begin
  2    ctx_ddl.drop_preference('mywordlist');
  3  end;
  4  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> begin
  2    ctx_ddl.create_preference('mywordlist', 'BASIC_WORDLIST');
  3    ctx_ddl.set_attribute('mywordlist','PREFIX_INDEX','TRUE');
  4    ctx_ddl.set_attribute('mywordlist','PREFIX_MIN_LENGTH', '3');
  5    ctx_ddl.set_attribute('mywordlist','PREFIX_MAX_LENGTH', '4');
  6    --ctx_ddl.set_attribute('mywordlist','SUBSTRING_INDEX', 'YES');
  7  end;
  8  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create table test1 (a1 number, a2 varchar2(200));

Table created.

ops$tkyte%ORA11GR2> insert into test1 values (1, 'warehousing');

1 row created.

ops$tkyte%ORA11GR2> CREATE INDEX myindex on test1(a2)
  2  indextype is ctxsys.context
  3  parameters('wordlist mywordlist');

Index created.

ops$tkyte%ORA11GR2> select token_text from DR$MYINDEX$I;

TOKEN_TEXT
----------------------------------------------------------------
WAR
WARE
WAREHOUSING

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> drop table test1 purge;

Table dropped.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> begin
  2    ctx_ddl.drop_preference('mywordlist');
  3  end;
  4  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> begin
  2    ctx_ddl.create_preference('mywordlist', 'BASIC_WORDLIST');
  3    ctx_ddl.set_attribute('mywordlist','PREFIX_INDEX','TRUE');
  4    ctx_ddl.set_attribute('mywordlist','PREFIX_MIN_LENGTH', '5');
  5    ctx_ddl.set_attribute('mywordlist','PREFIX_MAX_LENGTH', '7');
  6    --ctx_ddl.set_attribute('mywordlist','SUBSTRING_INDEX', 'YES');
  7  end;
  8  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create table test1 (a1 number, a2 varchar2(200));

Table created.

ops$tkyte%ORA11GR2> insert into test1 values (1, 'warehousing');

1 row created.

ops$tkyte%ORA11GR2> CREATE INDEX myindex on test1(a2)
  2  indextype is ctxsys.context
  3  parameters('wordlist mywordlist');

Index created.

ops$tkyte%ORA11GR2> select token_text from DR$MYINDEX$I;

TOKEN_TEXT
----------------------------------------------------------------
WAREH
WAREHO
WAREHOU
WAREHOUSING