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

《Oracle 10g11g索引创建使用问题分析(针对编程人员).ppt》由会员分享，可在线阅读，更多相关《Oracle 10g11g索引创建使用问题分析(针对编程人员).ppt（91页珍藏版）》请在三一办公上搜索。

1、Oracle 10g/11g索引创建-使用-问题分析(针对编程人员),赵元杰中程在线(北京)科技有限公司2009.8,2023年3月1日星期三,Oracle 数据库设计与性能,2/90,内容提要,编程与索引各类索引适应场合索引的访问方法SQL语句有关的索引调整索引参数与创建索引缓冲索引问题分析对开发者的建议附录:Oracle 索引指标参考资料,2023年3月1日星期三,Oracle 数据库设计与性能,3/90,编程与索引,关于索引是什么?在列上定义避免全表扫描索引如何工作或不工作在insert/update 与 select 之间交替使用主键自动变为索引,2023年3月1日星期三,Oracle

2、 数据库设计与性能,4/90,应用系统设计人员：按照业务要求建立主键，可形成索引；可建立唯一索引。程序编码人员：按照业务查询特点创建各类索引；只要有CREATE INDEX权限即可；在编写查询程序时，要知道所访问表中哪些列已经创建了哪些索引；在编程中运用这些索引才能达到优化的目的。,编程与索引,2023年3月1日星期三,Oracle 数据库设计与性能,5/90,应用系统设计人员：在查询表数据前，要了解锁访问的表有哪些索引；每个索引的列的顺序；在Where 条件中使用索引。索引信息的获得：访问表的索引信息可从数据字典查询；可通过图形界面查看索引的列名词；可通过DBMS_METADATA.GET_

3、DDL导出索引的创建信息。,编程与索引-索引信息,2023年3月1日星期三,Oracle 数据库设计与性能,6/90,从SQL Developer 浏览索引信息：启动SQL Developer,连接到某个用户；选择“Index”了列出索引信息,编程与索引-索引信息,索引列,表名,2023年3月1日星期三,Oracle 数据库设计与性能,7/90,索引有多少？可查询：DBA_CONSTRAINTS 限制性的信息；DBA_INDEXES 表的主键，外部键及创建的索引的信息；DBA_CONS_COLUMNS 表中限制的列信息；DBA_IND_COLUMNS 表中的索引列信息。,DBA_CONSTRA

4、INTS描述约束的信息，包括PRIMARY KET、FOREIGN KEY、UNIQUE KEY、NOT NULL及CHECK。列CONSTRAINT_TYPE的含义如下:C(表中的CHECK约束)P(主键)U(惟一键)R(引用完整性,外键)V(视图中的Check检查)O(视图中的只读=Read only),编程与索引-信息查询,2023年3月1日星期三,Oracle 数据库设计与性能,8/90,查询索引列有关的数据字典：USER_IND_COLUMNS-索引列的次序COLUMN_POSITION；,SQL select table_name,index_name,COLUMN_POSITIO

5、N 2 from user_ind_columns 3*where table_name=EMP;TABLE_NAME INDEX_NAME COLUMN_POSITION-EMP PK_EMP 1,编程与索引-信息查询,2023年3月1日星期三,Oracle 数据库设计与性能,9/90,索引信息查询的必要性：程序开发人员了解访问表的索引列信息，才能在WHERE 条件句使用它。,SQL-查询表所包含的索引名称:SQLselect index_name,table_name from USER_INDEXES WHERE table_name=xxxxx;SQL-使用DBMS_METADATA.

6、GET_DDL包获得索引信息：SQLSELECT(INDEX,index_name)from DUAL;,编程与索引-信息查询,2023年3月1日星期三,Oracle 数据库设计与性能,10/90,内容提要,编程与索引各类索引适应场合索引的访问方法SQL语句有关的索引调整索引参数与创建索引缓冲索引问题分析对开发者的建议参考资料,2023年3月1日星期三,Oracle 数据库设计与性能,11/90,Oracle的各种索引,Oracle可创建下面索引:B-tree索引默认的通用索引B-tree cluster索引为 cluster创建的索引*逆键索引-索引列有规律产生的情景Hash索引-为 has

7、h cluster创建的索引*Bitmap索引-索引列的值变化可枚举的情景IOT表(索引结构表)-独立的以主键查询的表的情景,2023年3月1日星期三,Oracle 数据库设计与性能,12/90,1.B*tree 索引-,B*tree 存储示义图:,ADAMS,BIRD,BINDER,CHEN,DUNNE,EARL,FIGEROA,GEORGE,HANLY,JONES,KING,LEGRAND,LOKER,MITCHELL,SANCHEZ,YAMADA,Root Block:,H,=H,D,=D,LO,=LO,BranchBlocks:,Leaf:,员工名字:ADAMS,BIRD,BINDER

8、,CHEN,DUNNE,EARL,FIGEROA,GEORGE,HANLY,JONES.,2023年3月1日星期三,Oracle 数据库设计与性能,13/90,1.B*tree 索引-,B*tree 存储与使用示义图:,2023年3月1日星期三,Oracle 数据库设计与性能,14/90,1.B*tree-索引的块叶,每个索引块至少包含一个叶块每个叶块包含0个或多个数据行每个数据行一个键值与数据索引可以是唯一的或非唯一唯一的和非唯一索引的叶行格式不同,2023年3月1日星期三,Oracle 数据库设计与性能,15/90,1.B*tree-叶块内部结构,20 bytes,72 bytes,16

9、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 Size,2 bytes,2023年3月1日星期三,Oracle 数据库设计与性能,16/90,1.B*tree-块的分枝,索引可以包含分枝块分枝块指向其它的分枝块或叶块分枝块包含 0 行或多个行每个行有一个后缀键和指针指向下一个块压缩行以 0 xFE 字节结束,2023年3月1日星

10、期三,Oracle 数据库设计与性能,17/90,1.B*tree-分枝块结构,20 bytes,Block 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 Size,2 bytes,2023年3月1日星期三,Oracle 数据库设计与性能,18/90,1.B*Tree-分枝块,每个块有一个指针指向树的后面.这

11、是头的部分一个分枝快包含 N 行指向 N+1 块.,Branch Blocks,Root Block,Leaf Blocks,Level 0,Level 1,Level 2,2023年3月1日星期三,Oracle 数据库设计与性能,19/90,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 INDEXdept_unique_index ON dep

12、t(dname)TABLESPACE indx;,2023年3月1日星期三,Oracle 数据库设计与性能,20/90,1.B*tree-主键(=唯一索引),语法CREATE UNIQUE INDEX dept_name_unq ON department(name);建议加表空间等描述,ALTER TABLE departmentADD CONSTRAINT pk_dept_idPRIMARY KEY(dept_id);,2023年3月1日星期三,Oracle 数据库设计与性能,21/90,1.B*Tree主键与唯一索引,主键要求如果是一个列的话，该列不能为NULL如果多个列构成主键，至少有

13、一个列是非空 唯一索引可以空，但有值必须唯一：,2023年3月1日星期三,Oracle 数据库设计与性能,22/90,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,2023年3月1日星期三,Oracle 数据库设计与

14、性能,23/90,1.B*tree主键与唯一索引,练习1：创建表后加主键与索引：可加表空间存储描述等；,CREATE TABLE dept(dept_id VARCHAR2(5),dept_name VARCHAR2(30)TABLESPACE USERS;Add a PRIMARY KEY constraintAdd a UNIQUE constraint-SELECT constraint_name FROM user_constraints;SELECT index_name,table_name FROM user_indexes;-DROP INDEX(index name);,20

15、23年3月1日星期三,Oracle 数据库设计与性能,24/90,1.B*Tree-唯一索引例子,CREATE UNIQUE INDEX test_rsidxON test(b)REVERSEPCTFREE 30STORAGE(INITIAL 200K NEXT 200KPCTINCREASE 0 MAXEXTENTS 50)TABLESPACE indx;索引已创建。,练习2：创建唯一索引，如：,2023年3月1日星期三,Oracle 数据库设计与性能,25/90,2.Bitmap索引概念,row1234,NameJoeJaneJohnJames,M_StatusSingleMarriedD

16、ivorcedMarried,StatePACACAPA,GenderMFMM,M_Status-IDX,SingleMarriedDivorced,State-IDX,CAPA,Gender-IDX,MF,1 0 0 00 1 0 00 0 1 0,0 1 1 01 0 0 1,1 0 1 10 1 0 0,Select count(*)from customerswhere M_Status=married ANDState=CA AND Gender=M,2023年3月1日星期三,Oracle 数据库设计与性能,26/90,2.Bitmap适合的情况,Bitmap索引特点：当表很大（如数

17、百万行），且关键字列的基数低（如性别列）时，即该列有非常少的独特值例当查询经常使用涉及OR 运算符的多个WHERE 条件组合时当关键字列上存在只读或很少的更新操作时；Bitmap索引空间：比一般的索引占用更大空间；可专门分析存储再设置存储参数；见另外资料；,2023年3月1日星期三,Oracle 数据库设计与性能,27/90,2.Bitmap索引创建,CREATE BITMAP INDEX schema.indexON schema.table(column ASC|DESC,column ASC|DESC.)TABLESPACE tablespace PCTFREE integer INIT

18、RANS integer MAXTRANS integer storage-clause LOGGING|NOLOGGING NOSORT 注意，Bitmap 不能是 unique 的索引,创建BITMAP索引语句的语法加bitmap 关键字即可：,2023年3月1日星期三,Oracle 数据库设计与性能,28/90,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

19、TABLESPACE indx;索引已创建。SQL,创建BITMAP索引例子建议注意存储初始分配：,2023年3月1日星期三,Oracle 数据库设计与性能,29/90,2.Bitmap索引例子-电影级别,#1#2#3#4#5.,*,*,*,*,1,2,4,3,5,Rowid,rating,Star Wars I,2000.Exorcist,1975,.Castaway,2000Ghostbusters,1983Harry Potter,2001.,Table Data,将电影分级：Star Wars-星球大战 5级;Ghostbusters 幽灵为2级；,2023年3月1日星期三,Oracl

20、e 数据库设计与性能,30/90,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);,2023年3月1日星期三,Oracle 数据库设计与性能,31/90,2.Bitmap与B*Tree索引,B*Tree索引：适合高基数的列更新关键字列的费用相对较低使用OR 谓词的查询效

21、率低对OLTP 有用Bitmap索引：适合低基数的列更新关键字列的费用非常昂贵使用OR 谓词的查询效率高对数据仓库有用,2023年3月1日星期三,Oracle 数据库设计与性能,32/90,3.函数索引,函数索引概念：基于函数的索引就是存储预先计算好的函数或表达式值的索引；这些表达式可以是算术运算表达式、SQL或PL/SQL函数、C调用等；创建函数索引主要是依据Where子句来确定，比如：SELECT*FROM T1 WHERE col_a+col_b 10;CREATE INDEX idx ON T1(col_a+col_b);,2023年3月1日星期三,Oracle 数据库设计与性能,33

22、/90,3.函数索引,创建函数索引要求：创建函数索引，要设置下面的参数：QUERY_REWRITE_INTEGRITY=TRUSTEDQUERY_REWRITE_ENABLED=TRUECOMPATIBLE=8.1.0.0.0 及以上版本要具有GLOBAL QUERY REWRITE和CREATE ANY INDEX权限。,2023年3月1日星期三,Oracle 数据库设计与性能,34/90,3.函数索引-例子,一般的权限还不能创建函数索引：创建函数索引,-没有授权就创建函数索引的提示：SQL create index sal_comm on emp(sal+comm)*12,sal,comm

23、)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 privilegesSQL connect sys/sysora816Connected.SQL grant GLOBAL QUERY REWRITE,CREATE ANY INDEX to scott;Grant succeeded.-在连接到scott帐户，创建基于函数的索引：SQL

24、connect scott/tigerora816Connected.SQL create index sal_comm on emp(sal+comm)*12,sal,comm)2 tablespace users storage(initial 64k next 64k pctincrease 0);Index created.,2023年3月1日星期三,Oracle 数据库设计与性能,35/90,4.逆键索引,逆键索引概念：逆键索引也是B*Tree索引的一种，它通过反向键保持索引的所有叶子键上的插入分布；可用逆键索引来避免不平衡的索引。对于逆键索引可以进行下面操作：逆键索引使用：通过在A

25、LTER INDEX命令后加REBUILD NOREVERSE或REBUILD REVERSE子句来使索引边为反向键索引或普通索引；采用范围扫描的查询不能使用反向键索引；位图索引不能反向；索引编排表不能反向。,2023年3月1日星期三,Oracle 数据库设计与性能,36/90,KEY ROWIDEMPNO(BLOCK#ROW#FILE#)-1257 0000000F.0002.00012877 0000000F.0006.00014567 0000000F.0004.00016657 0000000F.0003.00018967 0000000F.0005.00019637 0000000F

26、.0001.00019947 0000000F.0000.0001.,4.逆向键值索引,Index on EMP(EMPNO),EMP table,EMPNO ENAME JOB.-7499 ALLEN SALESMAN7369 SMITH CLERK7521 WARD SALESMAN.7566 JONES MANAGER7654 MARTIN SALESMAN7698 BLAKE MANAGER7782 CLARK MANAGER.,逆键索引示意图：将数据反转后存储：,2023年3月1日星期三,Oracle 数据库设计与性能,37/90,4.逆键索引,逆键索引创建语法：在CREATE IN

27、DEX最后面加 REVERSE 子句：,CREATE UNIQUE INDEX schema.indexON schema.table(column ASC|DESC,column ASC|DESC.)TABLESPACE tablespace PCTFREE integer INITRANS integer MAXTRANS integer storage-clause LOGGING|NOLOGGING REVERSE（注意，这里不能使用 nosort）,2023年3月1日星期三,Oracle 数据库设计与性能,38/90,4.逆键索引-例子,创建逆键索引：创建简单逆键索引,例1：创建一个

28、反向键索引：CREATE INDEX i ON t(a,b,c)REVERSE;例2：使一个索引变为反向键索引：ALTER INDEX i REBUILD NOREVERSE;,2023年3月1日星期三,Oracle 数据库设计与性能,39/90,4.逆键索引-例子,逆键索引用途：大规模的直接数据插入可采用逆键索引；关键字前面一部分变化不大,Oracle会将REVERSE关键字指定给b-tree前把REVERSE字符串简化，逆键关键字索引是好方法。逆键索引可能更平衡，搜索也更快。,2023年3月1日星期三,Oracle 数据库设计与性能,40/90,5.其他类型索引,Hash索引：在CLUST

29、ER上创建的Hash索引 如果创建了Hash Cluster，则也要建立集群键详细参见Cluster与IOTIOT-索引结构表：是一种将表数据和索引数据项存储在一起的对象详细参见Cluster与IOT分区的索引：对大索引按照分区分方法进行分区详细参见Oracle分区设计位图连接索引：在数据仓库中使用详细参见Oracle 数据仓库设计,2023年3月1日星期三,Oracle 数据库设计与性能,41/90,内容提要,编程与索引各类索引适应场合索引的访问方法SQL语句有关的索引调整索引参数与创建索引缓冲索引问题分析对开发者的建议参考资料,2023年3月1日星期三,Oracle 数据库设计与性能,42

30、/90,创建索引主要目的就使用：无论索引是由主键构成或是为了提高查询速度而创建索引，都建议开发人员在Select 语句使用索引；索引由单个列构成，则在WHERE中直接使用；如果索引是由主键构成，要将该索引写在WHERE的更靠左边列判断中少数情况可不用索引：当表的数据量只有几千行时，不用索引可能更快；不用索引可在WHERE子句中不指定索引列即可；可通过提示来避免使用索引。,Where条件中使用索引,2023年3月1日星期三,Oracle 数据库设计与性能,43/90,索引列的使用：WHERE 从句指定索引列；不要再索引做任何的表达式，如,Where条件中使用索引,SQLselect*from e

31、mp WHERE EMPNO=7788;,2023年3月1日星期三,Oracle 数据库设计与性能,44/90,复合索引的选择：多列索引叫复合索引，复合索引有时比单列索引有更好的性能。如果在建立索引时采用了几个列作为索引。则在使用时也要按照建立时的顺序来描述，例如：对于这样的复合索引，下面几种可自动使用索引：,复合索引与Where子句,CREATE INDEX comp_ind ON tab1(x,y,z);这里的复合列变为 x,xy 和xyz 几个部分。如果索引被建立成复合型，则应该在查询语句中带有：where.and 来使用复合键。,SELECT col1,col2,FROM tab1 W

32、HERE 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;,2023年3月1日星期三,Oracle 数据库设计与性能,45/90,复合索引是否被使用：如果column1 is null 或者column1 is not null时，即使在column1列创建索引，优化器也不会用该索引；如果复合索引由两个以上列构成，在Where子句中没有出现第1个列，优化器也不会用该索引，如：,复合索引与Where子句,creat

33、e index skip1 on emp5(job,empno);index created.select count(*)from emp5where empno=7900;Elapsed:00:00:03.13Execution Plan0 SELECT STATEMENT Optimizer=CHOOSE(Cost=4 Card=1 Bytes=5)1 0 SORT(AGGREGATE)2 1 INDEX(FAST FULL SCAN)OF SKIP1(NON-UNIQUE)Statistics6826 consistent gets6819 physical reads,优化器执行的快

34、速全索引扫描,2023年3月1日星期三,Oracle 数据库设计与性能,46/90,复合索引与索引跳跃式扫描：Oracle 9i开始支持对，组合(concatented)索引采用跳跃式扫描，即Where子句中没有出现第1个列，优化器也用该索引，利用前面例子：,复合索引与索引跳跃式扫描,select/*+index(emp5 skip1)*/count(*)from emp5where empno=7900;Elapsed:00:00:00.56Execution Plan0 SELECT STATEMENT Optimizer=CHOOSE(Cost=6 Card=1 Bytes=5)1 0

35、SORT(AGGREGATE)2 1 INDEX(SKIP SCAN)OF SKIP1(NON-UNIQUE)Statistics21 consistent gets17 physical reads,优化器执行:索引跳跃式扫描,2023年3月1日星期三,Oracle 数据库设计与性能,47/90,必要的情况下(如月报数据的统计)可以允许使用全表扫描外，一般都尽量避免涉及全表扫描；下面情况Oracle可能使用全表扫描：所查询的表没有索引；需要返回所有的行；对索引主列有条件限制，但是使用了函数，则Oracle 使用全表扫描，如：where upper(city)=TOKYO;这样的语句不会使用索

36、引方法。所以就只能全表扫描。带有 is null 和is not null 及!=等子句。如：.where city is null;.where city is not null;.where city!=TOKYO;带like 并使用%这样的语句就使用全表扫描；,索引与全表扫描,2023年3月1日星期三,Oracle 数据库设计与性能,48/90,有时，使用索引进行查询可能速度更慢，如果用户希望避免使用类似的访问路径。则采用全表扫描方法；下面是强制优化程序使用全表扫描的方法：提示NO_INDEX能够提供CBO最大的灵活性，并且禁止对某些索引的使用；提示NULL 能够强制优化程序选择全表扫描

37、，而不是索引扫描；提示INDEX,INDEX_COBINE或AND-EQUAL能够强制优化程序使用某种索引，或者利用某种组合索引。,避免使用索引的语句,2023年3月1日星期三,Oracle 数据库设计与性能,49/90,索引谓词,创建索引目的是：提高从数据库表中检索少量数据的速度；预先排序结果集；下面语句不使用索引：,Select*from emp where empno=123;Select*from emp where sal*2 10;Select*from emp where to_char(hiredate,yyyy)=1980;,2023年3月1日星期三,Oracle 数据库设计

38、与性能,50/90,内容提要,编程与索引各类索引适应场合索引的访问方法SQL语句有关的索引调整索引参数与创建索引缓冲索引问题分析对开发者的建议参考资料,2023年3月1日星期三,Oracle 数据库设计与性能,51/90,索引不是由主键构成，可考虑：在有规律的成批的数据DML时，可先删除有关的非主键的索引；成批DML处理完成后还是要重创建索引；提示：不影响业务处理下才能考虑这样的方法。,索引有关的调整,2023年3月1日星期三,Oracle 数据库设计与性能,52/90,重新排序表记录以减少I/O：I/O 是反应时间中最重要的组成部分；Oracle从数据文件读取数据块时，必须等待物理I/O的完

39、成，而物理操作要比数据缓冲区操作慢1400倍；为减少I/O导致的冲突，采用CTAS方法进行重组也是一个办法；与磁盘的平衡原理一样，这样的操作由DBA合理地进行。,索引有关的调整,2023年3月1日星期三,Oracle 数据库设计与性能,53/90,调整索引存储块参数PCTFREE：Pctfree 参数用于控制块，默认10；索引的块pctfree过低-导致表的更新后的索引项数据也更新，可能产生块空间的不足；索引的块pctfree过高-导致空间的浪费；OLTP可设置pctfree 高些，对于DW/OLAP可设置为 0。,索引有关的调整-,2023年3月1日星期三,Oracle 数据库设计与性能,5

40、4/90,内容提要,编程与索引各类索引适应场合索引的访问方法SQL语句有关的索引调整索引参数与创建索引缓冲索引问题分析对开发者的建议参考资料,2023年3月1日星期三,Oracle 数据库设计与性能,55/90,optimizer_index_cost_adj参数：用来调整“基于代价的优化”相对于全表扫描访问代表一个百分比,取值范围在1到10000之间；可理解为Oracle执行多块（MultiBlock）I/O（比如全表扫描）的代价与执行单块（Single-block）I/O代价的相对比例缺省值100表示索引扫描成本等价转换与全表扫描成本若减小，则CBO倾向于使用索引（即单块I/O），反之则倾

41、向于全表扫描（多块I/O）,索引参数-OPTIMIZER_INDEX_COST_ADJ,2023年3月1日星期三,Oracle 数据库设计与性能,56/90,optimizer_index_cost_adj参数调整：多数OLTP系统，OPTIMIZER_INDEX_COST_ADJ可以设置在10到50之间对于数据仓库和DSS系统，可能不能简单的把OPTIMIZER_INDEX_COST_ADJ设置为50，通常我们需要反复调整取得一个合理值.更为具体的可以根据统计信息,db file scattered reads/db file sequential reads来计算,索引参数-OPTIMIZ

42、ER_INDEX_COST_ADJ,2023年3月1日星期三,Oracle 数据库设计与性能,57/90,OPTIMIZER_INDEX_CACHING参数：告诉Oracle你的索引在内存的数据缓冲区中的可能性有多大，即向优化器暗示位于Oracle数据 缓冲区的索引的数量参数设置将会影响到“基于代价的优化”做出的对一个表连接（嵌套循环）使用索引还是使用全表扫描选择参数optimizer_index_caching是一个百分比参数，有效取值范围是0到100,索引参数-OPTIMIZER_INDEX_CACHING,2023年3月1日星期三,Oracle 数据库设计与性能,58/90,OPTIMI

43、ZER_INDEX_CACHING参数调整：这个参数使得我们能够调节“基于代价的SQL优化器”的行为，从而选择最好的方式来访问想要的SQL查询结果：嵌套循环连接哈希连接访问全索引扫描全表扫描访问 optimizer_index_caching设置为较大的百分比，可以使嵌套循环连接在优化器看起来更便宜，从而优化器更有可能选择进行嵌套循环连接而不是哈希连接或者归并排序连接,索引参数-OPTIMIZER_INDEX_CACHING,2023年3月1日星期三,Oracle 数据库设计与性能,59/90,db_file_multiblock_read_count参数：这个参数表示每次从数据文件读取的块数

44、当使用更大的服务器时可设置大些基于CBO，优化器会识别出分散的（多块）读操作的代价或许比识别顺序读操作的代价更小一些。这就使得CBO更加倾向于全表扫描,参数-db_file_multiblock_read_count,2023年3月1日星期三,Oracle 数据库设计与性能,60/90,db_file_multiblock_read_count参数调整：10g版本为自动调整(不设置就表示自动管理)默认为I/O可执行的最大值与平台有关，不能超过缓冲区的10%通常相当于1MB对数据仓库/DSS可能不合适,参数-db_file_multiblock_read_count,2023年3月1日星期三,O

45、racle 数据库设计与性能,61/90,参数-db_file_multiblock_read_count,Oracle R2 版本与这个参数相关的功能有：V$PROCESS_MEMORY 辅助监视自动PGA管理与确定PGA_AGGREGATE_TARGET参数的最佳设置如果没有明确设置DB_FILE_MULTIBLOCK_READ_COUNT参数，则表示为自动调整大小与平台有关，但文档经常说明为1MB,SYSorcl select isdefault,description2 from v$parameter3 where name=db_file_multiblock_read_count

46、;ISDEFAULT DESCRIPTION-FALSE db block to be read each IO,2023年3月1日星期三,Oracle 数据库设计与性能,62/90,参数-skip_unusable_indexes,skip_unusable_indexes参数：在会话中设置，表示要跳过无效的索引 在EXP中表示要跳过无效的索引(不导出)在IMP中表示要跳过无效的索引(不维护)索引为什么无效：当将表从一个表空间迁移到另外新表空间后，原来的索引自动变为无效 可用alter index idxt unusable;命令使索引失效无效的索引的状态在user_indexes.stat

47、us列的值为“UNUSABLE”,2023年3月1日星期三,Oracle 数据库设计与性能,63/90,参数-skip_unusable_indexes,索引无效(UNUSABLE)的处理：可设置skip_unusable_indexes跳过失效的索引这个参数可在系统或会话级上设置，如：,SQL select*from a where id=1;select*from a where id=1*第 1 行出现错误:ORA-01502:索引 TEST.IDX_A_ID 或这类索引的分区处于不可用状态SQL alter system set skip_unusable_indexes=true s

48、cope=memory;系统已更改。SQL select*from a where id=1;,2023年3月1日星期三,Oracle 数据库设计与性能,64/90,参数-skip_unusable_indexes,索引无效处理建议：经常检查关键索引是否失效直接查询dba_indexes的status列是否为“UNUSABLE”即可对于处于无效的索引，建议找时间要重建，而不是一味的跳过，跳过只是一种临时的办法,2023年3月1日星期三,Oracle 数据库设计与性能,65/90,Oracle 9i/10g/11g的变化：从Oracle 9i开始，运行配置多种块大小：创建表空间的块大小为2K,4

49、K,8K,16K和32K并使用相似大小的表和索引来匹配表空间 我们应该理解这些新变化：大数据块的输入输出操作的边缘开销可忽略不计取一个32K大小的数据块的开销仅比取一个2K大小的数据块的开销多1，因为99的磁盘输入输出操作都是用来把磁盘读写头的移动到相应的磁盘柱面和轨道,创建索引缓冲区,2023年3月1日星期三,Oracle 数据库设计与性能,66/90,索引与大块的表空间：当索引被存储在大块表空间中时使用起来更有效。更深层的原因是：B-树可以更好地被平衡并且对顺序索引节点访问而言有更小的磁盘开销著名作家Robin Schumacher的研究表明32K块大小的Oracle索引在执行索引范围扫描

50、时需要更少的逻辑输入输出，并且性能更高,创建索引缓冲区,2023年3月1日星期三,Oracle 数据库设计与性能,67/90,创建索引缓冲：可在数据库处于活动状态创建索引缓冲首先，把所有索引都移至某个单独的表空间，定义成一个单独的数据缓冲区然后将参数optimizer_index_caching设置成正确的值分配一个32K的缓冲区空间alter system set db_32k_cache_size=100m;创建块大小32K的表空间create tablespace index_ts_32k blocksize 32k;,创建索引缓冲区,2023年3月1日星期三,Oracle 数据库设计与