oracle创建数据⽬录，数据库使⽤-oracle⽬录的创建和分类数据库使⽤-oracle索引的创建和分类
数据库使⽤-oracle索引的创建和分类
索引是数据库中⼀种可选的数据结构，她通常与表或簇相关。⽤户可以在表的⼀列或数列上建⽴索引，以提⾼在此表上执⾏SQL语句的性能。就像本⽂档的索引可以帮助读者快速定位所需信息⼀样，Oracle的索引提供了更为迅速地访问表数据的⽅式。正确地使⽤索引能够显著的减少磁盘I/O。
Oracle提供了多种类型的索引，可以互为补充地提升查询性能：
·平衡树索引(B-tree index)
·平衡树簇索引(B-tree cluster index)
·哈希簇索引(hash cluster index)
·反向键索引(reverse key indexes)
·位图索引(bitmap index)
·位图连接索引(bitmap join index)
·函数索引(function-based index)
⽆论索引是否存在都⽆需对已有的SQL语句进⾏修改。索引只是提供了⼀种快速访问数据的路径，因此她只会影响查询的执⾏速度，但是数据在编辑的过程中，会不断去创建或者更新索引，会让整个系统的磁盘开销⾮常⼤，进⼀步会影响整个系统的运⾏
因此，当我们要创建索引的时候，⼀定要问⾃⼰，这个索引真的有必要创建么？
索引在逻辑上和物理上都与其基表(base table)是相互独⽴的。⽤户可以随时创建(create)或移除(drop)⼀个索引，⽽不会影响其基表或基表上的其他索引。当⽤户移除⼀个索引时，所有的应⽤程序仍然能够继续⼯作，但是数据访问速度有可能会降低。作为⼀种独⽴的数据结构，索引需要占⽤存储空间。
当索引被创建后，对其的维护与使⽤都是Oracle⾃动完成的。当索引所依赖的数据发⽣插⼊，更新，删除等操作时，Oracle会⾃动地将这些数据变化反映到相关的索引中，⽆需⽤户的额外操作。
即便索引的基表中插⼊新的数据，对被索引数据的查询性能基本上能够保持稳定不变。但是，如果在⼀个表上建⽴了过多的索引，将降低其插⼊，更新，及删除的性能。因为Oracle必须同时修改与此表相关的索引信息。
优化器可以使⽤已有的索引来建⽴(build)新的索引。这将加快新索引的建⽴速度。
唯⼀索引和⾮唯⼀索引
索引(index)可以是唯⼀(unique)的或⾮唯⼀(nonunique)的。在⼀个表上建⽴唯⼀索引(unique
index)能够保证此表的索引列(⼀列或多列)不存在重复值。⽽⾮唯⼀索引(nonunique index)并不对索引列值进⾏这样的限制。Oracle建议使⽤CREATE UNIQUEINDEX语句显式地创建唯⼀索引(unique index)。通过主键(primary
key)或唯⼀约束(unique constraint)来创建唯⼀索引不能保证创建新的索引，⽽且⽤这些⽅式创建的索引不能保证为唯⼀索引。
复合索引
复合索引(composite index)(也被称为连结索引(concatenated index))是指创建在⼀个表的多列上的索引。复合索引内的列可以任意排列，她们在数据表中也⽆需相邻。
如果⼀个SELECT语句的WHERE⼦句中引⽤了复合索引(composite index)的全部列(all of the column)或⾃⾸列开始且连续的部分列(leading
portion of thecolumn)，将有助于提⾼此查询的性能。因此，索引定义中列的顺序是很重要的。⼤体上说，经常访问的列(most commonly accessed)或选择性较⼤的列(most
selective)应该放在前⾯。
⼀个常规的(regular)复合索引(composite index)不能超过32列，⽽位图索引(bitmap index)不能超过30列。索引中⼀个键值(key value)的总长度⼤致上不应超过⼀个数据块(data block)总可⽤空间的⼀半。
索引和键
索引(index)与键(key)是不同的概念，但是这两个术语经常被混⽤。索引是在数据库中实际存储的数据结构，⽤户可以使⽤SQL语句对其进⾏创建(create)，修改(alter)，或移除(drop)。索引提供了⼀种快速访问表数据的途径。⽽键只是⼀个逻辑概念。键的概念主要在Oracle的完整性约束(integrity constraint)功能中使⽤，完整性约束⽤于保证数据库中的业务规则(business rule)。
因为Oracle也会使⽤索引(index)来实现某些完整性约束(integrity constraint)，因此索引与键(key)这两个术语经常被混⽤。注意不要将⼆者混淆。
索引和空值
对于⼀个数据表的两⾏或多⾏，如果其索引列(key column)中全部⾮空(non-NULL)的值完全相同(identical)，那么在索引中这些⾏将被认为是相同的；反之，在索引中这些⾏将被认为是不同的。因此使⽤UNIQUE索引可以避免将包含NULL的⾏视为相同的。以上讨论并不包括索引列的列值(column val
ue)全部为NULL的情况。
Oracle不会将索引列(key column)全部为NULL的数据⾏加⼊到索引中。不过位图索引(bitmap
index)是个例外，簇键(cluster key)的列值(column value)全部为NULL时也是例外。
函数索引
如果⼀个函数(function)或表达式(expression)使⽤了⼀个表的⼀列或多列，则⽤户可以依据这些函数或表达式为表建⽴索引，这样的索引被称为函数索引(Function-Based
Index)。函数索引能够计算出函数或表达式的值，并将其保存在索引中。⽤户创建的函数索引既可以是平衡树类型(B-tree index)的，也可以是位图类型(bitmap index)的。
⽤于创建索引的函数可以是⼀个数学表达式(arithmetic expression)，也可以是使⽤了PL/SQL函数(PL/SQL function)，包函数(package function)，C外部调⽤(C callout)，或SQL函数(SQL function)的表达式。⽤于创建索引的函数不能包含任何聚合函数(ggregate function)，如果为⽤户⾃定义函数，则在声明中必须使⽤DETERMINISTIC关键字。如果在⼀个使⽤对象类型(object
type)的列上建⽴函数索引，则可以使⽤此对象的⽅法(method)作为函数，例如此对象的map⽅法。⽤户不能在数据类型为LOB，REF，或嵌套表(nested
table)的列上建⽴函数索引，也不能在包含LOB，REF，或嵌套表等数据类型的对象类型列上建⽴函数索引。
使⽤函数索引
如果⼀个SQL语句的WHERE⼦句中使⽤了函数，那么建⽴相应的函数索引(function-based index)是提⾼数据访问性能的有效机制。表达式(expression)的结果经过计算后将被存储在索引中。但是当执⾏INSERT和UPDATE语句时，Oracle需要进⾏函数运算以便维护索引。
例如，如果⽤户创建了以下函数索引：
CREATE INDEX idx ON table_1 (a + b * (c – 1),a, b);
当Oracle处理如下查询时就可以使⽤之前建⽴的索引：
SELECT a FROM table_1 WHERE a + b * (c – 1)< 100;
使⽤UPPER(column_name)或LOWER(column_name)函数建⽴函数索引(function-based
index)有助于与⼤⼩写⽆关(case-insensitive)的查询。例如创建以下函数索引：
CREATE INDEX uppercase_idx ON employees (UPPER(first_name));
有助于提⾼以下查询的性能：
SELECT * FROM employees WHEREUPPER(first_name) = ‘RICHARD’;
函数索引的优化
⽤户必须为优化器(optimizer)收集关于函数索引(unction-based
index)的统计信息(statistic)。否则处理SQL语句时将不会使⽤此索引。
当⼀个查询的WHERE⼦句中含有表达式(expression)时，优化器可以对函数索引(function-based index)进⾏索引区间扫描(index
range scan)。例如以下查询：
SELECT * FROM t WHERE a + b < 10;
如果使⽤表达式(expression)a+b建⽴的索引，优化器(optimizer)就能够进⾏索引区间扫描(index
range scan)。如果谓词(predicate，即WHERE⼦句)产⽣的选择性(selectivity)较低，则对区间扫描极为有利。此外，如果表达式的结果物化在函数索引内(function-based
index)，优化器将能更准确地估计使⽤此表达式的谓词的选择性。
优化器(optimizer)能够将SQL语句及函数索引(function-based index)中的表达式解析为表达式树(expression tree)并进⾏⽐较，从⽽实现表达式匹配。这个⽐较过程是⼤⼩写⽆关的(case-insensitive)，并将忽略所有空格(blank
space)。
逆序键索引
⽤户可以创建逆序键索引(reverse key index)，此处的逆序指索引列值(index
key value)得各个字节(byte)按倒序排列，⽽⾮索引列(index key)逆序排列。在RAC环境中，使⽤这样的排列⽅式可以避免由于对索引的修改集中在⼀⼩部分叶块(leaf block)上⽽造成的性能下降。通过使索引的键值逆序排列，可以使插⼊操作分布在索引的全部叶块中。
使⽤逆序键索引(reverse key index)后将⽆法对此索引进⾏索引区间扫描(index
range scanning)，因为在逆序键索引中，词汇上(lexically)相邻的索引键(index
key)在存储上未必相邻。因此在逆序键索引上只能进⾏确定键扫描(fetch-by-key scan)或全索引扫描(full-index
scan)。
有些情况下，使⽤逆序键索引(reverse key index)可以令RAC环境下的OLTP应⽤效率更⾼。例如，为⼀个e-mail应⽤中的所有邮件进⾏索引：由于⽤户可能保存旧的邮件，因此索引必须做到既能快速访问最新邮件，也能快速访问旧邮件。
⽤户使⽤REVERSE就可以轻易地创建逆序键索引(reverse
key index)。在CREATE INDEX语句中使⽤REVERSE关键字作为创建索引的选项：
CREATE INDEX i ON t (a,b,c) REVERSE;
⽤户也可以在REBUILD⼦句后添加NOREVERSE关键字将⼀个逆序键索引(reverse
key index)转换为常规的索引：
ALTER INDEX i REBUILD NOREVERSE;
如果REBUILD⼦句后没有使⽤NOREVERSE关键字，那么逆序键索引(reverse
key index)被重建后仍将保持逆序。
位图索引
位图索引也只⼀种⾮常重要的索引，在⼀些特定的环境中，有⾮常好的效果，但是⾮常容易被滥⽤，使⽤前后需要仔细验证看是否达到预期效果。
表分析
有的时候，我们会发现虽然有索引，但是执⾏计划不⾛索引，全表扫描跑的哗哗的，这个时候要查看下这个表的最后的分析时间，如果DBA 没有及时做表分析，那么索引可能⽤不到。
select * from user_tables t ;
到对应的表的last_analyzed时间；如果时间太⽼或者没有时间，那么说明这张表需要做表分析了。
表分析⼀般要在业务性能不太繁忙的时候做，先了解⼀下什么是表分析：
简单的说，就是收集表和索引的信息，CBO根据这些信息决定SQL最佳的执⾏路径。通过对表的分析，可以产⽣⼀些统计信息，通过这些信息oracle的优化程序可以优化。
oracle的联机⽂档描述了analyze的做⽤：oracle游标的使用
Use the ANALYZE statement to collect non-optimizer statistics, forexample, to:
--使⽤analyze功能可以收集⼀些没有进⾏优化的统计信息，例如：
Collect or delete statistics about an index or index partition, table ortable partition,
--聚集或删除索引或索引分区，表或表分区
index-organized table, cluster, or scalar object attribute.
--索引表，串，或游标的⽬标属性等统计信息
Validate the structure of an index or index partition, table or tablepartition, index-organized table,
--增加下列结构的有效性：索引或索引分区，表或表分区，索引表
cluster, or object reference (REF).
--串，⽬标参照
Identify migrated and chained rows of a table or cluster.
--鉴别⼀个表或串中已经被移动的抑或被锁住的列。
表分析的语句参考：
analyze table tableName compute statistics;
平衡树结构的优势
平衡树数据结构(B-tree structure)具有以下优势：
·平衡树(B-tree)内所有叶块(leaf block)的深度相同，因此获取索引内任何位置的数据所需的时间⼤致相同。
·平衡树索引(B-tree index)能够⾃动保持平。
·平衡树内的所有块容量平均在总容量的3/4左右。
·在⼤区间(wide range)范围内进⾏查询时，⽆论匹配个别值(exact match)还是搜索⼀个区间(range
search)，平衡树都能提供较好的查询性能。
·数据插⼊(insert)，更新(update)，及删除(delete)的效率较⾼，且易于维护键值的顺序(key
order)
·⼤型表，⼩型表利⽤平衡树进⾏搜索的效率都较好，且搜索效率不会因数据增长⽽降低。
索引唯⼀扫描
索引唯⼀扫描(index unique scan)是效率最⾼的数据访问⽅式之⼀。从平衡树索引(B-tree
index)中获取数据时将采⽤此种⽅式。当⼀个唯⼀索引(采⽤平衡树结构)的全部列都包含在查询条件中，且查询体条件表达式均为等号(equality)时，优化器将选择使⽤索引唯⼀扫描。
索引区间扫描
当访问选择性较⼤的数据(selective data)时Oracle常进⾏索引区间扫描(index range scan)。扫描区间可以是封闭的(bounded)(两端均封闭)，也可以是不封闭的(unbounded)(⼀端或两端均不封闭)。扫描所返回的数据按照索引列的升序进⾏排列，对于索引值相同的⾏将按ROWID的升序排列。
性能上及存储上的考虑
键压缩(key compression)能够节约⼤量存储空间，因此⽤户可以在⼀个索引块(index
block)内存储更多的索引键(index key)，从⽽减少I/O，提⾼性能。
键压缩(key compression)能够减少索引所需的存储空间，但索引扫描时需要重构(reconstruct)键值(key
value)，因此增加了CPU的负担。此外键压缩也会带来⼀些存储开销，每个前缀(prefix entry)需要4字节(byte)的管理开销。
使⽤键压缩
键压缩(key compression)在多种情况下都能够发挥作⽤，例如：
·对于⾮唯⼀索引(nonunique index)，Oracle会在每个重复的索引键(index key)之后添加rowid以便区分。如果使⽤了键压缩，在⼀个索引块(index block)内，Oracle只需将重复的索引键作为前缀((prefix entry))存储⼀次，并⽤各⾏的rowid作为后缀(suffix entry)。
·唯⼀索引(nonunique index)中也存在相同的情况。例如唯⼀索引(stock_ticker，transaction_time)的含
义是(项⽬，时间戳)，通常数千条记录中stock_ticker的值是相同的，但她们对应的transaction_time值各不相同。使⽤了键压缩后，⼀个索引块中每个stock_ticker值作为前缀只需存储⼀次，⽽各个transaction_time值则作为后缀存储，并引⽤⼀个共享的stock_ticker前缀。
·在⼀个包含VARRAY或NESTED TABLE数据类型(datatype)的索引表(index-organized
table)中，这些collection类型中各个元素(element)的对象标识符(object identifier)是重复的。⽤户可以使⽤键压缩以避免重复存储这些对象标识符。
有些情况⽆法使⽤键压缩(key compression)。例如，⼀个只有⼀个索引键(index
key)的唯⼀索引(unique index)就⽆法使⽤键压缩，因为索引键中不存在可供共享的分组⽚段(grouping piece)。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。