Search screens

Sterling Selling and Fulfillment Foundation provides a flexible search facility that allows users to look for orders, shipments and audit records with a wide range of criteria. Some search combinations are more expensive than others.

We recommend that you work with the user community to identify search combinations that are likely to be used in production. Each search combination should be tested to ensure they are optimized and acceptable in a production setting. When testing these searches, you need to make sure the tables searched are sufficiently large (e.g., over 1 million records). Inefficient queries may not be evident in small databases. In addition, ensure the tables are populated with an appropriate data mix. For example, if the query is looking for orders with certain attributes in the closed state, you should ensure that these attributes and the number of closed orders are representative. Database optimizers picks search paths that it believe are optimal for the data distribution.

It is likely that some search combinations require indexes to be created (see Indexes).

Case-insensitive search

The Sterling Selling and Fulfillment Foundation Search feature supports case-insensitive searches against the YFS_PERSON_INFO table on the following columns:

The data continues to be stored in the database in mixed-case (mixture of upper and lower case).

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 have to add a generated column for each searched column and an index on that generated column. For example, as in the example above, you need to perform the following:

   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 the example above, a generated column (emailid_up) was defined as a generated column and indexed.