The SearchSQL
class provides helper methods to construct the SQL statement, or you can pass an existing SQL statement in to a SearchSQL
instance. The helper methods are supplied for assistance in building SQL statements, and cannot provide the level of specification you can achieve with an independently-constructed statement.
Note: You cannot combine these two manners of construction. The SQL statement must be specified in its entirety using either the SearchSQL helper methods or an independently-constructed statement. However, for the purposes of development, you can use the helper methods to build the SQL statement, then use the SearchSQL.toString
method to get the SQL statement string and further refine the SQL statement manually before passing it to SearchSQL.setQueryString
.
The general sequence for constructing a SQL statement using the SearchSQL
methods is as follows:
Java™ Example
// Create the SearchSQL object. SearchSQL sqlObject = new SearchSQL(); // (Optional) Specify the maximum number of records to be returned. This defaults to the // value of ServerCacheConfiguration.NonPagedQueryMaxSize, if unspecified. sqlObject.setMaxRecords(150); // Specify the SELECT list using the setSelectList method. String select = "r.Title, s.Title"; sqlObject.setSelectList(select); // Specify the FROM clause using the setFromClauseInitialValue method. // Symbolic name of class. String myClassName1 = "Requirement"; // Alias name. String myAlias1 = "r"; // Indicates whether subclasses are included. boolean subclassesToo = false; sqlObject.setFromClauseInitialValue(myClassName1, myAlias1, subclassesToo); // For joins, specify an additional FROM clause using the setFromClauseAdditionalJoin // method. Symbolic name of class. String myClassName2 = "Specification"; // Alias name. String myAlias2 = "s"; // The property on the class specified for the setFromClauseInitialValue // method. This is one constituent in the underlying ON clause. The other constituent is // prop2. String prop1 = "s.ApplicationName"; // The property on the class specified for this method (myClassName2). This is the other // constituent of the underlying ON clause for the join. String prop2 = "r.ApplicationName"; // Indicates whether subclasses are included. subclassesToo = false; sqlObject.setFromClauseAdditionalJoin( JoinOperator.INNER, myClassName2, myAlias2, prop1, JoinComparison.EQUAL, prop2, subclassesToo); // Specify the WHERE clause using the setWhereClause method. String whereClause = "r.Title LIKE '%P8%'"; sqlObject.setWhereClause(whereClause); // Specify the ORDER BY clause using the setOrderByClause method. String orderClause = "r.Title"; sqlObject.setOrderByClause(orderClause); // Check the SQL statement constructed. System.out.println("SQL: " + sqlObject.toString()); // Create a SearchScope instance and test the SQL statement. SearchScope searchScope = new SearchScope(os); // Uses fetchRows to test the SQL statement. RepositoryRowSet rowSet = searchScope.fetchRows(sqlObject, null, null, new Boolean(true));
C# Example
// Create the SearchSQL object. SearchSQL sqlObject = new SearchSQL(); // (Optional) Specify the maximum number of records to be returned. This defaults to the // value of ServerCacheConfiguration.NonPagedQueryMaxSize, if unspecified. sqlObject.SetMaxRecords(150); // Specify the SELECT list using the setSelectList method. string select = "r.Title, s.Title"; sqlObject.SetSelectList(select); // Specify the FROM clause using the setFromClauseInitialValue method. // Symbolic name of class. string myClassName1 = "Requirement"; // Alias name. string myAlias1 = "r"; // Indicates whether subclasses are included. bool subclassesToo = false; sqlObject.SetFromClauseInitialValue(myClassName1, myAlias1, subclassesToo); // For joins, specify an additional FROM clause using the setFromClauseAdditionalJoin // method. Symbolic name of class. string myClassName2 = "Specification"; // Alias name. string myAlias2 = "s"; // The property on the class specified for the setFromClauseInitialValue // method. This is one constituent in the underlying ON clause. The other constituent is // prop2. string prop1 = "s.ApplicationName"; // The property on the class specified for this method (myClassName2). This is the other // constituent of the underlying ON clause for the join. string prop2 = "r.ApplicationName"; // Indicates whether subclasses are included. subclassesToo = false; sqlObject.SetFromClauseAdditionalJoin( JoinOperator.INNER, myClassName2, myAlias2, prop1, JoinComparison.EQUAL, prop2, subclassesToo); // Specify the WHERE clause using the setWhereClause method. string whereClause = "r.Title LIKE '%P8%'"; sqlObject.SetWhereClause(whereClause); // Specify the ORDER BY clause using the setOrderByClause method. string orderClause = "r.Title"; sqlObject.SetOrderByClause(orderClause); // Check the SQL statement constructed. System.Console.WriteLine("SQL: " + sqlObject.ToString()); // Create a SearchScope instance and test the SQL statement. SearchScope searchScope = new SearchScope(os); // Uses fetchRows to test the SQL statement. IRepositoryRowSet rowSet = searchScope.FetchRows(sqlObject, null, null, true);
Refer to the Searching for Database Rows example code for an illustration of iterating through the RepositoryRowSet.
The SearchSQL
instance (sqlObject) can then be specified in the SearchScope
parameter list to execute the search. See SQL Syntax Reference for detailed information about SQL statement syntax.
A SQL statement that conforms to IBM FileNet standards can be passed to SearchSQL in string format. You can use either the constructor SearchSQL(String)
, or the setQueryString
method.
Java Example
// The SQL statement "SELECT DocumentTitle Id FROM Document WHERE DocumentTitle LIKE // '%Acct%'" retrieves documents by ID (GUID) that have a document // title containing the character pattern "Acct". // You could store this statement in a String variable and pass it to the // setQueryString method, similar to this: String mySQLString = "SELECT DocumentTitle, Id FROM Document WHERE DocumentTitle LIKE '%Acct%'"; SearchSQL sqlObject = new SearchSQL(); sqlObject.setQueryString(mySQLString); // Alternatively, you could use the SearchSQL(String) constructor: // String mySQLString = "SELECT DocumentTitle, Id FROM Document WHERE DocumentTitle // LIKE '%Acct%'"; // SearchSQL sqlObject = new SearchSQL(mySQLString); // The SearchSQL instance (sqlObject) can then be specified in the // SearchScope parameter list to execute the search. Uses fetchRows to test the SQL // statement. SearchScope searchScope = new SearchScope(os); RepositoryRowSet rowSet = searchScope.fetchRows(sqlObject, null, null, new Boolean(true));
C# Example
// The SQL statement "SELECT DocumentTitle Id FROM Document WHERE DocumentTitle LIKE // '%Acct%'" retrieves documents by ID (GUID) that have a document // title containing the character pattern "Acct". // You could store this statement in a String variable and pass it to the // SetQueryString method, similar to this: String mySQLString = "SELECT DocumentTitle, Id FROM Document WHERE DocumentTitle LIKE '%Acct%'"; SearchSQL sqlObject = new SearchSQL(); sqlObject.SetQueryString(mySQLString); // The SearchSQL instance (sqlObject) can then be specified in the // SearchScope parameter list to execute the search. Uses fetchRows to test the SQL // statement. SearchScope searchScope = new SearchScope(os); IRepositoryRowSet rowSet = searchScope.FetchRows(sqlObject, null, null, true);
Refer to the Searching for Database Rows example code for an illustration of iterating through the RepositoryRowSet.
See SQL Syntax Reference for detailed information about SQL statement syntax.
You can include content searches (full-text searches) in queries constructed using either the SearchSQL
helper methods or independently-constructed SQL statements. The CONTAINS and FREETEXT operators perform the content search functions. CONTAINS is used for explicit or fuzzy full-text queries, and can search for content in all CBR-enabled properties on the object, or a single CBR-enabled property. The FREETEXT function is used for inexact, semantic-based full-text queries, and searches all CBR-enabled properties on the object only, so the first argument is always "*". The second argument is a filter expression.
See Content Searches and Full-Text Queries for more information.
To specify a content search with the SearchSQL
helper methods, use setContainsRestriction
and setFreetextRestriction
. The following example uses setContainsRestriction
to search for the specified content in all IsCBREnabled
properties of the Document class:
Note: The setContainsRestriction
helper method does not provide the same level of functionality as the CONTAINS operator can in an independently-constructed SQL statement. The setContainsRestriction
method does not accept a property parameter to constrain the full-text search to a specific property.
Java Example
SearchSQL sqlObject = new SearchSQL(); String myClassName = "Document"; sqlObject.setSelectList("DocumentTitle"); sqlObject.setFromClauseInitialValue(myClassName, null, false); String containsExpression = "'FileNet'"; sqlObject.setContainsRestriction(myClassName, containsExpression); // Displays the SQL statement. System.out.println("SQL: " + sqlObject.toString()); // Executes the content search. SearchScope searchScope = new SearchScope(os); RepositoryRowSet rowSet = searchScope.fetchRows(sqlObject, null, null, new Boolean(true));
C# Example
SearchSQL sqlObject = new SearchSQL(); string myClassName = "Document"; sqlObject.SetSelectList("DocumentTitle"); sqlObject.SetFromClauseInitialValue(myClassName, null, false); string containsExpression = "'FileNet'"; sqlObject.SetContainsRestriction(myClassName, containsExpression); // Displays the SQL statement. System.Console.WriteLine("SQL: " + sqlObject.ToString()); // Executes the content search. SearchScope searchScope = new SearchScope(os); IRepositoryRowSet rowSet = searchScope.FetchRows(sqlObject, null, null, true);
Refer to the Searching for Database Rows example code for an illustration of iterating through the RepositoryRowSet.
To specify a content search in an independently-constructed SQL statement, add the CONTAINS or FREETEXT functions within the WHERE clause. Only one CONTAINS or FREETEXT function (and not both) can be used in a single SQL statement.
The following examples illustrate the different ways these content searches can be constructed:
In this example, only content in the AccountName property will be searched (assuming IsCBREnabled is true for this property).
Java Example
String mySQLString = "SELECT DocumentTitle Id FROM Document d " + "INNER JOIN ContentSearch cs ON d.This = cs.QueriedObject " + "WHERE CONTAINS(Name, 'FileNet')"; SearchSQL sqlObject = new SearchSQL(mySQLString); // Executes the content search. SearchScope searchScope = new SearchScope(os); RepositoryRowSet rowSet = searchScope.fetchRows(sqlObject, null, null, new Boolean(true));
C# Example
String mySQLString = "SELECT DocumentTitle Id FROM Document d " + "INNER JOIN ContentSearch cs ON d.This = cs.QueriedObject " + "WHERE CONTAINS(Name, 'FileNet')"; SearchSQL sqlObject = new SearchSQL(mySQLString); // Executes the content search. SearchScope searchScope = new SearchScope(os); IRepositoryRowSet rowSet = searchScope.FetchRows(sqlObject, null, null, true);
In this example, the content of all properties on the object will be searched for a semantic correlation to "company" (assuming IsCBREnabled is true for all of these properties).
Java Example
String mySQLString = "SELECT DocumentTitle Id FROM Document d " + "INNER JOIN ContentSearch cs ON d.This = cs.QueriedObject " + "WHERE FREETEXT(*, 'company')"; SearchSQL sqlObject = new SearchSQL(mySQLString); // Executes the content search. SearchScope searchScope = new SearchScope(os); RepositoryRowSet rowSet = searchScope.fetchRows(sqlObject, null, null, new Boolean(true));
C# Example
String mySQLString = "SELECT DocumentTitle Id FROM Document d " + "INNER JOIN ContentSearch cs ON d.This = cs.QueriedObject " + "WHERE FREETEXT(*, 'company')"; SearchSQL sqlObject = new SearchSQL(mySQLString); // Executes the content search. SearchScope searchScope = new SearchScope(os); IRepositoryRowSet rowSet = searchScope.FetchRows(sqlObject, null, null, true);
Any of the SearchScope
methods can be used to search multiple repositories. The general sequence for specifying multiple repositories is as follows:
Java Example
// Create the ObjectStore array required as the initial SearchScope // parameter. (Assumes you have the object store objects.) ObjectStore[] osArray = new ObjectStore[]{os1,os2}; // Create the SearchScope instance using the constructor for multiple object // stores, and specifying the merge mode. SearchScope searchMultiple = new SearchScope(osArray, MergeMode.INTERSECTION);
C# Example
// Create the ObjectStore array required as the initial SearchScope // parameter. (Assumes you have the object store objects.) IObjectStore[] osArray = new IObjectStore[]{os1,os2}; SearchScope searchMultiple = new SearchScope(osArray, MergeMode.INTERSECTION);
See Merge Mode for more information about merge mode settings.
Refer to Searching Multiple Object Stores for more information.
Use the SearchScope.fetchObjects
method to search for objects in a repository. The fetchObjects
method returns a collection of IndependentObject
objects. The general sequence for performing a query for objects is as follows:
Java Example
// Create a SearchSQL instance and specify the SQL statement (using the // helper methods). SearchSQL sqlObject = new SearchSQL(); sqlObject.setSelectList("d.DocumentTitle, d.Id"); sqlObject.setMaxRecords(20); sqlObject.setFromClauseInitialValue("Document", "d", false); // Check the SQL statement. System.out.println("SQL: " + sqlObject.toString()); // Create a SearchScope instance. (Assumes you have the object store // object.) SearchScope search = new SearchScope(os); // Set the page size (Long) to use for a page of query result data. This value is passed // in the pageSize parameter. If null, this defaults to the value of // ServerCacheConfiguration.QueryPageDefaultSize. Integer myPageSize = new Integer(100); // Specify a property filter to use for the filter parameter, if needed. // This can be null if you are not filtering properties. PropertyFilter myFilter = new PropertyFilter(); int myFilterLevel = 1; myFilter.setMaxRecursion(myFilterLevel); myFilter.addIncludeType(new FilterElement(null, null, null, FilteredPropertyType.ANY, null)); // Set the (Boolean) value for the continuable parameter. This indicates // whether to iterate requests for subsequent pages of result data when the end of the // first page of results is reached. If null or false, only a single page of results is // returned. Boolean continuable = new Boolean(true); // Execute the fetchObjects method using the specified parameters. IndependentObjectSet myObjects = search.fetchObjects(sqlObject, myPageSize, myFilter, continuable);
C# Example
// Create a SearchSQL instance and specify the SQL statement (using the // helper methods). SearchSQL sqlObject = new SearchSQL(); sqlObject.SetSelectList("d.DocumentTitle, d.Id"); sqlObject.SetMaxRecords(20); sqlObject.SetFromClauseInitialValue("Document", "d", false); // Check the SQL statement. System.Console.WriteLine("SQL: " + sqlObject.ToString()); // Create a SearchScope instance. (Assumes you have the object store // object.) SearchScope search = new SearchScope(os); // Set the page size (Long) to use for a page of query result data. This value is passed // in the pageSize parameter. If null, this defaults to the value of // ServerCacheConfiguration.QueryPageDefaultSize. int myPageSize = 100; // Specify a property filter to use for the filter parameter, if needed. // This can be null if you are not filtering properties. PropertyFilter myFilter = new PropertyFilter(); int myFilterLevel = 1; myFilter.SetMaxRecursion(myFilterLevel); myFilter.AddIncludeType(new FilterElement(null, null, null, FilteredPropertyType.ANY, null)); // Set the (boolean) value for the continuable parameter. This indicates // whether to iterate requests for subsequent pages of result data when the end of the // first page of results is reached. If null or false, only a single page of results is // returned. bool continuable = true; // Execute the fetchObjects method using the specified parameters. IIndependentObjectSet myObjects = search.FetchObjects(sqlObject, myPageSize, myFilter, continuable);
See Constructing a SQL Statement for the general sequence and examples.
See Searching Multiple Repositories for the sequence and examples when searching multiple object stores.
Use the SearchScope.fetchRows
method to search rows in the Content Engine database. The fetchRows
method returns a collection of RepositoryRow
objects. The general sequence for performing a query for database rows is as follows:
Java Example
// Create a SearchSQL instance and specify the SQL statement (using the helper methods). SearchSQL sqlObject = new SearchSQL(); sqlObject.setSelectList("d.DocumentTitle, d.Id"); sqlObject.setMaxRecords(20); sqlObject.setFromClauseInitialValue("Document", "d", false); // Check the SQL statement. System.out.println("SQL: " + sqlObject.toString()); // Create a SearchScope instance. (Assumes you have the object store object.) SearchScope search = new SearchScope(os); // Set the page size (Long) to use for a page of query result data. This value is passed // in the pageSize parameter. If null, this defaults to the value of // ServerCacheConfiguration.QueryPageDefaultSize. Integer myPageSize = new Integer(100); // Specify a property filter to use for the filter parameter, if needed. // This can be null if you are not filtering properties. PropertyFilter myFilter = new PropertyFilter(); int myFilterLevel = 1; myFilter.setMaxRecursion(myFilterLevel); myFilter.addIncludeType(new FilterElement(null, null, null, FilteredPropertyType.ANY, null)); // Set the (Boolean) value for the continuable parameter. This indicates // whether to iterate requests for subsequent pages of result data. Boolean continuable = new Boolean(true); // Execute the fetchRows method using the specified parameters. RepositoryRowSet myRows = search.fetchRows(sqlObject, myPageSize, myFilter, continuable); // You can then iterate through the collection of rows to access the properties. int rowCount = 0; Iterator iter = myRows.iterator(); while (iter.hasNext()) { RepositoryRow row = (RepositoryRow) iter.next(); String docTitle = row.getProperties().get("DocumentTitle").getStringValue(); Id docId = row.getProperties().get("Id").getIdValue(); rowCount++; System.out.print(" row " + rowCount + ":"); System.out.print(" Id=" + docId.toString()); if (docTitle != null) { System.out.print(" DocumentTitle=" + docTitle); } System.out.println(); }
C# Example
// Create a SearchSQL instance and specify the SQL statement (using the helper methods). SearchSQL sqlObject = new SearchSQL(); sqlObject.SetSelectList("d.DocumentTitle, d.Id"); sqlObject.SetMaxRecords(20); sqlObject.SetFromClauseInitialValue("Document", "d", false); // Check the SQL statement. System.Console.WriteLine("SQL: " + sqlObject.ToString()); // Create a SearchScope instance. (Assumes you have the object store object.) SearchScope search = new SearchScope(os); // Set the page size (Long) to use for a page of query result data. This value is passed // in the pageSize parameter. If null, this defaults to the value of // ServerCacheConfiguration.QueryPageDefaultSize. int myPageSize = 100; // Specify a property filter to use for the filter parameter, if needed. // This can be null if you are not filtering properties. PropertyFilter myFilter = new PropertyFilter(); int myFilterLevel = 1; myFilter.SetMaxRecursion(myFilterLevel); myFilter.AddIncludeType(new FilterElement(null, null, null, FilteredPropertyType.ANY, null)); // Set the (boolean) value for the continuable parameter. This indicates // whether to iterate requests for subsequent pages of result data. bool continuable = true; // Execute the fetchRows method using the specified parameters. IRepositoryRowSet myRows = search.FetchRows(sqlObject, myPageSize, myFilter, continuable); // You can then iterate through the collection of rows to access the properties. int rowCount = 0; foreach(IRepositoryRow row in myRows) { string docTitle = row.Properties.GetProperty("DocumentTitle").GetStringValue(); Id docId = row.Properties.GetProperty("Id").GetIdValue(); rowCount++; System.Console.WriteLine(" row " + rowCount + ":"); System.Console.WriteLine(" Id=" + docId.ToString()); if (docTitle != null) { System.Console.WriteLine(" DocumentTitle=" + docTitle); } System.Console.WriteLine(); }
See Constructing a SQL Statement for the general sequence and examples.
See Searching Multiple Repositories for the sequence and examples.
Use the SearchScope.fetchSearchableClassDescriptions
method to search for metadata. The fetchSearchableClassDescriptions
method returns a collection of ClassDescription
objects. There is no SearchSQL
parameter for this method. The general sequence for performing a query for metadata is as follows:
Java Example
// Create a SearchScope instance. (Assumes you have the object store // object.) SearchScope search = new SearchScope(os); // Specify the names of the classes containing the metadata you want to find. This must // be a String array of the identifiers (symbolic names, display names, or object IDs). String[] myClassNames = new String[]{"Document", "Annotation"}; // Specify a property filter to use for the filter parameter, if needed. // This can be null if you are not filtering properties. PropertyFilter myFilter = new PropertyFilter(); int myFilterLevel = 1; myFilter.setMaxRecursion(myFilterLevel); myFilter.addIncludeType(new FilterElement(null, null, null, FilteredPropertyType.ANY, null)); // Execute the fetchSearchableClassDescriptions method using the specified // parameters. ClassDescriptionSet myMetadata = search.fetchSearchableClassDescriptions(myClassNames, myFilter);
C# Example
// Create a SearchScope instance. (Assumes you have the object store // object.) SearchScope search = new SearchScope(os); // Specify the names of the classes containing the metadata you want to find. This must // be a String array of the identifiers (symbolic names, display names, or object IDs). string[] myClassNames = new string[] { "Document", "Annotation" }; // Specify a property filter to use for the filter parameter, if needed. // This can be null if you are not filtering properties. PropertyFilter myFilter = new PropertyFilter(); int myFilterLevel = 1; myFilter.SetMaxRecursion(myFilterLevel); myFilter.AddIncludeType(new FilterElement(null, null, null, FilteredPropertyType.ANY, null)); // Execute the fetchSearchableClassDescriptions method using the specified // parameters. IClassDescriptionSet myMetadata = search.FetchSearchableClassDescriptions(myClassNames, myFilter);
See Searching Multiple Repositories for the sequence and examples.
Use the Enterprise Manager's Query Builder tool to construct your query or to quickly validate that your query works as intended. Be sure to include the object reference "this" in the SELECT clause when attempting to execute any query examples (included in this documentation) in Query Builder > View > SQL View. For example, this query:
SELECT d.Id FROM Document d WHERE d.DocumentTitle = 'MyDoc'
must be entered into Query Builder in this form:
SELECT d.this, d.Id FROM Document d WHERE d.DocumentTitle = 'MyDoc'
For more information on using Query Builder, see Content Engine Query Builder
Setting the values of certain properties can prevent a user from allocating excessive memory when making requests to the Content Engine server—a situation that could cause the server to become slow due to memory limitations. Limit the number of rows returned from your query to a usable number, either by specifying your query's row selection criteria to achieve that result, or by deciding on that number in advance by setting the following
ServerCacheConfiguration
properties:
The maximum number of rows returned for a single server request for a paged query. This value cannot be overridden by an individual query.
The default number of rows returned for a single server request for a paged query, if no value is explicitly specified.
The maximum number of rows returned from a non-paged query. This value cannot be overridden by an individual query.
You can also set the MaxQueryTimeLimit property to a value that limits the maximum amount of time that a query for a page can consume. Like the ServerCacheConfiguration
properties listed above, this ObjectStore
property limits excessive server resource usage by a single user.
Avoid referencing any non-indexed column in a JOIN, WHERE, or ORDER BY clause. For example, optimize the following query by creating an index on Document.DocumentTitle (database column DocVersion.xxx_documenttitle):
SELECT d.Id FROM Document d WHERE d.DocumentTitle = 'MyDoc'
Note: The Document class maps to the DocVersion table.
Also, avoid WHERE clauses with the LIKE operator when the searched-for column value does not permit the effective use of indexes. For example:
SELECT d.Id FROM Document d WHERE d.DocumentTitle LIKE '%abc'
In this example, even if an index exists for the DocumentTitle column, the query cannot use it due to the leading wildcard ("%") in the searched-for column value. Code your query to return a relatively small number of rows by placing a sufficient number of characters before the wildcard. In search dialogs, allow end users to perform "Starting with" searches (such as for "abc%") but not "Contains" searches (such as for "%abc%").
Queries that do not make effective use of indexes can potentially cause the database to lock the table. Other users can then be blocked from updating the table for the duration of the query, causing checkins to time out, and other such problems.
Avoid any column value comparison resulting from a JOIN, WHERE, or ORDER BY clause for any non-indexed column, or for any column belonging to an index, that does not directly or indirectly use the LOWER function. You should follow this guideline in these circumstances:
For DB2, generate a column by applying the LOWER function to a pre-existing column, and then create an index on the generated column. For Oracle, create a function-based index. For example, the index LOWER(DocVersion.xxx_documenttitle) makes the following queries more efficient:
SELECT d.Id FROM Document d WHERE d.DocumentTitle = 'MyDoc' SELECT d.Id, d.DocumentTitle, d.Creator FROM Document d ORDER BY d.DocumentTitle
Note: The Document class maps to the DocVersion table.
Avoid unnecessarily searching for subclass types by adding the EXCLUDESUBCLASSES operator to your query (a query has an implicit INCLUDESUBCLASSES operator by default).
For example, as a result of the implicit INCLUDESUBCLASSES, the following query can return objects belonging to a Document subclass, in addition to those belonging to the Document class:
SELECT d.Id FROM Document d WHERE DocumentTitle = 'MyDoc'
Presuming only objects from the Document class are needed, instead of writing the query as:
SELECT d.Id FROM Document d WHERE DocumentTitle = 'MyDoc' AND ISCLASS(d, Document)
use the EXCLUDESUBCLASSES operator in this manner:
SELECT d.Id FROM Document d WITH EXCLUDESUBCLASSES WHERE DocumentTitle = 'MyDoc'
See SQL Syntax Reference for more information about the EXCLUDESUBCLASSES and INCLUDESUBCLASSES operators.
Avoid returning all of the columns in a table when only some are needed. For example, instead of:
SELECT d.* FROM Document d WHERE d.DocumentTitle = 'MyDoc'
specify the needed columns, as in this example:
SELECT d.Id FROM Document d WHERE d.DocumentTitle = 'MyDoc'
This minimizes the amount of data transmitted over the network. Also, when all of the columns in the SELECT clause are indexed, the data for the query might be retrieved directly from the index, making the physical row lookup unnecessary.
Avoid referencing three or more tables in a query. More tables can degrade query performance, and complicate performance-tuning efforts.
Avoid an unnecessary ORDER BY clause. Explicit row ordering may be unnecessary when you can rely on your query returning its results in a particular order as a side-effect of an indexed search. For example, the ORDER BY clause in the following query may be unnecessary since a composite index exists for the Container table on (parent_container_id, name):
SELECT f.FolderName FROM Folder f WHERE f.Parent = Object('/sub1/sub1a') AND f.IsHiddenContainer = false ORDER BY f.FolderName
Note: The Folder class maps to the Container table.
Avoid using subqueries and operators that indirectly generate subqueries whenever you are querying an object store that uses Oracle as the database engine. Potential subquery-related issues exist with the Oracle optimizer. Specifically, use an INNER JOIN instead of a subquery (however, see also the guideline on avoiding complex table linkages). For example, rewrite the potentially slow query:
SELECT d.Id FROM Document d WHERE d.Id IN (SELECT b.Bp8ObjectGuid FROM Bp8Attachment b)
in this functionally equivalent form:
SELECT d.Id FROM Document d INNER JOIN Bp8Attachment b ON d.Id = b.Bp8ObjectGuid
Note: FileNet's Business Process Framework product uses the Bp8Attachment table.
Also, because the Content Engine uses a subquery to implement the INFOLDER operator, avoid using that operator. For example, rewrite the query:
SELECT f.FolderName FROM Folder f WHERE f.this INFOLDER '/sub1/sub1a' AND f.IsHiddenContainer = false
in this manner:
SELECT f.FolderName FROM Folder f WHERE f.Parent = OBJECT('/sub1/sub1a') AND f.IsHiddenContainer = false
Also, rewrite a query like this:
SELECT d.id FROM Document d WHERE d.This INFOLDER '/sub1/sub1a'
in this more efficient form:
SELECT d.Id FROM Document d INNER JOIN ReferentialContainmentRelationship r ON d.This = r.Head WHERE r.Tail = OBJECT('/sub1/sub1a')
See SQL Syntax Reference for more information about the INFOLDER operator.
Queries that contain two or more multi-valued property conditions in an OR clause can result in query timeouts or unacceptable query performance because it can be difficult for the database engine to create an optimal query plan. To avoid this situation, issue each of the multi-valued property query conditions as a separate search.