039：【实验：索引扫描方法对嵌套环连接的影响】

最新推荐文章于 2024-07-18 07:30:00 发布

cuihu2560

最新推荐文章于 2024-07-18 07:30:00 发布

阅读量87

点赞数

select e.*,t.deptno
from scott.dept2 t,scott.dept e
where t.deptno=e.deptno;
dept2是小表，dept是大表

1.在dept.deptno上创建“非唯一值索引”
执行SQL前收集统计信息
SQL> /

DEPTNO DNAME LOC SE CITY XL DEPTNO
---------- -------------- ------------- -- ---------- ---------- ----------
20 20 aaaaa 男 0号城市 4级别学历 20
30 30 aaaaa 女 1号城市 5级别学历 30
10 10 aaaaa 女 9号城市 3级别学历 10

Execution Plan
----------------------------------------------------------
Plan hash value: 3871708630

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

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

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

| 0 | SELECT STATEMENT | | 3 | 123 | 8 (0)|
00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 38 | 2 (0)|
00:00:01 |

| 2 | NESTED LOOPS | | 3 | 123 | 8 (0)|
00:00:01 |

| 3 | TABLE ACCESS FULL | DEPT2 | 3 | 9 | 2 (0)|
00:00:01 |

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

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

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

4 - access("T"."DEPTNO"="E"."DEPTNO")

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
12 consistent gets
0 physical reads
0 redo size
863 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
2.将dept.deptno列上的索引改为唯一索引
没收集统计信息
SQL> /

DEPTNO DNAME LOC SE CITY XL DEPTNO
---------- -------------- ------------- -- ---------- ---------- ----------
20 20 aaaaa 男 0号城市 4级别学历 20
30 30 aaaaa 女 1号城市 5级别学历 30
10 10 aaaaa 女 9号城市 3级别学历 10

Execution Plan
----------------------------------------------------------
Plan hash value: 3243467368

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

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

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

| 0 | SELECT STATEMENT | | 3 | 123 | 5 (0)|
00:00:01 |

| 1 | NESTED LOOPS | | 3 | 123 | 5 (0)|
00:00:01 |

| 2 | TABLE ACCESS FULL | DEPT2 | 3 | 9 | 2 (0)|
00:00:01 |

| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 38 | 1 (0)|
00:00:01 |

|* 4 | INDEX UNIQUE SCAN | IDX_UNIQUE | 1 | | 0 (0)|
00:00:01 |

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

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

4 - access("T"."DEPTNO"="E"."DEPTNO")

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
12 consistent gets
4 physical reads
0 redo size
863 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
3.收集表的统计信息后再执行一次
SQL> /

DEPTNO DNAME LOC SE CITY XL DEPTNO
---------- -------------- ------------- -- ---------- ---------- ----------
20 20 aaaaa 男 0号城市 4级别学历 20
30 30 aaaaa 女 1号城市 5级别学历 30
10 10 aaaaa 女 9号城市 3级别学历 10

Execution Plan
----------------------------------------------------------
Plan hash value: 3243467368

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

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

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

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

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

| 2 | TABLE ACCESS FULL | DEPT2 | 3 | 9 | 2 (0)|
00:00:01 |

| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 38 | 0 (0)|
00:00:01 |

|* 4 | INDEX UNIQUE SCAN | IDX_UNIQUE | 1 | | 0 (0)|
00:00:01 |

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

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

4 - access("T"."DEPTNO"="E"."DEPTNO")

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
12 consistent gets
0 physical reads
0 redo size
863 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
【小结】
1）非UNIQUE索引的扫描方式为“range scan”。表的连接方法为：“内部表（dept）的索引”与“驱动表”执行nested loops，然后再跟据ROWID找到内部表DEPT的数据。
2）第2步，说明了过时的统计量信息会提高COST。
3）第3步，使用了唯一索引，扫描方式为UNIQUE scan。表的连接路径为：通过内部表的索引找到匹配数据后，立即与驱动表连接，直到完成所有NESETED loops.
4）本实验中，使用唯一索引的效率最高,cost为2。非唯一索引的COST为8。两者的consistent gets是一样的。
5）阅读执行计划。从实验结果我们看到，NESTED LOOP操作的阅读应该是自上向下的。如下图所示的NETED LOOPS操作的驱动表是ID为2的DEPT2表。

Execution Plan
----------------------------------------------------------
Plan hash value: 3243467368

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

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

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

| 0 | SELECT STATEMENT | | 3 | 123 | 5 (0)|
00:00:01 |

| 1 | NESTED LOOPS | | 3 | 123 | 5 (0)|
00:00:01 |

| 2 | TABLE ACCESS FULL | DEPT2 | 3 | 9 | 2 (0)|
00:00:01 |

| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 38 | 1 (0)|
00:00:01 |

|* 4 | INDEX UNIQUE SCAN | IDX_UNIQUE | 1 | | 0 (0)|
00:00:01 |

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