创建实验表，插入10万行数据
SQL> create table test (id number,name varchar2(10));
Table created.
SQL> declare
begin
for i in 1..100000 loop
insert into test values(1,'a');
commit;
end loop;
end;
/
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
查看表的统计信息，统计信息为空
SQL> select table_name ,num_rows,blocks,avg_row_len from user_tables where table_name='TEST';
TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN
------------------------------ ---------- ---------- -----------
TEST
收集表的统计信息
SQL> exec dbms_stats.gather_table_stats('SCOTT','TEST');
PL/SQL procedure successfully completed.
SQL> select table_name,num_rows,blocks,avg_row_len from user_tables where table_name='TEST';
TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN
------------------------------ ---------- ---------- -----------
TEST 100000 244 5
查看全表扫描的执行计划与cost
SQL> set lines 300 pages 300
SQL> explain plan for select * from test;
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 488K| 69 (2)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TEST | 100K| 488K| 69 (2)| 00:00:01 |
--------------------------------------------------------------------------
8 rows selected.
计算将行数由10万提高到200万所使用的块数
SQL> select round(244*2000000/100000) from dual;
ROUND(244*2000000/100000)
-------------------------
4880
通过使用set_table_stats修改统计信息
SQL> exec dbms_stats.set_table_stats(ownname=>'SCOTT',tabname=>'TEST',numrows=>2000000,numblks=>4880,avgrlen=>5);
PL/SQL procedure successfully completed.
SQL>select table_name,num_rows,blocks,avg_row_len from user_tables where table_name='TEST';
TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN
------------------------------ ---------- ---------- -----------
TEST 2000000 4880 5
查看全表扫描的执行计划与cost
SQL> explain plan for select * from test;
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2000K| 9765K| 1341 (2)| 00:00:17 |
| 1 | TABLE ACCESS FULL| TEST | 2000K| 9765K| 1341 (2)| 00:00:17 |
--------------------------------------------------------------------------
8 rows selected.
如果觉得《使用dbms_stats.gather_table_stats调整表的统计信息》对你有帮助，请点赞、收藏，并留下你的观点哦！
