Spent several hours practicing my Introduction to SQL Tuning talk yesterday and it has made me think through the things I’m saying. I don’t want to lead anyone astray by my talk, or the things I’ve posted on this blog. I’ve found something about the use_nl and use_hash hints that I’ve misunderstood for some time. I thought that these hints specified both the outer and inner tables in the join. Another way to say this is the left and right table in the join. But, according to the documentation and my testing these hints really do work on the inner table of the join only.
In my example, which I used in the paper and the slides I wanted the join order to be sales, products, customers but sales and products to be a hash join and the join to customers to be a nested loops. Here are the hints I used to force this join order and join methods:
/*+ leading(sales products) use_hash(sales products) use_nl(products customers) */
Here is the plan:
----------------------------------------- | Id | Operation | Name | ----------------------------------------- | 0 | SELECT STATEMENT | | | 1 | NESTED LOOPS | | |* 2 | HASH JOIN | | |* 3 | TABLE ACCESS FULL| SALES | |* 4 | TABLE ACCESS FULL| PRODUCTS | |* 5 | TABLE ACCESS FULL | CUSTOMERS | -----------------------------------------
Now this is the right plan and I think that is why it is so confusing. But you can accomplish the same thing by just listing the intended inner tables – products for the hash join and customers for the nested loops join. Here is the simplified and correct hint – which produces the same plan:
/*+ leading(sales products) use_hash(products) use_nl(customers) */
Just to prove these really are the inner tables I tried using sales in the hash join hint and products in the use_nl hint:
/*+ leading(sales products) use_hash(sales) use_nl(products) */ ----------------------------------------- | Id | Operation | Name | ----------------------------------------- | 0 | SELECT STATEMENT | | |* 1 | HASH JOIN | | | 2 | NESTED LOOPS | | |* 3 | TABLE ACCESS FULL| SALES | |* 4 | TABLE ACCESS FULL| PRODUCTS | |* 5 | TABLE ACCESS FULL | CUSTOMERS | -----------------------------------------
Notice that now we use a nested loops join with products as the inner table and sales as the outer table. Sales can’t be the inner table of a join because of the leading hint so the use_hash hint is ignored and customers is the inner table of the hash join which is just something the optimizer chose on its own.
So, bottom line is when you are specifying use_hash or use_nl hints only list the inner or right table of your join.
– Bobby
For USE_NL this lines up.
For USE_HASH, the argument looks to be the hashed table, ie the table that get’s probed into, which happens to be the first table in the order of execution. Thus though it maybe be the inner table in the sense that the the outer table probes the inner table, the inner table actually the first in the execution plan, which makes sense as one has to hash the inner table before one can start probing the inner table in a hash join. This part with the inner table showing up first in the order of execution I find counter intuitive, even though it makes logical sense, because I think of the inner table as the second table in the order or execution.
– Kyle Hailey
Kyle,
Thanks for your comment. Maybe I should stick with the term “right” or just “the table listed second in the explain plan”. My main point was that I used to list both tables in the hint rather than just the second one. One funny thing about hash joins is that I worked on a Neoview system for a while and it listed the tables in a hash join in the opposite order in the plan from Oracle. It was kind of confusing working on both Oracle and Neoview at that time. I had to scratch my head and remember which table built the hash table and which one probed it.
– Bobby
Yeah, I can imagine that being confusing! I get confused just between NL and HJ orders on Oracle alone. One small but awesome enhancement I wanted to get into DB Optimizer, which would have been cross database, was indicating which table was the inner and outer graphically which would make understanding right deep joins and left deep joins easier like the arrow directions and the “hashed” label seen here: http://dboptimizer.com/2011/12/09/right-deep-left-deep-and-bushy-joins/
– Kyle Hailey
BTW this blog post of yours is a great reference. I just googled around and the several Oracle doc hits I looked at were uninformative and still showed two tables in the NL and HJ hints
Pingback: Top Ten Posts So Far | Bobby Durrett's DBA Blog
Pingback: Kyle Hailey » Right Deep, Left Deep and Bushy Joins in SQL
Hello sir,
I have one query and table access full is coming and due to which it is taking too long to run in production.
please tell solution to get rid of it :
explain plan for select cc.segment1, cc.segment2, h.currency_code, SUM(NVL(l.accounted_dr,0) - NVL(l.accounted_cr,0)) from gl_code_combinations cc ,gl_je_lines l ,gl_je_headers h ,gl_je_batches b ,gl_periods p1 ,gl_periods p2 where cc.code_combination_id = l.code_combination_id AND b.je_batch_id = h.je_batch_id AND b.status = 'P' AND l.je_header_id = h.je_header_id AND h.je_category = 'Revaluation' AND h.period_name = p1.period_name AND p1.period_set_name = 'Equant Master' AND p2.period_name = 'SEP-16' AND p2.period_set_name = 'Equant Master' AND p1.start_date <= p2.end_date AND h.set_of_books_id = '1429' GROUP BY cc.segment1, cc.segment2, h.currency_code explain plan is here : -------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 7532 | 1059K| | 17224 (1)| | 1 | HASH GROUP BY | | 7532 | 1059K| 2328K| 17224 (1)| |* 2 | HASH JOIN | | 7532 | 1059K| | 16980 (1)| | 3 | TABLE ACCESS BY INDEX ROWID | GL_JE_LINES | 746 | 14920 | | 271 (0)| | 4 | NESTED LOOPS | | 7532 | 934K| | 3947 (1)| | 5 | NESTED LOOPS | | 10 | 1070 | | 3239 (1)| | 6 | NESTED LOOPS | | 10 | 990 | | 3219 (1)| | 7 | NESTED LOOPS | | 12 | 744 | | 8 (0)| | 8 | TABLE ACCESS BY INDEX ROWID| GL_PERIODS | 1 | 31 | | 2 (0)| |* 9 | INDEX UNIQUE SCAN | GL_PERIODS_U1 | 1 | | | 1 (0)| |* 10 | TABLE ACCESS FULL | GL_PERIODS | 12 | 372 | | 6 (0)| |* 11 | TABLE ACCESS BY INDEX ROWID | GL_JE_HEADERS | 1 | 37 | | 670 (0)| |* 12 | INDEX RANGE SCAN | GL_JE_HEADERS_N2 | 3096 | | | 11 (0)| |* 13 | TABLE ACCESS BY INDEX ROWID | GL_JE_BATCHES | 1 | 8 | | 2 (0)| |* 14 | INDEX UNIQUE SCAN | GL_JE_BATCHES_U1 | 1 | | | 1 (0)| |* 15 | INDEX RANGE SCAN | GL_JE_LINES_U1 | 746 | | | 4 (0)| | 16 | TABLE ACCESS FULL | GL_CODE_COMBINATIONS | 1851K| 30M| | 13023 (1)|Thank you for your comment. It is hard to say without having all of the details. There are some cases where full scans are part of the best possible plan. In this case I do wonder about the full scan of GL_CODE_COMBINATIONS. Does it have an index on code_combination_id and is code_combination_id a unique key for the table? Maybe you are just missing an index.
As an Oracle noob, I’m still sometimes puzzled by the hints. USE_HASH(t) is particularly vexing, since (unlike USE_NL(t)) it does *not* always mean “use t as the inner of the join”. (By “inner”, I mean the table that is hashed, which is sometimes called the “right” tables, and which Oracle calls the “build” table). The documentation (https://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements006.htm#SQLRF50703) says that “The USE_HASH hint instructs the optimizer to join each specified table with another row source using a hash join.” Period. It doesn’t guarantee that the referenced table is probe or hash, left or right, inner or outer. By contrast, for USE_NL, it says “The USE_NL hint instructs the optimizer to join each specified table to another row source with a nested loops join, using the specified table as the inner table.”.
For USE_HASH, if you really want to nail down whether table t is the outer/left/probe table or the inner/right/hash table, you have to use
USE_HASH(t) SWAP_JOIN_INPUTS(t) (makes t the right/inner/build)
USE_HASH(t) NO_SWAP_JOIN_INPUTS(t) (makes t the left/outer/probe)
I’ve verified this experimentally and there are lots of other blog posts that explain this; I like https://jonathanlewis.wordpress.com/2013/09/07/hash-joins/
Thanks for the link. The SWAP_JOIN_INPUTS and NO_SWAP_JOIN_INPUTS hints look interesting.
Really, I think the main point of my post was not which table is the hash table but how to tell the optimizer to use a particular join type for each join. So, in my example the leading hint forces the join order to be sales-products-customers. This has two joins, sales-products and the result of that join to customers. To use a hint to specify the join type of each join you use the second of the tables in each join as listed in the join order. This was an eye opener to me because I have seen this listed incorrectly in other places and used it incorrectly for a long time.
But, it is neat to see that there are hints to control which table is hashed. I don’t think I’ve seen that before.
I was obviously mistaken about the use_nl-hint too, until I came recently noticed Jonathan Lewis’ blog-post: https://jonathanlewis.wordpress.com/2017/01/13/use_nl-hint/
I saw Jonathan’s post also. Thanks for the link. I don’t know how many years I have gone using the hint incorrectly.