Discussions
Categories
- 197.1K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.7K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.3K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 466 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
hint 'leading' 'ordered' issue when using 'count(distinct xxx)'

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?
Answers
-
The hints are only hints to the CBO - not mandatory.
-
you mean it's not mandatory, but why for this sql? using 'count(distinct xx)', hints 'leading' becomes useless.
-
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/
-
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
-
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?
-
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