your database front-end

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'.
  • 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.

Single Column Index

Creating an Oracle Text Index on a single column is as simple as running:

CREATE INDEX myindex ON docs(text) INDEXTYPE IS CTXSYS.CONTEXT;

Multi-Column Index

Oracle does not permit multiple Oracle Text indexes on a single table to be queried using a single request.  This means a query that is looking for multiple terms must find all of those terms in a single index or column.  This can be partially resolved by merging multiple columns into a single index as described below.  The following statements allow multiple columns to be combined into a single index.  Please review the linked article to gain a full understanding:

BEGIN
          ctx_ddl.create_preference('my_multi', 'MULTI_COLUMN_DATASTORE');
          ctx_ddl.set_attribute('my_multi', 'columns', 'column1, column2, column3');
END;
CREATE INDEX myindex ON mytable(docs)
          indextype IS ctxsys.context
          parameters ('DATASTORE my_multi');

One unfortunate side-effect of combining columns is that only the primary column will appear to be indexed.  A second side-effect is that it can become difficult to run a query on a single column when required.

This can potentially be resolved by creating multiple indexes covering the same columns.

Content you want the user to see goes here.
close