Content

The following information has been added to the SQL Syntax Reference documentation in the FileNet P8 Content Engine Developer's Guide:

Full-Text Joins

Whenever a CONTAINS clause is used, a join is performed on the (internal) ContentSearch class.

NOTE Instances of the ContentSearch class cannot be retrieved. However, values for the properties of this class can be specified, and are defined in the metadata for this class. For more information, see ContentSearch Properties.

The join is necessary because when a query with a full-text search is executed, the full-text search is performed first, then the data from the full-text search is copied to a temporary database table that is referenced by the ContentSearch class name. The remainder of the search statement is then executed against the repository, joining to this temporary table to access the full-text search data.

Only one join to the ContentSearch class is allowed per query statement, because only one CONTAINS clause is allowed.

The following is an example of an inner join in a full-text search:

   SELECT … FROM Document D INNER JOIN ContentSearch CS ON D.This=CS.QueriedObject 
            WHERE CONTAINS(*,'text') AND D.Creator = 'Frank'

This query is executed by first evaluating the CONTAINS(*, 'text') clause and writing that data to a temporary table. Then the remainder of the query is executed:

   SELECT … FROM Document D INNER JOIN ContentSearch CS ON D.This=CS.QueriedObject 
            WHERE CS.QueriedObject IS NOT NULL AND D.Creator = 'Frank'

Note that the CONTAINS clause has been replaced by CS.QueriedObject IS NOT NULL.

In the previous example, the CONTAINS clause is used with an AND operator. An OR operator would yield confusing results due to the presence of the inner join (which is an effective AND). For this reason, an AND operator is required with an INNER JOIN, and an OR operator is disallowed.

An example of an outer join is:

   SELECT … FROM Document D LEFT OUTER JOIN ContentSearch CS ON D.This=CS.QueriedObject 
            WHERE CONTAINS(*,'text') OR D.Creator = 'Frank'

The outer join above first executes the CONTAINS clause and builds the temporary table in the database that holds the full-text search results, then executes the query:

   SELECT … FROM Document D LEFT OUTER JOIN ContentSearch CS ON D.This=CS.QueriedObject 
            WHERE CS.QueriedObject IS NOT NULL OR D.Creator = 'Frank'

When using outer joins, only the ContentSearch class can be joined conditionally. The query above would not be allowed to use a right outer join, because that would potentially return only ContentSearch data, which is not allowed. Outer joins must also use an OR operator when combining the CONTAINS clause with other conditions. Use of an AND operator here is disallowed.

ContentSummary Queries

FullTextRowLimit

A query might have a CONTAINS clause that matches many thousands of rows in the full-text index. However, a user might not want to pull all these rows into the temporary database table prior to running the remainder of the query. In this case, you can set FullTextRowLimit to a lesser value, enabling the user to see a subset of the matches more quickly.

Do not set the FullTextRowLimit too low. For example, suppose a query has the WHERE clause "WHERE color = 'red' and CONTAINS(*, 'blue')". This query might have a thousand rows that match "CONTAINS(*, 'blue')", but only ten rows that match "color = 'red'" and "CONTAINS(*, 'blue')". If FullTextRowLimit is set to 500, then only 500 rows are pulled from the full-text index and written to the temporary table. However, those 500 rows might not be the same rows that also have "color = 'red'" in the database, so all ten rows might not be found as a result of the query.

If a value for FullTextRowLimit is not supplied, the value of the ObjectStore.FullTextRowDefault property (stored in the GCD) is used.

If a value for FullTextRowLimit is supplied and this value is greater than the value of ObjectStore.FullTextRowMax, then the value of ObjectStore.FullTextRowMax is used instead. The FullTextRowMax property is present on the ObjectStore class, enabling the system administrator to prevent queries from using too much processing time. The ObjectStore.FullTextRowMax property defaults to infinity unless changed by the system administrator.

FullTextRowLimit and Optimized Queries

A full-text query is optimized if the following is true:

• The query is continuable.
• The query is ordered by descending ContentSearch.Rank value, or the query is unordered (without an ORDER BY clause).
• The query does not have a DISTINCT clause.
• The query uses an INNER JOIN to include the ContentSearch class.
• The query is not used in a merged scope object store search.

An optimized full-text query can return results faster in some instances, and will also allow the user to retrieve more rows without hitting the FullTextRowLimit value, as well as possible timeout errors or out of memory conditions.

If a full-text query is optimized, when a request for the first page is generated, the server retrieves the FullTextRowLimit number of rows from the full-text search engine and stores them in the temporary database table, then executes the relational part of the query. However, if an insufficient number of rows are found to fill the current page, the server retrieves the next set of rows from the full-text search engine, repeating the process until enough rows are found. When the server pulls the second and subsequent set of rows from the full-text search engine, the server has the percentage of retrieved rows that are actually used in the resultant data to be returned to the user, and therefore it no longer uses the FullTextRowLimit, and instead retrieves the number of rows based on the prior percentage used.

When the second or subsequent page is requested for an optimized full-text query, the server also retrieves the next set of rows from the full-text search engine. Therefore, this type of query can be used to browse through an unlimited number of rows that match a content search.

Optimization might not be suitable for all types of queries: ordering by descending ContentSearch.Rank slows the relational part of the query, and some callers might not want the data to be returned in descending order.

A continuable query that is not optimized will not continue past the number of rows specified by the value of FullTextRowLimit. When the query is not optimized, the server cannot retrieve the next set of full-text hits for subsequent pages past the FullTextRowLimit. Instead, the server retrieves the same amount of rows for each page processed, using the FullTextRowLimit value for the number of rows.

A continuable query that is not optimized will not continue past the number of rows specified by the value of FullTextRowLimit. When the query is not optimized, the server cannot retrieve the next set of full-text hits for subsequent pages past the FullTextRowLimit. Instead, the server retrieves the same set of rows for each page processed, using the FullTextRowLimit value for the number of rows.

FullTextRowLimit and Non-Optimized Queries

Queries that are not continuable pull only one set of a FullTextRowLimit number of rows from the full-text search engine. The value of FullTextRowLimit must be chosen carefully in this case.

Queries that are not optimized might return a subset of the number of rows matching the query if more than the FullTextRowLimit number of matches exists in the full-text index. If this condition occurs, the server throws a CBR_FULLTEXTROWLIMIT_EXCEEDED exception to notify the client that not all matches have been returned. This exception will be thrown when the caller iterates through the rows returned for the query, after the last row is found.

If a value for FullTextRowLimit is not supplied, the value used is the value of the ObjectStore.FullTextRowDefault property stored in the Global Configuration Data (GCD) database.

If a value for FullTextRowLimit is supplied and this value is greater than the value of the ObjectStore.FullTextRowMax property, the ObjectStore.FullTextRowMax value is used instead. The FullTextRowMax property enables system administrators to prevent queries from using too much processing time.