Oracle数据库SQL查询语句优化方法研究.docVIP

摘 要 数据库自从成为最常用的数据管理方式以后，逐步向数据结构日渐复杂、数据量日渐庞大、应用场合日渐广泛的方向迅速发展和完善。数据库与应用软件的紧密结合使用户对数据库性能的需求逐渐提高，尤其是在海量存储的数据库系统中变得愈加明显。除了软件和硬件条件本身的限制外，数据库中查询语句的执行效率也是影响其性能的关键因素，SQL语句的执行消耗大部分的数据库资源，包括CPU消耗、内存消耗、物理块读取消耗等，因此有必要对SQL语句进行优化调整，对提升数据库性能有重要的意义。 本文分析了Oracle数据库中SQL语句的执行过程和查询机制，研究了SQL语句在Oracle数据库中的处理方式、执行路径和影响执行计划的因素，并作为SQL语句优化规则制定和生效的理论来源。同时结合对语句进行优化的目标得出具体的优化规则，优化规则的内容包括已经存在的优化规则、研究中必威体育精装版提出的优化规则和通常认识中错误的优化规则，对优化的语句样本进行分析统计并总结出规则生效或失效的原因。 关键词：数据库开销；执行计划；SQL优化；优化规则 ABSTRCT Since database become the most useful management way of data, it has developed and perfected rapidly to the direction that the data structure is more and more complex, the amount of data becomes larger and larger and the application becomes wider and wider. The tight relationship and fusion with the application system makes the users improve the requirement of it performance, especially in the database with a mass mount of data. Besides the limit of software and hardware, the efficiency of inquiring statement becomes the key factor in the performance. The running SQL statement consumes most of the resource of database, so it is necessary to adjust it and it makes sense to improve the performance of the database. This paper researches and analyses the execution procedure and mechanism of Oracle database, and learns the way of handling the SQL statement and the factor of affecting the execution plan. Then the paper makes it be the theory source of the optimization rule. The paper puts forward optimization rules of SQL statement based on the optimization target in Oracle database. The rules contain the ones that has been already exist, the new ones raised by this paper and the wrong ones that known by people, at the same time it analyzes the reason of the rule. Key Words: Database performance; Execution plan; SQL optimization; Optimization rule; Optimization advice 目 录 TOC \o 1-3 \h \z \u 第一章 绪论 1 1.1研究背景 1 1.2国内外研究现状 1 1.3研究的内容及意义 3 1.4论文的组织结构 3 第二章 相关知识技术研究 5 2.1结构化查询语言 5 2.2 Oracle命令执行