2022年ORACLE索引介绍与高性能SQL优化 .pdf

ORACLE 索引介绍与高性能SQL 优化2007-9-19 来源:不详 作者:佚名点击：481 次什么是索引索引是建立在表的一列或多个列上的辅助对象，目的是加快访问表中的数据；Oracle 存储 索引的 数据结构 是 B*树，位图索引也是如此，只不过是叶子节点不同B*数索引；索引由根节点、分支节点和叶子节点组成，上级索引块包含下级索引块的索引数据，叶节点包含索引数据和确定行实际位置的rowid。使用索引的目的加快查询速度减少 I/O 操作消除磁盘排序何时使用索引查询返回的记录数排序表 40%非排序表 40%，排序表 7%，建议采用并行机制来提高访问速度，DDS；索引访问。最常用的方法，包括索引唯一扫描和索引范围扫描，OLTP；快速完全索引扫描。访问索引中所有数据块，结果相当于全表扫描，可以用索引扫描代替全表扫描，例如：Select serv_id,count(*)from tg_cdr01 group by serv_id;评估全表扫描的合法性如何实现并行扫描。永久并行化（不推荐）alter table customer parallel degree 8;。单个查询并行化select/*+full(emp)parallel(emp,8)*/*from emp;上一页 1 2 3 4 下一页名师资料总结-精品资料欢迎下载-名师精心整理-第 7 页，共 12 页 -分区表效果明显优化 SQL语句排序排序的操作：。order by 子句。group by 子句。select distinct子句。创建索引时。union 或 minus。排序合并连接如何避免排序。添加索引。在索引中使用distinct 子句。避免排序合并连接使用提示进行调整使用提示的原则。语法：/*+hint*/。使用表别名:select/*+index(e dept_idx)*/*from emp e。检验提示常用的提示。rule。all_rows。first_rows。use_nl。use_hash。use_merge。index。index_asc 名师资料总结-精品资料欢迎下载-名师精心整理-第 8 页，共 12 页 -。no_index。index_desc（常用于使用max 内置函数）。index_combine(强制使用位图索引)。index_ffs（索引快速完全扫描）。use_concat(将查询中所有or 条件使用union all)。parallel。noparallel。full。ordered（基于成本）调整表连接表连接的类型。等连接where 条件中用等式连接；。外部连接（左、右连接）在 where 条件子句的等式谓词放置一个(+)来实现，例如：select a.ename,m from emp a,bonus b where a.ename=b.ename(+);该语句返回所有emp 表的记录；。自连接Select a.value total,B.value hard,(A.value-b.value)soft,Round(b.value/a.value)*100,1)perc From v$sysstat a,v$sysstat b Where a.statistic#=179 and B.statistic#=180;反连接反连接常用于not in or not exists中，是指在查询中找到的任何记录都不包含在结果集中的子查询；不建议使用not in or not exists;。半连接查询中使用exists，含义：即使在子查询中返回多条重复的记录，外部查询也只返回一条记录。名师资料总结-精品资料欢迎下载-名师精心整理-第 9 页，共 12 页 -嵌套循环连接。被连接表中存在索引的情况下使用；。使用 use_nl。hash 连接。Hash 连接将驱动表加载在内存中，并使用 hash 技术连接第二个表，提高等连接速度。适合于大表和小表连接；。使用 use_hash。排序合并连接。排序合并连接不使用索引。使用原则：连接表子段中不存在可用索引；查询返回两个表中大部分的数据快；CBO 认为全表扫描比索引扫描执行的更快。使用 use_merge 使用临时/中间表多个大表关联时，可以分别把满足条件的结果集存放到中间表，然后用中间表关联；SQL 子查询的调整关联与非关联子查询。关联：子查询的内部引用的是外部表，每行执行一次；。非关联：子查询只执行一次，存放在内存中。调整 not in 和 not exists语句名师资料总结-精品资料欢迎下载-名师精心整理-第 10 页，共 12 页 -。可以使用外部连接优化not in 子句，例如：select ename from emp where dept_no not in(select dept_no from dept where dept_name=,Math?);改为：select ename from emp,dept where emp.dept_no=dept.dept_no and dept.dept_name is null;使用索引调整SQL Oracle 为什么不使用索引。检查被索引的列或组合索引的首列是否出现在PL/SQL 语句的 WHERE 子句中，这是“执行计划”能用到相关索引的必要条件。看采用了哪种类型的连接方式。ORACLE的共有 Sort Merge Join（SMJ）、Hash Join（HJ）和Nested Loop Join（NL）。在两张表连接，且内表的目标列上建有索引时，只有Nested Loop 才能有效地利用到该索引。SMJ 即使相关列上建有索引，最多只能因索引的存在，避免数据排序过程。HJ 由于须做HASH 运算，索引的存在对数据查询速度几乎没有影响。看连接顺序是否允许使用相关索引。假设表emp 的 deptno 列上有索引，表dept 的列 deptno 上无索引，WHERE 语句有 emp.dep tno=dept.deptno条件。在做 NL 连接时，emp做为外表，先被访问，由于连接机制原因，外表的数据访问方式是全表扫描，emp.deptno上的索引显然是用不上，最多在其上做索引全扫描或索引快速全扫描。上一页 1 2 3 4 下一页。是否用到系统数据字典表或视图。由于系统数据字典表都未被分析过，可能导致极差的“执行计划”。但是不要擅自对数据字典表做分析，否则可能导致死锁，或系统性能下降。索引列是否函数的参数。如是，索引在查询时用不上。是否存在潜在的数据类型转换。如将字符型数据与数值型数据比较，ORACLE会自动名师资料总结-精品资料欢迎下载-名师精心整理-第 11 页，共 12 页 -将字符型用to_number()函数进行转换，从而导致上一种现象的发生。是否为表和相关的索引搜集足够的统计数据。对数据经常有增、删、改的表最好定期对表和索引进行分析，可用 SQL语句“analyze table xxxx compute statistics for all indexes;”。ORACLE 掌握了充分反映实际的统计数据，才有可能做出正确的选择。索引列的选择性不高。我们假设典型情况，有表emp，共有一百万行数据，但其中的 emp.deptno列，数据只有4 种不同的值，如10、20、30、40。虽然 emp 数据行有很多，ORACLE 缺省认定表中列的值是在所有数据行均匀分布的，也就是说每种deptno 值各有 25 万数据行与之对应。假设SQL 搜索 条件 DEPTNO=10，利用 deptno 列上的索引进行数据搜索效率，往往不比全表扫描的高。索引列值是否可为空（NULL）。如果索引列值可以是空值，在SQL 语句中那些要返回 NULL 值的操作，将不会用到索引，如COUNT（*），而是用全表扫描。这是因为索引中存储值不能为全空。看是否有用到并行查询（PQO）。并行查询将不会用到索引。如果从以上几个方面都查不出原因的话，我们只好用采用在语句中加hint 的方式强制 ORACLE 使用最优的“执行计划”。hint采用注释的方式，有行注释和段注释两种方式。如 我 们 想 要 用 到A表 的IND_COL1索 引 的 话，可 采 用 以 下 方式：“SELECT /*+INDEX（A IND_COL1）*/*FROM A WHERE COL1=XXX;如何屏蔽索引语句的执行计划中有不良索引时，可以人为地屏蔽该索引，方法：。数值型：在索引字段上加0，例如select*from emp where emp_no+0=v_emp_no;。字符型：在索引字段上加,?，例如select*from tg_cdr01 where msisdn?=v_msisdn;名师资料总结-精品资料欢迎下载-名师精心整理-第 12 页，共 12 页 -