Dissertation/ Thesis
Database query optimisation based on measures of regret
| Title: | Database query optimisation based on measures of regret |
|---|---|
| Authors: | Alyoubi, Khaled Hamed |
| Publisher Information: | Birkbeck, University of London, 2016. |
| Publication Year: | 2016 |
| Subject Terms: | 005.75 |
| Description: | The query optimiser in a database management system (DBMS) is responsible for �nding a good order in which to execute the operators in a given query. However, in practice the query optimiser does not usually guarantee to �nd the best plan. This is often due to the non-availability of precise statistical data or inaccurate assumptions made by the optimiser. In this thesis we propose a robust approach to logical query optimisation that takes into account the unreliability in database statistics during the optimisation process. In particular, we study the ordering problem for selection operators and for join operators, where selectivities are modelled as intervals rather than exact values. As a measure of optimality, we use a concept from decision theory called minmax regret optimisation (MRO). When using interval selectivities, the decision problem for selection operator ordering turns out to be NP-hard. After investigating properties of the problem and identifying special cases which can be solved in polynomial time, we develop a novel heuristic for solving the general selection ordering problem in polynomial time. Experimental evaluation of the heuristic using synthetic data, the Star Schema Benchmark and real-world data sets shows that it outperforms other heuristics (which take an optimistic, pessimistic or midpoint approach) and also produces plans whose regret is on average very close to optimal. The general join ordering problem is known to be NP-hard, even for exact selectivities. So, for interval selectivities, we restrict our investigation to sets of join operators which form a chain and to plans that correspond to left-deep join trees. We investigate properties of the problem and use these, along with ideas from the selection ordering heuristic and other algorithms in the literature, to develop a polynomial-time heuristic tailored for the join ordering problem. Experimental evaluation of the heuristic shows that, once again, it performs better than the optimistic, pessimistic and midpoint heuristics. In addition, the results show that the heuristic produces plans whose regret is on average even closer to the optimal than for selection ordering. |
| Document Type: | Thesis Or Dissertation |
| Language: | English |
| Access URL: | https://ethos.blethoshyku.com/concern/thesis_or_dissertations/715336 |
| Accession Number: | edsble.715336 |
| Database: | British Library EThOS |
| FullText | Text: Availability: 0 CustomLinks: – Url: https://ethos.blethoshyku.com/concern/thesis_or_dissertations/715336 Name: EDS - British Library EThOS (ns324271) Category: fullText Text: View record in EThOS |
|---|---|
| Header | DbId: edsble DbLabel: British Library EThOS An: edsble.715336 RelevancyScore: 937 AccessLevel: 3 PubType: Dissertation/ Thesis PubTypeId: dissertation PreciseRelevancyScore: 937.178283691406 |
| IllustrationInfo | |
| Items | – Name: Title Label: Title Group: Ti Data: Database query optimisation based on measures of regret – Name: Author Label: Authors Group: Au Data: <searchLink fieldCode="AR" term="%22Alyoubi%2C+Khaled+Hamed%22">Alyoubi, Khaled Hamed</searchLink> – Name: Publisher Label: Publisher Information Group: PubInfo Data: Birkbeck, University of London, 2016. – Name: DatePubCY Label: Publication Year Group: Date Data: 2016 – Name: Subject Label: Subject Terms Group: Su Data: <searchLink fieldCode="DE" term="%22005%2E75%22">005.75</searchLink> – Name: Abstract Label: Description Group: Ab Data: The query optimiser in a database management system (DBMS) is responsible for �nding a good order in which to execute the operators in a given query. However, in practice the query optimiser does not usually guarantee to �nd the best plan. This is often due to the non-availability of precise statistical data or inaccurate assumptions made by the optimiser. In this thesis we propose a robust approach to logical query optimisation that takes into account the unreliability in database statistics during the optimisation process. In particular, we study the ordering problem for selection operators and for join operators, where selectivities are modelled as intervals rather than exact values. As a measure of optimality, we use a concept from decision theory called minmax regret optimisation (MRO). When using interval selectivities, the decision problem for selection operator ordering turns out to be NP-hard. After investigating properties of the problem and identifying special cases which can be solved in polynomial time, we develop a novel heuristic for solving the general selection ordering problem in polynomial time. Experimental evaluation of the heuristic using synthetic data, the Star Schema Benchmark and real-world data sets shows that it outperforms other heuristics (which take an optimistic, pessimistic or midpoint approach) and also produces plans whose regret is on average very close to optimal. The general join ordering problem is known to be NP-hard, even for exact selectivities. So, for interval selectivities, we restrict our investigation to sets of join operators which form a chain and to plans that correspond to left-deep join trees. We investigate properties of the problem and use these, along with ideas from the selection ordering heuristic and other algorithms in the literature, to develop a polynomial-time heuristic tailored for the join ordering problem. Experimental evaluation of the heuristic shows that, once again, it performs better than the optimistic, pessimistic and midpoint heuristics. In addition, the results show that the heuristic produces plans whose regret is on average even closer to the optimal than for selection ordering. – Name: TypeDocument Label: Document Type Group: TypDoc Data: Thesis Or Dissertation – Name: Language Label: Language Group: Lang Data: English – Name: URL Label: Access URL Group: URL Data: <link linkTarget="URL" linkTerm="https://ethos.blethoshyku.com/concern/thesis_or_dissertations/715336" linkWindow="_blank">https://ethos.blethoshyku.com/concern/thesis_or_dissertations/715336</link> – Name: AN Label: Accession Number Group: ID Data: edsble.715336 |
| PLink | https://search.ebscohost.com/login.aspx?direct=true&site=eds-live&db=edsble&AN=edsble.715336 |
| RecordInfo | BibRecord: BibEntity: Languages: – Text: English Subjects: – SubjectFull: 005.75 Type: general Titles: – TitleFull: Database query optimisation based on measures of regret Type: main BibRelationships: HasContributorRelationships: – PersonEntity: Name: NameFull: Alyoubi, Khaled Hamed IsPartOfRelationships: – BibEntity: Dates: – D: 01 M: 01 Type: published Y: 2016 Identifiers: – Type: issn-locals Value: edsble |
| ResultId | 1 |