One of the less well-known hints is the hint /*+ use_nl_with_index() */ (link to 19c reference manual) which appeared in the 10g timeline. I may be wrong but I don’t think I saw a good description of this hint in the manuals until the 19c manual supplied the following:
The USE_NL_WITH_INDEX hint will cause the optimizer to join the specified table to another row source with a nested loops join using the specified table as the inner table but only under the following condition. If no index is specified, the optimizer must be able to use some index with at least one join predicate as the index key. If an index is specified, the optimizer must be able to use that index with at least one join predicate as the index key.
It looks like a fairly redundant hint, really, since it could easily (and with greater safely, perhaps) be replaced by the pair /*+ use_nl() index() */ with the necessary details of query block, object alias etc. on the hints. In fact I think I’ve only ever seen the hint “in the wild” once, and that was in an internal view definition where it had been used incorrectly (see this recent update to a note on one of the dynamic performance views that I wrote a few years ago).
The note I’ve just referenced prompted me to take a closer look at the hint to see how accurate the definition was. Here’s a data set I created for testing:
rem
rem Script: use_nl_with_index.sql
rem Author: Jonathan Lewis
rem Dated: Mar 2021
rem
rem Last tested
rem 19.3.0.0
rem 12.2.0.1
rem
create table t1
as
with generator as (
select
rownum id
from dual
connect by
level <= 1e4 -- > comment to avoid WordPress format issue
)
select
rownum id,
mod(rownum,10) n10,
mod(rownum,1000) n1000,
mod(rownum,2000) n2000,
lpad(mod(rownum,1000),10,'0') v1000,
lpad('x',100,'x') padding
from
generator v1,
generator v2
where
rownum <= 1e5 -- > comment to avoid WordPress format issue
;
create table t2 as
select distinct
n10, n1000, v1000
from
t1
;
create index t1_i1000 on t1(n1000);
create index t1_i10_1000 on t1(n10,n1000);
create index t1_i2000 on t1(n2000);
create bitmap index t1_b1 on t1(n1000, n10);
I’ve set up the data to do a join between t2 and t1, and I’m going to hint a query to force the join order t2 -> t1, and thanks to the data pattern the default path should be a hash join. Once I’ve established the default path I’m going to use the use_nl_with_index() hint to see how it behaves with respect to the various indexes I’ve created. So here’s the query with the default path:
set autotrace traceonly explain
select
/*+ leading(t2 t1) */
t1.*
from t2, t1
where
t2.n10 = 1
and t1.n1000 = t2.n1000
;
Execution Plan
----------------------------------------------------------
Plan hash value: 2959412835
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 1318K| 259 (8)| 00:00:01 |
|* 1 | HASH JOIN | | 10000 | 1318K| 259 (8)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| T2 | 100 | 700 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T1 | 100K| 12M| 252 (6)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."N1000"="T2"."N1000")
2 - filter("T2"."N10"=1)
Note
-----
- this is an adaptive plan
So the join order is as required, and the default is a hash join. The join predicate is t1.n1000 = t2.n1000, and if you examine the indexes I’ve created you’ll see I’ve got
- t1_i1000 on t1(n1000) – the perfect index
- t1_i10_1000 on t1(n10, n1000) – which could be used for a skip scan
- t1_i2000 on t1(n2000) – which doesn’t include the column in the join predicate
- t1_b1 on t1(n1000, n10) – which is a bitmap index
So here are the first batch of tests – all rolled into a single statement with optional hints included:
select
/*+
leading(t2 t1)
use_nl_with_index(t1)
-- use_nl_with_index(t1 t1_i1000)
-- use_nl_with_index(t1(n1000))
*/
t1.*
from t2, t1
where
t2.n10 = 1
and t1.n1000 = t2.n1000
;
Execution Plan
----------------------------------------------------------
Plan hash value: 3315267048
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 1318K| 10133 (1)| 00:00:01 |
| 1 | NESTED LOOPS | | 10000 | 1318K| 10133 (1)| 00:00:01 |
| 2 | NESTED LOOPS | | 10000 | 1318K| 10133 (1)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | T2 | 100 | 700 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T1_I1000 | 100 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T1 | 100 | 12800 | 101 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T2"."N10"=1)
4 - access("T1"."N1000"="T2"."N1000")
If I don’t specify an index the optimizer picks the best possible index; alternatively I can specify the index on (n1000) by name or by description and the optimizer will still use it. So what do I get if I reference the index on (n2000):
select
/*+
leading(t2 t1)
use_nl_with_index(t1(n2000))
*/
t1.*
from t2, t1
where
t2.n10 = 1
and t1.n1000 = t2.n1000
;
Execution Plan
----------------------------------------------------------
Plan hash value: 2959412835
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 1318K| 259 (8)| 00:00:01 |
|* 1 | HASH JOIN | | 10000 | 1318K| 259 (8)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| T2 | 100 | 700 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T1 | 100K| 12M| 252 (6)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."N1000"="T2"."N1000")
2 - filter("T2"."N10"=1)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
3 - SEL$1 / T1@SEL$1
U - use_nl_with_index(t1(n2000))
Note
-----
- this is an adaptive plan
I’m back to the tablescan with hash join – and since I’m testing on 19.3.0.0 Oracle kindly tells me in the Hint Report that I have an unused hint: the one that can’t be used because the referenced index doesn’t have any columns that are join predicates.
So what about the skip scan option:
select
/*+
leading(t2 t1)
use_nl_with_index(t1(n10, n1000))
-- use_nl_with_index(t1(n10))
-- index_ss(t1 (n10))
*/
t1.*
from t2, t1
where
t2.n10 = 1
and t1.n1000 = t2.n1000
;
Even though the index I’ve specified in the hint does contain a column in the join predicate the execution plan reports a full tablescan and hash join – unless I include an explicit index_ss() hint: but in that case I might as well have used the vanilla flavoured use_nl() hint. I did have a look at the 10053 (CBO) trace file for this example, and found that if I didn’t include the index_ss() hint the optimizer calculated the cost of using an index full scan (and no other option) for every single index on t1 before choosing the tablescan with hash join.
Finally, and without repeating the query, I’ll just note that when I referenced t1_b1 (n1000, n10) in the hint Oracle was happy to use the index in a nested loop join:
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 1318K| 2182 (1)| 00:00:01 |
| 1 | NESTED LOOPS | | 10000 | 1318K| 2182 (1)| 00:00:01 |
| 2 | NESTED LOOPS | | 10000 | 1318K| 2182 (1)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | T2 | 100 | 700 | 2 (0)| 00:00:01 |
| 4 | BITMAP CONVERSION TO ROWIDS| | | | | |
|* 5 | BITMAP INDEX RANGE SCAN | T1_B1 | | | | |
| 6 | TABLE ACCESS BY INDEX ROWID | T1 | 100 | 12800 | 2182 (1)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T2"."N10"=1)
5 - access("T1"."N1000"="T2"."N1000")
filter("T1"."N1000"="T2"."N1000")
Summary
The use_nl_with_index() hint generally works as described in the manuals – with the exception that it doesn’t consider an index skip scan as a valid option when trying to match the join predicate. That exception is one of those annoying little details that could waste a lot of your time.
Since it’s so easy to replace use_nl_with_index() with a pair of hints – including an index hint that could be an index_desc(), index_ss(), or index_combine() hint – I can’t come up with a good reason for using the use_nl_with_index() hint.
