Oracle 索引基础

合集下载

oracle_primary用法_概述及解释说明

oracle primary用法概述及解释说明1. 引言1.1 概述在关系型数据库中，Primary Key（主键）是一种重要的数据库约束，用于唯一标识一个表中的每一行数据。

它是数据库设计中非常重要的概念之一，对于保障数据完整性和提高查询效率都具有很大作用。

本文将详细介绍Oracle数据库中Primary Key的用法及其相关解释说明。

1.2 文章结构本文由以下几个部分组成。

首先是引言部分，对文章进行概述，并介绍文章的结构。

接下来是Oracle Primary用法部分, 介绍了Primary Key的基本定义、作用以及如何定义和使用Primary Key。

然后是Primary Key的特点和限制部分, 包括唯一性约束、非空约束以及自动递增特性等。

随后是如何选择和设计Primary Key部分, 讨论根据业务需求选择合适的Primary Key类型、设计复合主键时需要考虑的因素以及索引对Primary Key性能的影响等方面内容。

最后是结论部分，总结了Primary Key在数据库设计中的重要性与应用价值，并给出了正确使用Primary Key提高数据完整性和查询效率的建议，并展望了未来Database设计发展趋势。

1.3 目的本文旨在全面介绍Oracle Primary Key的用法，帮助读者深入了解Primary Key 的概念、作用和限制，以及如何正确选择和设计Primary Key。

通过本文的阅读，读者将能够更好地理解Primary Key在数据库设计中的重要性，并能够合理运用它来提高数据库的性能和数据完整性。

此外，本文还对未来Database设计发展趋势进行了展望，为读者提供思考与探索的方向。

2. Oracle Primary用法：2.1 什么是Primary KeyPrimary Key（主键）是数据库表中的一列或一组列，其具有唯一性和非空性的特点。

每个数据库表都应该有一个Primary Key，它用于唯一标识表中的每行数据。

ORACLE数据库基础知识总结

ORACLE数据库基础知识总结1、RMAN全备备份⽂件的顺序备份归档⽇志、所有的数据⽂件、控制⽂件、spfile、再次备份归档⽇志2、redo⽇志丢失恢复redo⽇志的三种状态是current、active、inactiveinactive,可以重建 clear logactive、current不能变成inactive，只能通过不完全恢复进⾏恢复，然后重建⽇志⽂件3、⼝令⽂件丢失恢复丢失可重建 orapwd file= password= enfries=重建完成之后ORACLE正常使⽤4、控制⽂件丢失恢复a> rman 可以备份控制⽂件b> 控制⽂件可以cp⼀份备⽤c> 控制⽂件可以重建⼿写5、体系结构物理：ORACLE数据库包括instance、database两部分。

instance包括SGA（系统全局区）跟⼀些后台进程组成的。

SGA包括：share pool、db buffer cache、redo log buffer、流池、⼤型池、JAVA POOL、share pool(共享池) ：库缓存：缓存最近执⾏的代码，同样的sql多次执⾏不需要频繁读取数据字典中得数据数据字典缓存：存储oracle中得对象定义PL/SQL区：缓存存储过程、函数触发器等数据库对象。

db buffer cache(数据库缓存区)redo log buffercache（⽇志缓存区）常见的后台进程：DBWn：⽤于数据库缓存写⼊磁盘LGWn：⽤于log⽇志写⼊磁盘CKPT：检查点进程SMON：实例维护进程，系统监视器MMON：AWR主要进程PMON：维护⽤户进程，进程监视器ARCN：归档进程database包括数据⽂件、控制⽂件、⽇志⽂件等。

逻辑：oracle数据块-区-段-表空间-数据库-⽅案多个oracle数据块组成⼀个区，多个区组成⼀个段，多个段组成⼀个表空间，多个表空间组成⼀个数据库表空间和数据⽂件的关系：表空间是由⼀个或多个数据⽂件组成的，⼀个数据⽂件只属于⼀个表空间，表空间的⼤⼩是所有数据⽂件⼤⼩的总和。

oracle重建分区索引语句

oracle重建分区索引语句摘要：1.Oracle 分区索引简介2.重建分区索引的原因3.重建分区索引的步骤4.示例：使用SQL 语句重建分区索引正文：一、Oracle 分区索引简介Oracle 分区索引是一种在分区表上的索引，它可以提高查询效率。

分区索引与普通索引类似，但它是基于分区表的，因此具有更高的查询性能。

当数据量较大时，分区索引能够有效地减少查询数据的范围，提高查询速度。

二、重建分区索引的原因重建分区索引通常有以下原因：1.索引损坏：当分区索引损坏时，需要进行重建。

2.数据表分区改变：当数据表的分区发生变化时，需要重建分区索引以适应新的分区结构。

3.优化查询性能：在某些情况下，重建分区索引可以提高查询性能。

三、重建分区索引的步骤1.备份数据：在进行分区索引重建之前，需要备份数据以防止数据丢失。

2.使用ALTER INDEX 命令：使用ALTER INDEX 命令可以重建分区索引。

需要指定要重建的索引名称以及分区信息。

3.检查重建结果：重建完成后，需要检查重建结果以确保索引正确无误。

四、示例：使用SQL 语句重建分区索引以下是一个使用SQL 语句重建分区索引的示例：```sqlALTER INDEX index_nameREBUILD PARTITION p_number;```其中，`index_name`是要重建的分区索引名称，`p_number`是要重建的分区编号。

可以根据实际情况修改相应的参数值。

通过以上步骤和示例，可以完成Oracle 分区索引的重建工作。

在进行分区索引重建时，需要谨慎操作，确保数据安全。

oracle 查索引的聚簇因子语句

oracle 查索引的聚簇因子语句【原创版】目录1.Oracle 数据库简介2.索引和聚簇因子的概念3.Oracle 中查询索引的聚簇因子的方法4.实际操作示例正文【1.Oracle 数据库简介】Oracle 数据库是当前业界最流行的关系数据库管理系统之一，它以其高性能、安全性和可扩展性而闻名。

Oracle 数据库支持多种数据类型和复杂数学运算，适用于各种行业和领域。

在 Oracle 数据库中，为了提高查询效率，通常会对数据表建立索引。

【2.索引和聚簇因子的概念】索引是一种数据库对象，它可以帮助数据库快速定位到所需的数据行。

通过建立索引，可以大大减少查询数据的时间。

在 Oracle 数据库中，索引分为 B 树索引、位图索引等类型。

聚簇因子是指在 Oracle 数据库中，一个表的数据行在物理存储上是按照什么顺序进行存储的。

聚簇因子通常是一个或多个列，它可以影响表的查询性能。

了解聚簇因子对于优化查询语句具有重要意义。

【3.Oracle 中查询索引的聚簇因子的方法】在 Oracle 数据库中，可以使用如下 SQL 语句查询某个索引的聚簇因子：```sqlSELECT INDEX_NAME, COLUMN_NAME, ORDERINGFROM INDEXESWHERE INDEX_NAME = "索引名称";```其中，`索引名称`需要替换为实际要查询的索引名称。

查询结果会返回索引名、聚簇因子列名以及聚簇顺序（ASC 表示升序，DESC 表示降序）。

【4.实际操作示例】假设有一个名为`employees`的表，其中有一个名为`id`的列，现在要查询`id`列的聚簇因子。

可以使用如下 SQL 语句：```sqlSELECT INDEX_NAME, COLUMN_NAME, ORDERINGFROM INDEXESWHERE INDEX_NAME = "IDX_employees_id";```查询结果如下：```INDEX_NAME | COLUMN_NAME | ORDERING------------ | ----------- | ----------IDX_employees_id | id | ASC```从结果中可以看出，`id`列的聚簇因子是按照升序（ASC）顺序存储的。

oracle 查询索引语句

oracle 查询索引语句一、查询索引定义1. 查询Oracle数据库中所有的索引```SELECT index_name, table_name FROM all_indexes;```2. 查询指定表中的所有索引```SELECT index_name FROM all_indexes WHERE table_name = '表名';```3. 查询指定索引的定义```SELECT index_name, table_name, column_name FROM all_ind_columns WHERE index_name = '索引名';```4. 查询索引的类型```SELECT index_name, table_name, index_type FROM all_indexes;```5. 查询索引的存储方式```SELECT index_name, table_name, index_type, index_subpartition_name, index_partition_name FROM all_indexes;```二、查询索引状态与统计信息1. 查询索引的状态（有效/无效）```SELECT index_name, status FROM all_indexes;```2. 查询索引的使用情况（最后一次访问时间、读取次数等）```SELECT index_name, last_analyzed, num_rows, leaf_blocks, distinct_keys, clustering_factor FROM all_indexes;```3. 查询索引的大小```SELECT index_name, table_name, index_type, ROUND(bytes/1024/1024, 2) AS size_mb FROM dba_segments WHERE segment_type = 'INDEX';```4. 查询索引的碎片化情况```SELECT index_name, table_name, index_type, blevel, leaf_blocks, distinct_keys, clustering_factor FROM all_indexes; ```5. 查询索引的使用情况（是否被频繁访问）```SELECT index_name, table_name, user_reads, user_updates FROM all_indexes;```三、查询索引的相关约束1. 查询索引所属的表的主键约束```SELECT index_name, table_name FROM all_indexes WHERE index_type = 'NORMAL' AND uniqueness = 'UNIQUE';```2. 查询索引所属的表的外键约束```SELECT index_name, table_name FROM all_indexes WHERE index_type = 'NORMAL' AND uniqueness = 'NONUNIQUE';```3. 查询索引所属的表的唯一约束```SELECT index_name, table_name FROM all_indexes WHERE index_type = 'NORMAL' AND uniqueness = 'NONUNIQUE';```4. 查询索引所属的表的检查约束```SELECT index_name, table_name FROM all_indexes WHERE index_type = 'NORMAL' AND uniqueness = 'NONUNIQUE';```5. 查询索引所属的表的默认值约束```SELECT index_name, table_name FROM all_indexes WHERE index_type = 'NORMAL' AND uniqueness = 'NONUNIQUE';```四、查询索引的相关操作1. 查询索引的创建语句```SELECT dbms_metadata.get_ddl('INDEX', '索引名') FROM dual; ```2. 查询索引的重建语句```SELECT 'ALTER INDEX ' || index_name || ' REBUILD;' FROM all_indexes;```3. 查询索引的重命名语句```SELECT 'ALTER INDEX ' || index_name || ' RENAME TO 新索引名;' FROM all_indexes;```4. 查询索引的删除语句```SELECT 'DROP INDEX ' || index_name || ';' FROM all_indexes;```5. 查询索引的禁用语句```SELECT 'ALTER INDEX ' || index_name || ' UNUSABLE;' FROM all_indexes;```五、查询索引的相关性能优化1. 查询索引是否需要重新构建```SELECT index_name, table_name, last_analyzed FROM all_indexes WHERE last_analyzed < SYSDATE - 30;```2. 查询未使用的索引```SELECT index_name, table_name FROM all_indexes WHERE status = 'VALID' AND (user_reads = 0 OR user_updates = 0); ```3. 查询索引碎片化严重的情况```SELECT index_name, table_name, blevel, leaf_blocks, distinct_keys, clustering_factor FROM all_indexes WHERE blevel > 3;```4. 查询索引的大小是否过大```SELECT index_name, table_name, index_type, ROUND(bytes/1024/1024, 2) AS size_mb FROM dba_segments WHERE segment_type = 'INDEX' AND bytes/1024/1024 > 100;```5. 查询索引的选择性是否低```SELECT index_name, table_name, distinct_keys, num_rows, (distinct_keys/num_rows) AS selectivity FROM all_indexes WHERE selectivity < 0.1;```六、查询索引的相关性能统计1. 查询索引的读取次数与更新次数```SELECT index_name, table_name, user_reads, user_updates FROM all_indexes;```2. 查询索引的平均访问时间```SELECT index_name, table_name, blevel, leaf_blocks, distinct_keys, clustering_factor, num_rows, (leaf_blocks/clustering_factor) AS avg_access_time FROM all_indexes;```3. 查询索引的存储效率```SELECT index_name, table_name, blevel, leaf_blocks,distinct_keys, clustering_factor, (leaf_blocks/clustering_factor) AS storage_efficiency FROM all_indexes;```4. 查询索引的选择性```SELECT index_name, table_name, distinct_keys, num_rows, (distinct_keys/num_rows) AS selectivity FROM all_indexes;```5. 查询索引的碎片率```SELECT index_name, table_name, blevel, leaf_blocks, distinct_keys, clustering_factor, (leaf_blocks/clustering_factor) AS fragmentation FROM all_indexes;```以上是关于Oracle查询索引的一些常用语句，通过这些语句可以方便地查询索引的定义、状态、统计信息以及进行相关操作和性能优化。

oracle索引原理详解

oracle索引原理详解Oracle数据库中的索引是用于提高数据检索速度的重要工具。

了解Oracle索引的原理对于数据库管理员和开发人员来说是非常重要的。

一、索引的基本概念索引是Oracle数据库中的一个对象，它可以帮助数据库系统更快地检索数据。

索引类似于书籍的目录，可以快速定位到所需的数据。

二、索引的分类1. B-Tree索引：这是Oracle中最常用的索引类型，基于平衡多路搜索树（B-Tree）实现。

B-Tree索引适用于大多数数据类型，包括字符、数字和日期等。

2. Bitmap索引：位图索引主要用于处理包含大量重复值的列。

通过位图索引，可以更高效地处理这些列的查询。

3. 函数基索引：函数基索引允许在列上应用函数，然后对该结果进行索引。

这可以用于优化包含函数操作的查询。

4. 反转键索引：反转键索引是一种特殊类型的B-Tree索引，用于优化插入操作。

通过反转键顺序，可以更高效地处理插入操作。

三、索引的创建和维护1. 创建索引：创建索引的基本语法是“CREATE INDEX index_name ON table_name (column_name)”。

其中，index_name是索引的名称，table_name是要创建索引的表名，column_name是要索引的列名。

2. 维护索引：定期维护索引可以确保其性能和可靠性。

常用的维护操作包括重建索引（REBUILD INDEX）和重新组织索引（ORGANIZE INDEX）。

四、索引的优点和缺点1. 优点：使用索引可以显著提高数据检索速度，减少查询时间。

此外，索引还可以用于优化复杂查询的性能。

2. 缺点：虽然索引可以提高性能，但它们也会占用额外的磁盘空间。

此外，当表中的数据发生变化时，索引也需要更新，这可能会影响写操作的性能。

五、最佳实践1. 在经常用于搜索和排序的列上创建索引。

2. 根据查询模式和数据分布选择合适的索引类型。

3. 定期分析和维护索引，确保其性能和可靠性。

Oracle第6章视图和索引操作

教
量（g_Number）信息，可以创建一个“热点”商品的视
师
图。
演
示
CREATE OR REPLACE VIEW SCOTT.vw_HotGoods
讲
AS
解
SELECT g_ID AS 商品号, g_Name AS 商品名称, t_ID
AS 类别号, g_Price AS 价格, g_Discount
案例完成步骤
（6）强制创建视图
正常情况下，如果基表不存在，创建视图就会失败。但是可
以使用FORCE选项强制创建视图（前提是创建视图的语句
没有语法错误），但此时该视图处于失效状态。
教
【例2-6】创建并验证基于Test表的强制视图
师演
（a）在Test表不存在的情况下，创建基于该表的强制视图
示
vw_TestForce。
示
重定义时没有使用该选项，则以前的此选项将自动删除。
讲
2．使用PL/SQL修改视图
解
（1）重命名视图
【例2-7】重命名用户方案SCOTT的视图
vw_MaxPriceGoods为vw_MaxPrice。
rename vw_MaxPriceGoods TO vw_MaxPrice;
Oracle数据库管理与应用实例教程
师
演
图。
示
CREATE OR REPLACE VIEW
讲
解
SCOTT.vw_MaxPriceGoods
AS
SELECT t_ID, Max(g_Price) AS MaxPrice
FROM SCOTT.Goods
GROUP BY t_ID
Oracle数据库管理与应用实例教程

Oracle索引详解

2）<column_name> | <expression> ASC | DESC：可以对多列进行联合索引，当为expression 时即“基于函数的索引”3）TABLESPACE：指定存放索引的表空间(索引和原表不在一个表空间时效率更高)4）STORAGE：可进一步设置表空间的存储参数5）LOGGING | NOLOGGING：是否对索引产生重做日志(对大表尽量使用NOLOGGING来减少占用空间并提高效率)6）COMPUTE STATISTICS：创建新索引时收集统计信息7）NOCOMPRESS | COMPRESS<nn>：是否使用“键压缩”(使用键压缩可以删除一个键列中出现的重复值)8）NOSORT | REVERSE：NOSORT表示与表中相同的顺序创建索引，REVERSE表示相反顺序存储索引值9）PARTITION | NOPARTITION：可以在分区表和未分区表上对创建的索引进行分区1.2 索引特点：第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。

Oracle 索引基础
如果一个表中包含有很多条记录，当对表执行查询时，必须将所有的记录一一取出，以便将每一条记录与查询条件进行比较，然后再返回满足条件的记录。

这样进行操作的时间开销和I/O开销都是十分巨大的。

这时就可以考虑通过建立索引来减少开销。

简单地说，如果将表看作一本书，索引的作用则类似于书中的目录。

在没有目录的情况下，要在书中查找指定的内容必须阅读全书，而有了目录之后，只需要通过目录就可以快速找到包含所需内容的页。

类似地，如果要在表中查询指定的记录，在没有索引的情况下，必须遍历整个表，而有了索引之后，只需要在索引中找到符合查询条件的索引字段值，就可以通过保存在索引中的ROWID（相当于页码）快速找到表中对应的记录。

因此，合理地使用索引可以大大降低磁盘的I/O次数，从而提高数据访问性能。

假设EMP表中的数据占用了10000了数据块，如果EMPNO列上不存在索引，那么当执行查询操作时需要执行全表扫描，这种操作的I/O次数为10000次；如果EMPNO列上存在索引（假设索引层次为2），那么执行查询时将使用索引进行扫描（I/O次数为4次）。

索引与表一样，不仅需要在数据字典中保存索引的定义，还需要在表空间中为它分配实际的存储空间。

当创建索引时，Oracle会自动在用户的默认表空间中或指定的表空间中创建一个索引段，为索引数据提供存储空间。

与创建表的情况类以，在创建索引时也可以为它设置存储参数。

为某个表创建的索引不必和该表保存在同一个表空间中。

如果将索引和对应的表分别存放在位于不同硬盘上的不同的表空间中，反而能够提高查询的速度。

因为Oracle能够并行读取不同硬盘中的数据，这样的查询可以避免产生I/O冲突。

在创建索引时，Oracle首先对将要建立索引的字段进行排序，然后将排序后的字段值和对应记录的ROWID存储在索引段中。

例如，假设使用下面的语句为EMP表中的ENAME 字段创建索引：
SQL> create index emp_ename on emp (ename);
索引已创建。

Oracle首先在EMP表中按照ENAME字段进行排序，默认为升序排序，然后按照排序后的顺序将ENAME字段值和对应的ROWID逐个保存在索引中。

建立索引的字段被称为索此字段，例如ENAME字段即为索引字段。

在索引创建之后，如果执行一条在WHERE子句中引用了ENAME字段的查询。

例如：SQL> select ename,hiredate,sal
2 from emp
3 where ename='SMITH';
ENAME HIREDA TE SAL
---------- -------------- ---------
SMITH 17-12月-80 960
Oracle将首先对索引中的ENAME字段进行一次快速搜索（因为索引中的ENAME字段已经排序，所以该搜索是非常快的），找到符合条件的ENAME字段值所对应的ROWID，然后再利用ROWID到EMP表中提取相应的记录。

这个操作比逐条读取EMP表中未排序的记录要快得多。

可以看出，在索引中只保存索引字段的值与相应的ROWID，这种组合称为
“索引条目”。

初学者常常会混淆索引与键，特别是对于惟一索引与主键。

其实索引与键两个术语在许多地方都可以互换，尤其是Oracle 有时也会利用索引，特别是惟一索引实现的一些完整性约束。

但是它们在本质上有着相当明显的区别。

索引是存储在数据库中的一种实体结构，可以通过SQL 语句创建、修改和删除。

而键只是一个逻辑上的概念，在Oracle 中，键是与完整性约束相对应的。

在Oracle 中可以创建多种类型的索引，以适应各种表的特点。

按照索引数据的存储方式可以将索引分为：B 树索引、反向键索引、位图索引和基于函数的索引等。

按照索引列的个数，索引又可以分为单列索引和复合索引。

按照索引列的惟一性，索引又可以分为惟一索引和非惟一索引。

单列索引是基于单个列所建立的索引，而复合索引是基于两个列或多个列所建立的索引。

需要注意，B 树索引列的个数不能超过32列，位图索引的个数不能超过30例。

可以在同一个表上建立多个索引，但要求列的组合必须不同，使用以下语句建立的两个索引是合法的：
SQL> create index emp_name_job_index on emp(ename,job);
索引已创建。

SQL> create index emp_job_name_index on emp(job,ename);
索引已创建。

如上所示，尽管索引EMP_NAME_JOB_INDEX 和EMP_JOB_NAME_INDEX 用到了相同的列（ENAME 和JOB 列），但因为顺序不同，所以是合法的。

如果顺序完全相同，则该复合索引是不合法的。

惟一索引是索引列的值不能有重复的索引；非惟一索引是索引列值允许重复值的索引。

无论是惟一索引还是非惟一索此，索引列都允许NULL 值。

需要注意，当定义主键约束时或惟一约束时，Oracle 会自动在相应的约束列上建立惟一索引。

建立和规划索引时，必须选择合适的表和列。

如果选择的表和列不合适，不仅无法提高查询速度，反而会极大地降低DML 操作的速度。

建立索引的策略如下：
● 索引应该建立在WHERE 子句经常引用的表列上。

如果在大表上频繁使用某列或
某几列作为条件执行检索操作，并且检索行数低于总行数的15%，那么应该考虑在这些列上建立索引。

● 为了提高多表连接的性能，应该在连接列上建立索引。

● 不要在小表上建立索引。

● 如果经常需要基于某列或某几列执行排序操作，那么通过在这些列上建立索引，可
以加快数据排序的速度。

● 限制表的索引个数。

索引主要用于加速查询速度，但会降低DML 操作的速度。

索
引越多，DML 操作的速度越慢，尤其会极大地影响INSERT 操作和DELETE 操作
注意具有索引的表与不具有索引的表，在编写SQL 查询语句时没有任何区别。

索引
只是提供一种快速访问指定记录的方法。

可以说，表是否具有索引，表的查询
速度影响最大，而对其他方法的影响则非常微小。

索引一旦创建，将由Oracle
自动管理和维护。

例如，在向表中插入、更新或删除一条记录时，Oracle 会自
动在索引中作出相应的修改。

的速度。

因引，规划索引时，必须仔细权衡查询和DML的需求。

●删除不再需要的索引。

因为索引会降低DML速度，所以应该删除不合理或不需要
的索引。

不合理地的索引包括：在小表上建立的索引，因为表很小，使用索引不会加速查询速度；查询语句不会引用的索引。

●指定索引块空间的使用参数。

基于表建立索引时，Oracle会将相应表列数据添加到
索引块。

当为索引块添加数据时，Oracle会按照PCTFREE参数确定在索引块上预留部分空间，该预留空间是为将来的INSERT操作准备的。

如果将来在表上执行大量INSERT操作，那么应该在建立索引时设置较大的PCTFREE。

需要注意，建立索引时不能指定PCTUSED关键字。

●指定索引所在的表空间。

建立索引时，即可以将索引和基表放在相同表空间中，也
可以放在不同表空间中。

将表和索引部署到相同表空间，可以简化表空间的管理；
将表和索引部署到不同表空间，可以降低访问表和索引的区别，从而提高访问性能。

●当在大表上建立索引时，使用NOLOGGING选项可以最小化重做记录。

使用
NOLOGGING选项可以节省重做日志空间、降低索引建立时间、提高索引并行建立的性能。