数据库索引 分区 查询优化.pptVIP

索引 分区 和查询优化 索引(index) 索引是为提高数据检索的性能而建立 索引可建立在一表的一列或多列上 索引分类 逻辑上 单列或组合索引 唯一非唯一索引 注意：组合索引最多可以为32个列，并且，所有列的大小不能超过块的1/3 物理上 分区或非分区 B 树 正常或反向键 位图 DML 操作对索引的影响 插入操作导致在适当的块中插入索引项 删除行只导致逻辑删除索引项，删除的行所占用的空间不能用于新项，直到删除块中的所有项 PCTFREE 对索引没有影响 索引行格式 b-tree 每个索引行有唯一的表示 create unique index iname on tname (a,b,c) create index iname on tname (a,b,c) Label：存储列数和锁定信息 逆向键值索引 Bitmap索引 更适合Bitmap的情况 B 树索引和位图索引 B 树 适合高基数的列 更新关键字列的费用相对较低 使用OR 谓词的查询效率低 对OLTP 有用 位图 适合低基数的列 更新关键字列的费用非常昂贵 使用OR 谓词的查询效率高 对数据仓库有用 OLAP 建立通常的B-Tree 索引 索引和空值 B*Tree索引不存储完全NULL(空)的条目，但是位图索引和聚族索引存储完全NULL(空)的条目。 oracle中除了group by会认为(NULL,NULL)=(NULL,NULL)，其他任何情况下的空值条目比较都视为不同的空值， 即(NULL,NULL) (NULL,NULL) 为了保证真正的唯一性，每个唯一约束（unique index）应该至少有一个NOT NULL字段。 索引和视图 如果基本表中定义了索引，一般情况下还是应该想办法用到那些索引 一些子句说明 INITRANS 指定每块中预先分配的事务项的数目，缺省值和最小值为2 MAXTRANS 限制分配给每块的事务项的数目，缺省值为255 建立B-TREE的例子 建立索引的原则 平衡查询和DML 需求 放在一个单独的表空间内（非 SYSTEM, RBS, TABLE） 使用统一的区大小(经验值： 五块的倍数或表空间MININUM EXTENT 大小的倍数） 考虑对大索引使用NOLOGGING 如果新的关键字值有可能位于当前范围内, 则设置高PCTFREE 建立逆向索引 建立逆向索引的例子 建立Bitmap索引 建立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 创建基于函数的索引 SQL CREATE INDEX TEST_BFI ON TEST(A + B); 索引已创建。 SQL SELECT * FROM TEST WHERE A+B 10; 未选定行 SQL 分配释放索引空间 重建索引 重建索引命令可用来: 将索引移到另一个表空间 改进空间利用率 将反向索引改为通常的 B-tree 索引，或者将B-tree 索引改为反向索引 定期重建索引可以提高效率 重建索引的特点 使用现有索引作为数据源建立新索引 使用现有索引建立索引时无须排序从而使性能更佳 在建立新索引期间需要有足够的空间以容纳新旧索引 在建立新索引的过程中查询可继续使用现有索引 重建索引的语法 ALTER INDEX [schema.] index REBUILD [TABLESPACE tablespace] [ PCTFREE integer ] [ INITRANS integer ] [ MAXTRANS integer ] [ storage-clause ] [ LOGGING | NOLOGGING ] [ REVERSE | NOREVERSE ] 联机创建或者重建索引 可以在最低限度的表锁定下完成重建索引，例如： ALTER INDEX test_bm_idx REBUILD ONLINE; 或者 CREATE INDEX TEST_BT_IDX ON TEST (A,B) ONLINE; 不能在临时表中重建索引 不能重建整个分区索引，必须分别重建各分区 考虑UNIQUE 和PRIMARY KEY约束 手工控制UNIQUE 和PRIMARY KEY的相应索引。 创建大索引 分配一个较大的临时表空间或者创建一个新的大尺寸的临时表空间 用ALTER USER 语句