NESTED LOOP JOIN探讨.docxVIP

本测试创建两个测试表，t1中有记录10万条，t2中记录10条。分别创造不同的条件，进行nested loop join，并比较不同条件下产生的逻辑读数量。在测试中均加了相应的hints，以避开优化器自动产生的执行计划。1. 创建测试表t1，t2SQL create table t1(a varchar2(10),b number(10));Table created.SQL create table t2(c number(10),d varchar2(10));Table created.2. 插入记录，大表t1，记录数100000条，小表t2，记录数10条。SQL begin 2 for i in 1 .. 100000 loop 3 insert into t1 values(to_char(i),i); 4 end loop; 5 commit; 6 end; 7 / PL/SQL procedure successfully completed.SQL begin 2 for i in 1 .. 10 loop 3 insert into t2 values(i,to_char(i)); 4 end loop; 5 commit; 6 end; 7 / PL/SQL procedure successfully completed.3.以t1表上的b字段和t2表上的c字段做关联，测试不同条件下的nest loop join对资源的消耗。（1）b和c字段上均无索引，且以t1为驱动表。SQL select /*+ use_nl(t1,t2) ordered */ * from t1,t2 where t1.b=t2.c; 。。。。10 rows selected. Execution Plan----------------------------------------------------------Plan hash value: 1967407726 ---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 10 | 400 | 28598 (1)| 00:05:44 || 1 | NESTED LOOPS | | 10 | 400 | 28598 (1)| 00:05:44 || 2 | TABLE ACCESS FULL| T1 | 105K| 2052K| 63 (2)| 00:00:01 ||* 3 | TABLE ACCESS FULL| T2 | 1 | 20 | 0 (0)| 00:00:01 |--------------------------------------------------------------------------- Predicate Information (identified by operation id):--------------------------------------------------- 3 - filter(T1.B=T2.C)Note----- - dynamic sampling used for this statement Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 300228 consistent gets 0 physical reads 0 redo size 732 bytes sent via SQL*Net to client 395 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 10 rows processed（2）b和c字段上均无索引，且以t2为驱动表。SQL s