oracle 数据库如何建立索引 如何用索引?

创建索引语法：

成都创新互联是一家专业提供乌恰企业网站建设,专注与网站设计、成都网站设计、HTML5建站、小程序制作等业务。10年已为乌恰众多企业、政府机构等服务。创新互联专业网站建设公司优惠进行中。

CREATE [UNIQUE] | [BITMAP] INDEX index_name

--unique表示唯一索引

ON table_name([column1 [ASC|DESC],column2

--bitmap，创建位图索引

[ASC|DESC],…] | [express])[TABLESPACE tablespace_name][PCTFREE n1]

--指定索引在数据块中空闲空间

[STORAGE (INITIAL n2)][NOLOGGING]

--表示创建和重建索引时允许对表做DML操作，默认情况下不应该使用

[NOLINE][NOSORT];

--表示创建索引时不进行排序，默认不适用，如果数据已经是按照该索引顺序排列的可以使用

扩展资料：

1、如果有两个或者以上的索引，其中有一个唯一性索引，而其他是非唯一，这种情况下oracle将使用唯一性索引而完全忽略非唯一性索引

2、至少要包含组合索引的第一列（即如果索引建立在多个列上，只有它的第一个列被where子句引用时，优化器才会使用该索引）

3、小表不要简历索引

4、对于基数大的列适合建立B树索引，对于基数小的列适合简历位图索引

5、列中有很多空值，但经常查询该列上非空记录时应该建立索引

6、经常进行连接查询的列应该创建索引

7、使用create index时要将最常查询的列放在最前面

8、LONG（可变长字符串数据，最长2G）和LONG RAW（可变长二进制数据，最长2G）列不能创建索引

9、限制表中索引的数量（创建索引耗费时间，并且随数据量的增大而增大；索引会占用物理空间；当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度）

Oracle数据库，两个select查询怎么合并成一个？

listagg()函数会将某列的查询结果通过分组的形式合并成一行，并且我们可以在各个结果集中定义自己想要的分隔符。

案例：

select * from test;

select id,listagg(name,',') within GROUP (order by name) from test group by id;

listagg()函数合并结果时，我们可以通过order by 设置它合并的先后顺序，通过 group by 来设置它合并的条件。有点类似于sum()或者count()的使用方法。

值得注意的是，这个函数的连接符只会出现在查询结果的中间，而且这个合并会根据分组条件无限叠加。因此，当我们需要将结果插入数据表的某一行时，请记得加上长度限制（通常我们使用substr()函数将太长的部分去掉），以免出现数据丢失等情况。

Oracle数据访问和索引的使用

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

· 通过ROWID访问数据；

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

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

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

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

注意事项：

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

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

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

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

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

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

注意事项：

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

· Rowid除了在同一集簇中可能不唯一外，每条记录的Rowid唯一。1.3 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 数据库查询两个表然后把结果合并成一个表

实现的方法和详细的操作步骤如下：

1、首先，在计算机桌面上双击“

Management Studio”图标，如下图所示，然后进入下一步。

2、其次，完成上述步骤后，单击左上角的“新建查询”按钮，如下图所示，然后进入下一步。

3、接着，完成上述步骤后，在界面中输入sql语句“

select * from test1，test2;”，将两个表合并成一个表，如下图所示，然后进入下一步。

4、然后，完成上述步骤后，单击下图红框标注的工具栏中的“运行三角形”图标，然后进入下一步。

5、最后，完成上述步骤后，在此界面中，表明两个表已成功合并，如下图所示。这样，问题就解决了。

如何管理oracle 的分区表和索引（续

一、创建分区表

1、范围分区

根据数据表字段值的范围进行分区

举个例子，根据学生的不同分数对分数表进行分区，创建一个分区表如下：create table range_fraction(

id number(8),

name varchar2(20),

fraction number(3),

grade number(2)

)

partition by range(fraction)

(

partition fraction_60 values less than(60), --不及格partition fraction_80 values less than(85), --及格partition fraction_100 values less than(maxvalue) --优秀)创建完分区表后向表中添加一些数据：declarename varchar2(10);

fraction number(5);

grade number(5);

i number(8):=1;

begin

for i in 1..100000 LOOP

SELECT CHR (ROUND (DBMS_RANDOM.VALUE (97, 122))) INTO NAME FROM DUAL;SELECT ABS(MOD(DBMS_RANDOM.RANDOM,101)) into fraction FROM DUAL;SELECT ABS(MOD(DBMS_RANDOM.RANDOM,10))+1 into grade FROM DUAL;insert into range_fraction values(seq_range_fraction.nextval ,name,fraction,grade);END LOOP;end;

查询分区表：--分别查询所有的，不及格的，中等的，优秀的成绩select * from range_fraction;select * from range_fraction partition(fraction_60) ;select * from range_fraction partition(fraction_80) ;select * from range_fraction partition(fraction_100) ;当我们的查询语句不指定分区的时候，如果分区字段出现在where条件之后，Oracle会自动根据字段值的范围扫描响应的分区：

select * from range_fraction where fraction30; 这句SQL执行的时候只会扫描不及格的分区select * from range_fraction where fraction80; 这句SQL执行的时候会扫描不及格和中等两个分区2、散列分区在范围分区中，分区字段的连续值通常出现在一个分区内，而在散列分区中，连续的字段值不一定存储在相同的分区中。散列分区把记录分布在比范围分区更多的分区上，这减少了I/O争用的可能性。

为了创建一个散列分区，应该用partition by hash语句代替partition by range子句，如下所示：

第一种为各个分区指定不同的表空间，表空间数量不用等于分区数量，当表空间数量大于分区数量的时候会循环写入各个表空间：

create table range_fraction1

(

id number(8),

name varchar2(20),

fraction number(3),

grade number(2)

)

partition by hash(fraction)

partitions 8

store in (users,tbs_haicheng)

第二种为每个分区指定一个分区名称并为其指定表空间：create table range_fraction1(

id number(8),

name varchar2(20),

fraction number(3),

grade number(1)

)

partition by hash(fraction)

(

partition p1 tablespace tbs_haicheng ,

partition p2 tablespace users

);

3、列表分区

还可以使用列表分区代替范围分区和散列分区。在列表分区中，告诉Oracle所有可能的值，并指定应当插入相应行的分区。

我们将1、2、3、4班级的数据放在一个分区，将6、7、8的数据放在一个分区，将其他的再放在一个分区，建表如下：

create table range_fraction1

(

id number(8),

name varchar2(20),

fraction number(3),

grade number(2)

)

partition by list(grade)

(

partition p1 values(1,2,3,4) tablespace tbs_haicheng ,partition p2 values(5,6,7,8) tablespace users,?

1

partition p3 values(default)

4、组合分区(创建子分区)

即分区的分区。例如可以先进行范围分区，再对各个范围分区创建列表分区。

对于非常大的表来说，这种组合分区是一种把数据分成可管理和可调整的组成部分的有效方法。

举个例子：按照分数范围分区后再将ID散列分区：

create table range_fraction1

(

id number(8),

name varchar2(20),

fraction number(3),

grade number(1)

)

partition by range(fraction)

subpartition by hash(id)

subpartitions 4

(

partition fraction_60 values less than(60), --不及格partition fraction_80 values less than(85), --及格partition fraction_100 values less than(maxvalue) --优秀)二、索引分区

在分区表上可以建立三种类型的索引：1和普通表一样的全局索引；2.全局分区索引；3.本地分区索引1.建立普通的索引create index index_fraction on range_fraction(fraction);2.建立本地分区索引（就是一个索引分区只能对应一个表分区）?

create index local_index_fraction on range_fraction(fraction) local;3.建立全局分区索引（属于散列索引分区，就是一个索引分区可能指向多个表分区）?

create index global_index_fraction on range_fraction(fraction)GLOBAL partition by range(fraction)(

part_01 values less than(1000),

part_02 values less than(MAXVALUE)

);

三、管理分区表

1、增加分区

对于范围分区来说，添加一个分区，必须该分区划定的界限高于原来的最大界限，也就是说只能往上加，不能往下加。那么对于用maxvalue关键字创建的范围分区就不能增加分区了举例：

create table range_fraction

(

id number(8),

name varchar2(20),

fraction number(3),

grade number(2)

)

partition by range(fraction)

(

partition fraction_60 values less than(40), --不及格partition fraction_80 values less than(60), --及格partition fraction_100 values less than(80) --优秀)对于该分区我们增加一个分区：

ALTER TABLE range_fraction ADD PARTITION fraction_100 VALUES LESS THAN (100);为列表分区添加一个分区：

create table range_fraction

(

id number(8),

name varchar2(20),

fraction number(3),

grade number(2)

)

partition by list(grade)

(

partition p1 values(1,2,3) tablespace tbs_haicheng ,partition p2 values(4,5,6) tablespace users);ALTER TABLE range_fraction ADD partition p3 VALUES (7,8);我们再为p3分区新增两个表分区值：

ALTER TABLE range_fraction MODIFY PARTITION p3 ADD VALUES(9,10);然后再将p3分区的表分区值中的10删掉：

ALTER TABLE range_fraction MODIFY PARTITION p3 DROP VALUES(10);为哈希分区添加一个子分区：

ALTER TABLE TABLENAME ADD PARTITION PARTNAME;添加一个子分区的格式：

ALTER TABLE TABLENAME MODIFY PARTITION PARTNAME ADD SUBPARTITION SUBPARTNAME;2、删除分区删除分区比较简单，格式如下：

ALTER TABLE ... DROP PARTITION part_name;3、分区合并合并父分区格式：ALTER TABLE TABLENAME MERGE PARTITIONS p1-1, p1-2 INTO PARTITION p1 UPDATE INDEXES;如果省略了UPDATE INDEXES 的话需要为受影响的分区重建索引合并子分区的格式：

ALTER TABLE TABLENAME

MERGE SUBPARTITIONS part_1_sub_2, part_1_sub_3 INTO SUBPARTITION part_1_sub_2 UPDATE INDEXES;4、转换分区可以将分区表转换成非分区表，或者几种不同分区表之间的转换。如下：

CREATE TABLE hash_part02 AS SELECT * FROMhash_example WHERE 1=2;ALTER TABLE hash_example EXCHANGE PARTITIONpart02 WITH TABLE hash_part02;这时，分区表hash_example中的part02分区的资料将被转移到hash_part02这个非分区表中。

网站栏目：oracle怎么索引合并 mysql索引合并
分享路径：http://kswjz.com/article/hjjocg.html