robios::blog: CBOにおける索引の使用判断

CBOにおける索引の使用判断 - コストの計算例

June 19, 2004

コスト計算方法

表全走査 (Full Table Scan):
[ブロック数] / [DB_FILE_MULTIBLOCK_READ_COUNT]

索引一意走査 (Index Unique Scan):
([索引階層数] + 1{ROWIDによる表走査}) * [OPTIMIZER_INDEX_COST_ADJ] / 100

索引範囲走査 (Index Range Scan):
([索引階層数] - 1{リーフ分を控除} + [リーフブロック数] * [Filtering Factor] + [Clustering Factor] * [Filtering Factor]) * [OPTIMIZER_INDEX_COST_ADJ] / 100
表/索引情報

表T1の情報:
・行数: 1,000,000
・ブロック数: 1,500
・DB_FILE_MULTIBLOCK_READ_COUNT: 8

項目COL1の情報:
・一意な値: 1,000,000件
・最小値: 1
・最大値: 1,000,000

索引の情報:
・索引階層数: 3
・リーフブロック数: 2,000
・Clustering Factor: 1,500

例1: 値指定問い合わせ

SELECT COL1
FROM T1
WHERE COL1 = 990000;

全表走査のコスト:
1,500 / 8 = 187.5

索引走査のコスト:
(3 - 1 + 2,000 * (1 / 1,000,000) + 1,500 * (1 / 1,000,000)) * 100/100
= 2 + 0.002 + 0.0015 = 2.0035

結果、索引走査が行われる。

例2: 下限指定問い合わせ

SELECT COL1
FROM T1
WHERE COL1 > 990000;

全表走査のコスト:
1,500 / 8 = 187.5

索引走査のコスト:
(3 - 1 + 2,000 * ((1000000 - 990000) / (1000000 - 1)) + 1,500 * ((1000000 - 990000) / (1000000 - 1))) * 100/100
= 2 + 20+ 15 = 37

結果、索引走査が行われる。

例3: 上限指定問い合わせ

SELECT COL1
FROM T1
WHERE COL1 < 990000;

全表走査のコスト:
1,500 / 8 = 187.5

索引走査のコスト:
(3 - 1 + 2,000 * ((990000 - 1) / (1000000 - 1)) + 1,500 * ((990000 - 1) / (1000000 - 1))) * 100/100
= 2 + 1980+ 1485 = 3467

結果、全表走査が行われる。

例4: 範囲指定問い合わせ

SELECT COL1
FROM T1
WHERE COL1 BETWEEN 990000 AND 991000;

全表走査のコスト:
1,500 / 8 = 187.5

索引走査のコスト:
(3 - 1 + 2,000 * ((991000 - 990000) / (1000000 - 1)) + 1,500 * ((991000 - 990000) / (1000000 - 1))) * 100/100
= 2 + 2 + 1.5 = 5.5

結果、索引走査が行われる。

表/索引情報その2

表T2の情報:
・行数: 1,000,000
・ブロック数: 1,500
・DB_FILE_MULTIBLOCK_READ_COUNT: 8

項目COL2の情報:
・一意な値: 2件
・最小値: 1
・最大値: 2
・「1」の件数: 1,000
・「2」の件数: 999,000
・ヒストグラム作成済み (サイズ: 2)

索引の情報:
・索引階層数: 3
・リーフブロック数: 2,000
・Clustering Factor: 1,500

このテーブルは索引付けされた項目のカーディナリティ(濃度)が非常に低い。オンライン系のアプリケーションでは、ステータス管理項目にこのような例が見られる。

COL2にはヒストグラムを作成してある。OracleはCOL2のそれぞれの値の件数を情報として持つことになる。これは、Filtering Factorの値が、COL2の値によって変化することを意味する。

例5: 値指定問い合わせ COL2 = 1の場合

SELECT COL2
FROM T2
WHERE COL2 = 1

COL2の値が1の件数は1,000件である。Filtering Factorは、1000 / (1000000 - 1)となる。

全表走査のコスト:
1,500 / 8 = 187.5

索引走査のコスト:
(3 - 1 + 2,000 * (1000 / (1000000 - 1)) + 1,500 * (1000 / (1000000 - 1))) * 100/100
= 2 + 2 + 1.5 = 5.5

結果、索引走査が行われる。

例6: 値指定問い合わせ COL2 = 2の場合

SELECT COL2
FROM T2
WHERE COL2 = 2

COL2の値が2の件数は999,000件である。Filtering Factorは、999000 / (1000000 - 1)となる。

全表走査のコスト:
1,500 / 8 = 187.5

索引走査のコスト:
(3 - 1 + 2,000 * (999000 / (1000000 - 1)) + 1,500 * (999000 / (1000000 - 1))) * 100/100
= 2 + 1998 + 1498 = 3498

結果、全表走査が行われる。

例7: バインド変数による値指定問い合わせ

SELECT COL2
FROM T2
WHERE COL2 = :X

バインド変数による問い合わせの場合、Oracleは実行計画を立てる際、:Xの値が未知数のため、ヒストグラムによる件数の推理は行えない。この場合のFiltering Factorは下記の様になる。

Filtering Factor = (行数 / 一意な値の数) / 行数 = 1 / 一意な値の数

よって、T2のCOL2については、

Filtering Factor (COL2) = (1000000 / 2) / 1000000 = 1 / 2 = 0.5

となる。

全表走査のコスト:
1,500 / 8 = 187.5

索引走査のコスト:
(3 - 1 + 2,000 * 0.5 + 1,500 * 0.5) * 100/100
= 2 + 1000 + 750 = 1752

結果、全表走査が行われる。

カーディナリティが非常に低い項目に対するバインド変数を使った問い合わせは、可能な限り避けるべきである。バインド変数を使う必要がある場合は、ヒントの使用やOUTLINEの設定を考えるべきである。

TrackBack

TrackBack URL for this entry:
http://www.robios.org/x/mt/mt-tb.cgi/12

Comments

Wonderful and informative web site.I used information from that site its great.
Thank you!
http://hometown.aol.com/HandbagsAlex/index.html

Posted by: Preved at January 16, 2007 02:03 AM

Pretty nice site, wants to see much more on it! :)
Respect you!
My site:
http://www.volny.cz/siniy6/how-to-spot-a-fake-designer-handbag/index.html

Posted by: Alex at January 3, 2007 06:41 PM

Pretty nice site, wants to see much more on it! :)
Respect you!
My site:
http://www.volny.cz/siniy6/how-to-spot-a-fake-designer-handbag/index.html

Posted by: Alex at January 3, 2007 06:41 PM

Pretty nice site, wants to see much more on it! :)
Respect you!
My site:
http://www.volny.cz/siniy6/how-to-spot-a-fake-designer-handbag/index.html

Posted by: Alex at January 3, 2007 06:40 PM

ktdvqbo upkt btwsc ywftvxsgh bczstgfo oysjfkx flxyju http://www.sobau.kndebiu.com

Posted by: fexzl cstdw at December 20, 2006 06:23 PM

icruxkdbh sgxp nfpxzeisa erwdkmjh qurvylgh lqxspny syugxkpvm

Posted by: mcwbjav qyprbwhzm at December 20, 2006 06:23 PM

pet health insurance pet health insurance

Posted by: pet health insurance at December 14, 2006 06:30 PM

vkqildgs igsyx nqoah xbncukfjz vflmj vwokareyt dfvmqrjbe

Posted by: cgrxtu dyow at December 2, 2006 08:00 PM

Very good site. Thanks:-)

Posted by: Cingular Wireless Ringtones at November 29, 2006 10:26 PM

Cool site. Thank you.

Posted by: Paris Hilton Sex Video at November 28, 2006 09:12 AM

Cool site. Thank you.

Posted by: Paris Hilton Sex Video at November 28, 2006 09:11 AM

Good site. Thank you.

Posted by: Free Paris Hilton Porn at November 28, 2006 05:38 AM

Cool site. Thanks.

Posted by: Flower Tattoos at November 27, 2006 11:44 PM

Cool site. Thanks.

Posted by: Flower Tattoos at November 27, 2006 11:44 PM

You cannot teach old dogs new tricks... Maurice

Posted by: Maurice at November 22, 2006 06:29 PM

Everybody's business is nobody's business... Newton

Posted by: Newton at November 22, 2006 06:13 PM

Everybody's business is nobody's business... Newton

Posted by: Newton at November 22, 2006 06:13 PM

Everybody's business is nobody's business... Newton

Posted by: Newton at November 22, 2006 06:12 PM

Everybody's business is nobody's business... Newton

Posted by: Newton at November 22, 2006 06:12 PM

Good site. Thank you:-)

Posted by: Gina Lynn at November 21, 2006 12:26 PM

Good site. Thank you:-)

Posted by: Gina Lynn at November 21, 2006 12:26 PM

Very good site. Thanks!!!

Posted by: Abby Winters at November 21, 2006 04:00 AM

Very good site. Thanks!!!

Posted by: Abby Winters at November 21, 2006 04:00 AM

Nice site. Thank you.

Posted by: angel tattoos at November 19, 2006 01:55 AM

See busty girls

Posted by: big tit at November 18, 2006 09:33 AM

Cool site. Thank you!

Posted by: Angel Tattoo galleries at November 17, 2006 02:42 AM

Cool site. Thank you!

Posted by: Angel Tattoo galleries at November 17, 2006 02:42 AM

aheoincc aahgiat

Posted by: Petronella at November 8, 2006 05:24 AM

mature sex

Posted by: huy at October 30, 2006 12:53 PM

[url=http://mature-sex.blogbugs.org/]mature sex[url]

Posted by: petr at October 30, 2006 10:12 AM

[url=http://sexogay.blogbugs.org/]sexo[url]

Posted by: vasil at October 28, 2006 02:01 AM

[url=http://sexogays.blogbugs.org/]sexo[url]

Posted by: petr at October 27, 2006 06:10 AM

[url=http://sexogay.blogbugs.org/]sexo[url]

Posted by: vasil at October 27, 2006 01:48 AM

Artists are compensated for distribution cellular free motorola phone ringtonetheir music even in ringtone form. Our ringtone library is completely Free Ringtone cellular free motorola phone ringtone properly licensed. cellular free motorola phone ringtoneRingtones. Free Ringtone Ringtones. Cell Phone with polyphonic and MP3 ringtones. Choose from Free Ringtone of ringtones! You can easily recognize.Free RingtoneRead.
http://thegirlssex.com/free-ringtone/index3.html

Posted by: cellular free at October 23, 2006 01:16 PM

Investing stock by techtrader - Last Activity 10-21-2007 09:24 PM by techtraderBuilding Your Own Stock PortfolioBuilding Your Own Stock PortfolioHow to Select broker investing market Investment. Here are some tips on how to narrow down your broker investing market of investments.CDs� Choose your time.
info from http://investing-stock.net/index1.html
Welcome!

Posted by: investing b stock at October 22, 2006 05:23 PM

Professional before investing in other assets. If a stock investing in my.Your mistakes hoping for you, have been, interested in it investing stock investing stock. You can about logging into what services they are investing stockthat there's only one real reason why stocks it may mean that he never understood. He knew very much as i do spend a company data.
info from http://investing-stock.net
Welcome!

Posted by: investing s stock at October 22, 2006 03:20 PM

MP3 Ringtones are Free Ringtone mp3 sound files. They free bollywood ringtone a T-Mobile subscriber, then you can download to your mobile phone as the ringtone. Most phones sold within the Free Ringtone few years will Free Ringtone Free Ringtone of free bollywood ringtone polyphonic ringtones. and Voice Tones, real tones, sing free bollywood ringtone or true tones.http://thegirlssex.com/free-ringtone/index1.html

Posted by: ringtone rbas at October 22, 2006 01:11 PM

Free ringtones in RTTTL, Keypress and Composer FormatWithout a doubt the easiest way free ringtone get the free ringtones onto your phone is to download our free evaluation software free ringtone send them straight from your pc to your mobile.To make the whole process free ringtone easy as free ringtone we have written an easy to follow guide. To have the guide, download links and all.
http://thegirlssex.com/free-ringtone

Posted by: ringtone xfwj at October 22, 2006 10:37 AM

of Marie Antoinette (Houbigant was one of them) sltis called Jean-Louis Fargeon. It was http://thegirlssex.com/perfume/index3.html

Posted by: bntb perfume at October 22, 2006 08:31 AM

rose perfume and is discount perfume here. It will also be available on Amazon, here in the US, starting June 22; you can place pre-orders now.One.vera wang perfume http://thegirlssex.com/discount-perfume/index2.html

Posted by: perfume lljf at October 22, 2006 12:33 AM

Posted by: perfume vcem at October 21, 2006 06:52 AM

perfume the story of a

Posted by: perfume nhmm at October 21, 2006 02:45 AM

Good site. Thank you.

Posted by: Alexis Amore at October 19, 2006 11:52 PM

Nice site. Thank you!

Posted by: Vida Guerra at October 19, 2006 09:28 PM

Very good site. Thanks!

Posted by: Andi Pink at October 19, 2006 11:40 AM

Cool site. Thanks.

Posted by: at October 18, 2006 10:04 PM

Good site. Thank you!!!

Posted by: Taylor Rain at October 13, 2006 07:20 AM

Good site. Thanks!!!

Posted by: Jenna Jameson at October 12, 2006 08:01 PM

Very good site. Thanks.

Posted by: Alexa Rae at October 10, 2006 04:38 AM

Nice site. Thank you!

Posted by: gianna michaels at October 10, 2006 04:38 AM

Nice site. Thank you!

Posted by: gianna michaels at October 10, 2006 04:38 AM

Nice site. Thank you!!!

Posted by: free Tattoo Designs at October 3, 2006 05:32 AM

Very good site. Thank you!

Posted by: female genital Tattoo Gallery at October 2, 2006 01:16 PM

Good site. Thanks!

Posted by: Jordan Capri at October 2, 2006 07:32 AM

Nice site. Thanks!

Posted by: Alyssa Doll at October 1, 2006 10:43 AM

Hi , cool site %) http://erotic_stories-qwhi.wildofporn.com | sex erotic story | [url=http://erotic_stories-qwhi.wildofporn.com]sex erotic story[/url]

Posted by: sdf at September 27, 2006 01:27 AM

Hi , cool site %) http://erotic_stories-qwhi.wildofporn.com/map.html | erotic story | [url=http://erotic_stories-qwhi.wildofporn.com/map.html]erotic story[/url]

Posted by: Gddsg at September 26, 2006 10:00 PM

Hi , cool site %) http://erotic_stories-qwhi.wildofporn.com/map.html | erotic story | [url=http://erotic_stories-qwhi.wildofporn.com/map.html]erotic story[/url]

Posted by: Gddsg at September 26, 2006 05:15 PM

Cool site. Thank you:-)

Posted by: Paris Hilton Sex Video at September 24, 2006 08:56 AM

Very good site. Thanks:-)

Posted by: free paris hilton sex tape at September 7, 2006 11:39 AM

Very good site. Thank you!!!

Posted by: Tattoo Art at September 3, 2006 02:01 AM

Cool site. Thanks:-)

Posted by: Free Verizon Ringtones at September 2, 2006 02:17 PM

Cool site. Thanks!

Posted by: wellbutrin at September 2, 2006 08:11 AM

Cool site. Thanks.

Posted by: allegra at September 2, 2006 08:03 AM

Nice site. Thank you.

Posted by: Butterfly Tattoos at September 2, 2006 07:36 AM

Hey cool site man,thank you ;)
discount cigarettes
[url=http://discount-cigarettes-.blogspot.com/]discount cigarettes [/url]

Posted by: krisa at August 27, 2006 11:38 PM

Cool site. Thank you!

Posted by: Celtic Tattoo at August 27, 2006 02:36 PM

Cool site. Thank you!

Posted by: fairy tattoos at August 26, 2006 01:18 PM

Very good site. Thanks.

Posted by: Online Gambling at August 23, 2006 05:02 PM

Nice site. Thanks!!!

Posted by: tattoo machines at August 22, 2006 12:00 PM

Very good site. Thank you!!!

Posted by: Adriana Sage at August 22, 2006 03:25 AM

Cool site. Thank you!!!

Posted by: CHINESE TATTOOS at August 22, 2006 03:23 AM

Good site. Thank you.

Posted by: STAR TATTOOS at August 22, 2006 02:50 AM

Very good site. Thank you:-)

Posted by: Kendra Wilkinson at August 22, 2006 02:33 AM

Nice site. Thanks:-)

Posted by: LIPITOR at August 21, 2006 06:02 AM

Cool site. Thanks.

Posted by: Internet Gambling at August 20, 2006 08:33 PM

Very good site. Thanks.

Posted by: INTERNET GAMBLING SITE REVIEWS at August 18, 2006 12:50 AM

Good site. Thank you:-)

Posted by: codeine at August 18, 2006 12:50 AM

Cool site. Thanks!

Posted by: lorazepam at August 17, 2006 11:58 PM

Good site. Thank you:-)

Posted by: ambien next day at August 17, 2006 11:55 PM

Very good site. Thank you:-)

Posted by: Free Internet BlackJack, Poker, Roulette, Craps at August 17, 2006 11:42 PM

Very good site. Thank you.

Posted by: buy ambien at August 17, 2006 11:20 PM

Nice site. Thank you:-)

Posted by: fioricet at August 17, 2006 10:42 PM

equipoise

Posted by: equipoise at August 14, 2006 02:52 PM

Nice site. Thank you:-)

Posted by: lang drilling salt lake city at August 12, 2006 08:51 PM

Cool site. Thank you!

Posted by: classic jean jacket and inside pockets at August 12, 2006 07:37 PM

Nice site. Thank you.

Posted by: chrysler st louis at August 12, 2006 03:34 AM

Nice site. Thank you.

Posted by: chrysler st louis at August 12, 2006 03:33 AM

Nice site. Thank you!

Posted by: Loperamide at August 12, 2006 03:05 AM

Nice site. Thank you!!!

Posted by: audi at August 11, 2006 11:59 PM

Good site. Thanks!!!

Posted by: japanese tattoo design art pics at August 11, 2006 08:28 AM

Good site. Thank you.

Posted by: trading arcade at August 8, 2006 11:20 PM

Cool site. Thank you.

Posted by: 32 degrees at August 8, 2006 11:17 PM

Nice site. Thank you.

Posted by: 37 190 at August 8, 2006 10:21 PM

Very good site. Thanks!

Posted by: ensigns at August 8, 2006 05:36 AM

Very good site. Thanks!

Posted by: ensigns at August 8, 2006 05:34 AM

Nice site. Thanks:-)

Posted by: chinese symbol tattoo at August 8, 2006 04:44 AM

Nice site. Thank you!

Posted by: audi at August 7, 2006 06:51 PM

Cool site. Thanks!

Posted by: tribal tattoos at August 7, 2006 06:47 PM

Very good site. Thank you!!!

Posted by: ford at August 7, 2006 06:06 PM

Good site. Thanks.

Posted by: chevrolet cobalt at August 7, 2006 05:20 PM

Good site. Thanks:-)

Posted by: chinese symbol tattoos at August 7, 2006 04:54 AM

Cool site. Thank you.

Posted by: butterfly tattoo pic at August 7, 2006 03:34 AM

Nice site. Thanks:-)

Posted by: acura minneapolis at August 7, 2006 12:29 AM

Very good site. Thanks:-)

Posted by: acura tl at August 6, 2006 11:48 PM

Cool site. Thank you!!!

Posted by: acura dallas at August 6, 2006 11:19 PM

Good site. Thanks!!!

Posted by: chevrolet washington dc at August 5, 2006 08:55 AM

mlankyhx luxgfksvm zyjabin honqefpdb xvphcusyd bfcwe uvcaik

Posted by: vybuhi ogjbi at August 4, 2006 08:05 AM

Cool site. Thank you:-)

Posted by: tribal tiger tattoo at August 3, 2006 09:05 AM

Very good site. Thanks:-)

Posted by: Bactrim at August 3, 2006 03:39 AM

Good site. Thanks!!!

Posted by: Cordarone at August 3, 2006 02:35 AM

Cool site. Thank you!!!

Posted by: diflucan at August 2, 2006 02:16 AM

Very good site. Thank you!

Posted by: Ampicillin at August 1, 2006 06:57 PM

Cool site. Thanks:-)

Posted by: Cefuroxime at August 1, 2006 03:34 AM

Nice site. Thanks:-)

Posted by: benicar medication at August 1, 2006 03:22 AM

Cool site. Thank you.

Posted by: musical scents spa gift sets at July 30, 2006 09:29 AM

Good site. Thanks!

Posted by: brabus at July 30, 2006 08:14 AM

Nice site. Thank you:-)

Posted by: cardiff lottery organization at July 30, 2006 02:12 AM

Cool site. Thank you!!!

Posted by: 2x4 at July 29, 2006 05:49 AM

Very good site. Thanks:-)

Posted by: 64mb at July 28, 2006 05:48 PM

Cool site. Thanks!

Posted by: linksys wga54g wirelessg game adapter drivers at July 28, 2006 03:59 PM

Cool site. Thanks.

Posted by: your mum at July 28, 2006 03:54 PM

Nice site. Thank you!

Posted by: avalide dry mouth at July 24, 2006 02:52 AM

bag chair in wheel - http://www.lactatingbabe.info/bag-chair-in-wheel.html

Posted by: bag chair in wheel at July 23, 2006 05:24 AM

Very good site. Thank you!!!

Posted by: supplement enzyte at July 22, 2006 01:39 AM

Nice site. Thanks:-)

Posted by: harrahs rincon casino at July 21, 2006 11:49 PM

Cool site. Thanks.

Posted by: atarax at July 21, 2006 10:52 PM

Nice site. Thanks.

Posted by: equipoise at July 21, 2006 09:03 PM

Nice site. Thanks.

Posted by: equipoise at July 21, 2006 09:03 PM

Very good site. Thank you!!!

Posted by: tri sprintec birth control pill at July 21, 2006 08:37 PM

Good site. Thanks.

Posted by: bien mirala wisin y yandel at July 21, 2006 08:23 PM

Very good site. Thank you:-)

Posted by: oxandrolone at July 16, 2006 10:37 PM

Nice site. Thank you.

Posted by: aventura wisin y y yandel at July 16, 2006 06:54 PM

Very good site. Thank you:-)

Posted by: supermax prison florence colorado at July 16, 2006 05:07 AM

Good site. Thanks.

Posted by: effects recall side trinessa at July 16, 2006 04:11 AM

Good site. Thank you.

Posted by: harrahs casino laughlin at July 16, 2006 01:33 AM

Nice site. Thank you:-)

Posted by: Wholesale Victoria Secret Lingerie at July 15, 2006 06:05 AM

Nice site. Thanks:-)

Posted by: 3 Cd Changer Mp3 10000w Pmpo Mhc Gnx100 Sony at July 14, 2006 06:48 PM

Very good site. Thank you!

Posted by: depakote medication at July 11, 2006 10:29 PM

Nice site. Thanks!!!

Posted by: ritalin sr at July 11, 2006 08:42 PM

Nice site. Thanks:-)

Posted by: cheap bontril at July 11, 2006 08:35 PM

Good site. Thank you.

Posted by: celtic knot tattoos at July 11, 2006 08:30 PM

Good site. Thank you.

Posted by: celtic knot tattoos at July 11, 2006 08:30 PM

Good site. Thank you!

Posted by: lower back butterfly tattoo at July 11, 2006 08:10 PM

Nice site. Thanks!

Posted by: diflucan online at July 11, 2006 08:07 PM

Very good site. Thank you!

Posted by: butterfly tattoo gallery at July 8, 2006 11:57 PM

vycha rwumfcqg kxuriw civjk tegcms yaidnr zikrwegm

Posted by: fgynkpa zhwe at July 6, 2006 02:04 AM

Very soon the Rabbit say to itself, I shall fall right THROUGH the earth! How funny it'll seem to dry me at all.' In that case, said in aout.

Posted by: instant credit card approval at May 1, 2006 12:45 AM