我的問題是為什么選項中有完全訪問權限但是我為 LIGPOR 和 DATRAI 表創建了兩個索引？ 如果有任何人可以幫助我清楚地閱讀sql oracle中的長執行計劃
我有這個查詢：
select *
FROM DATRAI D,
LIGPOR L
WHERE D.COMAR= L.COMAR
and TO_CHAR(l.daprm,'yyyy') ='2017';
這是執行計劃
SQL_ID 0v5mmx6sg8scu, child number 0
-------------------------------------
select * FROM LIGPOR L,DATRAI D WHERE D.COMAR= L.COMAR and
TO_CHAR(l.daprm,:"SYS_B_0") =:"SYS_B_1"
Plan hash value: 3308708566
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 9 (100)| |
| 1 | NESTED LOOPS | | 1 | 2177 | 9 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 2177 | 9 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | LIGPOR | 1 | 2138 | 8 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | DATRAI1 | 1 | | 0 (0)| |
| 5 | TABLE ACCESS BY INDEX ROWID| DATRAI | 1 | 39 | 1 (0)| 00:00:01
表創作
CREATE TABLE "user"."LIGPOR"
(
"COINT" VARCHAR2(11 BYTE) NOT NULL ENABLE,
"COMAR" VARCHAR2(5 BYTE) NOT NULL ENABLE,
"DAPRM" DATE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
CREATE TABLE "user"."DATRAI"
( "COMAR" VARCHAR2(5 BYTE) NOT NULL ENABLE,
"DATRA" DATE NOT NULL ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
索引創建
CREATE INDEX "user"."LIGPOR5" ON "user"."LIGPOR" ("COMAR")
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
CREATE UNIQUE INDEX "user"."DATRAI1" ON "user"."DATRAI" ("COMAR")
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "UBIX_TABLES" ;
thix :)
uj5u.com熱心網友回復：
您使用的 SQL 很簡單，計劃也很簡單。資料庫正在做的是從一個表開始，這里它選擇了 LIGPO，使用全掃描讀取資料，因為沒有謂詞允許它使用索引。然后，對于每一行，它使用第二個表上的索引來查看是否有任何匹配的行（因為第二個表在 COMAR 列上有一個索引）。如果找到一個，它會使用索引給出的 rowid 從表中讀取剩余的資料。根據您的表的大小，它可能會選擇相反的方式，即從使用全掃描的 DATRAI 開始，使用索引檢查 LIGPO 中的匹配行。
優化器所做的取決于物件的統計資訊，并且對于像您這樣的查詢，它很可能會選擇對兩個表進行完整掃描的哈希聯接。
最后一句話：不要寫謂詞 like TO_CHAR(l.daprm,'yyyy') ='2017'，因為即使你在 DAPRM 上有一個索引，資料庫也不會使用它。最好做類似的事情ldaprm >= date '2017-01-01' and ldaprm < date '2018-01-01'。
uj5u.com熱心網友回復：
首先，您必須意識到一般規則索引快 - 全表掃描慢幾乎沒有例外。
最明顯的是
表很小，它只包含幾個塊（一個簡單的多塊讀取比在索引和表之間重復跳轉更好）
您想訪問大部分表資料（再次在索引和表之間切換時死掉）
我想第一點是你的情況。
讓我們嘗試模擬它。基本上，你有一個父 DATRAI- 子 LIGPOR表，由連接PK/FK柱COMAR。
樣本資料
create table DATRAI as
select rownum COMAR,
date'2017-01-01' rownum DATRA
from dual connect by level <= 2;
CREATE UNIQUE INDEX "DATRAI1" ON "DATRAI" ("COMAR");
create table LIGPOR as
select rownum COINT,
mod(rownum,1000) 1 COMAR,
date'2017-01-01' rownum DAPRM
from dual connect by level <= 20;
CREATE INDEX "LIGPOR5" ON "LIGPOR" ("COMAR");
請注意，我是在18G中。當物件統計資訊都在網上聚集在創建表和索引，在以前的版本中你需要添加的呼叫dbms_stats.gather_table_stats
OLTP 示例
讓我們從典型示例開始（您可能已經想到） - 您使用主鍵( COMAR = 10)約束父級，并使用外鍵訪問所有對應的子行。
EXPLAIN PLAN SET STATEMENT_ID = 'EXPL_1' into plan_table FOR
SELECT *
FROM DATRAI D
JOIN LIGPOR L ON D.COMAR= L.COMAR
WHERE
D.COMAR = 10 AND
TO_CHAR(l.daprm,'yyyy') ='2017';
SELECT * FROM table(DBMS_XPLAN.DISPLAY('plan_table', 'EXPL_1','ALL'));
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 13 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 28 | 13 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID | DATRAI | 1 | 12 | 2 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | DATRAI1 | 1 | | 1 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID BATCHED| LIGPOR | 1 | 16 | 11 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | LIGPOR5 | 10 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("D"."COMAR"=10)
4 - filter(TO_CHAR(INTERNAL_FUNCTION("L"."DAPRM"),'yyyy')='2017')
5 - access("L"."COMAR"=10)
簡短說明如何閱讀執行計劃
您從具有最高意圖的第一行的行開始，即帶有 的行Id = 3。
在Id標有星號，讓你檢查Predicate Information的訪問/過濾條件-在這里access("D"."COMAR"=10)
THS意味著你使用指數（指數名稱是列Name）的科拉姆中選擇你作為甲骨文運籌學Rows 一個排。
Next ( Id = 2) 你rowid從索引中取出來訪問表
Next( Id = 1) 您在其中NESTED LOOPS，即對您執行該行的每一行Id 5 and 4，即再次索引訪問，access("L"."COMAR"=10)然后是rowid 表訪問。
請注意，這BATCHED是最近版本中的優化 - 我不能發表太多評論。
所以我猜 - 這是你會喜歡的執行計劃。
其他示例
讓我們回到您的示例，它基本上是相同的查詢 - 只是沒有 PK predicate。
EXPLAIN PLAN SET STATEMENT_ID = 'EXPL_2' into plan_table FOR
SELECT *
FROM DATRAI D
JOIN LIGPOR L ON D.COMAR= L.COMAR
WHERE
TO_CHAR(l.daprm,'yyyy') ='2017';
SELECT * FROM table(DBMS_XPLAN.DISPLAY('plan_table', 'EXPL_2','ALL'));
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 25 | 4 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 25 | 4 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | LIGPOR | 1 | 14 | 3 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | DATRAI1 | 1 | | 0 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| DATRAI | 1 | 11 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(TO_CHAR(INTERNAL_FUNCTION("L"."DAPRM"),'yyyy')='2017')
4 - access("D"."COMAR"="L"."COMAR")
同樣是起始行Id = 3，但這里 Oracle 選擇從子表開始LIGPOR。為什么？至少有一個過濾謂詞Id = 3可以降低 Oracle Cost Based Optimizer 的成本。
該替代將是表的全面掃描DATRAI，你有沒有過濾的。
*請注意訪問謂詞 - 主動索引選擇 - 和過濾謂詞之間的關鍵區別- 您必須處理所有行并丟棄其中一些行。
其余的基本上是和以前一樣的嵌套回圈，只是在技術上執行了兩次第一次索引訪問比表訪問。
為了不讓答案太長，我做了總結
不要
table access full過早地害怕select *如果可以限制結果列，請不要使用- 這可以使 Oracle 進行更好的優化
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/404892.html
標籤：