use_nl and use_hash hints for inner tables of joins

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

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I've been working as an Oracle database administrator and PeopleSoft administrator since 1994. I'm very interested in Oracle performance tuning.
This entry was posted in Uncategorized. Bookmark the permalink.

4 Responses to use_nl and use_hash hints for inner tables of joins

  1. Kyle Hailey says:

    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

    • Bobby says:

      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

  2. Kyle Hailey says:

    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

  3. Pingback: Top Ten Posts So Far | Bobby Durrett's DBA Blog

Leave a Reply