Writing Effective SQL Statements in Oracle Responsys

  • When a query uses ANDs (Example: select * from tableName where a=1 AND b=2 AND c=3), place the most efficient condition (condition that returns fewest rows) last, the second most efficient condition second to last, and so on.
  • When a query uses ORs (Example: select * from tableName where a=1 OR b=2 OR c=3), place the most efficient condition first, the second most efficient condition second, etc.
  • When searching a field for any value except NULL, and the field has an index created in Oracle Responsys, the function-based indexes convert the search key to all-lowercase using the lower() function.
  • If you create an index manually for a field, Responsys creates it as a function-based index and it is advisable to use the lower() function. In the following example, the store_country column is already in a lower state from an index point of view. It’s the value, ‘US’, that requires a recasting to take advantage of the index.

Example: select * from table where store_country = lower(‘US’)

Note: On a profile table, EMAIL_ADDRESS_ is automatically indexed. All values are forced to lower case at merge time. Do not use a lower() or an UPPER() function on EMAIL_ADDRESS_, because doing so may result in a poorly performing query.

If you need to join a supplemental data column’s value to the profile table’s EMAIL_ADDRESS_ column, use a lower() on the supplemental data table column if the values contain mixed case. The lower() operation is unnecessary if the supplemental data table’s email address column contains all lower case values.

Examples: Select * from contacts_list a inner join supp_table b on a.EMAIL_ADDRESS_ = lower(b.EMAIL);

Example: Select * from contacts_list a inner join supp_table b on a.EMAIL_ADDRESS_ = lower(b.EMAILADDR);

Example: Select * from contacts_list a inner join supp_table b on a.EMAIL_ADDRESS_ = lower(b.EMAIL_ADDRESS_);

Learn more