Hi Team,
I'm doing some Oracle Text work related to prefix_index and substring_index (we use Oracle 11g).
I'm interested in why:
(1)Prefix indexing improves performance for right truncated wildcard searches such as TO%
(2)A substring index improves left-truncated and double-truncated wildcard queries such as %ing or %benz%

Could you help to check whether my following understanding is right or wrong? Thanks!
(Maybe the following understanding and questions are not very clear. I just want to know the search logic and process of queries like %abc, abc%, %abc% using prefix_index and substring_index by Oracle Text)

(1) I want to confirm principles of extracting tokens using Oracle Text.
After we extract tokens from text (tokens are stored in table $I), keywords that user inputs to query will be extract into tokens, too. If at least one keyword token is equal to text token, then user can get matched text.
For example, text1 is extracted into token1 and token2 (stored in table $I). Then user inputs some keywords to query. Keywords are extracted into token2 and token3, since token2 is stroed in $I, user can get search result containing text1.
However, if keywords are extracted into token3 and token4. Suppose that token3 is prefix of token2, user still can't get search result containing text1 because token2 != token3.
Am I understanding right or wrong? Thanks.

(2)When we use prefix_index (and set prefix_length_min=2, prefix_length_max=3), some prefix tokens （token_type = 6 ) will be stored in $I. When we use right truncated wildcard searches, for example:
(a) Search to%. Length of "to" is 2. So it will directly search in tokens (only token_type=6) in $I table. If token "to" is in $I and its token_type is 6, then user can get search results matching to%. If token "to" is not in $I, then search result is empty.
But we find that sometimes one prefix token (token_type = 6) is stored in $I table (e.g. this token is "站起"). Then we search 站起%, the search result is empty. Do you know the reason? Thanks.
(b) Search toto%. Length of "toto" is 4 (prefix_legth_max is 3). So it will directly search every token (token_type = 0) in $I table.
Am I understanding right or wrong? Thanks.

(3)When we use substring_index, some substring tokens will be stored in $P. When we use left truncated wildcard searches, for example:
(a) Search %abc, then it will directly search in $P table using column PAT_PART2. If PAT_PART2 has token abc, then user can get search results matching %abc. If PAT_PART2 hasn't token abc, then search result is empty.
(b) Search %a (length of "a" is 1), since length of all tokens in $P table in column PAT_PART2 is larger than 1, so it will directly search every token (token_type = 0) in $I table.
Am I understanding right or wrong? Thanks.
And I want to know that why using substring_index can improves double-truncated wildcard queries like %benz% (I understand substring_index can improve left-truncated wildcard queries, but I want to know the process and principle of querying %benz% by Oracle Text. Could you help to explain it, thanks!)

PAT_PART1 PAT_PART2
--------- ------------
          FOOBARBAZ
F          OOBARBAZ
FO          OBARBAZ
FOO          BARBAZ
FOOB          ARBAZ
FOOBA          RBAZ
FOOBAR          BAZ

SQL> SELECT pat_part1||pat_part2 FROM dr$fooindex$p WHERE pat_part2 LIKE 'BAR%';

PAT_PART1||PAT_PART2
--------------------------------------------------------------------------------
FOOBARBAZ