hint 'leading' 'ordered' issue when using 'count(distinct xxx)'

lxmlhh

Version is Oracle Database 11g Enterprise Edition Release 11.2.0.3.

When I join two tables use hash, and use 'leading' hint, it shows as below, t_userserviceinfo is drive table, i think it is ok even its cardinality is lagerer.

But when I query using 'count(distinct a.phonenumber)', leading drive table changed to t_personallib, it is not the table I specified.

I thought the drive table should be always the table a as I specified using 'leading(a)'.

Why?

top.gun

The hints are only hints to the CBO - not mandatory.

lxmlhh

you mean it's not mandatory, but why for this sql? using 'count(distinct xx)', hints 'leading' becomes useless.

Martin Preiss

as top.gun already said: using hints is not mandatory and should be handled with care - especially in prod systems. Though to get a better understanding of the optimizer's strategies hint can be useful.

In the given case I would take a look at a CBO trace (event 10053) - but I think that the leading hint is only telling the optimizer to use "a" in the first join and not sufficient to force the optimizer to choose the first table in the from clause as the build table: there is the additional hint swap_join_inputs which can be used to change the order as Jonthan Lewis mentioned in https://jonathanlewis.wordpress.com/2013/09/07/hash-joins/

Jonathan Lewis

Hints are directives to the optimizer. If you use them correctly (and allowing for bugs and poor documentation) the optimizer will do what you tell it; but very few people know how to use hints correctly.

In this case you can see a "distinct aggregate" transformation taking place (VW_DAG_0), and Oracle has managed to transform your query into something where your hints don't apply.  Implicitly your hints apply to a query block called @sel$1, but you want your leading hint and use_hash() hint (and no_swap_join_inputs() hint) to apply in the query block generated by the transformation. You can either work out the name of that query block (show the ALIAS and OUTLINE columns from the plan table) to apply the hints in the right place, or block the transformation - /*+ no_transform_distinct_agg(@sel$1) */

Regards

Jonathan Lewis

AndrewSayer

top.gun wrote:

The hints are only hints to the CBO - not mandatory.

Hints are instructions and will only be "ignored" if they aren't correct. e.g. a hint to use an index that doesn't exist

In this case, if the query has been rewritten by the optimizer before the hint is applied. The CBO will still try to apply the leading hints but because the optimizer has rewritten the query (as can be seen by the VW_DAG_0) the objects they refer to no longer exist where the hint expects them to.

Or, in rare situations, a bug.

The golden rule here is that hints should mostly not be necessary. If you think you know better than the optimizer it's because there are missing stats. In this case it could be a missing histogram on t_userserviceinfo.servstatus or t_personallib.resourcetype?

Stefan Koehler

Hi,

just in addition what Jonathan said. You might want to check out my blog post [Oracle] Insights into SQL hints - Embedded global and local hints and how to use them to get more details about the hint differences.

Best Regards

Stefan Koehler

Freelance Oracle performance consultant and researcher