Good morning.
We have a customer search form where a variety of fields can be entered, with any of them populated or left blank.
Leading matches are supported. So the last_name field might contain the full last name, the first 3 letters, or only the 1st letter. Similarly, the mobile_phone field might contain the full phone number, or only the area code.
Then we use dynamic sql to construct a query that matches their request (joining only to relevant tables, etc) and submit it. Here's where we hit our first challenge. The predicate:
AND c.last_name like :p_last_name||'%'
has a wildly varying cardinality when p_last_name = ‘S’ as opposed to ‘Schwarzenegger’. The same applies across several fields. We've added dynamic hints like this:
'OPT_ESTIMATE(TABLE c ROWS='||l_est_customer_rows||')'
For last names we have a lookup of the most common last name prefixes and their cardinality, after that we just use a text frequency distribution for N letters, based on our actual last name values. We then handle partial values for phone, email, street address, etc to generate opt_estimate hints for a few of the tables in the query.
We join to several other tables, with some of these joins dynamically included or not, depending on whether any corresponding search fields are populated. This all works reasonably well and has a good performance signature.
However, opt_estimate is not a supported hint. Neither of course is cardinality. To be clear, it is not viable to solve this problem with structural hints (leading, use_nl), transformational hints (nest, merge) or such. We want to give the optimizer the information it lacks in order to do its job.
Is there a better approach? Surely other people have tackled this sort of problem, it's hardly rare.
Cheers.