- 1、本文档共50页,可阅读全部内容。
- 2、原创力文档(book118)网站文档一经付费(服务费),不意味着购买了该文档的版权,仅供个人/单位学习、研究之用,不得用于商业用途,未经授权,严禁复制、发行、汇编、翻译或者网络传播等,侵权必究。
- 3、本站所有内容均由合作方或网友上传,本站不对文档的完整性、权威性及其观点立场正确性做任何保证或承诺!文档内容仅供研究参考,付费前请自行鉴别。如您付费,意味着您自己接受本站规则且自行承担风险,本站不退款、不进行额外附加服务;查看《如何避免下载的几个坑》。如果您已付费下载过本站文档,您可以点击 这里二次下载。
- 4、如文档侵犯商业秘密、侵犯著作权、侵犯人身权等,请点击“版权申诉”(推荐),也可以打举报电话:400-050-0827(电话支持时间:9:00-18:30)。
查看更多
17 18 5 2 2 7 3 7 7 10 11 8 8 9 9 12 13 14 15 16 16 17 12 13 14 16 Enumeration of Alternative Plans There are two main cases: Single-relation plans (base case) Multiple-relation plans (induction) Single-table queries include selects, projects, and grouping/aggregate operations: Consider each available access path (file scan / index) Choose the one with the least estimated cost Selection/Projection done on the fly Result pipelined into grouping/aggregation Cost Estimates for Single-Relation Plans Index I on primary key matches selection: Cost is Height(I)+1 for a B+ tree. Clustered index I matching one or more selects: (NPages(I)+NPages(R)) * product of RF’s of matching selects. Non-clustered index I matching one or more selects: (NPages(I)+NTuples(R)) * product of RF’s of matching selects. Sequential scan of file: NPages(R). Recall: Must also charge for duplicate elimination if required Example If we have an index on rating: Cardinality = (1/NKeys(I)) * NTuples(R) = (1/10) * 40000 tuples Clustered index: (1/NKeys(I)) * (NPages(I)+NPages(R)) = (1/10) * (50+500) = 55 pages are retrieved. (This is the cost.) Unclustered index: (1/NKeys(I)) * (NPages(I)+NTuples(R)) = (1/10) * (50+40000) = 4005 pages are retrieved. If we have an index on sid: Would have to retrieve all tuples/pages. With a clustered index, the cost is 50+500, with unclustered index, 50+40000. Doing a file scan: We retrieve all file pages (500). SELECT S.sid FROM Sailors S WHERE S.rating=8 Queries Over Multiple Relations A System R heuristic:only left-deep join trees considered. Restricts the search space Left-deep trees allow us to generate all fully pipelined plans. Intermediate results not written to temporary files. Not all left-deep trees are fully pipelined (e.g., SM join). B A C D B A C D C D B A Enumeration of Left-Deep Plans Left-deep plans differ in the order of relations the access method for each relation the join method for each join. Enumerated using N
您可能关注的文档
最近下载
- 初中语文新课标教学设计及反思.pptx
- 就地型馈线自动化选型技术原则(试行).ppt
- 小学生交通安全知识竞赛课件.ppt
- 科技经费管理培训通用课件.ppt
- 激光模拟打靶技术方案.pptx
- 《思想政治教育方法论》PPT课件 2.第八章 课件 第八章《思想政治教育的一般方法》201812.pptx VIP
- 餐饮劳务外包合同(五篇).doc VIP
- 2023年成都信息工程大学数据科学与大数据技术专业《操作系统》科目期末试卷B(有答案).docx VIP
- 中小型企业局域网的规划与设计.doc VIP
- 2024-2025学年新教材高中英语 Unit 3 On the move理解 课文精研读教案 外研版必修第二册.docx
文档评论(0)