Forum Stats

  • 3,759,035 Users
  • 2,251,494 Discussions
  • 7,870,475 Comments

Discussions

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

lxmlhh
lxmlhh Member Posts: 16
edited Mar 7, 2016 7:43AM in General Database Discussions

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.

1.jpg

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

2.jpg

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

Why?

Tagged:

Answers

  • top.gun
    top.gun Member Posts: 3,666 Gold Crown
    edited Mar 7, 2016 5:22AM

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

  • lxmlhh
    lxmlhh Member Posts: 16
    edited Mar 7, 2016 5:34AM

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

  • Martin Preiss
    Martin Preiss Member Posts: 2,381 Gold Trophy
    edited Mar 7, 2016 5:54AM

    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
    Jonathan Lewis Member Posts: 9,756 Gold Crown
    edited Mar 7, 2016 6:53AM

    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
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited Mar 7, 2016 7:10AM
    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
    Stefan Koehler Member Posts: 281 Bronze Badge
    edited Mar 7, 2016 7:43AM

    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

This discussion has been closed.