CLUSTERING_FACTOR影響執行計劃
SCOTT@ prod1>select count(*) from t2;
COUNT(*)
----------
72594
SCOTT@ prod1>select num_rows,blocks from user_tables;
NUM_ROWS BLOCKS
---------- ----------
72594 1059
SCOTT@ prod1>select 72594/1059 from dual;
72594/1059
----------
68.5495751
SCOTT@ prod1>drop table t2;
Table dropped.
SCOTT@ prod1>create table t2 as select * from dba_objects where rownum<69;
Table created.
SCOTT@ prod1>analyze table t2 compute statistics;
Table analyzed.
SCOTT@ prod1>insert into t2 select * from t2;
8832 rows created.
SCOTT@ prod1>commit;
SCOTT@ prod1>create index ind_t2 on t2(object_id);
Index created.
SCOTT@ prod1>analyze table t2 compute statistics for all indexed columns;
Table analyzed.
SCOTT@ prod1>analyze table t2 compute statistics;
Table analyzed.
SCOTT@ prod1>select table_name,num_rows,blocks from user_tables;
T2 17664 244
SCOTT@ prod1>select * from t2 where object_id=11;
256 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1513984157
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 256 | 20224 | 68 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T2 | 256 | 20224 | 68 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=11)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
266 consistent gets
0 physical reads
0 redo size
5155 bytes sent via SQL*Net to client
606 bytes received via SQL*Net from client
19 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
256 rows processed
Table created.
SCOTT@ prod1>create index ind_t3 on t3(object_id);
Index created.
SCOTT@ prod1>analyze table t3 compute statistics;
Table analyzed.
SCOTT@ prod1>select index_name,table_name,clustering_factor from user_indexes where table_name = 'T3';
INDEX_NAME TABLE_NAME CLUSTERING_FACTOR
------------------------------ ------------------------------ -----------------
IND_T3 T3 202
SCOTT@ prod1>select table_name,num_rows,blocks from user_tables where table_name = 'T2';
TABLE_NAME NUM_ROWS BLOCKS
------------------------------ ---------- ----------
T2 17664 244
SCOTT@ prod1>select table_name,num_rows,blocks from user_tables where table_name = 'T3';
TABLE_NAME NUM_ROWS BLOCKS
------------------------------ ---------- ----------
T3 17664 212
SCOTT@ prod1>
SCOTT@ prod1>
SCOTT@ prod1>set autotrace traceonly;
SCOTT@ prod1>select * from t3 where object_id=11;
256 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1203691736
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 256 | 20224 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T3 | 256 | 20224 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T3 | 256 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=11)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
41 consistent gets
0 physical reads
0 redo size
24259 bytes sent via SQL*Net to client
606 bytes received via SQL*Net from client
19 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
256 rows processed
SCOTT@ prod1>select /*+ full(t3) */ * from t3 where object_id = 11;
256 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4161002650
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 256 | 20224 | 59 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T3 | 256 | 20224 | 59 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=11)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
222 consistent gets
0 physical reads
0 redo size
5155 bytes sent via SQL*Net to client
606 bytes received via SQL*Net from client
19 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
256 rows processed
COUNT(*)
----------
72594
SCOTT@ prod1>select num_rows,blocks from user_tables;
NUM_ROWS BLOCKS
---------- ----------
72594 1059
SCOTT@ prod1>select 72594/1059 from dual;
72594/1059
----------
68.5495751
SCOTT@ prod1>drop table t2;
Table dropped.
SCOTT@ prod1>create table t2 as select * from dba_objects where rownum<69;
Table created.
SCOTT@ prod1>analyze table t2 compute statistics;
Table analyzed.
SCOTT@ prod1>insert into t2 select * from t2;
8832 rows created.
SCOTT@ prod1>commit;
SCOTT@ prod1>create index ind_t2 on t2(object_id);
Index created.
SCOTT@ prod1>analyze table t2 compute statistics for all indexed columns;
Table analyzed.
SCOTT@ prod1>analyze table t2 compute statistics;
Table analyzed.
T2 17664 244
SCOTT@ prod1>select * from t2 where object_id=11;
256 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1513984157
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 256 | 20224 | 68 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T2 | 256 | 20224 | 68 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=11)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
266 consistent gets
0 physical reads
0 redo size
5155 bytes sent via SQL*Net to client
606 bytes received via SQL*Net from client
19 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
256 rows processed
SCOTT@ prod1>select /* + index(t2) */ * from t2 where object_id=11;
256 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1513984157
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 256 | 20224 | 68 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T2 | 256 | 20224 | 68 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=11)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
266 consistent gets
0 physical reads
0 redo size
5155 bytes sent via SQL*Net to client
606 bytes received via SQL*Net from client
19 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
256 rows processed
SCOTT@ prod1>select index_name,table_name,clustering_factor from user_indexes where table_name = 'T2';
INDEX_NAME TABLE_NAME CLUSTERING_FACTOR
------------------------------ ------------------------------ -----------------
IND_T2 T2 12708
SCOTT@ prod1>create table t3 as select * from t2 order by object_id;256 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1513984157
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 256 | 20224 | 68 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T2 | 256 | 20224 | 68 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=11)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
266 consistent gets
0 physical reads
0 redo size
5155 bytes sent via SQL*Net to client
606 bytes received via SQL*Net from client
19 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
256 rows processed
SCOTT@ prod1>select index_name,table_name,clustering_factor from user_indexes where table_name = 'T2';
INDEX_NAME TABLE_NAME CLUSTERING_FACTOR
------------------------------ ------------------------------ -----------------
IND_T2 T2 12708
Table created.
SCOTT@ prod1>create index ind_t3 on t3(object_id);
Index created.
SCOTT@ prod1>analyze table t3 compute statistics;
Table analyzed.
SCOTT@ prod1>select index_name,table_name,clustering_factor from user_indexes where table_name = 'T3';
INDEX_NAME TABLE_NAME CLUSTERING_FACTOR
------------------------------ ------------------------------ -----------------
IND_T3 T3 202
SCOTT@ prod1>select table_name,num_rows,blocks from user_tables where table_name = 'T2';
TABLE_NAME NUM_ROWS BLOCKS
------------------------------ ---------- ----------
T2 17664 244
SCOTT@ prod1>select table_name,num_rows,blocks from user_tables where table_name = 'T3';
TABLE_NAME NUM_ROWS BLOCKS
------------------------------ ---------- ----------
T3 17664 212
SCOTT@ prod1>
SCOTT@ prod1>
SCOTT@ prod1>set autotrace traceonly;
SCOTT@ prod1>select * from t3 where object_id=11;
256 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1203691736
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 256 | 20224 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T3 | 256 | 20224 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T3 | 256 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=11)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
41 consistent gets
0 physical reads
0 redo size
24259 bytes sent via SQL*Net to client
606 bytes received via SQL*Net from client
19 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
256 rows processed
256 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4161002650
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 256 | 20224 | 59 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T3 | 256 | 20224 | 59 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=11)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
222 consistent gets
0 physical reads
0 redo size
5155 bytes sent via SQL*Net to client
606 bytes received via SQL*Net from client
19 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
256 rows processed
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29108064/viewspace-2125505/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- cluster factor對執行計劃的影響
- Oracle優化案例-統計資訊對執行計劃的影響(十三)Oracle優化
- 【CURSOR】Oracle繫結變數、執行計劃對遊標的影響Oracle變數
- 執行計劃-1:獲取執行計劃
- 分析執行計劃優化SQLORACLE的執行計劃(轉)優化SQLOracle
- MySQL執行計劃MySql
- SYBASE執行計劃
- MySQL 執行計劃MySql
- 【執行計劃】Oracle獲取執行計劃的幾種方法Oracle
- MySQL執行計劃解析MySql
- mysql explain 執行計劃MySqlAI
- explain執行計劃分析AI
- mysql執行計劃explainMySqlAI
- oracle 固定執行計劃Oracle
- Oracle sql執行計劃OracleSQL
- 執行計劃執行步驟原則
- 【PG執行計劃】Postgresql資料庫執行計劃統計資訊簡述SQL資料庫
- CSS3實現動畫不會影響主執行緒,JS實現動畫會影響主執行緒CSSS3動畫執行緒JS
- mongodb執行計劃解釋MongoDB
- 檢視 OceanBase 執行計劃
- MySQL執行計劃解析(四)MySql
- 讀懂MySQL執行計劃MySql
- Explain執行計劃詳解AI
- PostgreSQL執行計劃變化SQL
- explain 查詢執行計劃AI
- [20190111]執行計劃bitmap and.txt
- mysql explain 執行計劃詳解MySqlAI
- 十六、Mysql之Explain執行計劃MySqlAI
- SqlServer的執行計劃如何分析?SQLServer
- 柱狀圖與執行計劃
- DB2執行計劃分析DB2
- Calcite執行計劃最佳化
- Oracle-繫結執行計劃Oracle
- MySQL Explain執行計劃 - 詳解MySqlAI
- 帶你看懂MySQL執行計劃MySql
- 獲取執行計劃之Autotrace
- sqm執行計劃的繫結
- [20210926]並行執行計劃疑問.txt並行
- GitHub漏洞允許任意程式碼執行,Windows不受影響GithubWindows