INDEX'S COST VS TABLE ACCESS FULL'S COST

發文回覆瀏覽次數：2982

推到 Plurk!

推到 Facebook!

INDEX'S COST VS TABLE ACCESS FULL'S COST

答題得分者是：VICSYS

alvin26
初階會員

發表：24
回覆：41
積分：28
註冊：2005-04-12

發送簡訊給我

#1 引用回覆回覆

發表時間：2007-09-11 16:58:19

IP:219.87.xxx.xxx 訂閱

以下是一個TABLE在 ORACLE 的結購:

SQL_EMP 的結構：
deptno

欄位

Key

empno

Number

PK_SQL_EMP

empname

Varchar2(10)

Number

IDX_SQL_EMP_DEPTNO

資料由１～１０

然後查看下列兩個SQL的執行計劃:
第一個：
select /* INDEX(SQL_EMP, PK_SQL_EMP) */ empno, empname, deptno
from SQL_emp order by empno, empname, deptno

以下是第一個的執行計劃結果：
DescriptionSELECT STATEMENT, GOAL = ALL_ROWS

Description		GOAL = ALL_ROWS	SORT ORDER BY	TABLE ACCESS BY INDEX ROWID			INDEX FULL SCAN
Object Owner	Object Name	Cost
				ECOPER	SQL_EMP	8

想請教一下各位大大：
這個TABLE裡有１萬筆資料，
有兩個 index :
PK_SQL_EMP
IDX_SQL_EMP_DEPTNO
我有兩個問題：
１：為什麼有用 INDEX IDX_SQL_EMP_DEPTNO 了，怎麼還是 TABLE ACCESS FULL呢？？
２：兩個 INDEX 的 Explain Plan 不同，一個有用到 INDEX( PK_SQL_EMP) ，
另一個雖然已經指定 INDEX(IDX_SQL_EMP_DEPTNO)了，
但是還是用 TABLE ACCESS FULL，更奇怪的是，TABLE ACCESS FULL 竟然比用 INDEX( PK_SQL_EMP)的 COST 小？？
為什麼 TABLE ACCESS FULL 的 COST 反而比較小呢？？

------
弟子規，聖人訓，首孝悌，次謹信，泛愛眾，而親仁，有餘力，則學文。

編輯記錄

alvin26 重新編輯於 2007-09-12 09:47:55，註解無‧
alvin26 重新編輯於 2007-09-12 10:27:31，註解無‧
alvin26 重新編輯於 2007-09-12 10:29:10，註解無‧
alvin26 重新編輯於 2007-09-12 10:31:19，註解無‧
alvin26 重新編輯於 2007-09-12 10:33:17，註解無‧
alvin26 重新編輯於 2007-09-12 10:33:42，註解無‧
alvin26 重新編輯於 2007-09-12 10:35:04，註解無‧
alvin26 重新編輯於 2007-09-12 10:37:08，註解無‧
alvin26 重新編輯於 2007-09-13 17:49:51，註解無‧
alvin26 重新編輯於 2007-09-13 17:50:24，註解無‧
alvin26 重新編輯於 2007-09-13 17:51:11，註解無‧

VICSYS
初階會員

發表：21
回覆：64
積分：32
註冊：2002-10-10

發送簡訊給我

#2 引用回覆回覆

發表時間：2007-09-11 21:54:20

IP:59.105.xxx.xxx 訂閱

不知道為什麼, 版面很亂, 看不出來, 到底你想要用那一個 Index
不過 EMPNO 不就是 PRIMARY KEY 嗎?
還有讀取的 SQL 都是讀取全部!
Index 只是作為排序的機制, 不是過濾資料 (100 萬筆資料找出一些資料)
本來就是 TABLE ACCESS FULL(不是過濾資料), 跑 TABLE ACCESS FULL 很正常!

這兩個例子, 應該不需要指定 Index 及 Order by
select empno, empname, deptno from SQL_emp where empno >' '
ORACLE 自動會用 PK 來排序, 不需要去指定 Index!

alvin26
初階會員

發表：24
回覆：41
積分：28
註冊：2005-04-12

發送簡訊給我

#3 引用回覆回覆

發表時間：2007-09-12 10:23:41

IP:61.63.xxx.xxx 訂閱

真是不好意思，我把版面重排了，希望這次能讓各位大大比較了解我要問的是什麼。
其實我想問的，是:
１：有用 INDEX 為何還是會 TABLE ACCESS FULL?
２：TABLE ACCESS FULL 的 COST 竟然比用 INDEX 的 COST 還小？？
真是不解 @@

另外，我覺得索引好像不是用來排序的，
書上不都說，用索引可以增加查詢的速度嗎.....？
不過還是感謝VICSYS大大熱心的回答，3Q3Q~

------
弟子規，聖人訓，首孝悌，次謹信，泛愛眾，而親仁，有餘力，則學文。

編輯記錄

alvin26 重新編輯於 2007-09-12 10:25:12，註解無‧

Fishman
尊榮會員

發表：120
回覆：1949
積分：2163
註冊：2006-10-28

發送簡訊給我

#4 引用回覆回覆

發表時間：2007-09-12 11:35:42

IP:59.124.xxx.xxx 未訂閱

沒指定 WHERE Condition 當然是 FULL SCAN 囉！
索引確實可以增加查詢的速度，但是必須搭配 WHERE 的語句才可發揮效用！

------
Fishman

alvin26
初階會員

發表：24
回覆：41
積分：28
註冊：2005-04-12

發送簡訊給我

#5 引用回覆回覆

發表時間：2007-09-12 12:10:52

IP:219.87.xxx.xxx 訂閱

是沒錯，但因為我是自行指定要用哪一個 INDEX 做 QUERY,
(像第一個QUERY)，我沒有下 WHERE
ORACLE 還是會按照我指定的 INDEX 去做 EXPLAIN PLAN.
只是第二個雖然指定了INDEX，但還是 TABLE ACCESS FULL.
這就是我的疑問..

===================引用 Fishman 文章===================
沒指定 WHERE Condition 當然是 FULL SCAN 囉！
索引確實可以增加查詢的速度，但是必須搭配 WHERE 的語句才可發揮效用！

------
弟子規，聖人訓，首孝悌，次謹信，泛愛眾，而親仁，有餘力，則學文。

Fishman
尊榮會員

發表：120
回覆：1949
積分：2163
註冊：2006-10-28

發送簡訊給我

#6 引用回覆回覆

發表時間：2007-09-12 12:26:20

IP:59.124.xxx.xxx 未訂閱

想像一下你今天在一座圖書館的入口，你的BOSS跟你說：
1.去把圖書館的書全部搬出來(select * from yourtable)
1.1
無Index-->進入圖書館，推著推車，把一本一本的書放進推車然後全部搬出來(FULL SCAN & Return All Data)
1.2
指定Index-->進入圖書館，按照指定索引表找出書存放位置，把一本一本的書放進推車然後全部搬出來(一樣是Full Scan)
PS:一般 select * from yourtable 是不會用到 Index 的，除非指定
2.去把所有書名叫做 "xxx" 的書搬出來(select * from yourtable where BookName = 'xxx')
2.1
無Index-->進入圖書館，推著推車，把一本一本的書拿出來看，若是書名是'xxx'就放進推車不是就放回去，將全部書看過一遍後把他車推出來(FULL SCAN & Return Some Data or No Data)
2.2
有Index-->進入圖書館，查詢索引表(索引表有排序功能，找出符合的第一筆資料，往下直到不符合的資料即可停止)，若是索引中沒有該書，也無須進入存放書的區域找書，找出所有書名是'xxx'放的存放位置，再到指定位址取出書放入推車中，完成後推出去
哪一個最快？哪一個最慢？

------
Fishman

alvin26
初階會員

發表：24
回覆：41
積分：28
註冊：2005-04-12

發送簡訊給我

#7 引用回覆回覆

發表時間：2007-09-12 14:20:48

IP:219.87.xxx.xxx 訂閱

這一點(1.2)我就是不解，
既然是指定了 INDEX ,
為何是在 TABLE 中做 FULL SCAN,
而不是在 INDEX 做 FULL SCAN 呢?
因為EXPLAIN PLAN 是寫 TABLE ACCESS FULL 而不是 INDEX FULL SCAN 耶...(不解中)
跟資料的變化有關係嗎？因為 DEPTNO 的值只有由１到１０而已，我只是懷疑...

===================引用 Fishman 文章===================
1.2
指定Index-->進入圖書館，按照指定索引表找出書存放位置，把一本一本的書放進推車然後全部搬出來(一樣是Full Scan)
PS:一般 select * from yourtable 是不會用到 Index 的，除非指定

------
弟子規，聖人訓，首孝悌，次謹信，泛愛眾，而親仁，有餘力，則學文。

VICSYS
初階會員

發表：21
回覆：64
積分：32
註冊：2002-10-10

發送簡訊給我

#8 引用回覆回覆

發表時間：2007-09-13 08:10:00

IP:59.105.xxx.xxx 訂閱

select /*+ INDEX(SQL_EMP, IDX_SQL_EMP_DEPTNO) */ empno, empname, deptno from SQL_emp order by empno, empname, deptno
不合理的指定, Oracle 忽略了"指定 Index"!

下列才會有用
select /* INDEX(SQL_EMP, IDX_SQL_EMP_DEPTNO) */ empno, empname, deptno from SQL_emp order by deptno

編輯記錄

VICSYS 重新編輯於 2007-09-13 08:12:03，註解無‧

alvin26
初階會員

發表：24
回覆：41
積分：28
註冊：2005-04-12

發送簡訊給我

#9 引用回覆回覆

發表時間：2007-09-13 09:43:17

IP:61.63.xxx.xxx 訂閱

我把 select /*+ INDEX(SQL_EMP, IDX_SQL_EMP_DEPTNO) */ empno, empname, deptno from SQL_emp order by deptno
上機測了一下：
SELECT STATEMENT, GOAL = ALL_ROWS

Object owner

Cost

SORT ORDER BY

TABLE ACCESS FULL

SQL_EMP

TABLE ACCESS FULL 耶...
另外，如果排序的方式：order by empno, empname, deptno 不能改變的話，還有其他辦法嗎？

===================引用 VICSYS 文章===================
select /* INDEX(SQL_EMP, IDX_SQL_EMP_DEPTNO) */ empno, empname, deptno from SQL_emp order by empno, empname, deptno
不合理的指定, Oracle 忽略了"指定 Index"!

下列才會有用
select /* INDEX(SQL_EMP, IDX_SQL_EMP_DEPTNO) */ empno, empname, deptno from SQL_emp order by deptno

------
弟子規，聖人訓，首孝悌，次謹信，泛愛眾，而親仁，有餘力，則學文。

編輯記錄

alvin26 重新編輯於 2007-09-13 17:48:13，註解無‧

VICSYS
初階會員

發表：21
回覆：64
積分：32
註冊：2002-10-10

發送簡訊給我

#10 引用回覆回覆

發表時間：2007-09-13 16:17:39

IP:59.105.xxx.xxx 訂閱

改為 where 來測試!
select /* INDEX(SQL_EMP, IDX_SQL_EMP_DEPTNO) */ empno, empname, deptno from SQL_emp
where deptno >' '

另外, 排序為 empno, empname, deptno
怎麼會用 IDX_SQL_EMP_DEPTNO 來當 Index?
Oracle 應該會把不合理的"指定"忽略!

您是希望 Oracle 用下列的方式去跑嗎?
select empno, empname, deptno
from(
select /* INDEX(SQL_EMP, IDX_SQL_EMP_DEPTNO) */ empno, empname, deptno from SQL_emp
where deptno >' '
)
order by empno, empname, deptno

kadee
高階會員

發表：11
回覆：141
積分：165
註冊：2002-03-20

發送簡訊給我

#11 引用回覆回覆

發表時間：2007-09-13 17:22:50

IP:59.127.xxx.xxx 訂閱

如果 SQL中沒有where的時候，一般情況下database引擎會傾向使用TABLE SCAN。
order by 的要求會在把資料從HD抓到RAM中再行排序，因為RAM的
操作速度比HD要快很多,如果為了order by 先讀取index，再到table中
按index提供的位置讀取紀錄，HD的IO會是TABLE SCAN的兩倍以上。
除非DB本身知道此table的紀錄筆數太大，沒有辦法一次全部搬到
RAM做排序，或者你的QUERY只要dataset中的最前頭幾筆紀錄( SQL中有 first 1 之類的)，
DB才會先找有沒有適當的INDEX，經由INDEX的指標到table中抓紀錄。

以上是我所知道的一般DB 引擎通則，ORACAL我沒用過，是不是因為此原因，純屬猜測。

kadee/www.bigredinf.net

------
Kadee/BigRed Ent.
www.tw165.com

alvin26
初階會員

發表：24
回覆：41
積分：28
註冊：2005-04-12

發送簡訊給我

#12 引用回覆回覆

發表時間：2007-09-13 17:34:00

IP:219.87.xxx.xxx 訂閱

果然下 where 會比較好，下面是執行結果：
select /* INDEX(SQL_EMP, IDX_SQL_EMP_DEPTNO) */ empno, empname, deptno
from SQL_emp
where deptno >' '

Description	Object name	Cost
SELECT STATEMENT, GOAL = ALL_ROWS		3
TABLE ACCESS BY INDEX ROWID	SQL_EMP	3
INDEX RANGE SCAN	IDX_SQL_EMP_DEPTNO	2

看來就算是我指定了 INDEX , 但如果指定的不合理的 INDEX ，
ORACLE 也不會採用。反而是用 WHERE 更能強迫
ORACLE 使用該欄位的 INDEX。

其實我不是要非得用這個奇怪的 INDEX 不可，
只是發現用了這一個 INDEX 之後的 COST 很小，
而且還是 TABLE ACCESS FULL ，
讓我覺得不解而已，我想知道為什麼會是這樣的結果。
現在覺得是 ORACLE 不採用我指定的 INDEX
而自行分析使用最好的查詢方式去做，
不知道我這個想法合理嗎？

===================引用 VICSYS 文章===================
改為 where 來測試!
select /* INDEX(SQL_EMP, IDX_SQL_EMP_DEPTNO) */
empno, empname, deptno from SQL_emp
where deptno >' '

另外, 排序為 empno, empname, deptno
怎麼會用 IDX_SQL_EMP_DEPTNO 來當 Index?
Oracle 應該會把不合理的"指定"忽略!

您是希望 Oracle 用下列的方式去跑嗎?
select empno, empname, deptno
from(
select /* INDEX(SQL_EMP, IDX_SQL_EMP_DEPTNO) */
empno, empname, deptno from SQL_emp
where deptno >' '
)
order by empno, empname, deptno

------
弟子規，聖人訓，首孝悌，次謹信，泛愛眾，而親仁，有餘力，則學文。

編輯記錄

alvin26 重新編輯於 2007-09-13 17:42:19，註解無‧
alvin26 重新編輯於 2007-09-13 17:45:04，註解無‧
alvin26 重新編輯於 2007-09-13 17:45:51，註解無‧
alvin26 重新編輯於 2007-09-13 17:46:57，註解無‧

VICSYS
初階會員

發表：21
回覆：64
積分：32
註冊：2002-10-10

發送簡訊給我

#13 引用回覆回覆

發表時間：2007-09-14 10:45:58

IP:59.105.xxx.xxx 訂閱

Fishman 的圖書館例子很好, 解釋了 "全部搬出來" 跟按索引表全部搬出來的 COST 情況!
按索引表 COST 比較高! 但是我大都必須使用這樣的方式!

按您的例子, 如果 Table 有上百萬筆, 用程式去顯示, 以 EMPNO 的排序資料,
程式如果是一次讀取上百萬筆, 這時使用 TABLE ACCESS FULL 即可!

但是程式如果分段讀取, 第一次讀取 100 筆, 這時會如何?
Oracle 是不是要"盡快"將排序過的資料回傳給程式顯示!
那麼要用 INDEX FULL SCAN ....

COST 不是指執行更快! 事實上我都不管 COST.
甚至為了回應快速, 常常用 /* FIRST_ROW*/!, 這大多會增加 COST!

有時 Oracle Index 用抓錯(不能說是抓錯, 只是抓了不合意) !
像一個大量資料的表格 "連接" 一個小量資料的表格!
兩個連接欄位都有 Index, 如果 Oracle 使用了小量資料的 Index,
我會強制改變 Where 條件, 讓 Oracle 使用大量資料的 Index!

select ..
from LargeTable L, SmallTable S
where L.IndexField = S.IndexField||''
...

S.IndexField||'' 就會改變 Oralce 讓 SmallTable.IndexField Index 失效!

這種情況 Cost 會如何? 我不在意!
重點是您要 Oracle 如何回應資料
where deptno >' ' COST 很少, 但是按部門排序(只有 0 跟 1), 這不是您要的!
您要的是按員工編號排序, where EMPNO >' ' (在FORM 這樣寫), Order By EMPNO(REPORT 這樣寫)

alvin26
初階會員

發表：24
回覆：41
積分：28
註冊：2005-04-12

發送簡訊給我

#14 引用回覆回覆

發表時間：2007-09-17 09:21:11

IP:219.87.xxx.xxx 訂閱

我的希望就是要下sql時，能越有效率越好，
但又不能只是回應部份資料，要全部都出來，
我一直在看 cost ，是因為 cost 越低，
oracle 回應的速度越快(不使用/* FAST ROWS*/)。
這兩天看到一本書有討論效能調校的問題，
( 博碩文化資料庫達人 ORACLE 經驗分享實務講座)，
裡面說 TABLE FULL SCAN 因為會同時 SCAN 多重區塊，
所以若要找的資料佔 TABLE 的絕大部份，或幾乎是全部的話，
那麼用 TABLE FULL SCAN 會比用 INDEX 來的快，
因為 INDEX 還多了一個先到 INDEX 去 SEARCH 的動作，
再用找到的資料對應 ROW ID 去把資料取出。

我想這就是我要的答案吧。
感謝各位大大熱心的幫助，都讓我更了解資料庫，
由於 VICSYS 大大最熱心回答問題，
所以我把這一次的最佳解答投給他了，
感恩各位大大~

===================引用 VICSYS 文章===================
Fishman 的圖書館例子很好, 解釋了 "全部搬出來" 跟按索引表全部搬出來的 COST 情況!
按索引表 COST 比較高! 但是我大都必須使用這樣的方式!

按您的例子, 如果 Table 有上百萬筆, 用程式去顯示, 以 EMPNO 的排序資料,
程式如果是一次讀取上百萬筆, 這時使用 TABLE ACCESS FULL 即可!

但是程式如果分段讀取, 第一次讀取 100 筆, 這時會如何?
Oracle 是不是要"盡快"將排序過的資料回傳給程式顯示!
那麼要用 INDEX FULL SCAN ....

COST 不是指執行更快! 事實上我都不管 COST.
甚至為了回應快速, 常常用 /* FIRST_ROW*/!, 這大多會增加 COST!

有時 Oracle Index 用抓錯(不能說是抓錯, 只是抓了不合意) !
像一個大量資料的表格 "連接" 一個小量資料的表格!
兩個連接欄位都有 Index, 如果 Oracle 使用了小量資料的 Index,
我會強制改變 Where 條件, 讓 Oracle 使用大量資料的 Index!

select ..
from LargeTable L, SmallTable S
where L.IndexField = S.IndexField||''
...

S.IndexField||'' 就會改變 Oralce 讓 SmallTable.IndexField Index 失效!

這種情況 Cost 會如何? 我不在意!
重點是您要 Oracle 如何回應資料
where deptno >' ' COST 很少, 但是按部門排序(只有 0 跟 1), 這不是您要的!
您要的是按員工編號排序, where EMPNO >' ' (在FORM 這樣寫), Order By EMPNO(REPORT 這樣寫)

------
弟子規，聖人訓，首孝悌，次謹信，泛愛眾，而親仁，有餘力，則學文。

編輯記錄

alvin26 重新編輯於 2007-09-17 09:25:03，註解無‧

系統時間：2024-11-24 16:04:44

聯絡我們 | Delphi K.Top討論版

本站聲明

1. 本論壇為無營利行為之開放平台，所有文章都是由網友自行張貼，如牽涉到法律糾紛一切與本站無關。
2. 假如網友發表之內容涉及侵權，而損及您的利益，請立即通知版主刪除。
3. 請勿批評中華民國元首及政府或批評各政黨，是藍是綠本站無權干涉，但這裡不是政治性論壇！