灌入大量数据后手工采集统计信息的重要性 - 数据库编程

------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 79 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TBL_STAT | 1 | 79 | 1 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_TBL_STAT | 1 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------------- PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=1) 14 rows selected.
插入100万记录后，发现仍是索引范围扫描。
7. 创建第二个测试表TBL_STAT_2，以及索引 SQL> create table tbl_stat_2 as select * from tbl_stat;

Table created.

SQL> create index idx_tbl_stat_2 on tbl_stat_2 (object_id);
Index created.

SQL> select count(*) from tbl_stat_2;
COUNT(*)
----------
1190725

8. 检索TBL_STAT和TBL_STAT_2关联查询的执行计划 SQL> explain plan for select a.object_name, b.object_name from tbl_stat a, tbl_stat_2 b where a.object_Id = b.object_id;

Explained.

SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 752230886
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 158 | 27 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TBL_STAT_2 | 25 | 1975 | 25 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 158 | 27 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | TBL_STAT | 1 | 79 | 2 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 4 | INDEX RANGE SCAN | IDX_TBL_STAT_2 | 25 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
4 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
Note
-----
- dynamic sampling used for this statement

20 rows selected. 可以看到这里对TBl_STAT使用的是全表扫描，对TBL_STAT_2使用的是索引扫描，表之间是嵌套循环连接。

SQL> explain plan for select a.object_name, b.object_name from tbl_stat_2 a, tbl_stat b where a.object_Id = b.object_id;
Explained.

SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 752230886
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------

灌入大量数据后手工采集统计信息的重要性(二)