When trying to search for a contact containing an email with an underscore character, why are we receiving a query processes too much data error?

Environment:

Oracle B2C Service, Analytics, All versions

Resolution:

In SQL, the underscore character is treated as a wildcard character, similar to the % symbol. If the report you are using contains a filter based on contacts.email and using the LIKE operator, searching for a contact that contains an underscore in the email will treat the underscore as a wild card. For example, searching for a_test@emailaddress.com, will search for all occurrences of:

aatest@emailaddress.com
abtest@emailaddress.com
actest@emailaddress.com
adtest@emailaddress.com

and so on. Because of the wildcard character, more rows will need to be processed when performing this search and you may receive the query processes too much data error. Please see the following answer for additional information about this error, Answer 4278: Report is processing too much data.

To prevent this from happening, you can use the backslash escape character in your search so that the underscore character will be treated literally and not as a wildcard. For example:

a\_test@emailaddress.com

Or you can edit the report's contacts.email filter to use the EQUALS operator instead of the LIKE operator. Please see the following answer on how to edit report filters: Answer 2509: Creating a basic custom report