заранее сорри если совсем наивный вопрос.
Выполняется такой запрос (10.2.0.4.0, линух):
SQL> SELECT T1.MA, T1.WE, T1.DR, T1.SBA, T1.DSM, T1.SAG, T1.SU9, B1.IA
FROM T1, B1 WHERE T1.IHKZ=1 AND T1.MA=B1.MA AND T1.WE=B1.WE AND T1.SBA=B1.SBA
AND B1.IA IN (20,30,99)
ORDER BY T1.LT, T1.LZ, T1.SA DESC;
no rows selected
Elapsed: 00:00:29.08
Execution Plan
----------------------------------------------------------
Plan hash value: 996744477
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 249 | 18924 | 521 (1)| 00:00:01 |
| 1 | SORT ORDER BY | | 249 | 18924 | 521 (1)| 00:00:01 |
|* 2 | TABLE ACCESS BY INDEX ROWID | B1 | 1 | 22 | 2 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 249 | 18924 | 520 (1)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 250 | 13500 | 17 (6)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IND1_T1 | 252 | | 1 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | PK_B1 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("B1"."IA"=20 OR "B1"."IA"=30 OR "B1"."IA"=99)
5 - access("T1"."IHKZ"=1)
6 - access("T1"."MA"="B1"."MA" AND "T1"."WE"="B1"."WE"
AND "T1"."SBA"="B1"."SBA")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
249544 consistent gets
27969 physical reads
0 redo size
600 bytes sent via SQL*Net to client
437 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed
в таблице T1 селективный индекс IND1_T1 на IHKZ=1.
после добавления хинта USE_NL время выполнения и статистики улучшаются на несколько порядков, хотя оптимайзер и так использует NESTED LOOPS - и видно, что план не меняется:
SQL> SELECT /*+ USE_NL (B1 T1) */ T1.MA, T1.WE, T1.DR, T1.SBA, T1.DSM, T1.SAG,
T1.SU9, B1.IA FROM T1, B1 WHERE T1.IHKZ=1 AND T1.MA=B1.MA AND T1.WE=B1.WE
AND T1.SBA=B1.SBA AND B1.IA IN (20,30,99)
ORDER BY T1.LT, T1.LZ, T1.SA DESC;
no rows selected
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 996744477
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 249 | 18924 | 521 (1)| 00:00:01 |
| 1 | SORT ORDER BY | | 249 | 18924 | 521 (1)| 00:00:01 |
|* 2 | TABLE ACCESS BY INDEX ROWID | B1 | 1 | 22 | 2 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 249 | 18924 | 520 (1)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 250 | 13500 | 17 (6)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IND1_T1 | 252 | | 1 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | PK_B1 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("B1"."IA"=20 OR "B1"."IA"=30 OR "B1"."IA"=99)
5 - access("T1"."IHKZ"=1)
6 - access("T1"."MA"="B1"."MA" AND "T1"."WE"="B1"."WE"
AND "T1"."SBA"="B1"."SBA")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
27 consistent gets
0 physical reads
0 redo size
600 bytes sent via SQL*Net to client
437 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed