Search screens

Some search combinations are more expensive than others. The application provides a flexible search facility that allows you to search for orders, shipments, and audit records with a wide range of criteria.

Note: By default, the getPersonInfoList API uses a deprecated approach to implement the case-insensitive search. The implementation is changed to use shadow columns for case-insensitive search. For more information, see Enabling case-insensitive searches.

To identify the search combinations that are likely to be used in production, work with the user community. Test each search combination to ensure that the search combinations are optimized and accepted in a production environment. When you test these search combinations, ensure that the tables that are searched are sufficiently large, for example, over 1 million records. Inefficient queries might not be evident in small databases. Also, ensure that the tables are populated with an appropriate data mix. For example, if the query is looking for orders with certain attributes in the closed status, ensure that these attributes and the number of closed orders are representative. The database optimizer picks up the search paths that it believes are optimal for data distribution.

It is likely that some search combinations require indexes to be created. For more information, see Indexes.

Case-insensitive search

The Sterling Order Management System Software Search feature supports case-insensitive searches against the YFS_PERSON_INFO table on the following columns:

  • FIRST_NAME
  • LAST_NAME
  • EMAILID
  • ADDRESS_LINE1
  • ADDRESS_LINE2
  • CITY
  • STATE
  • ZIP_CODE
  • COUNTRY

The data remains to be stored in the database in mixed-case, which is both upper and lowercase.

Oracle

To support case-insensitive searches in Oracle, you must add function-based indexes on the searched columns. To create a function-based index that supports case-insensitive searches on the emailid column, issue the following:


   create index yfs_person_info_cust1 on yfs_person_info(upper(emailid))
Note: Creating an index does not result in case-insensitive searches. The index only helps in the speedy retrieval of records when a query is fired on email ID.

Db2®

For Db2, you must add a generated column for each searched column and an index on that generated column.


   set integrity for yfs_person_info off
   
   alter table yfs_person_info 
      add column emailid_up generated always as (upper(emailid))
   
   set integrity for yfs_person_info 
      immediate checked force generated
   
   create index extn_per_info_i1 
      on yfs_person_info(emailid_up)
   
   select * 
   from yfs_person_info 
   where upper(emailid) = 'SMITH'

In this example, a generated column, which is emailid_up is defined as a generated column and indexed.