ORACLE-技术文档-oracle 驱动表（包含 hint使用 nested loop has(3)

Predicate Information (identified by operation id): ---------------------------------------------------

3 - filter(\

4 - access(\ Note -----

- dynamic statistics used: dynamic sampling (level=2)

Statistics

---------------------------------------------------------- 10 recursive calls 0 db block gets 73 consistent gets 1 physical reads 0 redo size

542 bytes sent via SQL*Net to client

543 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed

SQL>

--代价和hash join差不多，另外，即使强制B表作为驱动表，仍然不能将B表作为驱动表。

两个都有索引的情况

SQL> create index id_a_id on a(id);

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'A',CASCADE=>TRUE);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(user,'B',cascade => true);

PL/SQL procedure successfully completed.

SQL>

SQL> Select /*+ ordered use_nl(b) */count(*) from a,b where a.id=b.object_id 2 Andobject_id=53;

COUNT(*) ---------- 1

1 row selected.

Elapsed: 00:00:00.01

Execution Plan

---------------------------------------------------------- Plan hash value: 2751652919

-------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 9 | 2 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 9 | | |

| 2 | NESTED LOOPS | | 1 | 9 | 2 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN| ID_A_ID | 1 | 4 | 1 (0)| 00:00:01 |

|* 4 | INDEX RANGE SCAN| ID_B_OBJECT_ID | 1 | 5 | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------

Predicate Information (identified by operation id): ---------------------------------------------------

3 - access(\ 4 - access(\

Statistics

---------------------------------------------------------- 1 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size

542 bytes sent via SQL*Net to client

543 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed

SQL>

--hint强制不能将B表作为驱动表

两个表较小，如果不使用hint，执行计划走sort mergegate方式 代价明显变小，又减少一倍（索引是多么重要）

一个语句使用多个hint的写法

仅举例

SELECT /*+ USE_HASH(TA,TB) parallel(TA,4) parallel(TB,4) */ FROM BSEMPMS TA, BSDPTMS TB

WHERE TA.DPT_NO=TB.DPT_NO;

百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，免费范文网，提供经典小说综合文库ORACLE-技术文档-oracle 驱动表（包含 hint使用 nested loop has(3)在线全文阅读。