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: column like '%value%' 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 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.