優化04-選擇率和直方圖

選擇率

在Oracle數據庫中，選擇率（Selectivity） 是優化器（CBO，基于成本的優化器）用來評估SQL語句中某個條件（如WHERE子句）過濾數據的比例的關鍵指標。它直接影響優化器選擇執行計劃的策略，例如決定是否使用索引或全表掃描。

選擇率表示滿足某個條件的行數占總行數的比例，

對于等值查詢，參考Oracle的數據字典dba_tab_columns的DENSITY和NUM_DISTINCT字段。

col table_name for a20
col column_name for a20
col LOW_VALUE for a20
col HIGH_VALUE for a20
set line 400
select table_name,column_name,num_distinct,density,NUM_NULLS,LOW_VALUE,HIGH_VALUE
from dba_tab_columns where table_name='EMP';TABLE_NAME           COLUMN_NAME          NUM_DISTINCT    DENSITY  NUM_NULLS LOW_VALUE            HIGH_VALUE
-------------------- -------------------- ------------ ---------- ---------- -------------------- --------------------
EMP                  EMPNO                          14 .071428571          0 C24A46               C25023
EMP                  ENAME                          14 .071428571          0 4144414D53           57415244
EMP                  JOB                             5         .2          0 414E414C595354       53414C45534D414E
EMP                  MGR                             6 .038461538          1 C24C43               C25003
EMP                  HIREDATE                       13 .076923077          0 77B40C11010101       77BB0517010101
EMP                  SAL                            12 .083333333          0 C209                 C233
EMP                  COMM                            4        .25         10 80                   C20F
EMP                  DEPTNO                          3 .333333333          0 C10B                 C11F

對于等值查詢，如果該列沒有空值和直方圖統計信息，選擇率就是DENSITY的值或（1/NUM_DISTINCT）；如果有空值，則可選擇率為：（1/NUM_DISTINCT）*（NUM_ROWS-NUM_NULLS）/NUM_ROWS

對于范圍查詢，選擇率的計算方法就在上述基礎上加入最大值和最小值的統計信息，這里就不多做贅述。

選擇率和索引

選擇率影響著一個SQL的執行計劃，準確的來說，選擇率影響表的訪問方式（即全表掃描還是索引掃描）。Oracle的SQL優化器是基于成本的，我們稱為CBO,CBO會依據選擇率來確定對某一數據集的訪問的成本（COST）,從而選擇成本最低的訪問方式。

例如，表A有8行數據，在表A上對列col1有索引，列col1上有8個不同值，如果SQLA的謂詞條件為col1的等值查詢,對與SQLA的最優執行計劃，CBO會選擇索引掃描；如果表A對列col2有索引，列col2上只有2個不同值，如果SQLB的謂詞條件為col2的等值查詢,對與SQLB的最優執行計劃，CBO可能會選擇全表掃描，因為索引掃描的尋找葉子塊+回表的成本可能會大于全表掃描的成本。

下面我們做一下選擇率的測試

--創建表和索引
create table tab1(id int,name varchar2(10),gender varchar2(5));
create index idx_id on tab1(id);
create index idx_gender on tab1(gender);
--插入2000條數據，id列從1遞增，name列為隨機的5個字符串，gender列為隨機的‘f’或‘m’。
DECLARE-- 定義記錄類型和集合類型TYPE t_employee IS RECORD (id       NUMBER,name     VARCHAR2(5),gender   CHAR(1));TYPE t_employee_tbl IS TABLE OF t_employee;v_data t_employee_tbl := t_employee_tbl(); -- 初始化集合
BEGIN-- 批量生成測試數據（200行）SELECT LEVEL AS id,-- 生成5位隨機大寫字母和數字組合（若只要字母可改用'X'參數）DBMS_RANDOM.STRING('X', 5) AS name,CASE WHEN DBMS_RANDOM.VALUE < 0.5 THEN 'm' ELSE 'f' END AS genderBULK COLLECT INTO v_dataFROM DUALCONNECT BY LEVEL <= 2000;-- 批量插入數據（使用FORALL提升性能）FORALL i IN 1 .. v_data.COUNTINSERT INTO scott.tab1 (id, name, gender)VALUES (v_data(i).id, v_data(i).name, v_data(i).gender);COMMIT; -- 提交事務
EXCEPTIONWHEN OTHERS THENROLLBACK; -- 異常回滾RAISE;
END;
/
--查看統計信息
ANALYZE TABLE scott.tab1 COMPUTE STATISTICS;
col table_name for a10
col column_name for a10
col LOW_VALUE for a20
col HIGH_VALUE for a20
set line 400
select table_name,column_name,num_distinct,density,NUM_NULLS,LOW_VALUE,HIGH_VALUE
from dba_tab_columns where table_name='TAB1';TABLE_NAME COLUMN_NAM NUM_DISTINCT    DENSITY  NUM_NULLS LOW_VALUE            HIGH_VALUE
---------- ---------- ------------ ---------- ---------- -------------------- --------------------
TAB1       ID                 2000      .0005          0 C102                 C215
TAB1       NAME               2000      .0005          0 3030463839           5A5A555138
TAB1       GENDER                2         .5          0 66                   6D
--分別以id和gener列為謂詞條件查看
SQL> set autotrace traceonly;
SQL> select * from scott.tab1 where id=6;Execution Plan
----------------------------------------------------------
Plan hash value: 4102116554----------------------------------------------------------------------------------------------
| Id  | Operation                           | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |        |     1 |     9 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TAB1   |     1 |     9 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IDX_ID |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------SQL> select * from scott.tab1 where gender='f';1019 rows selected.Execution Plan
----------------------------------------------------------
Plan hash value: 2211052296--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1000 |  9000 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TAB1 |  1000 |  9000 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

直方圖

上面都是該列上數據分布均勻的情況，如果數據分布不均勻，及時統計信息是最新的，但其執行計劃可能不是最優的。下面我們測試，

假設一個中學6年級有2000名學生，期中考試分為ABCDE五個等級，其中大部分同學的分數都集中在B,那么查詢分數為B的同學信息可能存在執行計劃不優的情況。

--創建表和索引
SQL> create table tab2(id int,name varchar2(10),grade char(1));
SQL> create  index grade_idx on tab2(grade);
--插入數據
DECLARE-- 定義記錄類型和集合類型TYPE t_student IS RECORD (id     NUMBER,name   VARCHAR2(5),grade  CHAR(1));TYPE t_student_tbl IS TABLE OF t_student;v_data t_student_tbl := t_student_tbl(); -- 初始化集合
BEGIN-- 批量生成測試數據（300行）SELECT LEVEL AS id,-- 生成5位隨機大寫字母和數字組合（若只要字母可改用'X'參數）DBMS_RANDOM.STRING('X', 5) AS name,CHR(65 + FLOOR(DBMS_RANDOM.VALUE(0,5))) AS grade  -- 生成A-EBULK COLLECT INTO v_dataFROM DUALCONNECT BY LEVEL <= 2000;-- 批量插入數據（使用FORALL提升性能）FORALL i IN 1 .. v_data.COUNTINSERT INTO scott.tab2 (id, name, grade)VALUES (v_data(i).id, v_data(i).name, v_data(i).grade);COMMIT; -- 提交事務
EXCEPTIONWHEN OTHERS THENROLLBACK; -- 異常回滾RAISE;
END;
/把id為50-250的學生分數改為B
update tab2 set grade='B' where id>=50 and id <=2500;
commit;#查看統計信息
SQL> ANALYZE TABLE scott.tab2 COMPUTE STATISTICS;Table analyzed.select table_name,column_name,num_distinct,density,NUM_NULLS,LOW_VALUE,HIGH_VALUE2  from dba_tab_columns where table_name='TAB2';TABLE_NAME COLUMN_NAM NUM_DISTINCT    DENSITY  NUM_NULLS LOW_VALUE            HIGH_VALUE
---------- ---------- ------------ ---------- ---------- -------------------- --------------------
TAB2       ID                 2000      .0005          0 C102                 C215
TAB2       NAME               2000      .0005          0 3031324C58           5A5A543245
TAB2       GRADE                 5         .2          0 41                   45#查詢分數為B的學生
SQL> set  autotrace traceonly statistic;
SQL> select * from scott.tab2 where grade='B';1963 rows selected.Execution Plan
----------------------------------------------------------
Plan hash value: 1237454846-------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |           |   400 |  3600 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TAB2      |   400 |  3600 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | GRADE_IDX |   400 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------2 - access("GRADE"='B')Statistics
----------------------------------------------------------1  recursive calls0  db block gets272  consistent gets0  physical reads0  redo size59224  bytes sent via SQL*Net to client2037  bytes received via SQL*Net from client132  SQL*Net roundtrips to/from client0  sorts (memory)0  sorts (disk)1963  rows processed
--上面查詢采用的索引掃描，邏輯讀為272，如果強制讓SQL使用全表掃描SQL> select /*+FULL(tab2) */ *  from scott.tab2 where grade='B';1963 rows selected.Execution Plan
----------------------------------------------------------
Plan hash value: 2156729920--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   400 |  3600 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TAB2 |   400 |  3600 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------1 - filter("GRADE"='B')Statistics
----------------------------------------------------------1  recursive calls0  db block gets138  consistent gets0  physical reads0  redo size54904  bytes sent via SQL*Net to client2037  bytes received via SQL*Net from client132  SQL*Net roundtrips to/from client0  sorts (memory)0  sorts (disk)1963  rows processed--邏輯讀為138，

明明全表掃描的邏輯讀更低，為什么CBO還是使用索引掃描的執行計劃呢？因為統計信息不知道列grade分布不均勻，安裝1/5的選擇率生成的執行計劃，這種情況可以通過收集列的直方圖來解決。

收集之前確定grade沒有直方圖統計信息

 select table_name,column_name,num_distinct,density,HISTOGRAMfrom dba_tab_columns where table_name='TAB2';
TABLE_NAME COLUMN_NAM NUM_DISTINCT    DENSITY HISTOGRAM
---------- ---------- ------------ ---------- ---------------
TAB2       ID                 2000      .0005 NONE
TAB2       NAME               2000      .0005 NONE
TAB2       GRADE                 5         .2 NONE

收集grade列直方圖

EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'TAB2', METHOD_OPT => 'FOR COLUMNS GRADE SIZE AUTO');

再次查看grade列統計信息

SQL> select table_name,column_name,num_distinct,density,HISTOGRAM2   from dba_tab_columns where table_name='TAB2';TABLE_NAME COLUMN_NAM NUM_DISTINCT    DENSITY HISTOGRAM
---------- ---------- ------------ ---------- ---------------
TAB2       ID                 2000      .0005 NONE
TAB2       NAME               2000      .0005 NONE
TAB2       GRADE                 5     .00025 FREQUENCY

這時我們再次查看分數為B的學生信息

SQL> set autotrace traceonly;
SQL>  select * from scott.tab2 where grade='B';1963 rows selected.Execution Plan
----------------------------------------------------------
Plan hash value: 2156729920--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1963 | 19630 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TAB2 |  1963 | 19630 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------1 - filter("GRADE"='B')Statistics
----------------------------------------------------------1  recursive calls0  db block gets138  consistent gets0  physical reads0  redo size54904  bytes sent via SQL*Net to client2037  bytes received via SQL*Net from client132  SQL*Net roundtrips to/from client0  sorts (memory)0  sorts (disk)1963  rows processed

收集了直方圖后，選擇了成本更低的執行計劃。

本文來自互聯網用戶投稿，該文觀點僅代表作者本人，不代表本站立場。本站僅提供信息存儲空間服務，不擁有所有權，不承擔相關法律責任。
如若轉載，請注明出處：http://www.pswp.cn/bicheng/79874.shtml
繁體地址，請注明出處：http://hk.pswp.cn/bicheng/79874.shtml
英文地址，請注明出處：http://en.pswp.cn/bicheng/79874.shtml

如若內容造成侵權/違法違規/事實不符，請聯系多彩編程網進行投訴反饋email:809451989@qq.com，一經查實，立即刪除！

相關文章

python實戰:通過輸入文字匹配在docx文檔中的具體位置

python實戰:通過輸入文字匹配在docx文檔中的具體位置

在指定的docx文檔中,輸入一串文字來查看該文字在文檔中的具體位置;方便后續處理(如替換文字,高亮顯示等等操作) from docx import Documentdef find_text_in_docx(file_path, search_text):# 讀取docx文件doc = Document(file_path)# 遍歷段落，查找匹配的文本for i
閱讀更多...
Flutter——數據庫Drift開發詳細教程(二)

Flutter——數據庫Drift開發詳細教程(二)

目錄 1.核心API1.1查詢數據列表分頁1.2 列表排序1.3推遲獲取與觀察 1.核心API 1.1查詢數據列表分頁 限制返回的結果數量limit&#xff0c;從某一位置開始查詢offset ///limit10, offset10 Future<List<TodoItem>> limitTodos(int limit, {int? offset}) {return …
閱讀更多...
mux-vlan基礎配置

mux-vlan基礎配置

1.top配置 2.各個交換機設置 sw3交換機的 sysname swb # undo info-center enable # vlan batch 10 20 30 100 # vlan 10description financial vlan vlan 20description marketing vlan vlan 30description client vlan vlan 100description principal vlanmux-vlansubordi…
閱讀更多...
SAM詳解2(初級應用)

SAM詳解2(初級應用)

SAM SAM5. 初級應用5.1 靜態本質不同子串個數5.2 字符串匹配5.3 關于子串出現次數5.4 動態添加時本質不同子串個數SAM 5. 初級應用 記 l o n g e s t ( x ) longest(x) longest(x) 為點 x x x 代表子串集合中最長串的長度。記 s h o r t e s t ( x ) shortest(x) shortest(…
閱讀更多...
【日擼 Java 三百行】Day 4（條件語句實戰——閏年問題）

【日擼 Java 三百行】Day 4（條件語句實戰——閏年問題）

目錄 Day 4&#xff1a;條件語句實戰——閏年問題 一、基礎知識及代碼思路 二、代碼及測試 小結 Day 4&#xff1a;條件語句實戰——閏年問題 Task&#xff1a; if 語句的嵌套.基本規律自行百度.布爾類型. 一、基礎知識及代碼思路 1. 什么是閏年&#xff1f; 閏年是歷法中…
閱讀更多...
MySQL 中 EXISTS (SELECT 1 FROM ...) 的用法詳解

MySQL 中 EXISTS (SELECT 1 FROM ...) 的用法詳解

EXISTS (SELECT 1 FROM ...) 是 MySQL 中用于存在性檢查的核心語法&#xff0c;其核心邏輯是判斷子查詢是否返回至少一行數據。以下從作用原理、使用場景、性能優化等方面展開解析&#xff0c;并結合具體示例說明。 1. 基本語法與作用原理 語法結構&#xff1a; SELECT 列名 F…
閱讀更多...
阿里云服務器防御是怎么做出來的?服務器攻擊方式有幾種？

阿里云服務器防御是怎么做出來的?服務器攻擊方式有幾種？

阿里云服務器防御是怎么做出來的?服務器攻擊方式有幾種&#xff1f; 服務器防御是一個多層次、多維度的體系&#xff0c;通常包括以下核心措施&#xff1a; 1. 網絡層防御 防火墻&#xff08;Firewall&#xff09;&#xff1a;過濾非法流量&#xff0c;僅允許授權通信&#xf…
閱讀更多...
ElasticSearch深入解析（八）：索引設置、索引別名、索引模板

ElasticSearch深入解析（八）：索引設置、索引別名、索引模板

一、索引的動態設置、靜態設置 索引設置包含兩部分核心內容&#xff1a; 靜態設置(static index settings)&#xff0c;只允許在創建索引時或者針對已關閉的索引進行設置。指動態設置(dynamic index settings)&#xff0c;可以借助更新設置(update settings)的方式進行動態更新…
閱讀更多...
Prometheus實戰教程：k8s平臺手動部署Grafana

Prometheus實戰教程：k8s平臺手動部署Grafana

以下是一個可用于生產環境的 Kubernetes 部署 Grafana 的 YAML 文件。該配置包括 Deployment、Service、ConfigMap 和 PersistentVolumeClaim&#xff0c;確保 Grafana 的高可用性和數據持久化。 Grafana 生產部署 YAML 文件 ☆實操示例 cat grafana-deployment.yaml 登錄后復制…
閱讀更多...
VSTO外接程序與VBA的聯動嘗試

VSTO外接程序與VBA的聯動嘗試

文章目錄 前言一、第一坑&#xff1a;安裝offic2007后excel加載項找不到了二、示例1 通過Ribbon XML自定義Excel主菜單并添加新項二、示例1 總結三、示例2 創建VSTO外接程序三、示例2 總結四、示例 3 C# VSTO外接程序示例四、示例 3 總結五、實現C# 的VSTO調用VBA函數(xlam)六、…
閱讀更多...
DeepSeek驅動的金市情緒量化：NLP解析貿易政策文本的情緒傳導路徑

DeepSeek驅動的金市情緒量化：NLP解析貿易政策文本的情緒傳導路徑

【AI觀察】政策信號與市場情緒的量化關聯 基于自然語言處理技術對全球財經文本的情緒分析顯示&#xff0c;4月30日亞盤時段現貨黃金價格波動率較前日下降12.3%&#xff0c;與技術面修正指標呈現強相關性。特政府最新關稅政策調整引發市場風險偏好指數&#xff08;RPI&#xff…
閱讀更多...
期末代碼Python

期末代碼Python

以下是 學生信息管理系統 的簡化版代碼示例&#xff08;控制臺版本&#xff0c;使用文件存儲數據&#xff09;&#xff0c;包含核心功能&#xff1a; 1. 定義學生類 class Student: def __init__(self, sid, name, score): self.sid sid # 學號 self.name name # 姓名 self.s…
閱讀更多...
zotero pdf中英翻譯插件使用

zotero pdf中英翻譯插件使用

最近發現一個pdf中英翻譯的神器zotero-pdf2zh&#xff0c;按照官方安裝教程走一遍的時候&#xff0c;發現一些流程不清楚的問題&#xff0c; 此文就是整理一些安裝需要的文件以及遇到的問題&#xff1a; 相關文件下載地址 Zotero 是一款免費的、開源的文獻管理工具&#xff0…
閱讀更多...
本地MySQL連接hive

本地MySQL連接hive

1、首先需要修改MySQL的配置&#xff0c;允許遠程連接&#xff1a; # 在本地MySQL服務器上執行 sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf找到 bind-address 行&#xff0c;將其修改為&#xff1a; bind-address 0.0.0.02、在本地MySQL中創建用戶并授權&#xff08;注意…
閱讀更多...
Nginx核心功能2

Nginx核心功能2

一&#xff1a;正向代理 正向代理&#xff08;Forward Proxy)是一種位于客戶端和原始服務器之間的代理服務器&#xff0c;其主要作用是將客戶端的請求轉發給目標服務器&#xff0c;并將響應返回給客戶端Nginx的正向代理充當客戶端的“中間人”&#xff0c;代表用戶訪問外部資源…
閱讀更多...
高定電視，一場關于生活方式的覺醒

高定電視，一場關于生活方式的覺醒

需要有自己的工作室&#xff0c;雇用3個以上專職模特&#xff0c;至少15名全職員工和20名技術工匠?&#xff1b; 每年都要參加巴黎高級時裝周&#xff0c;展示至少50款原創設計&#xff1b; 使用的面料必須高質量、昂貴且不同尋常&#xff0c;設計上注重細節和個性&#x…
閱讀更多...
用PyTorch搭建卷積神經網絡實現MNIST手寫數字識別

用PyTorch搭建卷積神經網絡實現MNIST手寫數字識別

用PyTorch搭建卷積神經網絡實現MNIST手寫數字識別 在深度學習領域&#xff0c;卷積神經網絡&#xff08;Convolutional Neural Network&#xff0c;簡稱CNN&#xff09;是處理圖像數據的強大工具。它通過卷積層、池化層和全連接層等組件&#xff0c;自動提取圖像特征&#xff…
閱讀更多...
Tensorrt 基礎入門

Tensorrt 基礎入門

什么是tensorrt? 其他廠商: Qualcomm, Hailo, google TPU tensorrt的優劣勢 使用tensorrt的pipeline tensorrt使用中存在的問題以及解決方案 tensorrt的應用場景 自動駕駛模型部署需要關注的問題&#xff1a; 邊端硬件資源有限 散熱&#xff08;不能水冷&#xff09; 實時性&…
閱讀更多...
Qt 顯示QRegExp 和 QtXml 不存在問題

Qt 顯示QRegExp 和 QtXml 不存在問題

QRegExp 和 QtXml 問題 在Qt6 中 已被棄用&#xff1b; 1&#xff09;QRegExp 已被棄用&#xff0c;改用 QRegularExpression Qt5 → Qt6 重大變更&#xff1a;QRegExp 被移到了 Qt5Compat 模塊&#xff0c;默認不在 Qt6 核心模塊中。 錯誤類型解決方法QRegExp 找不到改用 Q…
閱讀更多...
玩玩OCR

玩玩OCR

一、Tesseract: 1.下載windows版&#xff1a; tesseract 2. 安裝并記下路徑&#xff0c;等會要填 3.保存.py文件 import pytesseract from PIL import Image def ocr_local_image(image_path):try:pytesseract.pytesseract.tesseract_cmd rD:\Programs\Tesseract-OCR\tesse…
閱讀更多...
最新文章

Copyright @ 2022~2023