1 / 6

Query Processing & Optimization

Query Processing & Optimization. Database Systems (Fourth Edition - 28.5 + 28.5.1). SQL Standarder. SQL standarder B etegnelse for de features der er implementeret i SQL U dbedre svagheder i tidligere versioner SQL:2003 standarden Har mangel indenfor bl.a.:

mirit
Download Presentation

Query Processing & Optimization

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Query Processing & Optimization Database Systems (Fourth Edition - 28.5 + 28.5.1)

  2. SQL Standarder • SQL standarder • Betegnelse for de features der er implementeret i SQL • Udbedre svagheder i tidligere versioner • SQL:2003 standarden • Har mangel indenfor bl.a.: • Mulighed for definering af nye indexstructures • Mulighed for at give query-optimizeren informationer om ”cost” på UserDefinedFunctions • Variationer fra produkt til produkt

  3. Query Optimizer • Formål • Optimerer det enkelte query og ”flader” det ud • Muligt hvis man anvender interne UDF’s i SQL • Eksterne UDF • Query optimizerenkan ikke ”tolke” disse • QO fodres med følgende oplysninger: • A : CPU cost for funktionen • B : Forventede antal bytes • C : CPU cost pr. byte

  4. Query Optimizer • ADT (?) • A : CPU cost for funktionen (pr. kald) • B : Det forventede antal bytes (procentvis) fra argumentet som anvendes • C : CPU costpr. byte der læses • Samlet ”cost” • I/O • ( B * expected_size_of_argument ) • CPU • A + C * ( B * expected_size_of_argument ) • Alternativ • Experimentiel fremgangsmåde

  5. Eksempel • Query:SELECT *FROM PropertyForRentWHERE nearPrimarySchool(postcode) < 2.0ANDcity = ”Glasgow”

  6. New Index Types • Relationelle DBMS’er anvender B-treeindex • Ikke optimalt i forhold til ORDB • Nogle ORDBMS med support for nye index: • GenericB-tree (tillader B-treeindex på alle datatyper og ikke kun alfanumeriske datatyper) • QuadTrees • K-D-B Trees • R-trees (region trees– giver hurtig adgang til to- og tredimensionelle datastrukturer) • Grid files • D-Trees (tekst support) • PluginUserDefinedIndexes

More Related