Oracle Full-Text Search (Enterprise)
Users of the Enterprise version of dbFront 1.1.3 have direct access to the Full-Text search functionality found in MySQL, Oracle and SQL Server.
Oracle Text searches can be run in three different modes: Default, Like (L:) and Contains (C:).
To use Full-Text search in Oracle, specialized indexes must be created. See Creating Oracle Text Indexes below for instructions.
Contains Full-Text Search
As soon as dbFront sees any advanced search operators then it will pass the query directly to Oracle Text.
The following is a small subset of the Oracle Text functionality. For more details see the links below.
- AND ( & ): Specify that both words must be found. e.g. 'John AND Smith' returns all records having both "John" and "Smith".
- OR ( | ): Specify that both words must be found. e.g. 'John OR Smith' returns all records having either "John" or "Smith". The OR pattern can easily be used to return records that match multiple values, e.g. 'Red | Blue | Green | Yellow'.
- ACCUMulate ( , ): Rank results that contain the widest representation of the specified keywords. e.g. A query for 'Red, Blue, Green, Yellow' returns all rows that contain any of the specified keywords, but gives preference to results that contains the widest representation of the specified keywords. See: Oracle ACCUMulate
- NEAR ( ; ): Specify that two words must be found near each other. e.g. 'John NEAR Smith' returns all records having "John" and "Smith" in the same field or document. An alternate syntax of 'NEAR((John, Smith), 3)' specifies that there can't be any more than three other words between "John" and "Smith".
- NOT ( ~ ): Specify that the second word must NOT be found. e.g. 'John NOT Smith' returns all records having "John" and not "Smith".
- Wildcards ( %, _ ): Return all rows containing words using the specified Wildcard. e.g ' John% AND Smith' will return all rows that contain "Smith" and some form of the name "John", such as "John", "Johnny", "Johnson", etc.
- $, Prefixing a word with $ causes Oracle to stem the word.
- (, ): Bracket parts of a search expression. e.g. '(Red | Blue | Green | Yellow) AND NOT Black'.
Creating Oracle Text indexes
Oracle Text supports adding Context indexes to the following types: CHAR, VARCHAR, VARCHAR2, BLOB, CLOB, BFILE, XMLType, or URIType.
Creating an Oracle Text Index on a column is as simple as running:
CREATE INDEX myindex ON docs(text) INDEXTYPE IS CTXSYS.CONTEXT;