Search type configuration
By setting search types for database columns, you can improve the results that are returned by user queries. Setting the search type can also reduce the load on the database.
Tables with fewer than 2000 or 3000 records are typically scanned regardless of indexes. The input output (I/O) cost to read an entire table is less than the average I/O cost of the index lookup plus the table lookup. The SEARCHTYPE value does not affect database behavior when such scans are performed. Tables with relatively few rows have no noticeable degradation in performance.
The following search types are available for user queries.
| Search type | Description | Benefit | Entry required |
|---|---|---|---|
| Exact | Filters data based on the keywords that you
specify. You specify an exact search type when you require accurate
and targeted results. Exact searches use wild cards only if a user explicitly enters wildcard characters on the List tab or in the WHERE clause. |
Key fields, such as Work Order and Purchase Order, and value list fields, such as Work Order Status, can benefit from the indexing that is used in exact searches. | = |
| Wildcard | The default search type is wildcard search.
You can apply a wildcard search on description fields of tables that
have a relatively small number of rows for example, 2000 or fewer
rows. When a user enters a value in a field on the List tab,
the wildcard search type condition looks like this: In wildcard searching, the database engine cannot use indexes. Searching without indexes can result in slower search times, especially on tables with many rows. |
Wildcard searching provides flexibility for the users. | % |
| Full text | You can specify a text search type on description fields of tables with large
numbers of rows, for example, tens of thousands of rows. The text search engine takes time to
refresh the indexes, so new records might not be found until the text search index refreshes itself.
Stem search is also performed. For example, a search for service returns servicing and serviced. Most system tables have one or more ALN data type columns for descriptions, memos, or remarks. You can define text search types and a corresponding IBM® DB2® text search index, Oracle text index, or SQL Server full text catalog for columns that have excessive text.
Text indexing increases the load on the database because of the constant background processing to keep the indexes synchronized. However, text indexing produces efficient word searching of description fields. |
Text searches produce faster search responses
than wildcard searches. Fields that are text-search enabled have text
search indexes and, therefore, result in a faster search response. If an object is enabled for text search, the full text searches on the attributes provided. |
Any combination of the words in the text search |
| None | The none search type is used for columns that cannot be searched. If you do not specify a search type, the value defaults to none or no search. | You use this search type to specify that a column should not be searched. |
You can use a combination of methods to refine searches.
- Application Designer application
- You can use the Application Designer application to customize an application by adding or removing columns from the List tab. You can then ensure that the columns to be queried are all indexed.
- Application cloning feature
- You can clone an application and then use the Application Designer application to create an alternate version with a restricted number of columns that can be queried.
- Security groups feature
- After you clone applications, you can use security groups to assign users to specific application clones. You can also use security groups to prohibit access to the More Search fields and WHERE clause advanced query options. When you prohibit access to those options, you limit users to query on the List tab of the application.