1. 数据访问方式：
Oracle直接访问表中数据的方法有两种：一种是全表扫描；另一种是ROWID扫描
全表扫描：全表扫描(TABLE ACCESS FULL)，从第一个区(EXTENT)的第一个块(BLOCK)开始扫描，读取高水位线(High Water Mark)标记以下所有格式化块，可以过滤行，如允许则执行多块读 DB_FILE_MULTIBLOCK_READ_COUNT，数据量很大时,比索引范围扫描快。数据量越多，全表扫描所需要的时间自然就越多，这里我们需要注意的是直接删了表中数据查询速度并不会变快，因为即使我们删了数据，高位水线并不会改变，也就是同样需要扫描等量的数据块。
ROWID扫描：ROWID扫描（TABLE ACCESS BY ROWID），ROWID是数据存放在数据库中的物理地址，能够唯一标识表中的一条数据。ROWID指出了一条记录所在的数据文件、块号以及行号的位置，因此通过ROWID定位单行数据是最快的方法。ROWID作为一个伪列，其数值并不存储在数据库中，当查询时才进行计算。ROWID除了在同一集簇中可能不唯一外，每条记录的ROWID唯一。
Oracle通过索引（INDEX）访问：通过索引查找相应数据行的rowid，再根据rowid查找表中实际数据的方式称为“索引查找”或者“索引扫描”。一个rowid对应一条数据行（根据rowid查找结果，仅需要对rowid相应数据的数据块进行一次I/O操作），因此该方式属于“单块读”。索引本身除了存储索引的数据外，还保存有该数据对应的rowid信息。
2. 索引概述：
如果我们把数据库中的表当作一本书籍，那么索引就是这本书籍的目录，通过目录我们就能快速访问到我们所需要的信息。Oracle索引就是类似书籍目录的数据结构，是一种与表或表簇相关联的可选结构，在表中的一个或多个列上创建索引能够快速地从随机分布的表行中检索出所需要的行，从而来提高数据访问速度，索引是减少磁盘I/O的许多手段之一。
索引是一种模式对象，它在逻辑上和物理上都与其相关联的对象中的数据保持独立。因此，可以删除或创建索引而不会实际影响相关的表。如果删除一个索引，应用程序将仍然可以工作，不过，访问之前索引过数据可能变慢。在创建索引后，数据库会自动维护并使用它们。数据库还会自动反映对相关表数据的更改，如添加、 更新、 删除行等，用户不需要对所有相关索引做任何操作，即使在插入行时，被索引数据的检索性能仍然几乎不变。但是，在表上存在过多的索引，会降低DML性能，因为数据库还必须更新索引。我们在建表时主键和唯一键会自动生成索引，必要的时候还可以在外键上创建索引。
3. 索引类型：
复合索引：复合索引，也称为连接索引，是在某个表中的多个列上的索引。复合索引中的列应该以在检索数据的查询中最有意义的顺序出现，但在表中不必是相邻的。若WHERE子句引用了复合索引中的所有列或前导列，复合索引可以加快
SELECT语句的数据检索速度。所以，在定义中所使用的列顺序很重要。一般地，最常被访问的列放在前面，在某些业务场景中，表中个别几个字段访问频率较高，这时就可以为这几个字段单独建立一个组合索引，加快访问的速度减少磁盘开销，如下是创建复合索引的SQL：CREATE INDEX employees_ix ON employees (last_name, job_id, salary);
注意
如果查询会访问所有的三个列，或仅 last_name 列，或仅 last_name 和job_id 列，则会使用此索引，不访问 last_name 列的查询，刚不会使用索引。
最左侧列匹配：假如我们设计的索引是INDEX(class_name, student_name, course_name)，查询的SQL语句where里不一定要根据三个字段来查询，只要最左侧的部分字段来查，就可以了
比如：select * from student_score where class_name='xx' and student_name='xx'，只有class_name和student_name可以在索引里搜索，就可以查询某个学生所有科目的成绩。
但如果你SQL是select * from where course_name='xx’，那就不能走索引了，因为B+树里必须先按class_name查找，再按student_name查找，不能跳过前面两个字段。
最左侧匹配规则：假如你要用like语法来查，比如select * from student_socre where class_name like '1%'，查找所有1开头的班级的分数，那么也是可以用索引的。
因为你的联合索引的B+树里，是按照class_name排序的，所以你要是给出class_name的最左前缀就是1，然后后面的给一个模糊匹配符号，那也是可以基于索引来查找的。
但如果你where条件是class_name like '%班'，左侧是模糊查询，就没法用索引了。
范围查找：假如你查询的SQL是：select * from student_score where class_name>'1班’ and class_name<'5班'，查找几个班的分数。也是可以用到索引的。
因为我们的索引的最下层的数据页都是按顺序组成双向链表的，所以完全可以先找到'1班'对应的数据页，再找到'5班'对应的数据页，两个数据页中间的那些数据页，就全都是在你范围内的数据了！
唯一索引和非唯一索引：索引可以是唯一的或非唯一的。唯一索引保证在表的键列或键列集上没有具有重复的值的行。在一个唯一索引中，对每个数据值都存在一个 rowid。叶块中的数据仅根据键排序。非唯一索引允许在索引的列或列集中有重复的值。对于非唯一索引，rowid被包含在键中且已排序，因此非唯一索引按索引键和rowid（升序）进行排序。
B-树索引：B-树索引类似平衡树也被称为平衡树索引，简称B-树，是最常见的数据库索引类型。一个 B-树索引是被划分为多个范围的已排序的值列表。通过将键与一行或行范围关联起来，B-树索引可以对多种类型的查询提供优秀的检索性能，包括精确匹配和范围搜索等。B-树索引包括很多扩展类型，如索引组织表、反键索引、降序索引、B-树簇索引
注意
如果需要的行数只占表的总行数的一小部分，而且表很大，则B-树是十分有效的检索行的方式。
但如果WHERE子句涉及很广泛的范围，甚至包括表中每一行，例如
select count(*) from employees where last_name between 'A%' and 'Z%'
那么，与全表扫描相比，速度将慢很多。一般认为，如果查询要检索超过2%-4%的行，则全表扫描更快。
总结，如果遇到以下情况，应该使用B-树索引：
1、列的基数（不同值的个数）很大
2、表的行数很多
3、列用在where子句或join条件中
位图索引和位图联接索引：在位图索引中， 数据库为每个索引键存储一个位图。在传统的 B-树索引中， 一个索引条目指向单个行。在位图索引中，每个索引键存储指向多个行的指针。位图索引主要用于数据仓库，或在以特定方式引用很多列的查询环境中。
注意
一般而言，在具备以下条件时使用位图索引：
1、列的基数（不同值的个数）小
2、表的行数很多
3、列用于布尔代数运算
基于函数的索引：基于函数、 或涉及相关表的一个或多个列的表达式来创建索引。基于函数的索引计算函数或涉及一个或多个列的表达式的值，并将其存储在索引中。基于函数的索引可以是一个 B-树索引或位图索引。用于生成索引的函数可以是算术表达式，或一个包含SQL函数、 用户定义PL/SQL函数、包函数，或C调用的表达式。
应用程序域索引：应用程序域索引是一个特定于应用程序的自定义索引。Oracle数据库提供了可扩展的索引，用户可以在自定义的、 复杂的数据类型（如文档、 空间数据、 图像、和视频剪辑等）之上建立索引，也可使用专门的索引技术。
4. 索引聚簇因子：
Oracle聚簇因子是指按照索引键值排序的索引行和存储于对应表中数据行的存储顺序和相似度，索引聚簇因子用于测量相对于某个索引值的行顺序，被索引值的行存储得越有序，则聚簇因子越低，如果聚簇因子的值接近对应表的表块的数量，则说明目标索引行和存储于对应表中数据行的存储顺序相似程度非常高，反之则相似程度低，作为一种粗略测量通过索引读取整个表所需的 I/O 数，聚簇因子非常有用，如果聚簇因子较高，则在大型索引范围扫描过程中，数据库将执行相对较高数目的 I/O。索引条目指向随机表块，因此数据库可能必须一遍又一遍地来回重读索引所指向的同一数据块。Oracle按照如下的算法来计算聚簇因子的值：
聚簇因子的初始值为1。
定位到目标索引处于最左边的叶子块。
从最左边的叶子块的第一个索引键值所在的索引行开始顺序扫描，在顺序扫描的过程中，Oracle会比对当前索引行的rowid和它之前的那个索引行(它们是相邻的关系)的rowid，如果这两个rowid并不是指向同一个表块，那么Oracle就将聚簇因子的当前值递增1；如果这两个rowid是指向同一个表块，Oracle就不改变聚簇因子的当前值。注意，这里Oracle在比对rowid时不需要回表去访问相应的表块。
上述比对rowid的过程会一直持续下去，直到顺序扫描完目标索引所有叶子块里的所有索引行。
上述顺序扫描操作完成后，聚簇因子的当前值就是索引统计信息中的CLUSTERING_FACTOR，Oracle会将其存储在数据字典里。
SELECT INDEX_NAME, CLUSTERING_FACTOR FROM ALL_INDEXES;可以查询索引聚簇因子相关信息
5. SQL使用索引的条件：
当字段上建有索引时，通常会在以下情况中使用到索引：
INDEX_COLUMN = ?
INDEX_COLUMN > ?
INDEX_COLUMN >= ?
INDEX_COLUMN < ?
INDEX_COLUMN <= ?
INDEX_COLUMN BETWEEN ? AND ?
INDEX_COLUMN IN (?……?)
INDEX_COLUMN LIKE ‘?%’【如’123%‘才会使用到索引，’%123’不会使用索引】
T1.INDEX_COLUMN1 = T2.INDEX_COLUMN2 【两个表通过索引字段关联】
当字段上建有索引时，在以下情况中并不会使用到索引：
INDEX_COLUMN <> ? 【不等于操作不能使用索引】
INDEX_COLUMN NOT IN (?……?)【不等于操作不能使用索引】
function(INDEX_COLUMN) = ?【经过函数运算后的索引字段不能使用索引】
INDEX_COLUMN + 1 = ?【经过普通运算后的索引字段不能使用索引】
INDEX_COLUMN ||‘a’ = ?【经过普通运算后的索引字段不能使用索引】
INDEX_COLUMN LIKE ‘%?’||?【如’123%‘才会使用到索引，’%123’不会使用索引】
INDEX_COLUMN IS NULL 【B-树索引不包含字段为NULL值的记录，因此IS NULL不能使用索引，但位图索引中NULL可以被索引查询】
NUMBER_INDEX_COLUMN = ‘12345’ 【Oracle在做数值比较时，需要左右两边的数据类型一致，当两边数据类型不同时会对字段值做隐式转换，相当于做了函数处理，所以不能使用索引】
CHAR_INDEX_COLUMN = 12345 【同上】
T1.INDEX_COLUMN = T1.COLUMN1 【给索引查询的数据应是已知数据，不能是未知字段值】
6. 索引组织表：
索引组织表(index organized table, IOT)就是存储在一个索引结构中的表。存储在堆中的表是无组织的(也就是说，只要有可用的空间，数据可以放在任何地方)，IOT中的数据则按主键存储和排序。对你的应用来说，IOT表和一个“常规”表并无二致。索引组织表的数据按主键排序手段被存储在B-树索引中，除了存储主键列值外还存储非键列的值。普通索引只存储索引列，而索引组织表则存储表的所有列的值。
评论
0 点赞