提高查询性能01

--------------------索引改进性能的程度部分取决于数据的选择性以及表的块之间分布数据的方式。
(1)选择性是指oracle将能够快速查询匹配索引值的rowid的索引，并且可用快速查询少量的相关表块。
(2)数据分布：
    I执行全表扫描时，oracle使用多块读取以快速扫描表
    II索引的读取是单块读取，因此在使用索引时的目标时减少解决查询所需的单个块的数量。

---------------------判断使用索引的价值
方法就是判断索引中的唯一键或不同键的数量。
查询user_index视图的distinct_key列来研究分析结果。比较以下唯一键的数量和表中的行数。
（如user_index视图的num_row列所示）就可以判断索引的选择性，选择性越高，索引返回的行数就越少，该索引就越高。

--------------------两种常见的索引扫描类型是：唯一扫描和范围扫描

(1)在唯一扫描中，数据库知道索引包含一个唯一值列表。在创建索引时，使用create unique index命令可以创建唯一索引。
  在创建主键或unique约束时，oracle将基于指定的值的列自动创建唯一索引。
(2)在范围扫描中，数据库将根据查询标准从索引中返回多个值。
 快速全局扫描：查询中的所有列都被包含在索引中，而索引中的第一列并不在where条件中。
在索引的快速全局扫描期间，oracle读取B树索引上的所有树叶块。这个索引可以按顺序读取。这样就可用一次读取多个块。
初始化参数文件的DB_FILE_MULIBLOCK_READ_COUNT参数可以控制同时被读取的块的数目，相比全表扫描，快速全局扫描通常需要较少的物理I/O，并且允许更快速度地处理查询。
索引跳跃式扫描比全局索引扫描块得多，这是因为它只需要执行很少量的读取。
在引入跳跃式扫描之前，查询只能在where子句中使用索引的第一列时使用索引。跳跃式扫描，组合索引的第一列不在where子句中，会使用跳跃式扫描索引。

------------------索引种类

可以在同一张表上创建位图索引和B树索引。oracle将在查询处理期间动态执行必要的索引转化。
1).B树索引：最多包含32列
2).位图索引：最多包含30列。例如：包含sex列，只有男和女。这个基数只为2.如果用户频繁地根据sex列的值查询该表，这就是位图索引的基列。位图索引适合决策支持系统（DDS）和数据仓库。
          他们不应该用于通过事物处理应用程序访问的表。 对于针对大型的具有非常少不同值的静态数据集查询，位图索引最有效。
3).HASH索引:HASH索引必须使用HASH集群建立集群的同时也定义了集群键，这个键告诉oracle如何在集群中存储表。在存储数据时，所有与这个集群相关的行都被存储在一个数据快上。
          HASH索引可能是访问数据库中数据最快的方法，但它也有缺点，集群键上不同值的数目必须在创建HASH集群之前就要知道，需要在创建HASH集群的时候指定这个值。
          如果频繁地同时查询两个表，则使用集群就是改进性能的有效方法。根据他们的集群键，集群在相同的物理数据中存储来自多个表中的行。
          将列值与确切得值进行比较的查询叫作“等价查询”，散列集群设计用于改进等价查询性能，对范围查询的性能没有任何帮助。
4).索引组织表：索引组织表会把表的存储结构改为B树结构，以表的主键进行排序，由于表的特殊结构。rowid并没有被关联到表的行ID。
             对于总是通过对主键的精确匹配，或范围扫描进行访问的表。就需要考虑使用索引组织表。
           基于主键的update,delete语句的性能也同样会提高，这是因为行在物理上有序。由于键列的值在表和索引中没有重复，存储所需要的空间按也随之减少。
5).反向键索引：当载入一些有序的数据时，索引肯定会碰到与I/O相关的一些瓶颈。在数据载入期间，某部分索引和磁盘肯定会比其他部分使用频繁的多。
             为了解决这个问题，有2种方式.
             I.可以把索引表空间存放在能够把文件物理分割在多个磁盘上的磁盘体系结构上。
            II.oracle还提供了一种反向键索引的方法。不能对位图索引和索引组织表进行反向键处理。
6).基于函数索引：任何在列上执行了函数的查询都不能使用这个列的索引。必须基于成本优化器模式，而且设置以下参数，才可以使用基于函数的索引。
              query_rewrite_enable=true;
              query_rewrite_integrity=trusted(or force)
7).分区索引：本地分区索引可用是B树或位图索引。每个本地索引的分区只包含了它所关联的表分区的键的rowid。
          全局分区索引，只能是B树索引，在创建全局分区索引时，必须定义分区键的范围和值，全局分区索引在一个索引分区中包含来自多个表分区的键。
8).位图连接索引：位图连接索引是基于两个表的连接的位图索引。位图连接索引允许您在两张表的连接列间建立单一索引。
              一张表的rowid和另一张表一起存储。在位图连接索引中，表的rowid将和连接表的索引列一起保存。
               oracle中的位图连接索引更像在两张表间建立一个单一索引。必须为其中一个表建立一个主键，或者唯一约束。
               
create bitmap index emp_dept_idx on emp1 (dept1.deptno) from emp1,dept1 where emp1.deptno=dept1.deptno.
位图连接索引是包含一个连接条件的物化视图的替换形式，存储相关的rowid所需的存储空间可能远小于存储视图本身的结果所需的空间。
             限制：只有一个表可用由不同的事物处理并发地进行更新。在连接中任何表的出现都不能多于一次。不能在一个IOT或临时表上创建位图连接索引。
                  不能使用unique属性来创建位图连接索引。用于索引的连接列必须是主键，或者他们在表中具有唯一的约束，并且该表正要连接到带有位图索引的表。



对于较小的表（记录少于1000行）oracle中基于成本优化器通常会在只查询到1%的记录的情况下，使用索引。返回的行越少，就越需要索引。
create index sale2_idx1 on sale2(cust_id) tablespace rich storage (inital 400m next 10m pctincrease 0)
storage 子句基于表和列的大小，这个表超过2500万行，索引空间大概时461MB。
如果为表空间指定自动段空间管理，则可以让oracle自动关联段的空间从而达到最优性能。
可以执行alter session set sort_area_size=500000000 这样索引创建会更快。


当发现无效索引的查询时，限制索引应该是第一反应应该找出索引对其他查询的影响。
使用alter index命令来初始化监控工作，然后通过对视图v$object_usage的查询实现索引的跟踪。
alter index HRDT_index1 moitoring usage;
select index_name,table_name,monitoring,used,start_monitoring,end_monitoring from v$object_usage.
-----------------限制的因素
使用不等运算符（> < !=）;使用 is null,is not null ,使用函数，比较不匹配的数据类型

-----------------索引空间利用率
(1)下面命令可用作创建索引后的基线，然后周期性运行以查看空间利用是否无效。
analyze index hr.emp_job_idx validate struture;
select pct_used from index_Stats where name='emp_job_idx';
pct_used列表明分配给使用中索引的空间的百分比。

(2)估计索引的空间需求，可以使用dbms_space程序包的create_index_Cost过程来估计索引的空间需求。
输入变量包括执行创建索引的DDL命令以及本地计划表的名称（如果有）
DDL         CREATE INDEX命令
use_bytes    索引数据使用的字节数
alloc_bytes  分配给索引盘区的字节数
plan_table  使用的计划表（默认NULL）

------------------索引对其他的影响
虽然索引通过加速查询而提供了巨大的优点，但它对数据库中的空间利用率具有一定的影响。
1.如果没有完全使用索引，索引占用的空间就可以更好地用于其他某个位置。
2.如果不需索引，也可以节省对索引有影响的插入，更新和删除操作的处理时间。
索引影响查询和数据加载性能，在inset操作期间，行的顺序对加载性能有很很重要的影响。在insert之前对行进行适当的排序都可以将加载性能改进50%。
如果块中没有更多的空间添加新值，则该块不是索引中的最后一个块，块的条目将一分为二，一半的索引条目将保留在原始块中，而另一半移动到新的块中。
结果：性能在加载期间以及查询期间受到损害。
从加载率的角度来看，赞成使用较少的多列索引，而不是使用多个单列的索引。


---------------------------------------------------------提示

确保正确使用提示的最佳方式就是运行explan plan 或设置autotrace为on来查看提示是否被用。使用别名时，提示别名而不是表名。
按照功能分类
--------a:改变执行路径，
当优化器处理特定语句时，提示可用修改相应的执行路径。实例级参数optimizer_mode可以修改数据库中的所有语句，使其遵循特定的执行路径。
但不同执行路径的提示会覆盖原先初始化参数文件指定的任何信息。
1.first_rows提示可用最快速度检索第一行，还可用指定需要利用first_rows来优化的行数，默认1.这个值介于10-1000之间。
   如果很小，成本优化器会生成包括嵌套循环以及索引查找的计划。
   如果很大，成本优化器就会生成由散列连接和全表扫描组成的计划（类似all_rows）
 (1)在使用update ，delete时可忽略first_rows提示，因为查询的所有行都会被更新或删除。
（2）使用分组语句(group by ,distinct,intersect,minus,union)同时也会忽略first_rows提示，因为进行分组时必须检索所有的行。
（3）当语句中有order by子句时，如果索引扫描可以进行实际的排序工作这条语句会选择避免排序。
（4）当索引扫描可用，并且索引处于内部表时，优化器将选择nested loops而不是sortmerge 内部表会缩短连接到查询中外部表的结果集大小指定访问路径会重写该提示。
2.all_Rows提示以最快速度检索出所有行，all_rows提示可能会限制正常条件下索引的应用，指定访问路径的提示会重写这个提示的使用。

-----------b：使用访问方法提示：
访问方法组中的提示允许编码人员改变访问实际的查询方式。
1.full提示对指定的表进行全表扫描，full提示也会得到不可预期的结果，因为使用了不同的驱动表。
2.inedx ,no_index  index提示强制使用一个或多个索引。如果没有指定索引，index提示将不会进行全表扫描，即使没有指定任何索引，优化器也会为这个查询选择最佳的索引。
3.index_join提示可用将同一个表的各个不同索引进行合并，这样只需访问这些索引就行了，节省了重新检索表的时间。
            index_join提示不仅允许您只访问表上的索引，这样可以扫描更少的代码块，并且它比使用索引并通过rowid扫描表要快5倍。
4.index_combine 提示用来指定多个位图索引
5.index_Asc
6.index_Desc
7.index_ffs 执行一次索引的快速全局扫描，这个提示只访问索引而不是对应的表，只有查询需要检索的信息都会在索引上才使用索引的快速全局扫描。index_ffs只会处理索引，它不会处理结果和访问表。

----------c:使用连接操作提示，
提示的连接操作组显示了如何将连接表中的数据合并在一起。
1.order 告诉优化器基于from子句中的表顺序连接这些表，并且使用第一个表作为驱动。order提示时几个功能最强大的提示之一。它按照from子句中列出的先后顺序来处理查询中的表。许多变化都会
改变这种运行方式。oracle版本，表中索引的存在与否以及是否是已分析过表，当多表连接非常慢并且您不知道该做什么时，可以尝试使用order提示。
2.leading 提示可允许您指定驱动查询的表，优化器可判断在使用这个表后该使用那个表。
3.use_merge
4.use_nl
5.use_hash

----------d:其他提示
1.push_subq这个提示的最佳情况时当子查询只快速返回相对少的行时，这些行可用于充分限制外部查询中的行，push_subq可以在尽可能早的时间对子查询进行评估，当使用合并连接或带有远程表的连接时，那就不能使用该提示。
2.praaller 执行全表扫描的查询分成多个部分执行，然后在不同的操作系统进程中处理每个部分
3.append提示可以改进insert提示的性能，append提示不会检查当前是否有插入操作所需的块空间，相反他会直接将数据添加到该新块中
4.cache提示会将全表扫描全部缓存（固定）到内存中，访问同一个表的用户可直接在内存中查找数据，而不用到磁盘中进行查找。
5.hash
6.cursor_sharing_Exact
7.qb_name
8.richs_secret_hint

------------------------------------基于成本的优化器

基于成本的优化器以数据为中心
数据库所采用的优化器模式可以通过初始化参数optimizer_mode来设置。
choose对所有需要分析的表使用基于成本的优化。该模式对于那些结构良好的系统非常好。
oracle 10g启动了自动统计收集，帮助提高基于成本优化器的效率
有效使用基于成本的优化器需要有规则地分析应用程序中的表和索引分析的频率取决于对象中的改动率。

执行dbms_stats程序包的过程收集关于对象的统计。
如果分析表，也自动分析索引，也可以只分析索引，从而加速分析过程。
可以分析模式gather_schema_Stats
execute dbms_Stats.gather_Schema_Stats('practice','compute');
特殊的表gather_table_stats

可以使用analyze命令收集有关数据库对象的统计。基于成本优化器可以使用这些统计表来确定使用的最佳执行路径。
dba_tables,dba_tab_col_Statistics,dba_indexs查看关于表和索引的统计
dba_tab_columns 提供了一些列级别的统计
dba_part_col_statistics选项分析属于practice模式的所有对象。也可以根据指定百分比的表行评估统计。
estimate statistics 该选项将对象的统计基于部分数据的回顾，分心尽可能多的表，可指定进行分析的百分比，一般20%足够。
分析数据可能需大量的排序空间，因为分析也包括完整的表描述，应该在开始分析之前立刻改变会话设置
sort_area_size排序区域越大，则减少有可能需要将临时表空间用于排序段。
db_file_multiblock_read_count 多块读取计数越大，在一次物理读取期间哪呢该读取越多的块。