Oracle数据访问和索引的使用

· 通过全表扫描的方式访问数据；

· 通过ROWID访问数据；

· 通过索引的方式访问数据；

· Oracle顺序读取表中所有的行，并逐条匹配WHERE限定条件。

· 采用多块读的方式进行全表扫描，可以有效提高系统的吞吐量，降低I/O次数。

· 即使创建索引，Oracle也会根据CBO的计算结果，决定是否使用索引。

注意事项：

· 只有全表扫描时才可以使用多块读。该方式下，单个数据块仅访问一次。

· 对于数据量较大的表，不建议使用全表扫描进行访问。

· 当访问表中的数据量超过数据总量的5%—10%时，通常Oracle会采用全表扫描的方式进行访问。

· 并行查询可能会导致优化器选择全表扫描的方式。12ROWID访问表

· Rowid是数据存放在数据库中的物理地址，能够唯一标识表中的一条数据。

· Rowid指出了一条记录所在的数据文件、块号以及行号的位置，因此通过ROWID定位单行数据是最快的方法。

注意事项：

· Rowid作为一个伪列，其数值并不存储在数据库中，当查询时才进行计算。

· Rowid除了在同一集簇中可能不唯一外，每条记录的Rowid唯一。13 INDEX访问表

· 通过索引查找相应数据行的Rowid，再根据Rowid查找表中实际数据的方式称为“索引查找”或者“索引扫描”。

· 一个Rowid对应一条数据行（根据Rowid查找结果，仅需要对Rowid相应数据的数据块进行一次I/O *** 作），因此该方式属于“单块读”。

· 对于索引，除了存储索引的数据外，还保存有该数据对应的Rowid信息。

· 索引扫描分为两步：1）扫描索引确定相应的Rowid信息。 2）根据Rowid从表中获得对应的数据。

注意事项：

· 对于选择性高的数据行，索引的使用会提升查询的性能。但对于DML *** 作，尤其是批量数据的 *** 作，可能会导致性能的降低。

· 全表扫描的效率不一定比索引扫描差，关键看数据在数据块上的具体分布。

索引是关系数据库中用于存放每一条记录的一种对象，主要目的是加快数据的读取速度和完整性检查。建立索引是一项技术性要求高的工作。一般在数据库设计阶段的与数据库结构一道考虑。应用系统的性能直接与索引的合理直接有关。

(1) 单列索引

单列索引是基于单个列所建立的索引。

(2) 复合索引

复合索引是基于两列或是多列的索引，在同一张表上可以有多个索引，但是要求列的组合必须不同。

(1) 重命名索引

(2) 合并索引

（表使用一段时间后在索引中会产生碎片，此时索引效率会降低，可以选择重建索引或者合并索引,合并索引方式更好些，无需额外存储空间，代价较低）

(3) 重建索引

方式一：删除原来的索引，重新建立索引

当不需要时可以将索引删除以释放出硬盘空间。命令如下：

例如：

注：当表结构被删除时，有其相关的所有索引也随之被删除。

方式二： Alter index 索引名称 rebuild;

· 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

· 索引可以大大加快数据的检索速度，这是创建索引的最主要的原因。

· 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

· 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

· 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

· 索引的层次不要超过4层。

· 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

· 除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。

· 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

· 更新数据的时候，系统必须要有额外的时间来同时对索引进行更新，以维持数据和索引的一致性。

1) 不恰当的索引不但于事无补，反而会降低系统性能。因为大量的索引在进行插入、修改和删除 *** 作时比没有索引花费更多的系统时间。

1) 应该建索引的列

· 在经常需要搜索的列上，可以加快搜索的速度；

· 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；

· 在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；

· 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；

· 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；

· 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

2) 不应该建索引的列

· 在大表上建立索引才有意义，小表无意义。

· 对于那些在查询中很少使用或者参考的列不应该创建索引。

· 对于那些只有很少数据值的列也不应该增加索引。比如性别，在查询的结果中，结果集的数据行占了表中数据行的很大比例，。增加索引，并不能明显加快检索速度。

· 对于那些定义为blob数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。

· 当修改性能远远大于检索性能时，不应该创建索引。

一个表中有几百万条数据，对某个字段加了索引，但是查询时性能并没有什么提高，这主要可能是oracle的索引限制造成的。Oracle的索引有一些索引限制，在这些索引限制发生的情况下，即使已经加了索引，oracle还是会执行一次全表扫描，查询的性能不会比不加索引有所提高，反而可能由于数据库维护索引的系统开销造成性能更差。

下面的查询即使在djlx列有索引，查询语句仍然执行一次全表扫描。

把上面的语句改成如下的查询语句，这样，在采用基于规则的优化器而不是基于代价的优化器（更智能）时，将会使用索引。

特别注意：通过把不等于 *** 作符改成OR条件，就可以使用索引，避免全表扫描。

使用IS NULL或IS NOT NULL同样会限制索引的使用。因此在建表时，把需要索引的列设成NOT NULL。如果被索引的列在某些行中存在NULL值，就不会使用这个索引（除非索引是一个位图索引）。

如果不使用基于函数的索引，那么在SQL语句的WHERE子句中对存在索引的列使用函数时，会使优化器忽略掉这些索引。 下面的查询不会使用索引（只要它不是基于函数的索引）

也是比较难于发现的性能问题之一。比如：bdcs_qlr_xz中的zjh是NVARCHAR2类型,在zjh字段上有索引。如果使用下面的语句将执行全表扫描。

因为Oracle会自动把查询语句改为

特别注意：不匹配的数据类型之间比较会让Oracle自动限制索引的使用,即便对这个查询执行Explain Plan也不能让您明白为什么做了一次“全表扫描”。

(1) 索引无效

(2) 索引有效

Oracle数据库的核心是表，表中的列使用到的常见数据类型如下：

对应NUMBER类型的示例：

对于日期类型，可以使用sysdate内置函数可以获取当前的系统日期和时间，返回DATE类型，用systimestamp函数可以返回当前日期、时间和时区。

伪列的数据是由ORACLE进行维护和管理的，用户不能对这个列修改，只能查看。

所有的伪列要得到值必须要显式的指定。

最常用的两个伪列：rownum和rowid。

1、

ROWNUM（行号）：是在查询 *** 作时由ORACLE为每一行记录自动生成的一个编号。

每一次查询ROWNUM都会重新生成。（查询的结果中Oracle给你增加的一个编号，根据结果来重新生成）

rownum永远按照默认的顺序生成。（不受order by的影响）

rownum只能使用 <、 <= ，不能使用 > 、>= 符号，原因是：Oracle是基于行的数据库，行号永远是从1开始，即必须有第一行，才有第二行。

2、

ROWID（记录编号）：是表的伪列，是用来唯一标识表中的一条记录，并且间接给出了表行的物理位置，定位表行最快的方式。

主键:标识唯一的一条业务数据的标识。主键是给业务给用户用的。不是给数据库用的。

记录编号rowid：标识唯一的一条数据的。主要是给数据库用的。类似UUID。

sqlplus 下结束语句的方法有几个，一是查询之类的，输入分号后系统自动执行语句并返回结果，二是输入“/”，意思就是执行。三是输入“go”，意思当然也是执行。

如果要退出command窗口可以输入exit，回车。

你的存储过程当然有错了，你只申明了两个变量，还没有为变量赋值，调用的时候传入的参数是默认值，number是0，varchar是null，即使参数没有错误也不会有正确的返回结果， 在调用存储过程之前要为变量赋值。

一个end对应一个begin或if，while 等

常用的数据库字段类型如下：

字段类型 中文说明 限制条件 其它说明

CHAR 固定长度字符串 最大长度2000 bytes

VARCHAR2 可变长度的字符串 最大长度4000 bytes 可做索引的最大长度749

NCHAR 根据字符集而定的固定长度字符串 最大长度2000 bytes

NVARCHAR2 根据字符集而定的可变长度字符串 最大长度4000 bytes

DATE 日期（日-月-年） DD-MM-YY（HH-MI-SS） 经过严格测试，无千虫问题

LONG 超长字符串 最大长度2G（231-1） 足够存储大部头著作

RAW 固定长度的二进制数据 最大长度2000 bytes 可存放多媒体图象声音等

LONG RAW 可变长度的二进制数据 最大长度2G 同上

BLOB 二进制数据 最大长度4G

CLOB 字符数据 最大长度4G

NCLOB 根据字符集而定的字符数据 最大长度4G

BFILE 存放在数据库外的二进制数据 最大长度4G

ROWID 数据表中记录的唯一行号 10 bytes 格式，为0或1

NROWID 二进制数据表中记录的唯一行号 最大长度4000 bytes

NUMBER(P,S) 数字类型 P为整数位，S为小数位

DECIMAL(P,S) 数字类型 P为整数位，S为小数位

INTEGER 整数类型 小的整数

FLOAT 浮点数类型 NUMBER(38)，双精度

REAL 实数类型 NUMBER(63)，精度更高

数据类型 参数 描述

char(n) n=1 to 2000字节 定长字符串，n字节长，如果不指定长度，缺省为1个字节长（一个汉字为2字节）

varchar2(n) n=1 to 4000字节 可变长的字符串，具体定义时指明最大长度n，这种数据类型可以放数字、字母以及ASCII码字符集(或者EBCDIC等数据库系统接受的字符集标准)中的所有符号。

如果数据长度没有达到最大值n，Oracle 8i会根据数据大小自动调节字段长度，如果你的数据前后有空格，Oracle 8i会自动将其删去。VARCHAR2是最常用的数据类型。可做索引的最大长度3209。

number(m,n) m=1 to 38

n=-84 to 127 可变长的数值列，允许0、正值及负值，m是所有有效数字的位数，n是小数点以后的位数。

如：number(5,2)，则这个字段的最大值是99,999，如果数值超出了位数限制就会被截取多余的位数。

如：number(5,2)，但在一行数据中的这个字段输入575316，则真正保存到字段中的数值是57532。

如：number(3,0)，输入575316，真正保存的数据是575。

date 无 从公元前4712年1月1日到公元4712年12月31日的所有合法日期，Oracle 8i其实在内部是按7个字节来保存日期数据，在定义中还包括小时、分、秒。

缺省格式为DD-MON-YY，如07-11月-00 表示2000年11月7日。

long 无 可变长字符列，最大长度限制是2GB，用于不需要作字符串搜索的长串数据，如果要进行字符搜索就要用varchar2类型。long是一种较老的数据类型，将来会逐渐被BLOB、CLOB、NCLOB等大的对象数据类型所取代。

raw(n) n=1 to 2000 可变长二进制数据，在具体定义字段的时候必须指明最大长度n，Oracle 8i用这种格式来保存较小的图形文件或带格式的文本文件，如Miceosoft Word文档。

raw是一种较老的数据类型，将来会逐渐被BLOB、CLOB、NCLOB等大的对象数据类型所取代。

long raw 无 可变长二进制数据，最大长度是2GB。Oracle 8i用这种格式来保存较大的图形文件或带格式的文本文件，如Miceosoft Word文档，以及音频、视频等非文本文件。

在同一张表中不能同时有long类型和long raw类型，long raw也是一种较老的数据类型，将来会逐渐被BLOB、CLOB、NCLOB等大的对象数据类型所取代。

blob /clob /nclob 无 三种大型对象(LOB)，用来保存较大的图形文件或带格式的文本文件，如Miceosoft Word文档，以及音频、视频等非文本文件，最大长度是4GB。

LOB有几种类型，取决于你使用的字节的类型，Oracle 8i实实在在地将这些数据存储在数据库内部保存。

可以执行读取、存储、写入等特殊 *** 作。

bfile 无 在数据库外部保存的大型二进制对象文件，最大长度是4GB。

这种外部的LOB类型，通过数据库记录变化情况，但是数据的具体保存是在数据库外部进行的。

Oracle 8i可以读取、查询BFILE，但是不能写入。

大小由 *** 作系统决定。

以上就是关于Oracle数据访问和索引的使用全部的内容，包括:Oracle数据访问和索引的使用、oracle 所有的数据类型有哪些、在ORACLE数据库中什么叫伪列等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！