oracle實驗記錄 （選擇率）
card對於連線時候 驅動表之類有很大影響,下面只分析"字元類" 數字類與 日期類比較簡單
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod
PL/SQL Release 10.1.0.2.0 - Production
CORE 10.1.0.2.0 Production
TNS for 32-bit Windows: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 - Production
SQL> create table t1 (a varchar2(10), b char(10));
Table created.
SQL> insert into t1 values ('aa','aa');
1 row created.
SQL> insert into t1 values ('ab','ab');
1 row created.
SQL> insert into t1 values ('ac','ab');
1 row created.
SQL> insert into t1 values ('bb','bb');
1 row created.
SQL> insert into t1 values ('cc','cc');
1 row created.
SQL> commit;
Commit complete.
begin
dbms_stats.gather_table_stats(
ownname => user,
tabname => 't1',
cascade => true,
estimate_percent => null,
method_opt =>'for all columns size 10'
);
end;
/
SQL> column endpoint_value format 999,999,999,999,999,999,999,999,999,999,999,
9 heading "End Value"
1 select
2 column_name,
3 endpoint_value
4 from
5 user_tab_histograms
6 where
7* table_name = 'T1'
SQL> /
COLUMN_NA End Value
--------- ------------------------------------------------
A 505,620,189,009,433,000,000,000,000,000,000,000~~~~~~~~~~aa
A 505,640,471,419,036,000,000,000,000,000,000,000~~~~~~~~~~ab
A 505,660,753,828,640,000,000,000,000,000,000,000~~~~~~~~~~ac
A 510,832,768,277,571,000,000,000,000,000,000,000
A 516,045,347,545,709,000,000,000,000,000,000,000
B 505,622,734,252,991,000,000,000,000,000,000,000
B 505,643,016,662,594,000,000,000,000,000,000,000
B 510,835,313,521,129,000,000,000,000,000,000,000
B 516,047,892,789,268,000,000,000,000,000,000,000
9 rows selected.
set autotrace traceonly explain
SQL> select * from t1 where a between 'aa' and 'ac';~~~~~~~~~~~~~~~~~~~~~~~~~
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=ALL_ROWS (Cost=2 Card=2 Bytes=28)
1 0 TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=2 Card=2 Bytes=2
8)
PL/SQL procedure successfully completed.
SQL> select
2 column_name, num_distinct, density
3 from user_tab_columns
4 where table_name = 'T1'
5 ;
COLUMN_NAME NUM_DISTINCT DENSITY
------------------------------ ------------ ----------
A 5 .1
B 4 .1
SQL> select 5*((505660753828640-505620189009433)/(516047892789268-50562018900943~~~~~~~~~~~~~~~~~~~~~~~~~~~card
3) +2/5 ) from dual;
5*((505660753828640-505620189009433)/(516047892789268-505620189009433)+2/5)
---------------------------------------------------------------------------~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~字元藉助histogram
2.0194505
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~另外一個 實驗不收集histogram
SQL> create table t4 (a varchar2(10), b char(10));
Table created.
SQL> insert into t4 values ('aa','aa');
1 row created.
SQL> insert into t4 values ('ab','ab');
1 row created.
SQL> insert into t4 values ('ac','ac');
1 row created.
SQL> insert into t4 values ('bb','bb');
1 row created.
SQL> insert into t4 values ('cc','cc');
1 row created.
SQL> commit;
Commit complete.
1 select
2 column_name,
3 endpoint_value
4 from
5 user_tab_histograms
6 where
7* table_name = 'T4'
8 /
no rows selected~~~~~~~~~~~~~~~~~~~~~~~~~~~~沒有histograms
SQL> select * from t4 where a between 'aa' and 'ac';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=ALL_ROWS (Cost=2 Card=1 Bytes=19)
1 0 TABLE ACCESS (FULL) OF 'T4' (TABLE) (Cost=2 Card=1 Bytes=1~~~~~~~~~~~~~~~~~用的是8I 那中方法 base cost optimzer書中CBo_CHAR_VALUE那個函式算出來的,結果是錯的
9)
optimizer_dynamic_sampling 1~~~~~~~~~~~改改 動態取樣
SQL> startup force
ORACLE instance started.
Total System Global Area 188743680 bytes
Fixed Size 788068 bytes
Variable Size 145488284 bytes
Database Buffers 41943040 bytes
Redo Buffers 524288 bytes
Database mounted.
Database opened.
SQL> select * from t4 where a between 'aa' and 'ac';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=ALL_ROWS (Cost=2 Card=3 Bytes=57)
1 0 TABLE ACCESS (FULL) OF 'T4' (TABLE) (Cost=2 Card=3 Bytes=5 /~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~動採 太正確了
7)
SQL> select * from t4 where a between 'aa' and 'ac';
A B
---------- ----------
aa aa
ab ab
ac ac
1 select
2 column_name,
3 endpoint_value
4 from
5 user_tab_histograms
6* where table_name = 'T4'
7
no rows selected~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~動採後,不會自動有HISTOGRAMS
1 begin
2 dbms_stats.gather_table_stats(
3 ownname => user,
4 tabname => 't4',
5 cascade => true,
6 estimate_percent => null,
7 method_opt =>'for all columns size 10'
8 );
9* end;
10 /
PL/SQL procedure successfully completed.
SQL> select
2 column_name,
3 endpoint_value
4 from
5 user_tab_histograms
6 where table_name = 'T4'
7 /
COLUMN_NA End Value
--------- ------------------------------------------------
A 505,620,189,009,433,000,000,000,000,000,000,000
A 505,640,471,419,036,000,000,000,000,000,000,000
A 505,660,753,828,640,000,000,000,000,000,000,000
A 510,832,768,277,571,000,000,000,000,000,000,000
A 516,045,347,545,709,000,000,000,000,000,000,000
B 505,622,734,252,991,000,000,000,000,000,000,000
B 505,643,016,662,594,000,000,000,000,000,000,000
B 505,663,299,072,198,000,000,000,000,000,000,000
B 510,835,313,521,129,000,000,000,000,000,000,000
B 516,047,892,789,268,000,000,000,000,000,000,000
10 rows selected.
SQL> select * from t4 where a between 'aa' and 'ac';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=ALL_ROWS (Cost=2 Card=2 Bytes=28)~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~MD 收集了HISTOGRAM後 card又變成接近了
1 0 TABLE ACCESS (FULL) OF 'T4' (TABLE) (Cost=2 Card=2 Bytes=2
8)
SQL> select 5*((505660753828640-505620189009433)/(516047892789268-50562018900943
3)+1/5+1/5) as card from dual;
CARD
----------
2.0194505
SQL>
SQL> set autotrace off
SQL> explain plan for select * from t4 where a between 'aa' and 'ac';
Explained.
SQL> select * from table(dbms_xplan.display);;
select * from table(dbms_xplan.display);
*
ERROR at line 1:
ORA-00911: invalid character
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 176316199
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 28 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T4 | 2 | 28 | 2 (0)| 00:00:01 |~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~有必要看看dynamic simple 與histograms
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("A"<='ac' AND "A">='aa')
13 rows selected.
SQL>
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-607617/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 大資料實驗記錄大資料
- mysql load 相關實驗記錄MySql
- 直播系統原始碼，選擇驗證方式時選擇郵箱驗證原始碼
- Android WebView 實現檔案選擇、拍照、錄製視訊、錄音AndroidWebView
- 記錄資料無限期保留是MRAM理想的選擇
- 網路安全實驗室題目（選擇題篇）
- Oracle 選擇題知識點整理Oracle
- 【cbo計算公式】Join 選擇率(六)公式
- [20190827]函式索引與選擇率.txt函式索引
- STM32F207DAC實驗記錄
- 【cbo計算公式】單表選擇率(二)公式
- SEO 經驗記錄
- overlay網路隔離實驗失敗記錄
- 《learn to count everything》論文閱讀、實驗記錄
- Oracle實驗(04)：floatOracle
- 寫作平臺選擇記
- 【爬坑日記】.class.class選擇器的選擇問題
- 選擇排序（python）實現排序Python
- 如何在Oracle表中選擇主鍵列BWOracle
- 鴻蒙無許可權實現圖片選擇拍照和錄影片鴻蒙
- 【cbo計算公式】Frequenccy 直方圖選擇率(三)公式直方圖
- SAP中匯率取值選擇邏輯分析測試
- CSS筆記——屬性選擇器CSS筆記
- 如何選擇適合自己的實驗室資訊管理系統LIMS？
- vue實現城市列表選擇Vue
- 002---選擇器（標籤選擇器、類選擇器、id選擇器、偽類選擇器、萬用字元選擇器）字元
- STM32F207串列埠實驗記錄串列埠
- oracle sqldeveloper選擇性複製備份資料庫OracleSQLDeveloper資料庫
- 為什麼你應當選擇 PostgreSQL 而不是 Oracle？SQLOracle
- [轉帖]Oracle JDK 收費後我們如何選擇？OracleJDK
- oracle資料庫的ACFS圖形介面不可選擇Oracle資料庫
- Flutter開發日記-如何實現一個照片選擇器pluginFlutterPlugin
- jQuery選擇器介紹：基本選擇器、層次選擇器、過濾選擇器、表單選擇器jQuery
- 使用 CSS 選擇器實現對不含 title 屬性元素的選擇CSS
- 個人建站伺服器選擇經驗分享伺服器
- css 選擇器及權重筆記CSS筆記
- CSS 小結筆記之選擇器CSS筆記
- Jquery基礎筆記二（選擇器）jQuery筆記
- Oracle實驗(01)：字元 & 位元組Oracle字元