The Art of Indexing

In an online transactional database, administrators tend to avoid adding indexes because of their impact to inserts and updates. This is true in a laboratory setting, but a real-world system has to deal with a collection of concurrent workloads. OLTP ERP systems tend to be very read-heavy, in the neighborhood of 94% in my experience. Reducing the aggregate read load can free system resources, and if that means getting the system out of a heavily-loaded state, intelligently adding indexes can indirectly (and paradoxically) improve real-world write performance.

Your mileage may vary. I recommend iteratively measuring the effects of tuning your own system rather than following any given recipe.

"Match Case"
Oracle string comparisons are case-sensitive, unlike Microsoft SQL or MySQL. This means the query hints in Enterprise Explorer do not accurately reflect the effects of the "Match Case" flag, since extra work is necessary to scrub the table's values to uppercase first, turning an index sequential read into a full-table scattered read.

For example, this query will result in an index range scan instead of the more preferable index unique scan:

SELECT * FROM customer_order WHERE UPPER(order_no) = 'A123456';

Orders will tend to have uppercase order numbers, but are your users always unchecking that flag when it makes sense? Do you feel comfortable asking them to? (I certainly don't!)

An administrator can fix this, though. Oracle provides "function based indexes", allowing you to index the result of a deterministic function to avoid computing that function on every row during a query.

Implementing the Tip

Here are a few example indexes you might add to avoid the ill effects of unchecking "match case". Again, your mileage may vary.

CREATE INDEX c_customer_info_upper_ix ON customer_info_tab (UPPER(name)) TABLESPACE ifsapp_index;

CREATE INDEX c_customer_order_upper_ix ON customer_order_tab (UPPER(order_no)) TABLESPACE ifsapp_index;

CREATE INDEX c_iso_lang_upper_co_ix ON iso_language_tab (UPPER(language_code)) TABLESPACE ifsapp_index;

CREATE INDEX c_iso_lang_upper_des_ix ON iso_language_tab (UPPER(description)) TABLESPACE ifsapp_index;

CREATE INDEX c_supplier_upper_ix ON supplier_info_tab (UPPER(name)) TABLESPACE ifsapp_index;
 

Why UPPER()? Why not LOWER()? IFS uses UPPER() for case-insensitive comparisons, so indexing LOWER() won't improve query performance. In most software, UPPER is canonically preferred over LOWER: https://stackoverflow.com/questions/234591/upper-vs-lower-case