୷ܭၗسीၗૻϐЇፓਠБݤ
!
!
ᄃەৱ!!!!ଯ୯ঢ়!!!!ߪ!
୯ҥύᑫεᏢၗૻࣽᏢس
ieliao@nchu.edu.tw
ǵkfkao@cs.nchu.edu.twǵs9256048@cs.nchu.edu.tw
ᄔा!
!
ӧၗسύǴךॺதᙖҗࡌҥЇٰቚ у᠐ڗޑೲࡋǶՠࢂऩࡌҥό҅ዴޑЇǴόՠ ੁᓯӸޑޜ໔ǴԶЪᗋቚуЇ׳ཥਔޑԋ ҁǶଞჹঁπբॄၩ(workload)ǴӵՖࡌҥঁԖ ന٫ਏૈޑЇಔᄊǴךॺ߾ᆀϐࣁЇᒧୢ ᚒǶ೭ঁୢᚒޑբݤǴሡाଞჹঁ၌ᇟ ѡǴѐ၌ୢന٫ϯᏔ(optimizer)ᅿЇಔᄊޑԋ ҁǶ೭ኬޑբݤ࣬ੁᓯӸޜ໔ϷၮᆉૈΚǶࣁ Αᗉխ೭٤લᗺǴךॺճҔᓯӸܭၗسύޑ ीၗૻǴϷന٫ϯᏔӧຑᒧЇਔǴ܌٬Ҕډޑ ԋҁኧᏵǴวрঁཥޑЇᒧᄽᆉݤǶӧ೭ ঁᄽᆉݤύǴךॺஒԵໆډфૈ࣬߈Їϐ໔ޑ ඹж܄Ǵ٠ЪմନόӝӸӧޑЇǶҁፕЎ܌ග рϐБݤςӧ PostgreSQL ၗჴբǴ٠а TPC-HޑၗࢎᄬჴᡍǴаᡍҁፕЎ܌ගБݤ ϐԖਏ܄ǹჴᡍܴΑךॺගрޑБݤёаᙚӳ ޑЇಔᄊǴቚуঁπբॄၩՉਔޑਏૈǶ! ! ᜢᗖຒǺၗسǵЇᒧǵЇඹжǵЇ ፓਠǵPostgreSQLǶ!!
1.
ᙁϟ!
!
ၗЇᒧୢᚒ(Index Selection Problem,
ISP) [5]ࢂঁӧ NP-hard ޑୢᚒǴᏢೌࣚޔԖΓ ӧ೭Бय़ՉࣴزǶ܌аΨόᘐޑԖΓගр٤ᇨ ᏤԄБݤ(heuristic)ǴаӧԖज़ޑਔ໔ύǴှр߈՟ ന٫ှǶЇޑӸӧᗨฅёаቚуၗޑਏૈǴ ՠࢂࡌҥό҅ዴޑЇόՠคݤуமၗޑਏ ૈԶЪᗋੁᓯӸޑޜ໔ǴЪቚуЇ׳ཥਔޑ ԋҁ<ࡺӵՖᙚӳޑЇǴ٬ၗسวචന ٫ޑਏૈǴࢂЇᒧୢᚒޑख़ᗺǶ! ӧၗύǴЇᒧୢᚒޑݤࢂǺ ᒵֹޑπբॄၩ(workload)аٮ٣ࡕຑᒧਔၮ ᆉǴаϷଞჹঁ၌ѐ၌ୢന٫ϯᏔ(optimizer) ᅿЇಔᄊޑԋҁǹ೭ኬޑݤ࣬ᅶᅷ ޑޜ໔Ϸၮᆉਔ໔ǴࣁΑှ،ၗЇᒧ ୢᚒޑݤǴҁፕЎଞჹЇᒧୢᚒගрΑঁ ཥޑှ،БݤǴךॺᒵᓯӸܭسҞᒵύޑԖज़ ၗૻǴஒ೭٤ၗૻ༼ᕴଆٰǴ٠ਥᏵ೭٤Ԗज़ޑၗ ૻٰຑঁπբॄၩޑᡏਏૈǶ! Ӣࣁךॺ܌ᏱԖޑၗૻԖज़Ǵՠѝाന٫ϯᏔ ،ۓӧࢌঁ၌٬ҔࢌঁЇǴךॺ൩ёаᘐۓ ٬Ҕ೭ঁЇޑਏૈाК٬Ҕൻׇཛྷ൨ӳǶ܌аൻ ׇ ཛྷ ൨ ޑ ԋҁ ё а բ೭ ঁ Ї ޑ ज़(upper bound)Ǵ٬ҔࢌЇޑԋҁεܭൻׇཛྷ൨ޑԋ ҁਔǴ߾ךॺёаᇥԜЇόӝӸӧǶԜѦǴך ॺΨวΑຑӕঁၗ߄໔Їඹж܄ޑמ ೌǶԜ၌ᇟѡޑЇӢࣁόӝӸӧԶմନ ਔǴ߾Ԝ၌ᇟѡᒧځдޑឦ܄ࣁЇǴԜ ਔךॺ൩ѸሡஒմନЇޑԋҁᙯ౽ډځдޑ Ȩඹж܄ȩឦ܄ύǶၮҔ೭٤מೌǴךॺ൩ёаྗ ዴޑႣЇॶόॶளࡌҥǴբࣁၗسᙚ ЇޑୖԵǶ!
!
2.
࣬ᜢࣴز!
!
ᏃᆅЇᒧୢᚒࢂ NP-hard ޑୢᚒǴฅԶ Ϟςวрӭ߈՟ޑှ،Бݤٰ෧Ͽୢᚒޑፄ ᚇࡋǶҞӭޑၗس೬ᡏࣣςஒԜф ૈჴբܭၗسύǶ! ! 2.1Їᒧୢᚒ ! ЇᒧୢᚒёаඔॊࣁǺӧঁх֖ӭঁ ၗ၌(query)ޑسύǴ،ۓाࡌҥব٤ ЇǴૈ٬ঁၗسᕇளന٫ޑਏૈǴ٠Ъ಄ ӝ٬Ҕޜ໔ޑज़ڋǴ೭٤ၗ၌ޑӝךॺᆀ ࣁπբॄၩ(workload)ǶךॺଷπբॄၩMх֖m ঁၗ၌ǴԜ mঁၗ၌аqBiB߄ҢǴ 1ɩiɩmǶ! ၗ՛ܺᏔѐפ಄ӝ၌చҹޑБԄᆀ ࣁӸڗၡ৩(access path)Ǵന୷ҁޑӸڗၡ৩ࣁൻׇ ཛྷ൨(sequential scan)ǴќѦךॺΨёа٬ҔЇ բӸڗၡ৩ǴԖёૈբӸڗၡ৩ޑឦ܄ឯՏǴך ॺᆀࣁ࣬ᜢឯՏ(relevant columns)Ƕ܌Ԗ࣬ᜢឯՏ ޑᖄᆀࣁংᒧЇ(candidate index set)Ǵ߄ҢࣁNǶךॺଷNх֖nঁংᒧޑЇǴаaBjB߄ҢǴ 1ɩjɩnǶঁংᒧЇ܌՞ޑޜ໔аdBjB߄ҢǴ 1ɩjɩnǹسϢࡌҥЇޑޜ໔ज़аD߄ҢǶ ଷسѝගٮᅿЇǴ߾ঁংᒧЇߡԖ ࡌҥᆶόࡌҥٿᅿރᄊǴӢԜNޑᏈӝߡж߄س ܌ԖёૈࡌҥЇޑރᄊǶךॺע೭ঁރᄊᆀࣁ Їಔᄊ(index configuration)Ƕךॺଷ܌ԖЇ ಔᄊޑӝPх֖pঁЇಔᄊǴPх֖ޑЇಔᄊ аcBkB߄ҢǴ1ɩkɩpǶঁЇಔᄊkёჹᔈډ
ঁЇޑӝNBkBǴж߄ЇಔᄊcBkBύ܌ࡌҥЇޑ ӝǶ! ךॺۓကcost(qBi B, cBkB)ж߄ӧಔᄊcBkBਔǴՉၗ ၌qi܌ሡाޑՉਔ໔ԋҁǶqi٬ҔcBkBޑᕇճ߾ ߄Ңԋ
)
Ǵځύ BB BB(
qi ck)
t(
qi)
t(
qi ckgain , =cos ,φ −cos , φ ж
߄ޜӝǶӵ݀аጁᖐݤ(exhausted search)ٰှ Їᒧୢᚒ(ISP)Ǵߡࢂр܌ԖЇಔᄊуᕴၗ ၌Չԋҁࡕޑॶǹ೭ঁॶӵ݀಄ӝޜ໔ज़ڋ ٠Ъᕴਔ໔ԋҁനλǴߡࢂୢᚒޑന٫ှΑǶӵკ 1܌ҢǶ ӈޑҞڄԄၟज़ڋԄǴၟςుΕࣴز ޑ UFLP(Uncapacitated Facility Location Problem)ୢ
ᚒ\2-6^ǴᏱԖᜪ՟ޑኧᏢኳԄǶ܌аࡐܴዴޑǴ ЇᒧୢᚒΨࢂঁ NP-hard ޑୢᚒǶ! კ 1;ЇҬϕբҔޑԋҁᆉ߄ 2.2Microsoft SQL Server ༾೬ȐMicrosoftȑ܌วޑၗس೬ᡏ SQL ServerǴӧ 1997 ԃ܌ගٮޑ SQL Server 7.0 ύǴග ٮЇ༾ፓᆒᡫ\4^ǴаБߡၗᆅޣᆅၗ سǶ၀سճҔന٫ϯᏔຑπբॄၩԋҁ ޑБԄǴჴբЇ༾ፓπڀǴځЬाᡯӵΠǺ! ϩπբॄၩ(workload)Ǵஒёૈӧ൨פၗਔ ቹៜԋҁޑឯՏࡷᒧрٰǴ٠ԋࣁংᒧЇ (candidate index)ǶௗвಒϩংᒧЇǴ٠ख़ཥ ीᆉཥޑۓࡕޑԋҁǴղᘐ၀ЇӸӧਔޑԋ ҁϷԋځд၌ޑԋҁቹៜӢનǶҗόᘐޑ ीᆉǴ،ۓрനࡕޑЇۓӝٰᙚǶനࡕ߾ ࢂ׳ᆒዴϩࢂցԖѸा٬Ҕӭख़ឯՏϡЇޑ ឦ܄ෳ၂ǴऩԖ಄ӝӭख़ឯՏឦ܄߾уΕংᒧЇ ύӆԛຑਏനࡕஒᙚӝӣᔈ๏٬ҔޣǶ 2.3IBM DB2 IBM܌ගٮޑၗس DB2Ǵӧ 2000 ԃ DB2 6.0ހύҭගٮЇ༾ፓπڀ[12]ǴDB2 ༾ፓБݤᆶ ༾೬ޑࢎᄬբݤ࣬՟Ǵа SAEFIS ᄽᆉݤǴஒ၌ ᇟѡϩԋϖঁҽǺ 1.EQǺрӧܭచҹޑឯՏǶ
2.OǺрӧ order byǵgroup by Ϸ join ឯՏޑӝǶ
3.RANGEǺрӧጄൎ၌ޑឯՏӝǶ 4.SARGǺрӧࡰۓឯՏ၌ޑឯՏӝǶ 5.REFǺځᎩӧ၌ᇟѡύගϷޑឯՏǶ ၸϩຑࡕǴӧόຬၸޜ໔ज़ڋϐΠǴࡷ р ޑ ឯ Տ ٩ ख़ ा ܄ ଯ ե ࡌ ҥ ᔕ Ї (virtual index)Ǵ٠ीᆉрёૈሡाޑीၗૻǴനࡕҗന ٫ϯᏔࡷᒧǴऩᔕЇрӧࡷᒧӝύǴ߾߄ Ңᙚ၀ឯՏࡌҥЇǶ 2.4Oracle ! OracleϦљӧ Oracle 8i ޑౢࠔύΨගٮس༾ ፓޑπڀǺTuning Pack ٰӢᔈᆅޣБߡޑᆅၗ سǴঁ Tuning Pack[8]܌ගٮޑ༾ፓπڀх ֖Πӈኧᅿ࣬ᜢޑ༾ፓфૈǺ! Oracle ExpertǺճҔ٤ᙁܰޑᡯֹٰᔠຎ ٠ගϲسਏૈޑπڀǶ! SQL AnalyzeǺЬाࢂϩ٬Ҕޣ၌ᇟѡޑ фૈǴёа၁ಒղᘐ၌ᇟѡࢂցԖׯቪԋ ׳ԖਏޑБԄǴᗉխԋၗวғᝄख़ ޑسၗྍ՞ҔԶफ़եਏૈǶ!
Index Tuning WizardǺၮբБԄࢂаπբॄၩ
ࣁ୷ᘵǴسϣޑϩࡕౢғᙚ่݀ ӣᔈ๏٬ҔޣǶ!
!
3.
سࢎᄬ!
!
ҁЎගрޑБԄࢂ׆ఈЇᒧπڀѝၸ سҞᒵ(system catalog)ϣޑၗૻ൩ёа،ۓा ᙚޑЇಔᄊǹࡽόाᓯӸπբॄၩǴΨόाӆ ၸၗന٫ϯᏔѐᆉӚᅿόӕޑЇಔᄊΠ ޑԋҁǶკ 2 ࣁسҞᒵीБݤ(CSA)Їᒧ πڀޑπբࢬำࢎᄬკǶᗨฅךॺӧᙚЇਔό ӆѐୢന٫ϯᏔǴՠόж߄ךॺޑ،ؒԖ٩ྣന ٫ϯᏔޑᆉԋҁ،ۓǶ࣬ϸޑǴךॺޑБԄࢂֹ ӄ٩ྣന٫ϯᏔޑၗૻٰ،ۓǴԶ೭٤ၗૻӧՉ ၌ޑਔࡕߡϩ٠ᒵӧҞᒵύǴ೭٤ᒵߡ ࢂךॺᆀڥޑᆫी(aggregated statistics)ǴࡺԜ БԄϝࢂന٫ϯᏔԋҁ୷ᘵ(optimizer-cost based)ޑ БԄǶךॺஒ೭ኬޑБԄǴᘜયрΠӈ൳ᅿ܄Ǻ! 1. όҔӆѐϩ܈٬ҔπբॄၩǴؒԖѸाᓯ ӸπբॄၩǶ! 2. ीၗૻ܌՞ޑޜ໔ѝکၗ߄(table)ύޑ ឦ܄ঁኧԋ҅КǶ! 3. Їᒧπڀ٬ҔҞᒵीբࣁЇᙚޑ ୷ᘵǶ! 4. سҞᒵ٩ԛޑ၌ीჄ(query plan)ಕ ीၗૻǴځᒵޑၗૻаന٫ϯᏔࡷᒧ ၌ीჄ܌ၮҔޑၗૻϷسᅱ I0O ޑਏ ૈࣁЬǶ! ! ॊѤᗺ܄ύޑಃѤᗺǴځ܌ၮҔޑၗૻाࢂջਔڗளޑǴό׆ఈႽှ ISP ޑᄽᆉ ݤǴၮҔᔕЇ(virtual index)ѐ௨ӈӚᅿЇಔ ᄊǴаڗளᚐѦޑኧᏵǶךॺϐ܌а௦ڗ೭ኬݤ ޑЬाҞޑࢂ׆ఈૈ๓уճҔന٫ϯᏔࡌҥ၌ ीჄਔ܌ҔډޑၗૻǶӵ݀ךॺஒന٫ϯᏔຑᒧी Ⴤࡕޑၗૻᒵ܈ޣၮᆉǴ܌ޑᚐѦԋҁ൳Я ࣁ႟ǴӢࣁౢғ೭٤ၗૻޑԋҁচҁ൩ࢂന٫ϯᏔ ࡌҥीჄਔ൩ाбрޑǶךॺޑၮᆉ٠ᒵ೭ ٤ԖҔޑၗૻǴ҅ךॺӧЇᒧਔǴ൩όሡ ाӆѐ၌ୢന٫ϯᏔΑǶ! ! ! კ 2;CSA Їᒧπڀޑπբࢬำࢎᄬკ! ! 3.1ന٫ϯᏔޑၗૻᆶଷ! ! ന٫ϯᏔӧՉ၌ਔǴځ܌ၮҔޑၗૻӵ݀ คज़ڋᑩޑ၉Ǵךॺॊ܌ፕޑୢᚒஒคཀ ကǴࡺӧԜךॺჹന٫ϯᏔ܌၀ޕၰޑૻ৲բঁ ܴዴޑज़ڋǶךॺගраΠٿঁଷǺ! ଷ1.ʳ ന٫ϯᏔӧ،ۓ၌ीჄ(query plan)ਔǴ ѝޕၰ(ԿϿޕၰ)ځ܌٬Ҕ၌ीჄޑԋ ҁᆶ٬Ҕൻׇཛྷ൨ޑԋҁǶ! ଷ2.ʳ ന٫ϯᏔவҞΠޑ၌ीჄύǴࡷ ᒧрԋҁനեޑ၌ीჄǶඤѡ၉ᇥǴऩ ന٫ϯᏔ܌ࡷᒧޑ၌ीჄԖ٬ҔЇǴ ߾ਏૈᔈ၀К٬Ҕൻׇཛྷ൨ӳǶ! ୷ܭаଷǴךॺۓကΑڂࠠޑന٫ϯᏔଞ ჹൂ၌ຑᒧीჄਔǴ܌ҔډޑၗૻǺ! ၗૻ1.ʳ ၌ᇟѡύǴwhere ηѡ္ঁ಄ӝచҹឦ ܄ޑᒧڗ(selectivity)Ƕ! ၗૻ2.ʳ ঁၗ߄٬Ҕൻׇཛྷ൨ޑԋҁǶ! ၗૻ3.ʳ ၌ᇟѡޑᕴԋҁϷ၌ᐋ(query tree)Ƕ! ၗૻ4.ʳ ၌ᐋύঁᗺ(node)ޑԋҁϷႣ ޑၗኧ(tuple)ࢂӭϿǶ! ќѦᜪԖҔޑၗૻ൩ࢂၗسᒵΑ Ԗᜢ I0O ԛኧޑ࣬ᜢၗૻǴٯӵࢌঁၗ߄ᔞਢಕ ी༧Ӹڗ(block access)ޑԛኧ܈ޣፂڮύ (buffer hit)ޑԛኧǶךॺӵ݀ճҔ೭٤ၗૻǴٰࣁ ຑЇਏૈޑୖԵǴځ܌ޑȨᚐѦȩԋҁ൳Я ࣁ႟ǴӢࣁࡌҥ೭٤ၗૻޑԋҁচҁ൩ࢂၗس ܌ाбрޑǶ! ڂࠠޑၗسՉ၌ਔǴ܌ᅱޑૻ৲ ࡐӭǴаΠךॺۓကٿຑЇਏૈਔҔډޑ ၗૻǺ! ၗૻ5.ʳ ၌ՉਔǴঁၗ߄ᔞਢ(table file) ܈Їᔞਢ(index file)܌ޑ༧Ӹڗ ԛኧǶ! ॊޑၗૻύǴၗૻ 3 ж߄ޑࢂന٫ϯᏔޑႣ ॶǴѬх֖ϩύѧೀᏔ(CPU)ၮᆉޑԋ ҁǹԶၗૻ 5 ж߄ޑࢂჴෳॶǴѬൂપࢂ I0O ޑԋ ҁǹၗૻ 3 کၗૻ 5 ёаж߄ঁ၌ޑԋҁǶ! ! 3.2၌ᇟѡޑԋҁኳࠠ! ! ঁ ၌ᇟѡёа ϩԋ၌ (query)ǵ׳ཥ (update)ǵմନ(deletion)ǵකΕ(insertion)ѤᅿރݩǶ ӢࣁմନکකΕᆶ׳ཥᜪ՟Ǵ܌аӧԜךॺЬा ፕ၌Ϸ׳ཥٿᅿރݩǶӧ\4^ύςࡌҥ܄ޑ ԋҁኳࠠǴவ೭ঁԋҁኳࠠǴךॺёаע 2.2 ۓ ကޑcost(qBi B, cBkB)ӆಒϩࣁ᠐ڗԋҁ(read_cost)ǵᆢៈ Їԋҁ(maintain_index_cost)ϷᆶЇคᜢޑڰ ۓԋҁ(constant_cosuǴх֖׳ཥၗ߄ǵᒡр่ ݀Ǿբޑԋҁ)Ƕ߄ҢԋΠӈԄηǺ! ! !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!Ǿ(1)
!!!
ၗޑᆢៈЇԋҁ(maintain_index_cost) ჹࢌዴۓޑπբॄၩԶقࢂڰۓޑǴόᒿ ЇಔᄊόӕԶᡂǶ܌аჹࢌЇಔᄊcBkBޑᆢៈ ЇԋҁǴךॺ߄ҢࣁǺ!!Ǿ
(2)! ࡺჹࢌЇಔᄊcBkBύޑЇ{a}ޑᆢៈЇ ԋҁёа߄ҢࣁǺ!( )
c mi t(
q c)
k P t MI M i k i k =∑
∈ ∈ all for , , cos _ cos _ ! !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!Ǿ(3)! ! 3.3Їਏૈຑ! ! ךॺஒճҔسҞᒵύޑԖज़ၗૻǴКၨЇ ӸӧޑԋҁᆶόӸӧਔ٬Ҕൻׇཛྷ൨ޑԋҁǴٰຑ ЇࢂցॶளӸӧǶЇޑӸӧǴჹၗ׳ཥ ޑբѸۓाбрᚐѦޑᆢៈԋҁǴჹ၌ޑբ ߾ԖёૈගٮਏૈၨӳޑӸڗၡ৩Ƕඤѡ၉ᇥǴ ঁЇჹঁπբॄၩԶقॶளӸӧǴࢂӢࣁ٬Ҕ Їӧ᠐ڗᏹբ܌ᕇளޑճεܭځᆢៈޑԋҁǶ! ࢂց٬ҔࢌঁЇբӸڗၡ৩Ǵ߾ᒿЇ ಔᄊޑόӕԶׯᡂǶךॺзӧޑЇಔᄊࣁcBcurrentǹӧcB BcurrentBਔǴന٫ϯᏔղᘐ٬ҔЇaբӸڗ
ၡ৩ޑ၌ࣁM(Bcurrent,aB)Ǵ ǶM(Bcurrent,aB) ᙁᆀMBaBǶ! зMBaBȷ=M -MBaǴჹMB BaBȷԶقǴന٫ϯᏔӧcBcurrentB ᆶcBcurrentB-{a}܌ࡷᒧӸڗၡ৩ޑ᠐ڗԋҁஒ ኬǶӢࣁךॺଷന٫ϯᏔࡷᒧਔԋҁനեޑ ЇಔᄊǴ܌аࡽฅӧcBcurrentBਔό٬ҔЇaǴ߾ ߄ҢԖКЇ aԋҁ׳եޑӸڗၡ৩ǴӢԜӧ cBcurrentB-{a}Ψ٬Ҕ၀ԋҁၨեޑӸڗၡ৩Ǵց߾൩ ၴϸന٫ϯᏔࢂඵޑଷ(ଷ 2)Ƕ! ךॺ٬ҔմନЇޑᇨᏤԄ(heuristic)Бݤٰ ( ) M M current,a ⊂
ղ ᘐ Ї ࢂ ց ӝ Ӹ ӧ Ƕ ჹ Ї a Զ ق Ǵ Ǵж߄Їaό ӝӸӧǴӢࣁჹMBaBȷԶقǴځ᠐ڗԋҁࢂኬޑǴ ܌аѝाԵቾMBaBޑ᠐ڗԋҁϷЇޑᆢៈԋҁǶӢ ԜёஒԄᙯϯԋаΠԄǺ! ! !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!Ǿ(4) ёаӆϯᙁࣁǺ! ! !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!Ǿ(5) ځύ ж߄ЇaόӸӧޑ ݩΠǴന ڗၡ৩Ǵёૈޑݤࣁ ٬Ҕځд ཛྷ൨Ƕҗܭךॺคݤޕ ၰӧcBcurr ࢂց٬ҔځдЇǴӢ Ԝךॺѝӳीᆉ ٫ϯᏔёૈࡷޑӸ ޑЇ܈٬Ҕൻׇ entB-{a}ޑಔᄊΠǴMBaB ޑज़ॶ(upper bound)Ǵҭջ٬Ҕൻׇཛྷ൨ޑԋҁǶջࣁǺ Ǿ(6) ܌аӵϦԄ(7)܌ҢǺ Ǿ(7) ӵ݀ϦԄ(7)ԋҥǴ߾ךॺёаᘐЇ a ό ӝӸӧǴࡺஒЇ a մନǴჹঁπբॄၩԶقǴ Ԗ׳ӳޑᕴԋҁ(total cost)Ƕ ! 3.4Їඹж! ! ӧ 3.3 ٬ҔޑБݤǴᗨฅӧфૈςىа ᑔᒧр٤ܴᡉόӳޑЇǴՠྗዴࡋόଯǴচ ӢӧܭǺόӕޑЇಔᄊǴന٫ϯᏔёૈࡷᒧό ӕޑӸڗၡ৩ǶᖐٯٰᇥǴךॺҔcBcurrentBޑኧᏵ ղᘐޑ୷ྗǴӧմନঁЇϐࡕǴচҁ٬Ҕ೭ঁ Їޑ၌ᇟѡǴёૈᙯԶ٬ҔځдޑЇǴ܌ аځдЇޑཛྷ൨ԛኧ൩ёૈቚуǶӢԜǴךॺӧ ׯᡂЇಔᄊࡕǴᝩុ٬Ҕᙑޑԋҁၗૻٰ،ۓा մନবঁЇǴځ҅ዴ܄ࢂёૈౢғୃৡޑǶ܌ аǴךॺวঁמѯǴவ၌ᇟѡǴѐפрڀ ԖȨඹж܄ȩޑឦ܄ǶڀȨඹж܄ȩޑឦ܄ύǴ ӵ݀ځύࢌঁЇմନǴ߾Ӣࣁځдឦ܄ޑ ЇԖଯࡋёૈඹж೭ঁឦ܄ޑЇǴՉᜪ՟ޑ ၌ीჄǴ߾ךॺёаעմନЇޑឦ܄Ǵځ٬Ҕ Ї࣬ᜢޑԋҁϷЇཛྷ൨ԛኧᙯඤډඹжឦ܄ ္ۘ҂մନޑЇǶ! մନࢌঁЇࡕǴന٫ϯᏔ،ۓ٬Ҕځдব ঁЇǻЪȨճȩޑᙯ౽၀ӵՖȨඤᆉȩǻך ॺӧԜ௦ڗΑঁᇨᏤԄ(heuristic)ޑБݤǴךॺۓ ကΑ܌ᒏڀԖȨඹж܄ȩޑឦ܄Ǵඹж܄ޑឦ܄ ሡᢀჸ SQL ၌ᇟѡջёǴඹжឦ܄ϣޑឦ ܄ሡाڀԖаΠٿঁᗺǺ 1. ࢂӧ where ηѡύǴज़ۓ၌చҹޑឦ܄Ƕ 2. ᒧڗ(selectivity)եܭന٫ϯᏔ٬ҔЇ ޑߐᘖ(threshold)Ƕ Ȩճȩޑᙯ౽ၟЇޑ܄ԖᜢǶךॺଷ ঁᜢ߯߄R(relation R)ԖNၗ(tuples)Ǵ٬ҔBP + P ᐋ( BP + P -tree)ޑЇǴ߾ਥᏵ[1]ǴӸڗЇޑԋҁӵ ΠǺ җϦԄ(8)ǴךॺёаᇥǴ٬ҔЇӸڗӕঁၗ ߄ޑԋҁεऊᆶᒧڗ(selectivity)ԋ҅КǶךॺ ൩٬Ҕ೭ঁ܄፦ٰඤᆉڀȨඹж܄ȩឦ܄္Ї ޑԋҁǶ ಃΒϩाᙯ౽ޑࢂ٬ҔЇਔǴჹၗ߄Ӹ ڗޑԋҁǶךॺ٬Ҕ[5]܌ගрޑԋҁኳࠠǹவ p ঁ ।य़ύගڗ nt ၗޑ।य़Ӹڗኧ(NPA)ीᆉБ ԄǺ ! 3.5سҞᒵीၗૻ! ! ࣁΑᡣ 3.3 ගрޑຑЇਏૈޑמѯૈ ჴǴךॺीΑঁཥޑၗҞᒵޑᆜाǴх ֖Α relation_costǵindex_statisticsǵindex_substitution Οঁၗ߄Ƕ೭Οঁၗ߄ϣ܌х֖ޑឦ܄ӵ߄ 1 ܌ҢǶ! relation_cost ᒵᜢ߯߄(relation)࣬ᜢޑ୷ҁ ၗૻǶindex_statistics Ϸ index_substitution ၗ߄߾ ᒵၗՉਔ࣬ᜢಕीޑीၗૻǴନΑ index_substitution ೭ঁၗ߄ύឦ܄ޑኧॶሡा җϦԄ(8)ϷϦԄ(9)ࡪྣᒧڗޑКٯѐीᆉѦǴځ Ꭹٿঁၗ߄ύឦ܄ޑኧॶǴѸவ PostgreSQL ၗسύޑسҞᒵ(system catalogs)ளǶ! ၗޑᅿᏹբǴ٩߄ 1 ޑۓက׳ཥޑ բǶٯӵ௦ڗൻׇཛྷ൨ਔǴ߾ჹᔈޑ n_table_scan ൩у 1ǹԶӧЇ၌ਔǴךॺவ SQL ޑᇟ ѡύפр܌٬ҔޑЇࢂցԖȨඹж܄ȩޑឦ܄ Ƕӵ݀Ԗޑ၉Ǵ٩ྣځᒧڗǴࡷᒧрঁ ܌٬ҔޑЇόӸӧਔǴനёૈඹඤޑЇǴ٠ஒ ϐᒵډ index_substitution ೭ঁၗ߄ύǶ׳ཥ index_substitutionޑၗਔǴा٩ྣ 3.4 ௶ॊޑԋ ҁኳࠠǴࡪྣᒧڗޑКٯٰीᆉᙯඤޑԋҁǶ! Ǿ(8) Ǿ(9)
! ߄ 1;ၗ߄ޑӜᆀϷځ܌х֖ޑឦ܄! ! ! ϦԄ(7)ύǴ ж߄ ٬ҔЇ a ਔǴ᠐ڗᏹբޑԋҁϷᆢៈޑԋҁǴځ ॶ খ ӳ ܭ index_statistic ύ Ї a ޑ
ifile_blks_readǵifile_blks_write ᆶ rfile_blks_read ࣬
уǶ܌аϦԄ(7)ёаׯቪԋǺ! ! Ǿ(10) ࢌঁЇմନࡕǴךॺߡёа٩ྣ indexidᔠ index_substitution ၗ߄Ǵஒёаᙯඤ ޑԋҁीᆉрٰ٠ಕуډ index_statistics ၗ߄ ύǶӆ٩ྣ index_statistics ၗ߄ύޑၗૻख़ཥी ᆉࡕǴࡷᒧрΠঁाմନޑЇǶ! !
4.
سჴᡍ!
!
ך ॺ ஒ ಃ 3 ക ޑ ፕ ୷ ᘵ ჴ ሞ ჴ բ ӧ PostgreSQL 7.3.3ύ[9]Ǵ٠٬Ҕ phpPgAdmin ࣁ٬ Ҕޣϟय़ޑ໒วπڀǴᙖၸᆛ।ޑᏹբٰӸڗ ၗسǴԶЪа TPC-H ޑᆜा(schema)Ϸၗ \11^ࣁךॺჴᡍҔޑၗኧᏵǴ٠ࡌҥΑჴᡍ Ҕޑπբॄၩ(х֖၌Ϸ׳ཥ)Ƕ! ! 4.1ჴᡍᡯ! ! २Ӄӧ PostgreSQL ၗسύࡌҥ TPC-H ޑၗǴӆਥᏵךॺ܌ࡌҥޑπբॄၩǴவၗ ύᘏڗၗૻٰղᘐЇόӝӸӧǺ! 1. வ index_statistics ύ ी ᆉ ঁ Ї ޑ ifile_blks_read! ,! ifile_blks_write! ,! rfile_blks_readޑᕴӝǶ! 2. ஒ index_statistics ύ ޑ n_idx_scan ४ а relation_costύޑ table_scan_costǶ! 3. Кၨ 1 ک 2 ޑ่݀Ǵӵ݀ 1 ޑ่݀λܭ 2 ޑ ่݀Ǵ߾όϒǴϸϐǴ1 ޑ่݀εܭ 2 ޑ่݀Ǵ߾ԜЇόӝӸӧǴਥᏵךॺޑ ᄽᆉݤǴᔈ၀ஒϐմନǶ! 4. ਥ Ᏽ մ ନ Ї ޑ indexid Ǵ வ index_substitution ύפрඹж܄ޑឦ܄Ǵ٠ ஒ tran_ifile_blks_read Ϸ tran_rfile_blks_read ᙯඤډඹж܄ޑឦ܄Ƕ! 5. ӣډಃᡯǴᝩុפόӝӸӧޑ ЇǶ! 6. ܌ԖޑЇפֹࡕǴؒԖाմନޑ ЇਔǴԜਔޑЇಔᄊ൩ࢂךॺाᙚޑ ЇಔᄊǶ! ! 4.2ჴᡍ่݀! ! ךॺ٬ҔٿᅿБݤǺᅿࢂԖ٬ҔЇඹжޑ ᢀۺǴஒմନЇޑճᙯඤډඹж܄ޑឦ܄ύǹ ќᅿࣁؒԖ٬ҔЇඹжǴൂપஒό಄ӝԋҁޑ ЇմନǶ! ! ȜБݤȝค٬ҔЇඹжޑБݤ! ٩ྣךॺ܌ࡌҥޑ index_statistics ၗ߄Ǵך ॺёаीᆉঁЇޑԋҁϷ٬Ҕൻׇཛྷ൨ޑ ԋҁǴӵ߄ 2 ܌ҢǹЇ c_addressǵc_phoneǵ c_acctbalޑЇཛྷ൨ԋҁεܭൻׇཛྷ൨ԋҁǴࡺԜ ΟঁЇόӝӸӧǴӵ݀ؒԖ٬ҔЇޑඹж БݤǴ߾Їಔᄊࣁ{c_nameǵc_nationkey}Ǵךॺ ஒϐڮӜࣁ C1Ǵࡺ C1={c_nameǵc_nationkey}Ƕ! ! ȜБݤΒȝԖ٬ҔЇඹжޑБݤ! ӵ݀٬ҔЇඹжޑБݤǴ߾ਥᏵךॺගрޑ ᄽᆉݤǴӃஒനόӝӸӧޑЇմନǹҗ߄ 3 ޕǴc_phone നόӝӸӧǴࡺஒϐմନǴ٠ਥᏵc_phone ޑ indexid ѐפ index_substitution ၗ
߄Ǵפޑ่݀วմନ c_phone ϐࡕஒԋҁᙯ ඤډ c_address Ϸ c_acctbalǴ܌аךॺाஒᙑޑ ीၗૻ׳ཥǴஒᙯඤࡕޑԋҁϩձуډ c_address Ϸ c_acctbal ύǴӵ߄ 3 ܌Ңǹ׳ཥֹ index_statistics ࡕǴाख़ཥीᆉࢂցᗋԖόӝӸӧޑЇǴ่݀ ว c_acctbal ಄ӝմନޑచҹǴ܌аाஒ c_acctbal մ ନ ٠ ஒ ԋ ҁ ᙯ ඤ ډ ඹ ж ܄ ޑ ឦ ܄ ύ Ǵ ٩ ྣ index_substitution ၗ߄Ǵc_acctbal ؒԖඹж܄ޑ ឦ܄Ǵࡺѝሡஒ c_acctbal வीၗύմନջёǴ ӵ߄ 5ǹӧմନ c_phone Ϸ c_acctbal ࡕǴЇಔᄊ ᡂࣁ{ c_nameǵc_addressǵc_nationkey ~Ǵךॺஒϐ ڮ Ӝ ࣁ C2 Ǵ ࡺ C2={c_name ǵ c_address ǵ c_nationkey}Ƕ! !
߄ 2;٬ҔЇޑԋҁᆶൻׇཛྷ൨ޑԋҁ! ! ! ߄ 3; մନ c_phone ࡕǴӚЇޑ࣬ᜢԋҁ! ! ! ߄ 4; մନ c_acctbal ࡕǴځдЇޑ࣬ᜢԋҁ! ! ! ךॺ٬Ҕ೭ٿঁЇಔᄊ(C1 Ϸ C2)Ϸπբॄ ၩჴሞᡍѬॺޑਏૈǹךॺӧԜ٬Ҕന٫ϯᏔޑ Ⴃॶ(EXPLAIN)ϷჴሞՉπբॄၩ܌ޑਔ ໔Ǵӵ߄ 5 ࢂന٫ϯᏔႣޑ่݀Ǵ߄ 6 ࢂჴሞ Չπբॄၩޑਔ໔ǹךॺวǴόᆅࢂന٫ϯ ᏔޑႣ܈ࢂჴሞՉޑ่݀ǴᡉҢр٬ҔЇ ඹжБݤٰᙚЇಔᄊޑྗዴ܄Ϸਏૈၨ٫ǴΨ ჴΑךॺගрޑБݤёаԖਏޑᙚӝޑ ЇǴٰှ،ЇᒧޑୢᚒǶ! ! ߄ 5;C1(คЇඹж)ᆶ C2(ԖЇඹж)ਏૈКၨ! ! ! ߄ 6;C1(คЇඹж)ᆶ C2(ԖЇඹж)ჴሞՉ ޑਏૈ! !
!
5.
่ፕ!
!
ҁፕЎගрΑঁ٬ҔीၗૻޑБݤǴճҔ ӸӧܭၗسҞᒵύޑीၗૻǴٰຑЇ όӝӸӧǹᙖҗմନόӝӸӧޑЇٰቚу ၗޑਏૈǶךॺஒፕᔈҔӧ໒ܫচۈዸޑ PostgreSQLၗسǴਥᏵᒵӧسҞᒵύ ޑीၗٰीᆉঁЇޑЇᔞਢޑ༧ ӸڗኧϷၗ߄ᔞਢޑ༧Ӹڗኧ٠Ъуᕴଆ ٰǴஒځ่݀کൻׇཛྷ൨ޑԋҁКၨǴӵ݀εܭൻ ׇཛྷ൨ޑԋҁǴ߾ԜЇόӝӸӧǴ܌аѸஒ ϐմନǴ٠Ъஒځԋҁᙯඤډඹж܄ឦ܄ύǹӧ ჴᡍύǴךॺຑԖԵໆඹж܄ឦ܄کؒԖԵໆ ඹж܄ឦ܄Ǵ೭ٿᅿޑЇಔᄊǴךॺว ԖԵໆඹж܄ឦ܄ޑ่݀ǴځਏૈၨӳǴ܌аԜ БݤёаԖਏޑගٮၗᆅব٤Їॶள Ӹӧǵব٤ЇόॶளӸӧǴჹঁၗޑᆅ πբ׳ԖᔅշǶ!!
ୖԵЎ!
!
[1] Barcucci, E., Pinzani, R., and Sprugnoli, R., “Optimal Selection of Secondary Indexes“, IEEE Transactions on Software Engineering, Vol. 16, No. 1, 1990. pp. 32-38.
[2] Caprara, A., Fischetti, M., and Maio, D., “Exact and Approximate Algorithms for the Index Selection Problem in Physical Database Design“, IEEE Transactions on Knowledge and Data Engineering, Vol. 7, No. 6, December 1995, pp. 955-967.
[3] Caprara, A., and Salazar, J.J., “A Branch-and-Cut Algorithm for the Index Selection Problem“, Discrete Applied Mathematics 92, 1999, pp. 111-134.
[4] Chaudhuri, S., Narasayya, V., "An Efficient, Cost-Driven Index Selection Tool for Microsoft SQL Server," in Proceedings of the 23rd VLDB Conference Athens, Greece, 1997, pp146-155. [5] Choenni, S., Blanken, H.M., and Chang, T.,
“Index Selection in Relational Databases“, In Proceedings International Conference on Computing and Information, April 1993, pp. 491-496.
[6] Cornuejols, G., Nemhauser, G.L., and Wolsey, L.A., “The Uncapacitated Facility Location Problem“, in P.B. Mirchandani and R.L. Francis (Ed.s), Discrete Location Theory, John Wiley, New York, 1991. pp. 119-171.
[7] Finkelstein, S., Schkolnick, M., and Tiberio, P., “Physical Database Design for Relational Databases“, ACM Transactions on Database Systems, Vol. 13, No. 1, March 1988, pp. 91-128.
[8] Oracle Manager Quick Tours http://otn.oracle.com/products/oem/htdocs/qtour s/tuning/tune.htm
[9] PostgreSQL Documentation, [Administrator’s Guide], http://www.postgresql.org/docs/.
[10]Silberschatz, A., Korth, F. H., and Sudarshan, S., “Database System Concepts“, 4ed, Mc Graw Hill, Singapore, 2001, pp. 621-623.
[11]Transaction Processing Performance Council, TPC-H, http://www.tpc.org/tpch/.
[12]Valentin, G., Zuliani, M., Zilio, D. C., Lohman, G., and Skelley, A., “DB2 Advisor: An Optimizer Smart Enough to Recommend Its Own Indexes“, Proceedings, 16th IEEE Conference on Data Engineering, San Diego, CA, 2000, pp.101-110.
