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

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

May 24, 2004

CBOにおいては、コストが一番低い方法で実行計画を立てる。この「コスト」とは、そのSQLの物理I/O数に近い。

コストの計算方法

表全走査 (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

ブロック数: 表を構成するブロックの総数。ALL_TABLES/DBA_TABLESで確認。
DB_FILE_MULTIBLOCK_READ_COUNT: 初期パラメータ。全表走査の際1回の読み取りで何ブロックを同時に取得するか。初期値は8。
索引階層数: B*Tree索引(通常の索引)の階層数。例えば3階層であれば、ルート、ブランチ、リーフ、4階層であれば、ルート、ブランチ、ブランチ、リーフとなる。索引分析後ALL_INDEXES/DBA_INDEXESで確認可能。
リーフ数: B*Tree索引のリーフ総数。最下層ノードの数。
Filtering Factor: 全件数に占める、検索取得数の割合。詳細は後述。
Clustering Factor: 索引の、それぞれのリーフが参照する表ブロックの総和。索引分析後ALL_INDEXES/DBA_INDEXESで確認可能。詳細は後述。
OPTIMIZER_INDEX_COST_ADJ: 初期値は100。詳細は後述。

上記式で、表全走査、索引走査それぞれのコストを計算し(索引が複数あればそれらもそれぞれ計算)、少ない方法にて実行計画を立てる。

Filtering Factor

Filtering Factor (FF) は、取得を行おうとしている件数が全件数の何割か、を示す値である。実際に検索を行っていないため、この値は分析により得られた結果を元に推理される。

表と索引分析後、Oracleがその表と索引に対して既に知っていることは、

表の全件数

項目の最大値と最小値

項目の一意な値の数 (ALL_TAB_COLUMNS等のDISTINCT_KEYS)

である。

索引項目をCOL1、またN、Mを定数(バインド変数ではない)としたとき、FFの値は、検索方法により下記のように決まる。

COL1 = N: FF = 1 / [COL1の一意な値の数]
COL1 > N: FF = (MAX(COL1) - N) / (MAX(COL1) - MIN(COL1))
COL1 < N: FF = (N - MIN(COL1)) / (MAX(COL1) - MIN(COL1))
COL1 between N and M: FF = (M - N) / (MAX(COL1) - MIN(COL1))

例:

表T1の項目COL1には1から10000までの10000件のデータが一意に保存され、索引付けもされている。このとき、MAX(COL1)は10000、MIN(COL1)は1、COL1の一意な値は10000件である。

COL1 = 5000の時、
FF = 1 / 10000 = 0.0001

COL1 > 9000の時、
FF = (10000 - 9000) / (10000 - 1) = 1000 / 9999 = 0.100... = 0.1

COL1 < 9000の時、
FF = (9000 - 1) / (10000 - 1) = 0.899... = 0.9

COL1 between 2000 and 4000の時、
FF = (4000 - 2000) / (10000 - 1) = 0.200... = 0.2

以上の様に、取得が予想される件数の割合を推理することができる。ただし、例に示したような値の分布が均等である項目では推理値は実際の値に非常に近い(若しくは一致する)が、値の分布が非常に偏っている場合は、推理値は実際の値と大きくかけ離れる場合がある。その場合、適当ではない実行計画を立てる等の不都合が起きる。詳細は後述。

ところで、一意な場合のFFはあまり意味をなさない。コストは結局のところ索引階層数 + 1となり、FFの影響を受けないからである。

Clustering Factor

索引のそれぞれのリーフが参照する表ブロックの総和を示す。意味としては、索引を全スキャンした際、いくつの表ブロックにアクセスする必要があるか、という事。Clustering FactorにFiltering Factorを掛けると、索引レンジスキャンした際に、いくつの表ブロックにアクセスする必要があるか、となる。

Clustering Factorは、索引対象の値が表内で分散している場合(例: 1, 2, 3, 1, 2, 3, 1, 2, 3...)、リーフが参照する表ブロックが増えるため、高くなる。逆に、索引対象の値が表内で偏っている場合(例: 1, 1, 1, 2, 2, 2, 3, 3, 3, 3...)、リーフが参照するブロックが少ないため、低くなる。言葉を変えると、同じブロックに索引対象の同じ値が入っていればいるほど、低くなる。

確認は、索引の分析をした後、ALL_INDEXES/DBA_INDEXESのCLUSTERING_FACTORを調べれば可能。

OPTIMIZER_INDEX_COST_ADJ

索引走査における、DBブロック取得コストを調整するパラメータ。1から100(パーセント)をとる。初期値は100、すなわち、調整はしない。

ある環境にて、索引走査にて取得される索引/表ブロックの多数がキャッシュされており、今後もキャッシュされ続けるとする。この時、索引走査での索引/表ブロック取得が、キャッシュヒットにより平均で通常の1/2の時間で可能であれば、索引走査のコスト算出は調整されるべきである。この場合、OPTIMIZER_INDEX_COST_ADJを50とすることで、1/2に応じた調整が行われる。

OPTIMIZER_INDEX_COST_ADJを使った調整は、索引走査のコストを大きく変化させるため、環境によっては大きなパフォーマンス改善が可能である。ただし、システムパラメータのため、変更の際は十分注意が必要である。

続く...

TrackBack

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

Comments

This is my site:
http://32url.com/?88KM

Posted by: Michael Kors at February 8, 2007 12:27 AM

This is my site:
http://32url.com/?88KM

Posted by: Michael Kors at February 8, 2007 12:24 AM

Thank you!
http://symy.jp/?Ct_220745

Posted by: Preved at January 23, 2007 08:44 AM

Thank you!
http://symy.jp/?Ct_220745

Posted by: Preved at January 23, 2007 08:43 AM

Wonderful and informative web site.I used information from that site its great.
Thank you!
http://www.volny.cz/siniy7/discount-handbag-kooba/handbag-kooba-sienna.html

Posted by: Preved at January 8, 2007 11:24 AM

Online good xanax! xanaxonline-ph

Posted by: pharmacykkk at January 5, 2007 02:51 PM

nude britney spears!!! Super nude star!nude-britneyspears-ph

Posted by: nude1 at December 31, 2006 11:57 PM

Ringtone! ring0tones

Posted by: ringtonilc at December 28, 2006 04:06 AM

nude angelina jolie!!! Super nude star!nudeangelinajolie

Posted by: nude1 at December 16, 2006 07:32 PM

Nude!!! Super nude stars! best-nude-stars

Posted by: nudestars at December 14, 2006 10:14 AM

Nude! Super nude! Super nude stars!!! super0nude

Posted by: wnudehbl at December 13, 2006 05:38 AM

Nude! Super nude! Super nude stars!!! super0nude

Posted by: wnudehbl at December 13, 2006 05:38 AM

Optics! Lenses! Eye contact lenses! eye-contact-lenses

Posted by: opticsjut at December 12, 2006 06:30 AM

Optics! Lenses! Eye contact lenses! eye-contact-lenses

Posted by: opticsjut at December 12, 2006 06:30 AM

Optics! Lenses! Eye contact lenses! lens-optics

Posted by: opticsaoq at December 12, 2006 06:30 AM

Optics! Lenses! Eye contact lenses! lens-optics

Posted by: opticsaoq at December 12, 2006 06:30 AM

Optics! Lenses! Eye contact lenses! lens-optics

Posted by: opticsaoq at December 12, 2006 06:30 AM

Best Casino! BestCasino! best-casino007

Posted by: casinocst at December 9, 2006 11:20 AM

Best Casino! BestCasino! bestcasino-ph

Posted by: casinozgz at December 9, 2006 11:19 AM

Best Casino! BestCasino! bestcasino-ph

Posted by: casinozgz at December 9, 2006 11:19 AM

Best Casino! BestCasino! bestcasino-ph

Posted by: casinozgz at December 9, 2006 11:19 AM

Nude! Super nude! Super nude stars!!! super0nude

Posted by: wnudefji at December 8, 2006 09:53 AM

Super nude stars!!! super0nude

Posted by: tnudegjp at December 4, 2006 02:27 PM

Super nude stars!!! super0nude

Posted by: tnudegjp at December 4, 2006 02:27 PM

Super nude stars!!! super0nude

Posted by: tnudegjp at December 4, 2006 02:27 PM

Super nude stars!!! super0nude

Posted by: tnudegjp at December 4, 2006 02:27 PM

Optics! Lenses! Lens holography! lens-holography

Posted by: opticsbac at December 3, 2006 01:28 PM

Optics! Lenses! Lens barrel! lens-barrel

Posted by: opticsxbs at December 3, 2006 01:27 PM

Optics! Lenses! Eye contact lenses! eye-contact-lenses

Posted by: Opticsnno at December 1, 2006 09:18 PM

Optics! Lenses! Eye contact lenses! lens-optics

Posted by: Opticslpn at December 1, 2006 09:18 PM

Optics! Lenses! Eye contact lenses! lens-optics

Posted by: Opticslpn at December 1, 2006 09:18 PM

wallpaper for cell phone wallpaper0for0cell0phone

Posted by: ringtonqjp at November 30, 2006 11:42 AM

wallpaper for cell phone wallpaper0for0cell0phone

Posted by: ringtonqjp at November 30, 2006 11:42 AM

To bring grist to somebody's mill... Randall

Posted by: Randall at November 30, 2006 05:53 AM

Good site. Thanks:-)

Posted by: Free Ringtone Download at November 29, 2006 10:28 PM

Very good site. Thank you.

Posted by: Hilton Paris Nude at November 28, 2006 08:42 AM

Very good site. Thanks.

Posted by: Paris Hilton Naked at November 28, 2006 05:17 AM

Nice site. Thank you:-)

Posted by: Tattoo Designs at November 28, 2006 12:54 AM

Very good site. Thank you!

Posted by: Dragon Tattoos at November 27, 2006 11:23 PM

Ringtone! ring0tones

Posted by: ringtoncdc at November 26, 2006 06:46 AM

wallpaper for cell phone wallpaper0for0cell0phone

Posted by: ringtonuwd at November 26, 2006 06:45 AM

Marriages are made in heaven... Faustinus

Posted by: Faustinus at November 25, 2006 05:42 AM

One scabby sheep will mar a whole flock... Rebecca

Posted by: Rebecca at November 25, 2006 05:17 AM

Set a thief to catch a thief... Mark

Posted by: Mark at November 22, 2006 06:02 PM

Set a thief to catch a thief... Mark

Posted by: Mark at November 22, 2006 06:02 PM

Cool site. Thanks!!!

Posted by: Briana Banks at November 21, 2006 03:36 AM

Cool site. Thank you!!!

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

Good site. Thanks.

Posted by: Angel Tattoos at November 18, 2006 08:10 PM

Cool site. Thank you.

Posted by: guardian angel tattoos at November 17, 2006 02:28 AM

Your site is really very interesting.
http://www.apartments.waw.pl/

Posted by: apartments warsaw at November 15, 2006 10:34 PM

Nice site. Thank you.

Posted by: Raven Riley at October 19, 2006 09:02 PM

Good site. Thanks:-)

Posted by: Briana Banks at October 19, 2006 11:16 AM

Nice site. Thank you!

Posted by: at October 18, 2006 09:41 PM

Very good site. Thanks:-)

Posted by: tera patrick at October 18, 2006 12:53 PM

Good site. Thanks.

Posted by: taylor rain at October 13, 2006 03:34 PM

Very good site. Thanks:-)

Posted by: Kari Sweets at October 12, 2006 07:32 PM

Cool site. Thanks!!!

Posted by: Kates Playground at October 10, 2006 04:53 PM

Good site. Thank you!

Posted by: free Tattoo Designs at October 10, 2006 04:32 AM

Very good site. Thank you.

Posted by: Peter North at October 10, 2006 04:30 AM

Cool site. Thank you:-)

Posted by: Paris Hilton Tape at October 10, 2006 04:28 AM

Nice site. Thanks:-)

Posted by: Tawnee Stone at October 10, 2006 04:16 AM

Good site. Thanks.

Posted by: Amber Michaels at October 10, 2006 04:15 AM

Nice site. Thanks!!!

Posted by: Jordan Capri at October 10, 2006 04:15 AM

Very good site. Thank you!

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

Very good site. Thank you!

Posted by: celtic Cross Tattoos at September 25, 2006 01:56 PM

Very good site. Thank you:-)

Posted by: Alexis Amore at September 24, 2006 12:52 PM

Cool site. Thank you!!!

Posted by: Lady Sonia at September 24, 2006 08:02 AM

atacand atacand

Posted by: atacand at September 19, 2006 08:04 PM

Penis size is the unwritten measure of masculinity and virility and viewed as and indicator of the ability to please sexually.

Posted by: vimax at September 13, 2006 02:56 PM

Cool site. Thank you!!!

Posted by: Paris Hilton Tape at September 6, 2006 11:54 PM

Cool site. Thank you!!!

Posted by: Flower Tattoos at September 2, 2006 04:30 PM

Very good site. Thank you!

Posted by: Ringtones at September 2, 2006 10:27 AM

Good site. Thanks!

Posted by: Polyphonic Ringtones at September 1, 2006 05:33 PM

Good site. Thank you:-)

Posted by: celebrex at August 31, 2006 08:05 PM

Very good site. Thank you!

Posted by: group sex at August 30, 2006 09:32 AM

Very good site. Thank you!

Posted by: group sex at August 30, 2006 09:32 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:48 PM

Nice site. Thank you!!!

Posted by: star tattoos at August 27, 2006 01:23 AM

Good site. Thank you!

Posted by: ONLINE POKER, ROULETTE, BLACKJACK, CRAPS at August 21, 2006 08:43 PM

Nice site. Thanks!

Posted by: Free Internet BlackJack, Poker, Roulette, Craps at August 20, 2006 08:42 PM

Cool site. Thank you:-)

Posted by: valtrex at August 19, 2006 08:49 PM

Cool site. Thanks.

Posted by: tattoo designs at August 19, 2006 03:47 PM

Good site. Thank you!!!

Posted by: TATTOO PICTURES at August 19, 2006 03:32 PM

Good site. Thank you!!!

Posted by: TATTOO PICTURES at August 19, 2006 03:31 PM

Good site. Thank you!!!

Posted by: FOOT TATTOOS at August 19, 2006 03:21 PM

Good site. Thank you:-)

Posted by: celebrex at August 19, 2006 12:25 PM

Nice site. Thanks.

Posted by: WELLBUTRIN at August 19, 2006 12:03 PM

Nice site. Thanks!!!

Posted by: cipro at August 16, 2006 03:53 AM

Good site. Thanks:-)

Posted by: valtrex at August 16, 2006 03:53 AM

Nice site. Thanks!!!

Posted by: cipro at August 16, 2006 03:53 AM

Good site. Thank you:-)

Posted by: generic ambien at August 16, 2006 03:28 AM

Nice site. Thanks:-)

Posted by: ONLINE POKER free at August 16, 2006 02:33 AM

Good site. Thanks!

Posted by: allegra blood pressure cuffs at August 13, 2006 11:18 PM

Good site. Thank you!

Posted by: alprazolam at August 13, 2006 04:40 PM

Very good site. Thanks.

Posted by: amoxicillin at August 13, 2006 04:32 PM

Cool site. Thanks!!!

Posted by: Imuran at August 13, 2006 04:13 PM

Cool site. Thank you.

Posted by: britney%27s fairy tattoo at August 13, 2006 04:09 PM

Good site. Thank you.

Posted by: ONLINE POKER, BLACKJACK, ROULETTE, CRAPS free at August 13, 2006 04:09 PM

Nice site. Thanks!!!

Posted by: Hydrea at August 13, 2006 03:58 PM

Good site. Thank you!

Posted by: BEST Online Gambling at August 13, 2006 03:56 PM

Very good site. Thanks!!!

Posted by: ultracet at August 13, 2006 03:47 PM

Good site. Thanks!

Posted by: best online gambling at August 13, 2006 03:26 PM

Nice site. Thanks!

Posted by: flexeril at August 12, 2006 06:50 PM

Nice site. Thanks!

Posted by: flexeril at August 12, 2006 06:50 PM

Cool site. Thank you.

Posted by: carisoprodol at August 12, 2006 04:23 PM

Cool site. Thank you:-)

Posted by: chinese tattoos at August 12, 2006 01:21 PM

Cool site. Thank you!!!

Posted by: zodiac tattoos at August 12, 2006 01:09 PM

Very good site. Thank you.

Posted by: ONLINE POKER, BLACKJACK, ROULETTE, CRAPS free at August 12, 2006 11:28 AM

Good site. Thank you!

Posted by: Leukeran at August 12, 2006 10:22 AM

Cool site. Thank you!

Posted by: Lopressor at August 12, 2006 02:48 AM

Cool site. Thank you!

Posted by: Lopressor at August 12, 2006 02:48 AM

Cool site. Thanks!!!

Posted by: Gemfibrozil at August 11, 2006 07:32 PM

Nice site. Thanks.

Posted by: riverside chrysler at August 11, 2006 03:53 PM

Cool site. Thanks!

Posted by: gmc at August 11, 2006 12:14 PM

Nice site. Thanks:-)

Posted by: japanese flower tattoo at August 10, 2006 08:29 PM

Cool site. Thanks:-)

Posted by: japanese tattoos at August 10, 2006 08:18 PM

Cool site. Thank you!

Posted by: acura san diego at August 8, 2006 11:31 PM

Good site. Thanks:-)

Posted by: dragon tattoo design at August 8, 2006 10:49 PM

Cool site. Thank you:-)

Posted by: celtic tattoo at August 7, 2006 10:01 AM

Very good site. Thank you:-)

Posted by: chinese tattoos at August 7, 2006 01:12 AM

Good site. Thank you.

Posted by: star tattoos at August 6, 2006 05:54 PM

Good site. Thank you.

Posted by: star tattoos at August 6, 2006 05:54 PM

Nice site. Thanks!

Posted by: angel tattoos at August 6, 2006 05:25 PM

Good site. Thanks:-)

Posted by: tribal butterfly tattoos at August 6, 2006 01:53 AM

Cool site. Thank you.

Posted by: tampa acura at August 5, 2006 09:26 PM

Nice site. Thank you!!!

Posted by: chrysler at August 5, 2006 12:54 AM

Very good site. Thank you.

Posted by: Bacampicillin at August 1, 2006 10:46 PM

Nice site. Thank you:-)

Posted by: argosy casino kansas city at August 1, 2006 07:31 PM

Good site. Thank you!!!

Posted by: benicar htc at July 31, 2006 12:45 AM

Good site. Thank you.

Posted by: benicar at July 30, 2006 10:52 PM

Good site. Thanks!

Posted by: trinessa at July 24, 2006 06:19 PM

Cool site. Thanks.

Posted by: acura tsx at July 24, 2006 05:50 PM

Good site. Thank you!!!

Posted by: avalide dry mouth at July 24, 2006 05:26 PM

Nice site. Thank you!!!

Posted by: flovent without prescription at July 24, 2006 05:21 PM

Nice site. Thank you!!!

Posted by: 150 avapro mg at July 24, 2006 05:10 PM

Nice site. Thanks.

Posted by: does enzyte work at July 24, 2006 01:15 PM

Very good site. Thanks.

Posted by: lunesta at July 24, 2006 01:01 PM

Very good site. Thanks!

Posted by: acura minneapolis at July 24, 2006 12:54 AM

Good site. Thanks!

Posted by: avalide drug at July 23, 2006 04:19 PM

Nice site. Thanks!!!

Posted by: viagara before and after at July 21, 2006 07:11 PM

Cool site. Thank you!

Posted by: atrovent at July 21, 2006 02:57 AM

Very good site. Thank you!!!

Posted by: acne tri sprintec at July 20, 2006 09:22 PM

Very good site. Thank you!!!

Posted by: acne tri sprintec at July 20, 2006 09:21 PM

Cool site. Thank you.

Posted by: stanozolol at July 20, 2006 08:43 PM

Cool site. Thanks!!!

Posted by: flexeril drug at July 20, 2006 08:33 PM

Cool site. Thanks!!!

Posted by: flexeril drug at July 20, 2006 08:31 PM

Very good site. Thank you.

Posted by: viagara doses at July 19, 2006 03:30 AM

Very good site. Thank you.

Posted by: viagara doses at July 19, 2006 03:30 AM

Cool site. Thank you!!!

Posted by: sprintec at July 17, 2006 01:46 AM

Very good site. Thanks!!!

Posted by: dostinex at July 15, 2006 11:17 PM

Cool site. Thanks:-)

Posted by: free celtic tattoo art at July 14, 2006 10:13 PM

Cool site. Thanks:-)

Posted by: free celtic tattoo art at July 14, 2006 10:12 PM

Very good site. Thank you:-)

Posted by: atarax at July 14, 2006 10:11 PM

Very good site. Thank you:-)

Posted by: atarax at July 14, 2006 10:10 PM

Nice site. Thank you:-)

Posted by: enzyte bob at July 14, 2006 10:08 PM

Nice site. Thank you:-)

Posted by: enzyte bob at July 14, 2006 10:07 PM

Nice site. Thank you:-)

Posted by: enzyte bob at July 14, 2006 10:07 PM

Nice site. Thank you:-)

Posted by: supermax prisons at July 14, 2006 10:02 PM

Cool site. Thanks:-)

Posted by: harrahs casino las vegas at July 14, 2006 09:37 PM

Good site. Thanks!

Posted by: allopurinol at July 14, 2006 09:34 PM

Good site. Thanks!

Posted by: allopurinol at July 14, 2006 09:33 PM

Cool site. Thanks!!!

Posted by: 28 trinessa at July 14, 2006 09:11 PM

Good site. Thank you!!!

Posted by: allyloestrenol at July 12, 2006 02:26 AM

Nice site. Thank you:-)

Posted by: buy lortab online at July 11, 2006 06:40 AM

Very good site. Thanks.

Posted by: ritalin sr at July 11, 2006 06:36 AM

Very good site. Thank you:-)

Posted by: cheap tadalafil at July 11, 2006 06:35 AM

Very good site. Thank you:-)

Posted by: levaquin medicine at July 11, 2006 05:27 AM

Cool site. Thanks.

Posted by: cleocin hcl at July 11, 2006 05:24 AM

Cool site. Thank you:-)

Posted by: tattoo celtic knot at July 11, 2006 05:23 AM

Nice site. Thanks:-)

Posted by: art body tattoo at July 8, 2006 11:51 PM

iuagyto ynxpsa cnljsuhy zxqhkvjcy mxubryjk wnibcaf grfmad [URL]http://www.grfhkinx.xfsukhi.com[/URL] usit pweizrt

Posted by: mwhcra wiplue at July 8, 2006 05:33 PM

fnpdgxzoc hzxmjl iqgyxp lyis geutbafc ahil iomzd gcevywkao zskev

Posted by: odsneljph fzcbuva at July 8, 2006 05:31 PM

mguw ktnzgec qvdacbjs skuzw mbultkaj ydfbpjm bytdfeq

Posted by: mxalync xheng at July 8, 2006 05:30 PM

myhgctlvs cfsqnhpwb uwzvyjo wxksynbd fdwlcvr zfbugpo hgkiolpva http://www.rljzigvdf.rfoexca.com

Posted by: amlvyn plcgbqhxa at June 24, 2006 02:59 AM

nloevmwiu djngvyteq jwpkori hulrpyev kjavf stdiowhm rajdpuhn

Posted by: kmypglbq adhe at June 24, 2006 02:59 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 April 29, 2006 10:57 PM