17,090
社区成员
发帖
与我相关
我的任务
分享
SQL> create table t1 as select level id from dual connect by level < 5;
表已创建。
已用时间: 00: 00: 00.14
SQL> select * from t1;
ID
----------
1
2
3
4
已用时间: 00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 52 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T1 | 4 | 52 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
28 recursive calls
0 db block gets
10 consistent gets
1 physical reads
0 redo size
464 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)
4 rows processed
SQL> create table t5(id int);
表已创建。
SQL> insert into t5 values(1);
已创建 1 行。
SQL> insert into t5 values(2);
已创建 1 行。
SQL> insert into t5 values(3);
已创建 1 行。
SQL> insert into t5 values(4);
已创建 1 行。
SQL> commit;
提交完成。
SQL> exec dbms_stats.gather_table_stats('zch','T5');
PL/SQL 过程已成功完成。
SQL> select blocks from user_tables where table_name = 'T5';
BLOCKS
----------
5
SQL> exec dbms_stats.gather_table_stats('zch','T5');
PL/SQL 过程已成功完成。
SQL> select blocks from user_tables where table_name = 'T5';
BLOCKS
----------
5
SQL> create table t6 as select level id from dual connect by level < 5;
表已创建。
SQL> exec dbms_stats.gather_table_stats('zch','T6');
PL/SQL 过程已成功完成。
SQL> select blocks from user_tables where table_name = 'T6';
BLOCKS
----------
4
SQL> create table t7(id int);
表已创建。
SQL> insert into t7 values(1);
已创建 1 行。
SQL> commit;
提交完成。
SQL> exec dbms_stats.gather_table_stats('zch','T7');
PL/SQL 过程已成功完成。
SQL> select blocks from user_tables where table_name = 'T7';
BLOCKS
----------
5
SQL> select * from t1;
ID
----------
1
2
3
4
SQL> select blocks,empty_blocks from user_tables where table_name = 'T1';
BLOCKS EMPTY_BLOCKS
---------- ------------
4 0
--你没有生成统计数据,所以你的52bytes只是一个估计数据,下面是生成统计数据前后对比
SQL> create table t1 as select level id from dual connect by level < 5;
表已创建。
SQL> set autot on;
SQL> select * from t1;
ID
----------
1
2
3
4
执行计划
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 52 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T1 | 4 | 52 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
28 recursive calls
0 db block gets
10 consistent gets
5 physical reads
0 redo size
464 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)
4 rows processed
SQL> set autot off;
SQL> exec dbms_stats.gather_table_stats('test','t1');
PL/SQL 过程已成功完成。
SQL> set autot on;
SQL> select * from t1;
ID
----------
1
2
3
4
执行计划
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 12 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T1 | 4 | 12 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
464 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)
4 rows processed
SQL> set autot off;
SQL> select t.NUM_ROWS,t.AVG_ROW_LEN,t.NUM_ROWS*t.AVG_ROW_LEN bytes from user_tables t where t.TABLE
_NAME='T1';
NUM_ROWS AVG_ROW_LEN BYTES
---------- ----------- ----------
4 3 12
SQL>