11g組合索引的統計資訊會用於對錶返回基數的計算
10g的時候，CBO計算表返回多少資料，最簡單的情況就是各個列的DENSITY的乘積，不會用到索引的資訊。如果相關的2列之間有依賴關係，從統計資訊上是無法反應的。
11g新增了一個組合列統計的功能的功能，今天在測試時，發現組合索引的統計資訊，其實也用於了CBO計算表的card
測試的表，沒有收集直方圖
如下是沒有建立ID4，ID5上組合索引的情況
SQL> EXPLAIN PLAN FOR SELECT * FROM ctais2.TEST A WHERE id4=5 AND id5=5;
Explained.
SQL> SELECT * FROM TABLE(dbms_xplan.display);
Plan hash value: 2261054565
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 720 | 1005 (0)| 00:00:13 |
|* 1 | TABLE ACCESS BY INDEX ROWID| TEST | 10 | 720 | 1005 (0)| 00:00:13 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_ID4 | 1000 | | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
可以看到Table Access 的Card = 10 ,這是通過 錶行數 * ID4選擇率 * ID5選擇率 = 1000000 * .001000 * .010000 = 10
建立一個組合索引
CREATE INDEX idx_test_id45 ON TEST(id4,id5)
SQL> EXPLAIN PLAN FOR SELECT * FROM ctais2.TEST A WHERE id4=5 AND id5=5;
Explained.
SQL> SELECT * FROM TABLE(dbms_xplan.display);
Plan hash value: 3852160031
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 72000 | 1005 (0)| 00:00:13 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1000 | 72000 | 1005 (0)| 00:00:13 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_ID45 | 1000 | | 5 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
可以看到Table Access 的Card = 1000，這是通過錶行數 * 組合索引的選擇率 = 1000000 * (1/1000) = 1000
還可以看到，就算不用索引，索引的DISTINCT值，也已經進入了基數計算
SQL> EXPLAIN PLAN FOR SELECT /*+full(A)*/ * FROM ctais2.TEST A WHERE id4=5 AND id5=5;
Explained.
SQL> SELECT * FROM TABLE(dbms_xplan.display);
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 72000 | 2165 (1)| 00:00:26 |
|* 1 | TABLE ACCESS FULL| TEST | 1000 | 72000 | 2165 (1)| 00:00:26 |
--------------------------------------------------------------------------
開始懷疑建立索引，預設可能收集了複合列統計資訊，可是查詢了dba_stat_extensions以及其下的基表，都沒有發現什麼有價值的資訊。
嘗試修改索引的統計資訊：為了避免DBMS_STATS可能遞迴修改其他的資料，我選擇了直接UPDATE表
SQL> UPDATE ind$ SET distkey=2000 WHERE obj#=80692;
1 row updated.
SQL> commit;
Commit complete.
SQL> alter system flush shared_pool;
System altered.
SQL> EXPLAIN PLAN FOR SELECT /*+full(A)*/ * FROM ctais2.TEST A WHERE id4=5 AND id5=5;
Explained.
SQL> SELECT * FROM TABLE(dbms_xplan.display);
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 500 | 36000 | 2165 (1)| 00:00:26 |
|* 1 | TABLE ACCESS FULL| TEST | 500 | 36000 | 2165 (1)| 00:00:26 |
--------------------------------------------------------------------------
可以看到，返回的Card確實降低為了以前的1/2
如果除去索引外，還有其他謂詞，也可以使用組合索引的統計
SQL> EXPLAIN PLAN FOR SELECT /*+full(A)*/ * FROM ctais2.TEST A WHERE id4=5 AND id5=5 AND id3=5;
Explained.
SQL> SELECT * FROM TABLE(dbms_xplan.display);
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 250 | 18000 | 2165 (1)| 00:00:26 |
|* 1 | TABLE ACCESS FULL| TEST | 250 | 18000 | 2165 (1)| 00:00:26 |
--------------------------------------------------------------------------
250 = 1000000 * (1/2000) * 0.5 = 錶行數 * 組合索引的選擇率 * ID3選擇率
什麼時候無法使用索引的統計資訊來計算表的返回CARD
1.組合索引列非等值查詢
SQL> EXPLAIN PLAN FOR SELECT * FROM ctais2.TEST A WHERE id4=5 AND id5<5;
Explained.
SQL> SELECT * FROM TABLE(dbms_xplan.display);
Plan hash value: 3852160031
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 51 | 3672 | 54 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 51 | 3672 | 54 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_ID45 | 51 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
SQL> EXPLAIN PLAN FOR SELECT * FROM ctais2.TEST A WHERE id4<5 AND id5=5;
Explained.
SQL> SELECT * FROM TABLE(dbms_xplan.display);
Plan hash value: 2874578131
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 3600 | 65 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 50 | 3600 | 65 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | IDX_TEST_ID45 | 50 | | 14 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
刪除索引後：
SQL> EXPLAIN PLAN FOR SELECT * FROM ctais2.TEST A WHERE id4=5 AND id5<5;
Explained.
SQL> SELECT * FROM TABLE(dbms_xplan.display);
Plan hash value: 2261054565
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 51 | 3672 | 1005 (0)| 00:00:13 |
|* 1 | TABLE ACCESS BY INDEX ROWID| TEST | 51 | 3672 | 1005 (0)| 00:00:13 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_ID4 | 1000 | | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
SQL> EXPLAIN PLAN FOR SELECT * FROM ctais2.TEST A WHERE id4<5 AND id5=5;
Explained.
SQL> SELECT * FROM TABLE(dbms_xplan.display);
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 3600 | 2165 (1)| 00:00:26 |
|* 1 | TABLE ACCESS FULL| TEST | 50 | 3600 | 2165 (1)| 00:00:26 |
--------------------------------------------------------------------------
可以看到Rows沒有任何變化。
這時候CARD的計算為：
id4=5 AND id5<5 => 1000000 * 0.001 * (5-0)/(99-0) = 50.51 = 51
id4<5 AND id5=5 => 1000000 * (5-0)/(999-0) * 0.01 = 50.05 = 50
2.組合索引的列，收集了直方圖
SQL> EXPLAIN PLAN FOR SELECT * FROM ctais2.TEST A WHERE id4=5 AND id5=5;
Explained.
SQL> SELECT * FROM TABLE(dbms_xplan.display);
Plan hash value: 3852160031
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 720 | 13 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 10 | 720 | 13 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_ID45 | 10 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
SQL> EXEC dbms_stats.delete_column_stats('ctais2','TEST','ID5',col_stat_type => 'HISTOGRAM');
PL/SQL procedure successfully completed.
SQL> EXPLAIN PLAN FOR SELECT * FROM ctais2.TEST A WHERE id4=5 AND id5=5;
Explained.
SQL> SELECT * FROM TABLE(dbms_xplan.display);
Plan hash value: 3852160031
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 720 | 13 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 10 | 720 | 13 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_ID45 | 10 | | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
SQL> EXEC dbms_stats.delete_column_stats('ctais2','TEST','ID4',col_stat_type => 'HISTOGRAM');
PL/SQL procedure successfully completed.
2列上的直方圖都刪除後
SQL> EXPLAIN PLAN FOR SELECT * FROM ctais2.TEST A WHERE id4=5 AND id5=5;
Explained.
SQL> SELECT * FROM TABLE(dbms_xplan.display);
Plan hash value: 3852160031
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 72000 | 1005 (0)| 00:00:13 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1000 | 72000 | 1005 (0)| 00:00:13 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_ID45 | 1000 | | 5 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONE來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8242091/viewspace-758695/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MySQL中Innodb如何計算索引的統計資訊？MySql索引
- 用於計算數學統計的 PHP 包PHP
- 計算索引基數所採用的num_rows索引
- 計算組合數 (sdut oj)
- SQLServer索引優化（3）：對於建組合索引的要求SQLServer索引優化
- [sqlserver] 檢視錶的統計資訊SQLServer
- MySQL索引統計資訊更新相關的引數MySql索引
- ORACLE表統計資訊與列統計資訊、索引統計資訊Oracle索引
- 11g新特性--檢視錶的歷史統計資訊差異
- 【TUNE_ORACLE】檢視錶，列和索引的統計資訊SQL參考Oracle索引SQL
- lgB3717 計算組合數
- 【統計資訊】全面檢視錶所有統計資訊SQLSQL
- 【POJ 2249】 Binomial Showdown 組合數學 排列組合計算
- 用JavaScript計算字串佔用位元組數JavaScript字串
- Oracle錶的歷史統計資訊檢視Oracle
- 檢視錶的統計資訊SQL指令碼SQL指令碼
- 對於沒有任何統計資訊的表，ORACLE可能會動態取樣。Oracle
- 關於dbms_stats對系統統計資訊的管理
- 程式設計必備基礎 計算機組成原理+作業系統+計算機網路,計算機基礎——更適合程式設計師的程式設計必備基礎知識作業系統計算機網路程式設計師
- [統計資訊系列7] Oracle 11g的自動統計資訊收集Oracle
- 索引成本計算的基礎知識索引
- PostgreSQL中統計資訊計算SQL
- 對於計算機作業系統的認識計算機作業系統
- 11G新特性，待定的統計資訊
- 組合數的計算（利用楊輝三角/記憶化搜尋）
- MySQL 5.7 ANALYZE TABLE分析索引的統計資訊MySql索引
- 基於組合語言微控制器的時鐘設計組合語言
- 《初級會計電算化應用教程（金蝶KIS專業版）》——1.3電算化會計資訊系統
- 基於AD作用於域使用者和計算機的組策略（1）計算機
- 基於AD作用於域使用者和計算機的組策略（2）計算機
- 組合計數思維題
- 基於UML的資訊系統分析與設計 (轉)
- MONGODB03 - 分組計數/分組去重計數（基於 spring-data-mongodb）MongoDBSpring
- PostgreSQL DBA(12) - 統計資訊在計算選擇率上的應用#2SQL
- PostgreSQL DBA(11) - 統計資訊在計算選擇率上的應用#1SQL
- 用RMI實現基於Java的分散式計算(轉)Java分散式
- ICLR 2020 Oral | 基於計算約束下的有用資訊的資訊理論ICLR
- 計算機組成原理--浮點數-原碼補碼錶示範圍計算機