Oracle 10g/11g 索引创建-使用-问题分析 (针对编程人员) 赵元杰 中程在线(北京)科技有限公司 2009.8*1内容提要|编程与索引|各类索引适应场合|索引的访问方法|SQL语句有关的索引调整|索引参数与创建索引缓冲|索引问题分析|对开发者的建议|附录:Oracle 索引指标|参考资料Date2Oracle 数据库设计与性能编程与索引|关于索引是什么? | 在列上定义 | 避免全表扫描|索引如何工作或不工作|在insert/update 与 select 之间交替使用|主键自动变为索引rowID (hidden )Banne r IDNameAgeTel1B001Chris20232322B005Bates21232323B003Hugh29239024B010Mark28239025B008Martin33239206B002Jones2232902Banner IDRow IDB0011B0026B0033B0052B0085B0104Date3Oracle 数据库设计与性能|应用系统设计人员： | 按照业务要求建立主键，可形成索引； | 可建立唯一索引。|程序编码人员： | 按照业务查询特点创建各类索引； | 只要有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_POSITION2 from user_ind_columns3* 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 存储示义图:ADAMSADAMSBIRDBIRDBINDERBINDERCHENCHENDUNNEDUNNEEARLEARLFIGEROAFIGEROAGEORGEGEORGEHANLYHANLYJONESJONESKINGKINGLEGRANDLEGRANDLOKERLOKERMITCHELLMITCHELLSANCHEZSANCHEZYAMADAYAMADARoot Block:Root Block:=H=H=D=D=LO=LOBranchBranchBlocks:Blocks:Leaf:Leaf:员工名字: ADAMS, BIRD, BINDER, CHEN, DUNNE, EARL, FIGEROA, GEORGE, HANLY, JONESDate12Oracle 数据库设计与性能1.B*tree 索引-B*tree 存储与使用示义图:Date13Oracle 数据库设计与性能1.B*tree-索引的块叶|每个索引块至少包含一个叶块|每个叶块包含0个或多个数据行|每个数据行一个键值与数据|索引可以是唯一的或非唯一|唯一的和非唯一索引的叶行格式不同Date14Oracle 数据库设计与性能1.B*tree-叶块内部结构20 bytes72 bytes16 bytes16 bytes2 bytes per row4 bytesBlock Common HeaderTransaction HeaderIndex HeaderIndex Leaf HeaderIndex Leaf RowsTailFree SpaceSlot ArrayBlock Size2 bytesDate15Oracle 数据库设计与性能1.B*tree-块的分枝|索引可以包含分枝块|分枝块指向其它的分枝块或叶块|分枝块包含 0 行或多个行|每个行有一个后缀键和指针指向下一个块|压缩行以 0xFE 字节结束Date16Oracle 数据库设计与性能1.B*tree-分枝块结构20 bytesBlock Common HeaderTransaction HeaderIndex HeaderIndex Branch HeaderIndex Branch RowsTailFree SpaceSlot Array48 bytes16 bytes24 bytes2 bytes per row4 bytesBlock Size2 bytesDate17Oracle 数据库设计与性能1.B*Tree-分枝块|每个块有一个指针指向树的后面. 这是头 的部分|一个分枝快包含 N 行指向 N+1 块.SDEUDENENGSCO SPAUSAAUS BEL CANBranch BlocksRoot BlockLeaf BlocksLevel 0Level 1Level 2Date18Oracle 数据库设计与性能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_unqON 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 orderDate22Oracle 数据库设计与性能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) ;Date23Oracle 数据库设计与性能1.B*Tree-唯一索引例子CREATE UNIQUE INDEX test_rsidx ON test(b) REVERSEPCTFREE 30 STORAGE(INITIAL 200K NEXT 200K PCTINCREASE 0 MAXEXTENTS 50) TABLESPACE indx;索引已创建。|练习2： 创建唯一索引，如：Date24Oracle 数据库设计与性能2.Bitmap索引概念r o w 1 2 3 4NameJoe Jane John JamesM_StatusSingle Married Divorced MarriedStatePA CA CA PAGenderM F M MM_Status-IDXSingle Married Divorced State-IDXCA PA Gender-IDX M F1 0 0 0 0 1 0 0 0 0 1 00 1 1 0 1 0 0 11 0 1 1 0 1 0 0 Select count(*) from customers where M_Status = married AND State = CA AND Ge