Oracle 10g11g索引创建-使用-问题分析(针对编程人员).ppt

oracle 10g/11g 索引创建-使用-问题分析 (针对编程人员) 赵元杰 中程在线(北京)科技有限公司 2009.8 *1 内容提要 |编程与索引 |各类索引适应场合 |索引的访问方法 |sql语句有关的索引调整 |索引参数与创建索引缓冲 |索引问题分析 |对开发者的建议 |附录:oracle 索引指标 |参考资料 date2oracle 数据库设计与性能 编程与索引 |关于索引是什么? | 在列上定义 | 避免全表扫描 |索引如何工作或不工作 |在insert/update 与 select 之间交替使用 |主键自动变为索引rowid (hidden ) banne r id nameagetel 1b001chris2023232 2b005bates2123232 3b003hugh2923902 4b010mark2823902 5b008martin3323920 6b002jones2232902 banner idrow id b0011 b0026 b0033 b0052 b0085 b0104 date3oracle 数据库设计与性能 |应用系统设计人员： | 按照业务要求建立主键，可形成索引； | 可建立唯一索引。 |程序编码人员： | 按照业务查询特点创建各类索引； | 只要有create index权限即可； | 在编写查询程序时，要知道所访问表中 哪些列已经创建了哪些索引； | 在编程中运用这些索引才能达到优化的 目的。 编程与索引 date4oracle 数据库设计与性能 |应用系统设计人员： | 在查询表数据前，要了解锁访问的表有 哪些索引； | 每个索引的列的顺序； | 在where 条件中使用索引。 |索引信息的获得： | 访问表的索引信息可从数据字典查询； | 可通过图形界面查看索引的列名词； | 可通过dbms_metadata.get_ddl导 出索引的创建信息。 编程与索引-索引信息 date5oracle 数据库设计与性能 |从sql developer 浏览索引信息： | 启动sql developer,连接到某个用户； | 选择“index” 了列出索引信息 编程与索引-索引信息 索引列 表名 date6oracle 数据库设计与性能 |索引有多少？可查询： |dba_constraints 限制性的信息； | dba_indexes 表的主键，外部键及创建的 索引的信息； | dba_cons_columns 表中限制的列信息 ； | dba_ind_columns 表中的索引列信息。 dba_constraints描述约束的信息，包括primary ket、 foreign key、unique key、not null及check。 列constraint_type的含义如下: c (表中的check约束) p (主键) u (惟一键) r (引用完整性,外键) v (视图中的check检查) o (视图中的只读=read only) 编程与索引-信息查询 date7oracle 数据库设计与性能 |查询索引列有关的数据字典： | user_ind_columns -索引列的次 序column_position ； sql select table_name,index_name,column_position 2 from user_ind_columns 3* where table_name=emp; table_name index_name column_position - - - emp pk_emp 1 编程与索引-信息查询 date8oracle 数据库设计与性能 |索引信息查询的必要性： | 程序开发人员了解访问表的索引列信息， 才能在where 条件句使用它。 sql-查询表所包含的索引名称: sqlselect index_name, table_name from user_indexes where table_name=xxxxx; sql- 使用dbms_metadata.get_ddl包获得索引信息： sqlselect ( index,index_name) from dual; 编程与索引-信息查询 date9oracle 数据库设计与性能 内容提要 |编程与索引 |各类索引适应场合 |索引的访问方法 |sql语句有关的索引调整 |索引参数与创建索引缓冲 |索引问题分析 |对开发者的建议 |参考资料 date10oracle 数据库设计与性能 oracle的各种索引 |oracle可创建下面索引: |b-tree索引默认的通用索引 |b-tree cluster索引为 cluster创建的索引* |逆键索引-索引列有规律产生的情景 |hash索引-为 hash cluster创建的索引* |bitmap索引-索引列的值变化可枚举的情景 |iot表(索引结构表)-独立的以主键查询的表的情 景 date11oracle 数据库设计与性能 1.b*tree 索引- b*tree 存储示义图: adamsadams birdbird binderbinder chenchen dunnedunne earlearl figeroafigeroa georgegeorge hanlyhanly jonesjones kingking legrandlegrand lokerloker mitchellmitchell sanchezsanchez yamadayamada root block:root block:=h=h =d=d=lo=lo branchbranch blocks:blocks: leaf:leaf: 员工名字: adams, bird, binder, chen, dunne, earl, figeroa, george, hanly, jones date12oracle 数据库设计与性能 1.b*tree 索引- b*tree 存储与使用示义图: date13oracle 数据库设计与性能 1.b*tree-索引的块叶 |每个索引块至少包含一个叶块 |每个叶块包含0个或多个数据行 |每个数据行一个键值与数据 |索引可以是唯一的或非唯一 |唯一的和非唯一索引的叶行格式不同 date14oracle 数据库设计与性能 1.b*tree-叶块内部结构 20 bytes 72 bytes 16 bytes 16 bytes 2 bytes per row 4 bytes block common header transaction header index header index leaf header index leaf rows tail free space slot array block size2 bytes date15oracle 数据库设计与性能 1.b*tree-块的分枝 |索引可以包含分枝块 |分枝块指向其它的分枝块或叶块 |分枝块包含 0 行或多个行 |每个行有一个后缀键和指针指向下一个块 |压缩行以 0xfe 字节结束 date16oracle 数据库设计与性能 1.b*tree-分枝块结构 20 bytesblock common header transaction header index header index branch header index branch rows tail free space slot array 48 bytes 16 bytes 24 bytes 2 bytes per row 4 bytes block size2 bytes date17oracle 数据库设计与性能 1.b*tree-分枝块 |每个块有一个指针指向树的后面. 这是头 的部分 |一个分枝快包含 n 行指向 n+1 块. s deu denengsco spa usaaus bel can branch blocks root block leaf blockslevel 0 level 1 level 2 date18oracle 数据库设计与性能 1.b*treee-创建 |创建b*tree索引： create index emp_ename on emp(ename) tablespace users storage (initial 5m next 2m pctincrease 0 ) pctfree 0; |创建唯一索引： create unique index dept_unique_index on dept (dname) tablespace indx; date19oracle 数据库设计与性能 1.b*tree-主键(=唯一索引) |语法 create unique index dept_name_unq on department(name) ; | 建议加表空间等描述 alter table department add constraint pk_dept_id primary key (dept_id) ; date20oracle 数据库设计与性能 1.b*tree主键与唯一索引 |主键要求 | 如果是一个列的话，该列不能为null | 如果多个列构成主键，至少有一个列是 非空 |唯一索引 | 可以空，但有值必须唯一： date21oracle 数据库设计与性能 1.b*tree-nosort |如果创建索引时，表的列的值是有规律按照由 小到大排列，则创建索引时不需要对列的值进 行排序，可用 nosort子句描述，如： create index index1 on table1 (column1) nosort |如果列没有按照由小到大排列，则返回错 误: ora-01409: nosort option may not be used; rows are not in ascending order date22oracle 数据库设计与性能 1.b*tree主键与唯一索引 |练习1： | 创建表后加主键与索引： | 可加表空间存储描述等； create table dept ( dept_id varchar2(5), dept_name varchar2(30) ) tablespace users; add a primary key constraint add a unique constraint - select constraint_name from user_constraints ; select index_name, table_name from user_indexes ; - drop index (index name) ; date23oracle 数据库设计与性能 1.b*tree-唯一索引例子 create unique index test_rsidx on test(b) reverse pctfree 30 storage(initial 200k next 200k pctincrease 0 maxextents 50) tablespace indx; 索引已创建。 |练习2： 创建唯一索引，如： date24oracle 数据库设计与性能 2.bitmap索引概念 r o w 1 2 3 4 name joe jane john james m_status single married divorced married state pa ca ca pa gender m f m m m_status-idx single married divorced state-idx ca pa gender-idx m f 1 0 0 0 0 1 0 0 0 0 1 0 0 1 1 0 1 0 0 1 1 0 1 1 0 1 0 0 select count(*) from customers where m_status = married and state = ca and gender = m date25oracle 数据库设计与性能 2.bitmap适合的情况 |bitmap索引特点： |当表很大（如数百万行），且关键字列的基数低（如性别列 ）时，即该列有非常少的独特值例 |当查询经常使用涉及or 运算符的多个where 条件组合时 |当关键字列上存在只读或很少的更新操作时； |bitmap索引空间： |比一般的索引占用更大空间 ； |可专门分析存储再设置存储参数； |见另外资料； date26oracle 数据库设计与性能 2.bitmap索引创建 create bitmap index schema. index on schema. table (column asc | desc , column asc | desc .) tablespace tablespace pctfree integer initrans integer maxtrans integer storage-clause logging| nologging nosort 注意，bitmap 不能是 unique 的索引 |创建bitmap索引语句的语法 |加bitmap 关键字即可： date27oracle 数据库设计与性能 2.bitmap索引的例子 sql create bitmap index test_bm_idx 2 on test(c) 3 pctfree 30 4 storage(initial 200k next 200k 5 pctincrease 0 maxextents 50) 6 tablespace indx; 索引已创建。 sql |创建bitmap索引例子 |建议注意存储初始分配： date28oracle 数据库设计与性能 2.bitmap索引例子-电影级别 #1#1 #2#2 #3#3 #4#4 #5#5 . . * * * * * * * * * * 1 1 2 2 4 4 3 3 5 5 rowidrowid ratingrating star wars i, 2000.star wars i, 2000. exorcist, 1975, .exorcist, 1975, . castaway, 2000castaway, 2000 ghostbusters, 1983ghostbusters, 1983 harry potter, 2001harry potter, 2001 . . table datatable data 将电影分级：将电影分级：star wars-star wars-星球大战星球大战 5 5级级;ghostbusters ;ghostbusters 幽灵幽灵 为为2 2级级； date29oracle 数据库设计与性能 2.bitmap索引 |位图占空间特点： |每个bit 只包含一个位； |如果有 10万部电影，则： 100,000 movies * 5 ratings / movie = 500,000 possible ratings (bits) or (approx.) 50k bytes - create bitmap index movie$bitmap_rating_idx on movie(rating); date30oracle 数据库设计与性能 2.bitmap与b *tree索引 |b*tree索引： |适合高基数的列 |更新关键字列的费用相对较低 |使用or 谓词的查询效率低 |对oltp 有用 |bitmap索引： |适合低基数的列 |更新关键字列的费用非常昂贵 |使用or 谓词的查询效率高 |对数据仓库有用 date31oracle 数据库设计与性能 3.函数索引 |函数索引概念： |基于函数的索引就是存储预先计算好的函 数或表达式值的索引； |这些表达式可以是算术运算表达式、sql 或pl/sql函数、c调用等； |创建函数索引主要是依据where子句来确 定，比如： |select * from t1 where col_a + col_b create index sal_comm on emp ( (sal+comm)*12, sal,comm) 2 tablespace users storage(initial 64k next 64k pctincrease 0); create index sal_comm on emp ( (sal+comm)*12, sal,comm) * error at line 1: ora-01031: insufficient privileges sql connect sys/sysora816 connected. sql grant global query rewrite , create any index to scott; grant succeeded. -在连接到scott帐户，创建基于函数的索引： sql connect scott/tigerora816 connected. sql create index sal_comm on emp ( (sal+comm)*12, sal,comm) 2 tablespace users storage(initial 64k next 64k pctincrease 0); index created. date34oracle 数据库设计与性能 4.逆键索引 |逆键索引概念： |逆键索引也是b*tree索引的一种，它通过反向键保持 索引的所有叶子键上的插入分布； |可用逆键索引来避免不平衡的索引。对于逆键索引可 以进行下面操作： |逆键索引使用： |通过在alter index命令后加rebuild noreverse或rebuild reverse子句来使索引边为反向 键索引或普通索引； |采用范围扫描的查询不能使用反向键索引； |位图索引不能反向； |索引编排表不能反向。 date35oracle 数据库设计与性能 key rowid empno (block# row# file#) - - 1257 0000000f.0002.0001 2877 0000000f.0006.0001 4567 0000000f.0004.0001 6657 0000000f.0003.0001 8967 0000000f.0005.0001 9637 0000000f.0001.0001 9947 0000000f.0000.0001 4.逆向键值索引 index on emp (empno)index on emp (empno) emp tableemp table empno ename job . - - - 7499 allen salesman 7369 smith clerk 7521 ward salesman . 7566 jones manager 7654 martin salesman 7698 blake manager 7782 clark manager |逆键索引示意图： |将数据反转后存储： date36oracle 数据库设计与性能 4.逆键索引 |逆键索引创建语法： |在create index最后面加 reverse 子句： create unique index schema. index on schema. table (column asc | desc , column asc | desc .) tablespace tablespace pctfree integer initrans integer maxtrans integer storage-clause logging| nologging reverse （注意，这里不能使用 nosort） date37oracle 数据库设计与性能 4.逆键索引-例子 |创建逆键索引： |创建简单逆键索引 例1：创建一个反向键索引： create index i on t (a,b,c) reverse; 例2：使一个索引变为反向键索引： alter index i rebuild noreverse; date38oracle 数据库设计与性能 4.逆键索引-例子 |逆键索引用途： |大规模的直接数据插入可采用逆键索引； |关键字前面一部分变化不大,oracle会将reverse 关键字指定给b-tree前把reverse字符串简化，逆键 关键字索引是好方法。 |逆键索引可能更平衡，搜索也更快。 date39oracle 数据库设计与性能 5.其他类型索引 |hash索引： |在cluster上创建的hash索引 |如果创建了hash cluster，则也要建立集群键 |详细参见cluster与iot |iot-索引结构表： |是一种将表数据和索引数据项存储在一起的对象 |详细参见cluster与iot |分区的索引： |对大索引按照分区分方法进行分区 |详细参见oracle分区设计 |位图连接索引： |在数据仓库中使用 |详细参见oracle 数据仓库设计 date40oracle 数据库设计与性能 内容提要 |编程与索引 |各类索引适应场合 |索引的访问方法 |sql语句有关的索引调整 |索引参数与创建索引缓冲 |索引问题分析 |对开发者的建议 |参考资料 date41oracle 数据库设计与性能 |创建索引主要目的就使用： |无论索引是由主键构成或是为了提高查询速度而创建 索引，都建议开发人员在select 语句使用索引 ； |索引由单个列构成，则在where中直接使用； |如果索引是由主键构成，要将该索引写在where的 更靠左边列判断中 |少数情况可不用索引： |当表的数据量只有几千行时，不用索引可能更快； |不用索引可在where子句中不指定索引列即可； |可通过提示来避免使用索引。 where条件中使用索引 date42oracle 数据库设计与性能 |索引列的使用： | where 从句指定索引列； | 不要再索引做任何的表达式，如 where条件中使用索引 sqlselect * from emp where empno=7788; date43oracle 数据库设计与性能 |复合索引的选择： |多列索引叫复合索引，复合索引有时比单列索引有 更好的性能。如果在建立索引时采用了几个列作为索引 。则在使用时也要按照建立时的顺序来描述，例如： |对于这样的复合索引，下面几种可自动使用索引： 复合索引与where子句 create index comp_ind on tab1(x, y, z); 这里的复合列变为 x,xy 和xyz 几个部分。如果索引被建立成复合型， 则应该在查询语句中带有： where . . . and 来使用复合键。 select col1,col2, from tab1 where x=xxx and y=yyy and z=xxx; 或 select col1,col2, from tab1 where x=xxx and y=yyy ; 或 select col1,col2, from tab1 where x=xxx ; date44oracle 数据库设计与性能 |复合索引是否被使用： |如果column1 is null 或者column1 is not null时，即使在 column1列创建索引，优化器也不会用该索引 ； |如果复合索引由两个以上列构成，在where子句中没 有出现第1个列，优化器也不会用该索引 ，如： 复合索引与where子句 create index skip1 on emp5(job,empno); index created. select count(*) from emp5 where empno=7900; elapsed:00:00:03.13 execution plan 0 select statement optimizer=choose(cost=4 card=1 bytes=5) 1 0 sort(aggregate) 2 1 index(fast full scan) of skip1(non-unique) statistics 6826 consistent gets 6819 physical reads 优化器执行的快速 全索引扫描 date45oracle 数据库设计与性能 |复合索引与索引跳跃式扫描： | oracle 9i开始支持对，组合(concatented)索引 采用跳跃式扫描，即where子句中没有出现第1 个列，优化器也用该索引 ，利用前面例子： 复合索引与索引跳跃式扫描 select /*+ index(emp5 skip1) */ count(*) from emp5 where empno=7900; elapsed:00:00:00.56 execution plan 0 select statement optimizer=choose(cost=6 card=1 bytes=5) 1 0 sort(aggregate) 2 1 index( skip scan ) of skip1(non-unique) statistics 21 consistent gets 17 physical reads 优化器执行: 索引跳跃式扫描 date46oracle 数据库设计与性能 |必要的情况下(如月报数据的统计) 可以允许使 用全表扫描外，一般都尽量避免涉及全表扫描； |下面情况oracle可能使用全表扫描： |所查询的表没有索引； |需要返回所有的行； |对索引主列有条件限制，但是使用了函数，则 oracle 使用全表扫描，如：where upper(city)=tokyo; |这样的语句不会使用索引方法。所以就只能全表扫 描。 |带有 is null 和is not null 及 != 等子句。如： |. . . where city is null ; |. . . where city is not null; |. . . where city != tokyo; |带like 并使用%这样的语句就使用全表扫描； 索引与全表扫描 date47oracle 数据库设计与性能 |有时，使用索引进行查询可能速度更慢，如果 用户希望避免使用类似的访问路径。则采用全 表扫描方法； |下面是强制优化程序使用全表扫描的方法 ： |提示no_index能够提供cbo最大的灵活性， 并且禁止对某些索引的使用； |提示null 能够强制优化程序选择全表扫描， 而不是索引扫描； |提示index,index_cobine或and-equal能 够强制优化程序使用某种索引，或者利用某种组合 索引。 避免使用索引的语句 date48oracle 数据库设计与性能 索引谓词 |创建索引目的是： | 提高从数据库表中检索少量数据的速度 ； | 预先排序结果集； | 下面语句不使用索引： select * from emp where empno=123; select * from emp where sal*2 10 ; select * from emp where to_char(hiredate,yyyy)=1980; date49oracle 数据库设计与性能 内容提要 |编程与索引 |各类索引适应场合 |索引的访问方法 |sql语句有关的索引调整 |索引参数与创建索引缓冲 |索引问题分析 |对开发者的建议 |参考资料 date50oracle 数据库设计与性能 |索引不是由主键构成，可考虑： | 在有规律的成批的数据dml时，可先 删除有关的非主键的索引； | 成批dml处理完成后还是要重创建索 引； | 提示：不影响业务处理下才能考虑这样 的方法。 索引有关的调整 date51oracle 数据库设计与性能 |重新排序表记录以减少i/o： | i/o 是反应时间中最重要的组成部分； | oracle从数据文件读取数据块时，必须 等待物理i/o的完成，而物理操作要比数据 缓冲区操作慢1400倍； | 为减少i/o导致的冲突，采用ctas方法 进行重组也是一个办法； | 与磁盘的平衡原理一样，这样的操作由 dba合理地进行。 索引有关的调整 date52oracle 数据库设计与性能 |调整索引存储块参数调整索引存储块参数pctfreepctfree： | pctfree 参数用于控制块，默认10 ； | 索引的块pctfree过低-导致表的更新后的 索引项数据也更新，可能产生块空间的不 足； | 索引的块pctfree过高-导致空间的浪费； | oltp可设置pctfree 高些，对于 dw/olap可设置为 0 。 索引有关的调整- date53oracle 数据库设计与性能 内容提要 |编程与索引 |各类索引适应场合 |索引的访问方法 |sql语句有关的索引调整 |索引参数与创建索引缓冲 |索引问题分析 |对开发者的建议 |参考资料 date54oracle 数据库设计与性能 |optimizer_index_cost_adjoptimizer_index_cost_adj参数：参数： | 用来调整用来调整“ “基于代价的优化基于代价的优化” ”相对于全表相对于全表 扫描访问扫描访问 | 代表一个百分比代表一个百分比, ,取值范围在取值范围在1 1到到1000010000之之 间；可理解为间；可理解为oracleoracle执行多块（执行多块（multiblockmultiblock ） i/oi/o（比如全表扫描）的代价（比如全表扫描）的代价与与执行单块执行单块 （single-blocksingle-block）i/oi/o代价的相对比例代价的相对比例 | 缺省值缺省值100100表示索引扫描成本等价转换与表示索引扫描成本等价转换与 全表扫描成本全表扫描成本 | 若减小，则若减小，则cbocbo倾向于使用索引（即单倾向于使用索引（即单 块块i/oi/o），反之则倾向于全表扫描（多块），反之则倾向于全表扫描（多块i/oi/o ） 索引参数-optimizer_index_cost_adj date55oracle 数据库设计与性能 |optimizer_index_cost_adjoptimizer_index_cost_adj参数调整：参数调整： | 多数多数oltpoltp系统，系统， optimizer_index_cost_adjoptimizer_index_cost_adj可以设置可以设置 在在1010到到5050之间之间 | 对于数据仓库和对于数据仓库和dssdss系统，可能不能简系统，可能不能简 单的把单的把optimizer_index_cost_adjoptimizer_index_cost_adj设设 置为置为5050，通常我们需要反复调整取得一个合，通常我们需要反复调整取得一个合 理值理值. . | 更为具体的可以根据统计信息更为具体的可以根据统计信息,db file ,db file scattered reads/db file sequential readsscattered reads/db file sequential reads来计算来计算 索引参数-optimizer_index_cost_adj date56oracle 数据库设计与性能 |optimizer_index_cachingoptimizer_index_caching参数：参数： | 告诉告诉oracleoracle你的索引在内存的数据缓冲你的索引在内存的数据缓冲 区中的可能性有多大区中的可能性有多大，即向优化器暗示位于，即向优化器暗示位于 oracleoracle数据数据 缓冲区的索引的数量缓冲区的索引的数量 | 参数设置将会影响到参数设置将会影响到“ “基于代价的优化基于代价的优化” ” 做出的对一个表连接（嵌套循环）使用索引做出的对一个表连接（嵌套循环）使用索引 还是使用全表扫描选择还是使用全表扫描选择 | 参数参数optimizer_index_cachingoptimizer_index_caching是一个百分是一个百分 比参数，有效取值范围是比参数，有效取值范围是0 0到到100100 索引参数-optimizer_index_caching date57oracle 数据库设计与性能 |optimizer_index_cachingoptimizer_index_caching参数调整：参数调整： | 这个参数使得我们能够调节这个参数使得我们能够调节“ “基于代价的基于代价的 sqlsql优化器优化器” ”的行为，从而选择最好的方式来访的行为，从而选择最好的方式来访 问想要的问想要的sqlsql查询结果：查询结果： |嵌套循环连接嵌套循环连接 |哈希连接访问哈希连接访问 |全索引扫描全索引扫描 |全表扫描访问全表扫描访问 | optimizer_index_cachingoptimizer_index_caching设置为较大的百分设置为较大的百分 比，可以使嵌套循环连接在优化器看起来更便比，可以使嵌套循环连接在优化器看起来更便 宜，从而优化器更有可能选择进行嵌套循环连宜，从而优化器更有可能选择进行嵌套循环连 接而不是哈希连接或者归并排序连接接而不是哈希连接或者归并排序连接 索引参数-optimizer_index_caching date58oracle 数据库设计与性能 |db_file_multiblock_read_countdb_file_multiblock_read_count参数：参数： | 这个参数表示每次从数据文件读取的块数这个参数表示每次从数据文件读取的块数 | 当使用更大的服务器时可设置大些当使用更大的服务器时可设置大些 | 基于基于cbocbo，优化器会识别出分散的（多块，优化器会识别出分散的（多块 ）读操作的代价或许比识别顺序读操作的代价）读操作的代价或许比识别顺序读操作的代价 更小一些。这就使得更小一些。这就使得cbocbo更加倾向于全表扫描更加倾向于全表扫描 参数-db_file_multiblock_read_count date59oracle 数据库设计与性能 |db_file_multiblock_read_countdb_file_multiblock_read_count参数调整：参数调整： |10g版本为自动调整(不设置就表示自动管理 ) | 默认为i/o可执行的最大值 | 与平台有关，不能超过缓冲区的10% | 通常相当于1mb | 对数据仓库/dss可能不合适 参数-db_file_multiblock_read_count date60oracle 数据库设计与性能 参数-db_file_multiblock_read_count |oracle r2 版本与这个参数相关的功能有： |v$process_memory 辅助监视自动pga管理与确定 pga_aggregate_target参数的最佳设置 |如果没有明确设置 db_file_multiblock_read_count参数，则表示为 自动调整 |大小与平台有关，但文档经常说明为1mb sysorcl select isdefault, descriptionsysorcl select isdefault, description 2 from v$parameter2 from v$parameter 3 where name = db_file_multiblock_read_count;3 where name = db_file_multiblock_read_count; isdefault descriptionisdefault description - - - false db block to be read each iofalse db block to be read each io date61oracle 数据库设计与性能 参数-skip_unusable_indexes |skip_unusable_indexes参数： | 在会话中设置，表示要跳过无效的索引 | 在exp中表示要跳过无效的索引 (不导出) | 在imp中表示要跳过无效的索引 (不维护) |索引为什么无效： | 当将表从一个表空间迁移到另外新表空间 后，原来的索引自动变为无效 | 可用alter index idxt unusable;命令使索引失 效 | 无效的索引的状态在user_indexes. status列 的值为“unusable” date62oracle 数据库设计与性能 参数-skip_unusable_indexes |索引无效(unusable )的处理： | 可设置skip_unusable_indexes跳过失效的索 引 | 这个参数可在系统或会话级上设置，如： sql select * from a where id=1;sql select * from a where id=1; select * from a where id=1select * from a where id=1 * * 第第 1 1 行出现错误行出现错误: : ora-01502: ora-01502: 索引索引 test.idx_a_id test.idx_a_id 或这类索引的分区处于不或这类索引的分区处于不 可用状态可用状态 sql alter system set sql alter system set skip_unusable_indexesskip_unusable_indexes=true =true scope=memory;scope=memory; 系统已更改。系统已更改。 sql select * from a where id=1;sql select * from a where id=1; date63oracle 数据库设计与性能 参数-skip_unusable_indexes |索引无效处理建议： | 经常检查关键索引是否失效 | 直接查询dba_indexes的status列是否为 “unusable”即可 | 对于处于无效的索引，建议找时间要重建 ，而不是一味的跳过，跳过只是一种临时的办 法 date64oracle 数据库设计与性能 |oracle 9i /10g/11goracle 9i /10g/11g的变化：的变化： | 从从oracle 9ioracle 9i开始，运行配置多种块大小：开始，运行配置多种块大小： |创建表空间的块大小为创建表空间的块大小为2k,4k,8k,16k2k,4k,8k,16k和和32k32k |并使用相似大小的表和索引来匹配表空间并使用相似大小的表和索引来匹配表空间 | 我们应该理解这些新变化：我们应该理解这些新变化： |大数据块的输入输出操作的边缘开销可忽略大数据块的输入输出操作的边缘开销可忽略 不计不计 |取一个取一个32k32k大小的数据块的开销仅比取一个大小的数据块的开销仅比取一个 2k2k大小的数据块的开销多大小的数据块的开销多1 1，因为，因为9999的磁盘输入的磁盘输入 输出操作都是用来把磁盘读写头的移动到相应的磁盘输出操作都是用来把磁盘读写头的移动到相应的磁盘 柱面和轨道柱面和轨道 创建索引缓冲区 date65oracle 数据库设计与性能 |索引与大块的表空间：索引与大块的表空间： | 当索引被存储在大块表空间中时使用起来当索引被存储在大块表空间中时使用起来 更有效。更深层的原因是更有效。更深层的原因是： |b-b-树可以更好地被平衡树可以更好地被平衡 |并且对顺序索引节点访问而言有更小的磁盘并且对顺序索引节点访问而言有更小的磁盘 开销开销 | 著名作家著名作家robin schumacherrobin schumacher的研究表明的研究表明32k32k 块大小的块大小的oracleoracle索引在执行索引范围扫描时需索引在执行索引范围扫描时需 要更少的逻辑输入输出，并且性能更高要更少的逻辑输入输出，并且性能更高 创建索引缓冲区 date66oracle 数据库设计与性能 |创建索引缓冲：创建索引缓冲： | 可可在在数据库处于活动状态数据库处于活动状态创建索引缓冲创建索引缓冲 | 首先，把所有索引都移至某个单独的表空首先，把所有索引都移至某个单独的表空 间，定义成一个单独的数据缓冲区间，定义成一个单独的数据缓冲区 | 然后将参数然后将参数optimizer_index_cachingoptimizer_index_caching设置成设置成 正确的值正确的值 | 分配一个分配一个32k32k的缓冲区空间的缓冲区空间 |alter system set db_32k_cache_size = 100m;alter system set db_32k_cache_size = 100m; | 创建块大小创建块大小32k32k的表空间的表空间 |create create tablespacetablespace index_ts_32k index_ts_32k blocksizeblocksize 32k;32k; 创建索引缓冲区 date67oracle 数据库设计与性能 |创建索引缓冲创建索引缓冲( (续续) )： | 把所有索引移至这个把所有索引移至这个32k32k的表空间中的表空间中 |该命令把所有索引移至这个该命令把所有索引移至这个32k32k的表空间而的表空间而 无需中断当