Oracle11gR2-聚簇因子浅析
2024-09-26 22:11:11
创建表t1,t2
SQL> conn n1/n1
Connected.
SQL>
SQL>
SQL> create table t1 as select trunc(rownum/100) id ,object_name from all_objects where rownum<1000 ;
Table created.
SQL> create table t2 as select mod(rownum,100) id ,object_name from all_objects where rownum<1000;
Table created.
创建索引
SQL> create index inx_t1 on t1(id);
Index created.
SQL> create index inx_t2 on t2(id);
Index created.
SQL> exec dbms_stats.gather_table_stats(null,'T1',CASCADE=>true);
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(null,'T2',CASCADE=>true);
PL/SQL procedure successfully completed.
SQL> conn / as sysdba
Connected.
比较执行计划 查询t1走的索引范围扫描,t2确走的全表扫描
SQL> set autotrace trace exp stat
SQL> set linesize 300
SQL> select * from n1.t1 where id =2;
100 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2808986199
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 2100 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 100 | 2100 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INX_T1 | 100 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
18 consistent gets
0 physical reads
0 redo size
4386 bytes sent via SQL*Net to client
589 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
100 rows processed
SQL> select * from n1.t2 where id =2;
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1513984157
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 210 | 4 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T2 | 10 | 210 | 4 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=2)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
842 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
查看聚簇因子,可以看到t2 CLUSTERING_FACTOR很高,说明数据分布很散
SQL> conn n1/n1
Connected.
SQL> set linesize 300
SQL> col table_name format a30
SQL> col index_name format a30
SQL> select i.table_name,i.index_name,i.CLUSTERING_FACTOR,t.blocks,i.NUM_ROWS from
2 user_tables t,user_indexes i where t.table_name=i.table_name and t.table_name in
3 ('T1','T2');
TABLE_NAME INDEX_NAME CLUSTERING_FACTOR BLOCKS NUM_ROWS
------------------------------ ------------------------------ ----------------- ---------- ----------
T1 INX_T1 4 7 999
T2 INX_T2 400 7 999
解决方案:按照索引的顺序重新导入数据！
最新文章
- 使用Navicat修改SQLite数据库提示：no such collation sequence: LOCALIZED
- Stem函数绘图
- jsp页面路径问题
- 简单来谈谈alloc分配器
- 第五百八十三天 how can I 坚持
- Editing and Deleting Data
- hdu 2157 How many ways_ 矩阵快速幂
- js中字符串转换为日期型
- 【数学】HPU--1037 一个简单的数学题
- Win下安装虚拟机（Linux）
- CUDA程序的调试总结【不定时更新】
- 服务器多站点多域名HTTPS实现
- SQL Server 2016新特性：Query Store
- 第34章：MongoDB-索引--用户管理
- PDF，word ，PPT，等各种文件转换在线工具（免费）
- 从event loop规范探究javaScript异步及浏览器更新渲染时机
- 八、Django之Models（译）
- Oracle数据库导入dmp文件报错处理方法
- 解决Error:Android Dex: com.android.dex.DexIndexOverflowException: Cannot merge new index 65918 into a
- 消息编解码Nanopb - protocol buffers