Oracle分区索引

2022-05-04 03:35:01| 收藏本文下载本文作者：美人的舔狗

今天小编在这给大家整理了Oracle分区索引(共含11篇），我们一起来看看吧！同时，但愿您也能像本文投稿人“美人的舔狗”一样，积极向本站投稿分享好文章。

Oracle分区索引

篇1：Oracle分区索引

----------------------------------------------------------

Plan hash value: 2556877094

--------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 41 | 2 (0)| 00:00:01 | | |

| 1 | PARTITION RANGE SINGLE | | 1 | 41 | 2 (0)| 00:00:01 | 1 | 1 |

| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| BIG_TABLE | 1 | 41 | 2 (0)| 00:00:01 | 1 | 1 |

|* 3 | INDEX RANGE SCAN | BITA_CREATED_DATE_I | 1 | | 1 (0)| 00:00:01 | 1 | 1 |

--------------------------------------------------------------------------------------------------------------------------

3、全局分区索引演示

--为表添加主键

SQL> ALTER TABLE big_table ADD (

2 CONSTRAINT big_table_pk PRIMARY KEY (id)

3 );

Table altered.

SQL> select index_name,index_type,tablespace_name,global_stats,partitioned

2 from user_indexes where index_name='BIG_TABLE_PK';

INDEX_NAME INDEX_TYPE TABLESPACE_NAME GLO PAR

------------------------------ --------------------------- ------------------------------ --- ---

BIG_TABLE_PK NORMAL TBS_TMP YES NO

SQL> set autot trace exp;

SQL> select * from big_table where id=1412;

Execution Plan

----------------------------------------------------------

Plan hash value: 2662411593

-------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 62 | 2 (0)| 00:00:01 | | |

| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| BIG_TABLE | 1 | 62 | 2 (0)| 00:00:01 | ROWID | ROWID |

|* 2 | INDEX UNIQUE SCAN | BIG_TABLE_PK | 1 | | 1 (0)| 00:00:01 | | |

-------------------------------------------------------------------------------------------------------------------

--如上，在其执行计划中，Pstart与Pstop都为ROWID

--出现了GLOBAL INDEX ROWID，我们添加主键时并未指定Global，但其执行计划表明执行了全局索引访问

--这个地方有待证实，对于分区表，非分区键上的主键或唯一索引是否一定是全局索引

SQL> drop index bita_created_date_i;

--下面创建全局索引，创建时需要指定分区键的范围和值

SQL> CREATE INDEX bita_created_date_i

ON big_table (created_date)

GLOBAL PARTITION BY RANGE (created_date)

(

PARTITION

idx_1 VALUES LESS THAN (TO_DATE ('01/01/', 'DD/MM/YYYY'))

TABLESPACE idx1,

PARTITION

idx_2 VALUES LESS THAN (TO_DATE ('01/01/', 'DD/MM/YYYY'))

TABLESPACE idx2,

PARTITION idx_3 VALUES LESS THAN (maxvalue) TABLESPACE idx3);

SQL> select index_name, partitioning_type, partition_count,locality from user_part_indexes;

INDEX_NAME PARTITI PARTITION_COUNT LOCALI

------------------------------ ------- --------------- ------

BITA_CREATED_DATE_I_G RANGE 3 GLOBAL

SQL> select partition_name, high_value, tablespace_name from user_ind_partitions;

PARTITION_NAME HIGH_VALUE TABLESPACE_NAME

------------------------------ --------------------- ------------------------------

IDX_1 TO_DATE(' 2013-01-01 IDX1

IDX_2 TO_DATE(' 2014-01-01 IDX2

IDX_3 MAXVALUE IDX3

--下面是其执行计划，可以看出支持分区消除

SQL> set autot trace exp;

SQL> select * from big_table where created_date=to_date('20130625','yyyymmdd');

Execution Plan

----------------------------------------------------------

Plan hash value: 1378264218

---------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 41 | 2 (0)| 00:00:01 | | |

| 1 | PARTITION RANGE SINGLE | | 1 | 41 | 2 (0)| 00:00:01 | 2 | 2 |

| 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| BIG_TABLE | 1 | 41 | 2 (0)| 00:00:01 | 2 | 2 |

|* 3 | INDEX RANGE SCAN | BITA_CREATED_DATE_I | 1 | | 1 (0)| 00:00:01 | 2 | 2 |

---------------------------------------------------------------------------------------------------------------------------

--以下为范围查询，Pstart为1，Pstop为2，同样支持分区消除

SQL> select * from big_table

2 where created_date>=to_date('0625','yyyymmdd') and created_date<=to_date('20130625','yyyymmdd');

Execution Plan

----------------------------------------------------------

Plan hash value: 213633793

------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 3334 | 133K| 14 (0)| 00:00:01 | | |

| 1 | PARTITION RANGE ITERATOR| | 3334 | 133K| 14 (0)| 00:00:01 | 1 | 2 |

|* 2 | TABLE ACCESS FULL | BIG_TABLE | 3334 | 133K| 14 (0)| 00:00:01 | 1 | 2 |

------------------------------------------------------------------------------------------------------

篇2：Oracle 分区索引介绍和实例演示

作者：Leshami 字体：[增加减小] 类型：

这篇文章主要介绍了Oracle 分区索引介绍和实例演示,分区索引(或索引分区)主要是针对分区表而言的,本文主要描述了分区索引的相关特性并给出演示示例,需要的朋友可以参考下

分区索引(或索引分区)主要是针对分区表而言的，随着数据量的不断增长，普通的堆表需要转换到分区表，其索引呢，则对应的转换到分区索引。分区索引的好处是显而易见的。就是简单地把一个索引分成多个片断，在获取所需数据时，只需要访问更小的索引片断(块)即可实现。同时把分区放在不同的表空间可以提高分区的可用性和可靠性。本文主要描述了分区索引的相关特性并给出演示示例。

1、分区索引的相关概念

a、分区索引的几种方式：表被分区而索引未被分区；表未被分区，而索引被分区；表和索引都被分区

b、分区索引可以分为本地分区索引以及全局分区索引

本地分区索引：

本地分区索引信息的存放依赖于父表分区。也就是说对于本地索引一定是基于分区表创建的。

缺省情况下，创建本地索引时，如未指定索引存放表空间，会自动将本地索引存放到数据所在分区定义时的表空间。

本地索引的分区机制和表的分区机制一样，本地索引可以是是B树索引或位图索引。

本地索引是对单个分区的，每个分区索引只指向一个表分区，为对等分区。

本地索引支持分区独立性，因此对于这些单独的分区增加，截取，删除，分割，脱机等处理无需同时删除或重建。

本地索引多应用于数据仓库环境中。

全局分区索引：

全局分区索引时分区表和全局索引的分区机制不一样，在创建时必须定义分区键的范围和值。

全局分区索引在创建时应指定Global关键字且全局分区索引只能是B树索引。

全局索引可以分区，也可以是不分区索引，全局索引必须是前缀索引，即索引列必须包含分区键。

全局索引分区中，一个分区索引能指向n个表分区，同时，一个表分区，也可能指向n个索引分区。

默认情况下全局索引对于分区增加，截取，删除，分割等都必须重建或修改时指定update global indexs。

全局分区索引只按范围或者散列hash分区。

全局分区索引多应用于oltp系统中。

c、有前缀索引和无前缀索引

本地和全局分区索引又分为两个子类型即有前缀索引和无前缀索引。

前缀和非前缀索引都可以支持索引分区消除，前提是查询的条件中包含索引分区键。

有前缀索引:

有前缀索引包含了分区键，即分区键列被包含在索引中。

有前缀索引支持本地分区索引以及全局分区索引。

无前缀索引：

无前缀索引即没有把分区键的前导列作为索引的前导列。

无前缀索引仅仅支持本地分区索引。

2、本地分区索引演示

代码如下:

--环境

SQL> select * from v$version where rownum<2;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi

SQL> create user leshami identified by xxx;

SQL> grant dba to leshami;

--创建演示需要用到的表空间

SQL> create tablespace tbs_tmp datafile ‘/u02/database/SYBO2/oradata/tbs_tmp.dbf‘ size 10m autoextend on;

SQL> alter user leshami default tablespace tbs_tmp;

SQL> create tablespace tbs1 datafile ‘/u02/database/SYBO2/oradata/tbs1.dbf‘ size 10m autoextend on;

SQL> create tablespace tbs2 datafile ‘/u02/database/SYBO2/oradata/tbs2.dbf‘ size 10m autoextend on;

SQL> create tablespace tbs3 datafile ‘/u02/database/SYBO2/oradata/tbs3.dbf‘ size 10m autoextend on;

SQL> create tablespace idx1 datafile ‘/u02/database/SYBO2/oradata/idx1.dbf‘ size 10m autoextend on;

SQL> create tablespace idx2 datafile ‘/u02/database/SYBO2/oradata/idx2.dbf‘ size 10m autoextend on;

SQL> create tablespace idx3 datafile ‘/u02/database/SYBO2/oradata/idx3.dbf‘ size 10m autoextend on;

SQL> conn leshami/xxx

-- 创建一个lookup表

CREATE TABLE lookup (

id NUMBER(10),

description VARCHAR2(50)

);

--添加主键约束

ALTER TABLE lookup ADD (

CONSTRAINT lookup_pk PRIMARY KEY (id)

);

--插入数据

INSERT INTO lookup (id, description) VALUES (1, ‘ONE‘);

INSERT INTO lookup (id, description) VALUES (2, ‘TWO‘);

INSERT INTO lookup (id, description) VALUES (3, ‘THREE‘);

COMMIT;

CREATE TABLE big_table (

id NUMBER(10),

created_date DATE,

lookup_id NUMBER(10),

data VARCHAR2(50)

)

PARTITION BY RANGE (created_date)

(PARTITION big_table_2012 VALUES LESS THAN (TO_DATE(‘01/01/2013‘, ‘DD/MM/YYYY‘)) tablespace tbs1,

PARTITION big_table_2013 VALUES LESS THAN (TO_DATE(‘01/01/2014‘, ‘DD/MM/YYYY‘)) tablespace tbs2,

PARTITION big_table_2014 VALUES LESS THAN (MAXVALUE)tablespace tbs3 ) ;

--填充数据到分区表

DECLARE

l_lookup_id lookup.id%TYPE;

l_create_date DATE;

BEGIN

FOR i IN 1 .. 10000 LOOP

IF MOD(i, 3) = 0 THEN

l_create_date := ADD_MONTHS(SYSDATE, -24);

l_lookup_id := 2;

ELSIF MOD(i, 2) = 0 THEN

l_create_date := ADD_MONTHS(SYSDATE, -12);

l_lookup_id := 1;

ELSE

l_create_date := SYSDATE;

l_lookup_id := 3;

END IF;

INSERT INTO big_table (id, created_date, lookup_id, data)

VALUES (i, l_create_date, l_lookup_id, ‘This is some data for ‘ || i);

END LOOP;

COMMIT;

END;

--未指定索引分区及存储表空间情形下创建索引

SQL> CREATE INDEX bita_created_date_i ON big_table(created_date) LOCAL;

Index created.

SQL> select index_name, partitioning_type, partition_count from user_part_indexes;

INDEX_NAME PARTITI PARTITION_COUNT

------------------------------ ------- ---------------

BITA_CREATED_DATE_I RANGE 3

--Author : Leshami

--从下面的查询可知，索引直接存放到分表表对应的表空间

SQL> select partition_name, high_value, tablespace_name from user_ind_partitions;

PARTITION_NAME HIGH_VALUE TABLESPACE_NAME

------------------------------ ---------------------------------------- ------------------------------

BIG_TABLE_2014 MAXVALUE TBS3

BIG_TABLE_2013 TO_DATE(‘ 2014-01-01 00:00:00‘, ‘SYYYY-M TBS2

M-DD HH24:MI:SS‘, ‘NLS_CALENDAR=GREGORIA

BIG_TABLE_2012 TO_DATE(‘ 2013-01-01 00:00:00‘, ‘SYYYY-M TBS1

M-DD HH24:MI:SS‘, ‘NLS_CALENDAR=GREGORIA

--删除索引

SQL> drop index bita_created_date_i;

--指定索引分区名表空间名创建索引

SQL> CREATE INDEX bita_created_date_i

2 ON big_table (created_date)

3 LOCAL (

4 PARTITION idx_2012 TABLESPACE idx1,

5 PARTITION idx_2013 TABLESPACE idx2,

6 PARTITION idx_2014 TABLESPACE idx3)

7 PARALLEL 3;

Index created.

SQL> select partition_name, high_value, tablespace_name from user_ind_partitions;

PARTITION_NAME HIGH_VALUE TABLESPACE_NAME

------------------------------ ---------------------------------------- ------------------------------

IDX_2014 MAXVALUE IDX3

IDX_2013 TO_DATE(‘ 2014-01-01 00:00:00‘, ‘SYYYY-M IDX2

M-DD HH24:MI:SS‘, ‘NLS_CALENDAR=GREGORIA

IDX_2012 TO_DATE(‘ 2013-01-01 00:00:00‘, ‘SYYYY-M IDX1

M-DD HH24:MI:SS‘, ‘NLS_CALENDAR=GREGORIA

SQL> select * from big_table where rownum<2;

ID CREATED_ LOOKUP_ID DATA

---------- -------- ---------- --------------------------------------------------

1413 20120625 2 This is some data for 1413

--查看local index是否被使用，从下面的执行计划中可知，索引被使用，支持分区消除

SQL> set autot trace exp;

SQL> select * from big_table where created_date=to_date(‘20120625‘,‘yyyymmdd‘);

Execution Plan

----------------------------------------------------------

Plan hash value: 2556877094

--------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 41 | 2 (0)| 00:00:01 | | |

| 1 | PARTITION RANGE SINGLE | | 1 | 41 | 2 (0)| 00:00:01 | 1 | 1 |

| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| BIG_TABLE | 1 | 41 | 2 (0)| 00:00:01 | 1 | 1 |

|* 3 | INDEX RANGE SCAN | BITA_CREATED_DATE_I | 1 | | 1 (0)| 00:00:01 | 1 | 1 |

--------------------------------------------------------------------------------------------------------------------------

3、全局分区索引演示

代码如下:

--为表添加主键

SQL> ALTER TABLE big_table ADD (

2 CONSTRAINT big_table_pk PRIMARY KEY (id)

3 );

Table altered.

SQL> select index_name,index_type,tablespace_name,global_stats,partitioned

2 from user_indexes where index_name=‘BIG_TABLE_PK‘;

INDEX_NAME INDEX_TYPE TABLESPACE_NAME GLO PAR

------------------------------ --------------------------- ------------------------------ --- ---

BIG_TABLE_PK NORMAL TBS_TMP YES NO

SQL> set autot trace exp;

SQL> select * from big_table where id=1412;

Execution Plan

----------------------------------------------------------

Plan hash value: 2662411593

-------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 62 | 2 (0)| 00:00:01 | | |

| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| BIG_TABLE | 1 | 62 | 2 (0)| 00:00:01 | ROWID | ROWID |

|* 2 | INDEX UNIQUE SCAN | BIG_TABLE_PK | 1 | | 1 (0)| 00:00:01 | | |

-------------------------------------------------------------------------------------------------------------------

--如上，在其执行计划中，Pstart与Pstop都为ROWID

--出现了GLOBAL INDEX ROWID，我们添加主键时并未指定Global，但其执行计划表明执行了全局索引访问

--这个地方有待证实，对于分区表，非分区键上的主键或唯一索引是否一定是全局索引

SQL> drop index bita_created_date_i;

--下面创建全局索引，创建时需要指定分区键的范围和值

SQL> CREATE INDEX bita_created_date_i

ON big_table (created_date)

GLOBAL PARTITION BY RANGE (created_date)

(

PARTITION

idx_1 VALUES LESS THAN (TO_DATE (‘01/01/2013‘, ‘DD/MM/YYYY‘))

TABLESPACE idx1,

PARTITION

idx_2 VALUES LESS THAN (TO_DATE (‘01/01/2014‘, ‘DD/MM/YYYY‘))

TABLESPACE idx2,

PARTITION idx_3 VALUES LESS THAN (maxvalue) TABLESPACE idx3);

SQL> select index_name, partitioning_type, partition_count,locality from user_part_indexes;

INDEX_NAME PARTITI PARTITION_COUNT LOCALI

------------------------------ ------- --------------- ------

BITA_CREATED_DATE_I_G RANGE 3 GLOBAL

SQL> select partition_name, high_value, tablespace_name from user_ind_partitions;

PARTITION_NAME HIGH_VALUE TABLESPACE_NAME

------------------------------ --------------------- ------------------------------

IDX_1 TO_DATE(‘ 2013-01-01 IDX1

IDX_2 TO_DATE(‘ 2014-01-01 IDX2

IDX_3 MAXVALUE IDX3

--下面是其执行计划，可以看出支持分区消除

SQL> set autot trace exp;

SQL> select * from big_table where created_date=to_date(‘20130625‘,‘yyyymmdd‘);

Execution Plan

----------------------------------------------------------

Plan hash value: 1378264218

---------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 41 | 2 (0)| 00:00:01 | | |

| 1 | PARTITION RANGE SINGLE | | 1 | 41 | 2 (0)| 00:00:01 | 2 | 2 |

| 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| BIG_TABLE | 1 | 41 | 2 (0)| 00:00:01 | 2 | 2 |

|* 3 | INDEX RANGE SCAN | BITA_CREATED_DATE_I | 1 | | 1 (0)| 00:00:01 | 2 | 2 |

---------------------------------------------------------------------------------------------------------------------------

--以下为范围查询，Pstart为1，Pstop为2，同样支持分区消除

SQL> select * from big_table

2 where created_date>=to_date(‘20120625‘,‘yyyymmdd‘) and created_date<=to_date(‘20130625‘,‘yyyymmdd‘);

Execution Plan

----------------------------------------------------------

Plan hash value: 213633793

------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 3334 | 133K| 14 (0)| 00:00:01 | | |

| 1 | PARTITION RANGE ITERATOR| | 3334 | 133K| 14 (0)| 00:00:01 | 1 | 2 |

|* 2 | TABLE ACCESS FULL | BIG_TABLE | 3334 | 133K| 14 (0)| 00:00:01 | 1 | 2 |

------------------------------------------------------------------------------------------------------

篇3：分区索引（一）

分区索引（一）

1、分区索引

分区索引就是简单地把一个索引分成多个片断，通过把一个索引分成多个片断，可以访问更小的片断(也更快)，并且可以把这些片断分别存放在不同的磁盘驱动器上(避免I/O问题)。B树和位图索引都可以被分区，而HASH索引不可以被分区。可以有好几种分区方法：表被分区而索引未被分区；表未被分区而索引被分区；表和索引都被分区。不管采用哪种方法，都必须使用基于成本的优化器。分区能够提供更多可以提高性能和可维护性的可能性。

1、有两种类型的分区索引：本地分区索引和全局索引。

每个类型都有两个子类型，有前缀索引和无前缀索引。表各列上的索引可以有各种类型索引的组合。如果使用了位图索引，就必须是本地索引。把索引分区最主要的原因是可以减少所需读取的索引的大小，另外把分区放在不同的表空间中可以提高分区的可用性和可靠性。

在使用分区后的表和索引时，Oracle还支持并行查询和并行DML。这样就可以同时执行多个进程，从而加快处理这条语句。

（1）有前缀的分区索引指包含了分区键，并且将其作为引导列的索引。

如：

create index i_id_global on PDBA(id) global --引导列 2 partition by range(id) --分区键 3 (partition p1 values less than (200), 4 partition p2 values less than (maxvalue) 5 );

这里的ID 就是分区键，并且分区键id 也是索引的引导列。

（2）无前缀的分区索引的列不是以分区键开头，或者不包含分区键列。

如：

create index ix_custaddr_local_id_p on custaddr(id)local ( partition t_list556 tablespace icd_service, partition p_other tablespace icd_service)

这个分区是按照areacode来的。但是索引的引导列是ID。所以它就是非前缀分区索引。

（3）全局分区索引不支持非前缀的分区索引，如果创建，报错如下：

sql> create index i_time_global on PDBA(id) global --索引引导列 2 partition by range(time) --分区建 3 (partition p1 values less than (TO_DATE('-12-1', 'YYYY-MM-DD')), 4 partition p2 values less than (maxvalue) 5 );partition by range(time) *

第 2 行出现错误:

ORA-14038: GLOBAL 分区索引必须加上前缀

2、Local索引肯定是分区索引，Global索引可以选择是否分区，如果分区，只能是有前缀的分区索引。

2、类型

1）本地索引---局部分区索引 --局部索引比全局索引容易管理, 而全局索引比较快。

其分区形式与表的分区完全相同，依赖列相同，存储属性也相同。对于本地索引，其索引分区的维护自动进行，就是说你add/drop/split/truncate表的分区时，本地索引会自动维护其索引分区。

在表分区后，对每个表分区建立一个索引，对表的索引进行均匀分区称为局部分区。

局部分区索引广泛的应用在大型OLAP的数据仓库中。支持更高的高可用性，某个分区表的数据出现问题，只影响该分区表对应的局部索引，单独重构局部索引就行，而且恢复起来更方便，如果知道表进行MOVE后,ROWID变化了,所以索引也需要REBUILD，分区表也可以对单个分区进行MOVE，因为局部分区索引是跟表分区一致的，所以只要是对单个表分区进行MOVE,我们也只需要REBUILD单个局部索引分区，分区表技术可以实现将某一个分区恢复到某个时间点，而局部分区索引同步到该时间点，而数据仓库大量的IO容易造成部分磁盘或IO损坏，如果用局部分区索引影响非常小

2）全局索引: -----Oracle只支持2中类型的全局分区索引：range partitioned 和 Hash Partitioned.

即可以分区，也可以不分区。即可以建range分区，也可以建hash分区，即可建于分区表，又可创建于非分区表上，就是说，全局索引是完全独立的，因此它也需要我们更多的维护操作，

全局索引分区数据指向任何一个表分区的分区索引，9I全局索引只有范围全局索引，10G有HASH全局索引。

全局索引以前常用在OLTP。维护麻烦，影响到所有的全局分区索引，OLTP中最重要的是数据的完整性和准确性，而全局分区索引的及时重构将使得可靠性得到保证。

3、与分区索引有关的视图

dba_part_indexes 分区索引的概要统计信息，可以得知每个表上有哪些分区索引，分区索引的类型(local/global) dba_ind_partitions 每个分区索引的分区级统计信息 dba_indexes minus dba_part_indexes （minus操作）可以得到每个表上有哪些非分区索引create index i_id_global on PDBA(id) globalpartition by range(id)(partition p1 values less than (200),partition p2 values less than (4000),partition p3 values less than (8000),partition p4 values less than (maxvalue));

示例：

1、查询分区索引相关属性：索引类型

---dba_part_indexesSQL> select index_name,table_name,partitioning_type,locality,ALIGNMENT from dba_part_indexes where table_name='PDBA';INDEX_NAME TABLE_NAME PARTITI LOCALI ALIGNMENT------------------------------ ------------------------------ ------- ------ ------------I_ID_GLOBAL PDBA RANGE GLOBAL PREFIXED

2、查询索引关联的分区信息 -

-dba_ind_partitionsSQL> select index_name,partition_name, status from dba_ind_partitions where index_name='I_ID_GLOBAL';INDEX_NAME PARTITION_NAME STATUS------------------------------ ------------------------------ --------I_ID_GLOBAL P1 USABLEI_ID_GLOBAL P2 USABLEI_ID_GLOBAL P3 USABLEI_ID_GLOBAL P4 USABLE

3、查看索引对应的列

--dba_ind_partitionsset linesize 230col column_name format a30SQL> select table_name,index_name,column_name from dba_ind_columns where table_name='PDBA' and table_owner='SYS';TABLE_NAME INDEX_NAME COLUMN_NAME------------------------------ ------------------------------ ------------------------------PDBA I_ID_GLOBAL ID

4、查询非分区索引的基础信息：存储

--dba_indexesSQL> select owner,index_name,table_name,status from dba_indexes where INDEX_NAME='I_ID_GLOBAL';OWNER INDEX_NAME TABLE_NAME STATUS------------------------------ ------------------------------ ------------------------------ --------SYS I_ID_GLOBAL PDBA N/A

篇4：oracle数据库和分区

Oracle数据完整性和锁机制索引及优化之表分析表分析、约束及表间关系 Oracle体系结构1 Oracle体系结构2 海量数据库及分区1 海量数据库及分区2 海量数据库及分区3 海量数据库及分区4 高级SQL优化(一) 高级SQL优化(二) 高级SQL优化(三) 常用优化工具 PPT和源码下载： sishuok.com/forum/posts/list/6365.html 配套视频课程 Oracle性能优化 sishuok.com/product/601 海量数据库和高级SQL优化 sishuok.com/product/602 原规划本课的内容为表分区，即单级分区表的简要知识，规划为一课，

oracle数据库和分区

。由于近年来行业内海量数据处理人才紧缺，现根据读者反馈将内容调整为四课，以详细讨论海量数据库的全部主要知识，并辅以大量实践练习，以期有志于研究学习VLDB的读者通过加量的课程学习能达到建立起VLDB基础理论体系，迅速适应相应的海量数据库设计和开发工作的效果。 VLDB概述

引子很多现代企业如电子商务企业需要频繁的在一个包含数百个GB或者数个TB数据的数据库上运行关键任务。这种数据库称为超大型数据库（VLDB，Very Large Databases，又称海量数据库），其中的数据称为海量数据。实践中，VLDB一般包含两个关键要素：一是用户达到百万级甚至千万级，数据库的容量一般超过1TB(1TB=1024GB)；二是系统必须提供实时响应功能，不能停机、具有高度的可靠性和可扩展性。例如亚马逊、淘宝、苏宁易购等系统的数据库。对于VLDB的支持和维护的挑战工作催生了海量数据解决方案。VLDB的主要主题中，最关键的方法一般是使用分区、压缩、并行执行等技术。分区简介分区是将一个超大表或者索引分解成若干个更小、更易于管理的小的部分，这些小的部分称为分区。对于表而言称为分区表，对于索引而言称为分区索引。原来对超大表或超大索引的SQL查询和DML无需进行任何修改即可访问这些分区表和分区索引。定义了分区后， DDL语句就只访问和操纵单个分区，而不是整个表或索引，因而分区带来了高可维护性的好处。分区的一个基本要求是表或索引的每个分区具有相同逻辑属性，如列名、约束等；但不同的分区可以具有不同的物理属性，如压缩的启用/禁用，物理存储设置、表空间等。对于许多不同类型的应用，尤其是管理大量数据的应用程序而言分区是非常有用的，如对于OLTP分区可提高其可管理性和可用性、性能，而对于数据仓库分区则可提高其性能和可管理性。分区的优点如下： ? 显著缩短操作处理时间：这些操作包括数据装载、索引的创建和重建、备份与恢复。能显著缩短的原因是这些操作仅限于分区级(在个体的分区上)，而非表级（在整个表上) ? 提高查询响应性能：通常的查询只需要访问分区的一个子集即可完成，从而避免了访问整个表。这种技术称为分区裁剪，其对性能的提升可以达到量级单位 ? 显著缩短因维护操作导致的计划宕机时间：分区是独立于分区维护操作的，因此允许在表的不同分区或索引不同的分区上执行并发维护操作。如可以对分区执行并发的SELECT和DML语句等维护操作，其二者不会相互影响 VLDB与分区海量数据库没有最小的绝对大小，VLDB虽然类似于一个小型数据库，但管理一个VLDB依然存在很多挑战，这些挑战与执行操作的数据库的绝对规模及其成本效用是有关系的。数据库大小的稳步增长与以下几个趋势因素有关： ? 系统开发的独立性和综合合并应用需求的驱动：每个系统独立开发，但跨部门的综合应用需要数据库和应用进行整合合并，而整合带来的一个是基础数据和存储结构的不统一，二就是导致数据库大小的持续增长 ? 法律法规的规定：法律法规要求存储最小时间粒度的数据并保存一个较长时间，如日志信息、交易信息等 ? 客户公司发展因素：如公司的增长发展、收购等导致用户规模和业务规模的增长，这些均能带来数据规模和并发规模的增长分区策略是管理超大容量数据库的一个核心特性。分区地址的增长是VLDB的最基本挑战之一，Oracle使用“分而治之”的技术策略管理数据库中的表和索引，当这些表和索引增长时，该技术非常有效。分区的一个特性是当管理一个超大容量数据库时，无需投入更多的管理资源和硬件资源即可提供具有高度可伸缩性和统一的可靠数据库性能的数据库服务。 Oracle建议，哪怕小规模数据库（以MB为单位）也可以分区，分区同样能获得与以TB为单位的VLDB相同的性能和高可维护性。因此从这个角度触发，建议对每个大数据表均进行分区，亦即分区是数据库优化的一个重要手段。分区基础知识概览 1.分区的基本知识从数据库管理员的角度来看，一个分区的对象有多个块，可以集体管理或单独管理，这赋予管理员在管理分区对象相当大的灵活性。从应用程序的角度来看，一个分区表，非分区表完全相同，使用SQL查询和DML语句访问分区表时，无需作任何修改。2.分区键分区表的行必须明确的存储在单一的分区，分区键可以由一个或多个列组成，用来决定行所存储的分区，Oracle使用分区键来自动确定Insert、Update、Delete操作所对应的分区。如课程“索引及优化之表分析”中“用户交易表”基于年度、月度和日期建立分区，则该三列构成分区键，用来决定DML操作对应的分区。3.分区表表可以被分割为无数个分区，但如果表中包含LONG或LONG RAW类型的列是则不可分区，而CLOB和BLOB类型的列是支持分区的。为了减少磁盘和内存使用情况（特别是高速缓存），可以将表和分区的分区表进行压缩，表压缩也可以大大节约存储空间、加快查询执行速度。然而，有轻微的增加CPU的负担。压缩适合的情况是数据仓库环境，即读多写少的情况。建议考虑使用表分区的情形： (1).表的大小超过2GB时，或超过万行记录时 (2).表中包含历史数据，新数据会被写入新分区时 (3).表的内容必须被分布在不同类型的存储设备时建议考虑使用索引分区的情形： (1).对表数据进行迁移并且希望避免重建索引时 (2).对部分数据进行维护，而不希望维护期间使整个索引无效时 (3).减少索引中具有单一递增值的列引起的索引偏移时 4.分区与LOB类型数据 LOB类型列中存储的非结构化数据（图片、文档等）也能分区。当进行分区时，LOB类型的列存储在子集的表空间，且列存储在分区所在的表空间中。此技术在存储有大对象数据且不经常更新，而其他数据会经常被更新时非常高效，原因是将大对象数据与主数据进行了分离的存储。如员工记录数据，照片是大对象。分区的好处 1.提高性能分区通过限定操作数据的数量、对并行执行的数据是分布式的，因此带来了成倍的性能提升，主要包括： (1).分区修剪分区修剪是最简单也是最本质的性能提高手段，原因是限定了数据范围。常常可以将查询性能提高几个数量级。如某个订单表按月分区,该表中存有2年的数据，现在查询某个月度的订单数，则只需访问访问一个分区，而不是24个分区，因此性可能会提升20倍。 (2).智能化分区连接多表连接时，该技术能在下列两种情形下会得到应用 1).A表与B表连接，A表的键是colS，B表的键也是colS，两个表的分区均是基于列colS，且连接条件是A.colS= A.colS 2).参考完整性中的参考分区表与其父表连接此时智能化分区连接技术能将一个大的连接打破使用一个小的连接，从而节省时间。该技术无论是在串行化的还是在并行化的执行中具有重要意义。 2.易管理性由于分区后的存储是单个单元的，也就是提供了“分而治之”的管理方式，因此可以对单个分区而不是整个表进行维护操作，如备份等，

3.可用性不同的分区是独立的，因此提供可高可用性。如某个分区损坏，但其他分区依然可以使用，而不是整个表不能被使用。每个分区也可以存储在不同的表空间中，每个分区也可以设置不同的存储参数。分区策略概述 Oracle分区提供了三种基本的数据分区方法作为基本的分区策略，控制如何将数据放入单独的分区： l范围Range l哈希Hash l列表List 使用三种基本的分区方法，表可以被分区成单一分区列表或复合分区表，分别称为单级分区和组合分区。每个分区策略都有不同的优点和设计考虑。因此，不同的策略适合不同特定的情况。分区表环境准备 1.先备份前面的30课的数据 2.只准备使用表ACCOUNT_TRADE中的数据单级分区使用范围、哈希、列表三种分区策略的任何一中，在表的一个或多个列上建立分区。

1.范围分区策略－策略说明依据分区键值的范围不同进行分区，当数据在范围内均衡分布时，性能最好，是最常见的分区策略，一般与日期相关。该策略的每个分区均具有一个VALUES LESS THAN子句，用来指定该分区包含的范围数据的上限。任何分区键的值大于等于该值时被添加到下一个更高的分区。所有分区，除了第一个，有一个隐含的下限为前一个分区的VALUES LESS THAN值。此外，包含一个MAXVALUE用来义为最高的分区。 MAXVALUE代表一个虚拟的无限大的值，包括NULL值。 1.范围分区策略—语法1.范围分区策略—语法注意事项如果基于多个列创建分区，列之间用逗号分隔；值部分也是用逗号分隔，且该值必须与列的类型相同。值部分也可以使用函数，如TO_DATE等。还要注意LESS THAN相当于小于号（<），如： partition by range(ND,YD ) ( partition ACCOUNT_TRADE_SR_P201001 values less than(2010,'02'), partition ACCOUNT_TRADE_SR_P201002 values less than(2010,'03'), partition ACCOUNT_TRADE_SR_P201003 values less than(2010,'04'), partition ACCOUNT_TRADE_SR_P201004 values less than(2010,'05'), partition ACCOUNT_TRADE_SR_P201005 values less than(2010,'06'), partition ACCOUNT_TRADE_SR_P201006 values less than(2010,'07'), partition ACCOUNT_TRADE_SR_P201007 values less than(2010,'08'), partition ACCOUNT_TRADE_SR_P201008 values less than(2010,'09'), partition ACCOUNT_TRADE_SR_P201009 values less than(2010,'10'), partition ACCOUNT_TRADE_SR_P201010 values less than(2010,'11'), partition ACCOUNT_TRADE_SR_P201011 values less than(2010,'12'), partition ACCOUNT_TRADE_SR_P201012 values less than(,'01'), partition ACCOUNT_TRADE_SR_PMORE values less than(MAXVALUE,MAXVALUE)); 1.范围分区策略—示例体验 l 创建一个结构类似表ACCOUNT_TRADE的单级范围分区表ACCOUNT_TRADE_SR l从表ACCOUNT_TRADE读取的数据写入表ACCOUNT_TRADE_SR l分析表ACCOUNT_TRADE_SR l查看分区数据并将两个表的数据进行对比参见附件脚本3

1.范围分区策略—示例体验2.散列分区策略－策略说明依据Oracle的散列分区算法将数据映射到分区上，该算法不可人工改变。散列分区会尽量将数据均匀的分布在不同的分区上。当业务数据中没有历史数据时，选择散列分区是一个比较好的替代方法。这个分区方法能够保持I/O平衡，但是不可用于范围查询或不等式查询。注意：Oracle推荐Hash分区的分区数是2的幂，如2、4、8、16等。 2.散列分区策略－语法2.散列分区策略－语法注意事项单独散列分区（individual_hash_partitions）及其限制使用子名按照名字指定单个分区，分区名可以匆略。此时能指定的唯一子句是表空间。按照数量的散列分区（hash_partitions_by_quantity）可以指定分区的数目，Oracle此时指定分区的名字SYS_Pn。STORE IN子句指定分区所分布的表空间的名称。表空间的数目不一定和分区的数目相等。如果分区的数目比表空间的数目大，则分区按照名称循环使用表空间。如果在按数量分区时指了STORE IN和分区存储子句的表空间，则SOTRE IN指定了表创建分区的位置，TABLESPACE子句指定随后操作的缺省表空间。此时语句partitions后面只能跟数字，一般为2的幂，否则会引起ORA-14152错误。 2.散列分区策略—按照数量分区示例体验表为ACCOUNT_TRADE_SHQ，方法同前面，分区语句如下： partition by hash ( JYSJ ) partitions 4 store in ( TS_ORALEARN, TS_ORALEARN_IDX ); 参见附件脚本42.散列分区策略—单独散列分区示例体验表为ACCOUNT_TRADE_SHI，方法同前面，分区语句如下： partition by hash( JYSJ )( partition ACCOUNT_TRADE_SHI_P1 tablespace TS_ORALEARN, partition ACCOUNT_TRADE_SHI_P2 tablespace TS_ORALEARN_IDX, partition ACCOUNT_TRADE_SHI_P3 tablespace TS_ORALEARN, partition ACCOUNT_TRADE_SHI_P4 tablespace TS_ORALEARN ); 参见附件脚本53.列表分区策略－策略说明列表分区通过为分区键指定一系列离散的值（即枚举值）来明确的控制行数据的存储分区，其优点是可以将一些无序和不相关的数据组织在一起。如，银行系统的数据可以按区域分区。每个分区的list_values_clause（参见语法说明部分）最少有一个值。空值可能在多个分区中出现。你可以指定一个缺省分区，且缺省分区是定义分区的最后一个。 list_values_clause的每个值在表的所有分区必须唯一。 3.列表分区策略－语法3.列表分区策略—示例体验表为ACCOUNT_TRADE_SL，方法同前面，分区语句如下： partition by list ( ND ) ( partition ACCOUNT_TRADE_SL_P2010 values ( 2010 ) tablespace TS_ORALEARN, partition ACCOUNT_TRADE_SL_P2011 values ( 2011 ) tablespace TS_ORALEARN, partition ACCOUNT_TRADE_SL_PD values ( DEFAULT ) tablespace TS_ORALEARN_IDX ); 参见附件脚本6

篇5：分区索引（二）本地分区索引

可以使用与表相同的分区键和范围界限来对本地索引分区，每个本地索引的分区只包含了它所关联的表分区的键和ROWID。本地索引可以是B树或位图索引。如果是B树索引，它可以是唯一或不唯一的索引。

这种类型的索引支持分区独立性，这就意味着对于单独的分区，可以进行增加、截取、删除、分割、脱机等处理，而不用同时删除或重建索引。Oracle自动维护这些本地索引。本地索引分区还可以被单独重建，而其他分区不会受到影响。

（1）有前缀的索引

有前缀的索引包含了来自分区键的键，并把它们作为索引的前导。例如，让我们再次回顾participant表。在创建该表后，使用survey_id和survey_date这两个列进行范围分区，然后在survey_id列上建立一个有前缀的本地索引。这个索引的所有分区都被等价划分，就是说索引的分区都使用表的相同范围界限来创建。

技巧：本地的有前缀索引可以让Oracle快速剔除一些不必要的分区。也就是说没有包含WHERE条件子句中任何值的分区将不会被访问，这样也提高了语句的性能。

（2）无前缀的索引

无前缀的索引并没有把分区键的前导列作为索引的前导列。若使用有同样分区键(survey_id和survey_date)的相同分区表，建立在survey_date列上的索引就是一个本地的无前缀索引。可以在表的任一列上创建本地无前缀索引，但索引的每个分区只包含表的相应分区的键值。

如果要把无前缀的索引设为唯一索引，这个索引就必须包含分区键的子集。在这个例子中，我们必须把包含survey 和(或)survey_id 的列进行组合(只要survey_id不是索引的第一列，它就是一个有前缀的索引)。

技巧：对于一个唯一的无前缀索引，它必须包含分区键的子集。

（3）注意事项：

1)局部索引一定是分区索引，分区键等同于表的分区键。

2)前缀和非前缀索引都可以支持索引分区消除，前提是查询的条件中包含索引分区键。

3)局部索引只支持分区内的唯一性，无法支持表上的唯一性，因此如果要用局部索引去给表做唯一性约束，则约束中必须要包括分区键列。

4)局部分区索引是对单个分区的，每个分区索引只指向一个表分区；全局索引则不然，一个分区索引能指向n个表分区，同时，一个表分区，也可能指向n个索引分区，对分区表中的某个分区做truncate 或者move，shrink等，可能会影响到n个全局索引分区，正因为这点，局部分区索引具有更高的可用性。

5)位图索引必须是局部分区索引。

6)局部索引多应用于数据仓库环境中。

（4）示例：

sql> create index ix_custaddr_local_id on custaddr(id) local;

索引已创建。

和下面SQL 效果相同，因为local索引就是分区索引：

create index ix_custaddr_local_id_p on custaddr(id)

local (

partition t_list556 tablespace icd_service,

partition p_other tablespace icd_service

)

SQL> create index ix_custaddr_local_areacode on custaddr(areacode) local;

索引已创建，

验证2个索引的类型：

SQL> select index_name,table_name,partitioning_type,locality,ALIGNMENT from

user_part_indexes where table_name='CUSTADDR';

index_name table_name partition locali alignment

------------------------------ ---------- --------- ------ ------------

ix_custaddr_local_areacode custaddr list local prefixed

ix_custaddr_local_id custaddr list local non_prefixed

因为我们的custaddr 表是按areacode 进行分区的，所以索引ix_custaddr_local_areacode 是有前缀的索引（prefixed）。而 ix_custaddr_local_id是非前缀索引。

篇6：oracle数据库索引失效

这篇文章主要介绍了oracle数据库索引失效的原因及如何避免索引失效，有需要的小伙伴参考下，

今天一个同事突然问我索引为什么失效。说实在的，失效的原因有多种：

但是如果是同样的sql如果在之前能够使用到索引，那么现在使用不到索引，以下几种主要情况:

1. 随着表的增长，where条件出来的数据太多，大于15%，使得索引失效（会导致CBO计算走索引花费大于走全表）

2. 统计信息失效需要重新搜集统计信息

3. 索引本身失效需要重建索引

下面是一些不会使用到索引的原因

索引失效

1) 没有查询条件，或者查询条件没有建立索引

2) 在查询条件上没有使用引导列

3) 查询的数量是大表的大部分，应该是30％以上。

4) 索引本身失效

5) 查询条件使用函数在索引列上（见12）

6) 对小表查询

7) 提示不使用索引

8) 统计数据不真实

9) CBO计算走索引花费过大的情况。其实也包含了上面的情况，这里指的是表占有的block要比索引小。

10)隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误. 由于表的字段tu_mdn定义为varchar2(20),

但在查询时把该字段作为number类型以where条件传给Oracle,这样会导致索引失效.

错误的例子：select * from test where tu_mdn=13333333333;

正确的例子：select * from test where tu_mdn=‘13333333333‘;

11)对索引列进行运算导致索引失效,我所指的对索引列进行运算包括(+，-，*，/，! 等)

错误的例子：select * from test where id-1=9;

正确的例子：select * from test where id=10;

12)使用Oracle内部函数导致索引失效.对于这样情况应当创建基于函数的索引.

错误的例子：select * from test where round(id)=10;

说明，此时id的索引已经不起作用了正确的例子：首先建立函数索引，

create index test_id_fbi_idx on test(round(id));

然后 select * from test where round(id)=10; 这时函数索引起作用了 1, 2,单独的>,<,(有时会用到，有时不会)

3,like “%_” 百分号在前.

4,表没分析.

5,单独引用复合索引里非第一位置的索引列.

6,字符型字段为数字时在where条件里不添加引号.

7,对索引列进行运算.需要建立函数索引.

8,not in ,not exist.

9,当变量采用的是times变量，而表的字段采用的是date变量时.或相反情况，

10, 索引失效。

11,基于cost成本分析(oracle因为走全表成本会更小)：查询小表,或者返回值大概在10%以上

12,有时都考虑到了但就是不走索引,drop了从建试试在

13,B-tree索引 is null不会走,is not null会走,位图索引 is null,is not null 都会走

14,联合索引 is not null 只要在建立的索引列（不分先后）都会走,

in null时必须要和建立索引第一列一起使用,当建立索引第一位置条件是is null 时,

其他建立索引的列可以是is null（但必须在所有列都满足is null的时候）,

或者=一个值；当建立索引的第一位置是=一个值时,其他索引列可以是任何情况（包括is null =一个值）,

以上两种情况索引都会走。其他情况不会走。

篇7：分区索引笔记（三）全局分区索引

全局分区索引在一个索引分区中包含来自多个表分区的键，一个全局分区索引的分区键是分区表中不同的或指定一个范围的值。在创建全局分区索引时，必须定义分区键的范围和值。

全局索引只能是B树索引。Oracle在默认情况下不会维护全局分区索引。如果一个分区被截取、增加、分割、删除等，就必须重建全局分区索引，除非在修改表时指定ALTER TABLE命令的UPDATE GLOBAL INDEXES子句。

1、种类

（1）有前缀的索引

通常，全局有前缀索引在底层表中没有经过对等分区。没有什么因素能限制索引的对等分区，但Oracle在生成查询计划或执行分区维护操作时，并不会充分利用对等分区。如果索引被对等分区，就必须把它创建为一个本地索引，这样Oracle可以维护这个索引，并使用它来删除不必要的分区，每个分区都包含指向多个表分区中行的索引条目。

技巧：如果一个全局索引将被对等分区，就必须把它创建为一个本地索引，这样Oracle可以维护这个索引，并使用它来删除不必要的分区。

（2）无前缀的索引

到目前为止(10gR2)，Oracle不支持无前缀的全局索引。

2.、注意事项：

（1）全局索引可以分区，也可以是不分区索引，全局索引必须是前缀索引，即全局索引的索引列必须是以索引分区键作为其前几列。

（2）全局索引可以依附于分区表；也可以依附于非分区表。

（3）全局分区索引的索引条目可能指向若干个分区，因此，对于全局分区索引，即使只截断一个分区中的数据，都需要rebulid若干个分区甚至是整个索引。

（4）全局索引多应用于oltp系统中。

（5）全局分区索引只按范围或者散列分区，hash分区是10g以后才支持。

（6） oracle9i 以后对分区表做move或者truncate 的时可以用update global indexes 语句来同步更新全局分区索引，用消耗一定资源来换取高度的可用性。

（7）表用a列作分区，索引用b做局部分区索引，若where条件中用b来查询，那么oracle会扫描所有的表和索引的分区，成本会比分区更高，此时可以考虑用b做全局分区索引。

3、实验

示例1 全局索引，全局索引对所有分区类型都支持：

sql> create index ix_custaddr_ global_id on custaddr(id) global;

索引已创建。

示例2：全局分区索引，只支持Range 分区和Hash 分区：

（1）创建2个测试分区表：

sql> create table pdba (id number, time date) partition by range (time)

(

partition p1 values less than (to_date('-10-1', 'yyyy-mm-dd')),

partition p2 values less than (to_date('2010-11-1', 'yyyy-mm-dd')),

partition p3 values less than (to_date('2010-12-1', 'yyyy-mm-dd')),

partition p4 values less than (maxvalue)

);

表已创建，

SQL> create table Thash

(

id number primary key,

item_id number(8) not null

)

partition by hash(id)

(

partition part_01,

partition part_02,

partition part_03

);

表已创建。

（2）创建分区索引

示例2：全局分区索引

SQL> create index i_id_global on PDBA(id) global

partition by range(id)

(partition p1 values less than (200),

partition p2 values less than (maxvalue)

);

索引已创建。

-这个是有前缀的分区索引。

篇8：Oracle的索引原理数据库

Oracle提供了大量索引选项，知道在给定条件下使用哪个选项对于一个应用程序的性能来说非常重要。一个错误的选择可能会引发死锁，并导致数据库性能急剧下降或进程终止。而如果做出正确的选择，则可以合理使用资源，使那些已经运行了几个小时甚至几天的进

Oracle提供了大量索引选项。知道在给定条件下使用哪个选项对于一个应用程序的性能来说非常重要。一个错误的选择可能会引发死锁，并导致数据库性能急剧下降或进程终止。而如果做出正确的选择，则可以合理使用资源，使那些已经运行了几个小时甚至几天的进程在几分钟得以完成，这样会使您立刻成为一位英雄。这篇文章就将简单的讨论每个索引选项。主要有以下内容：

[1] 基本的索引概念

查询DBA_INDEXES视图可得到表中所有索引的列表，注意只能通过USER_INDEXES的方法来检索模式(schema)的索引。访问USER_IND_COLUMNS视图可得到一个给定表中被索引的特定列。

[2] 组合索引

当某个索引包含有多个已索引的列时，称这个索引为组合（concatented）索引。在 Oracle9i引入跳跃式扫描的索引访问方法之前，查询只能在有限条件下使用该索引。比如：表emp有一个组合索引键，该索引包含了empno、ename和deptno。在Oracle9i之前除非在where之句中对第一列（empno）指定一个值，否则就不能使用这个索引键进行一次范围扫描。

特别注意：在Oracle9i之前，只有在使用到索引的前导索引时才可以使用组合索引！

[3] ORACLE ROWID

通过每个行的ROWID，索引Oracle提供了访问单行数据的能力。ROWID其实就是直接指向单独行的线路图。如果想检查重复值或是其他对ROWID本身的引用，可以在任何表中使用和指定rowid列。

[4] 限制索引

限制索引是一些没有经验的开发人员经常犯的错误之一。在SQL中有很多陷阱会使一些索引无法使用。下面讨论一些常见的问题：

4.1 使用不等于操作符（、!=）

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

select cust_Id,cust_name

from customers

where cust_rating 'aa';

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

select cust_Id,cust_name

from customers

where cust_rating < 'aa' or cust_rating > 'aa';

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

4.2 使用IS NULL 或IS NOT NULL

使用IS NULL 或IS NOT NULL同样会限制索引的使用。因为NULL值并没有被定义。在SQL语句中使用NULL会有很多的麻烦。因此建议开发人员在建表时，把需要索引的列设成NOT NULL。如果被索引的列在某些行中存在NULL值，就不会使用这个索引（除非索引是一个位图索引，关于位图索引在稍后在详细讨论）。

4.3 使用函数

如果不使用基于函数的索引，那么在SQL语句的WHERE子句中对存在索引的列使用函数时，会使优化器忽略掉这些索引。

下面的查询不会使用索引（只要它不是基于函数的索引）

select empno,ename,deptno

from emp

where trunc(hiredate)='01-MAY-81';

把上面的语句改成下面的语句，这样就可以通过索引进行查找。

select empno,ename,deptno

from emp

where hiredate<(to_date('01-MAY-81')+0.9999);

4.4 比较不匹配的数据类型

比较不匹配的数据类型也是比较难于发现的性能问题之一。注意下面查询的例子，aclearcase/“ target=”_blank" >ccount_number是一个VARCHAR2类型，在account_number字段上有索引。下面的语句将执行全表扫描。

select bank_name,address,city,state,zip

from banks

where account_number = 990354;

Oracle可以自动把where子句变成to_number(account_number)=990354，这样就限制了索引的使用,改成下面的查询就可以使用索引：

select bank_name,address,city,state,zip

from banks

where account_number ='990354';

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

共2页: 1 [2] 下一页

原文转自：www.ltesting.net

篇9：改善Oracle的索引数据库

当我们使用CREATE INDEX来创建索引时， Oracle 提供了很多参数来改善创建索引的速度、索引所占用的空间和索引树的层次， 1、速度因素 PARALLEL选项：当创建索引时，Oracle首先会进行全表扫描来收集符号键和ROWID对，而PARALLEL选项允许多处理器并行扫描表，

当我们使用CREATE INDEX来创建索引时，Oracle提供了很多参数来改善创建索引的速度、索引所占用的空间和索引树的层次。

1、速度因素

PARALLEL选项：当创建索引时，Oracle首先会进行全表扫描来收集符号键和ROWID对，而PARALLEL选项允许多处理器并行扫描表，这样就会加速索引的创建过程，

一般推荐的PARALLEL值为CPU数减1。

NOLOGGING选项：NOLOGGING选项因为不写日志，所以大大提高了性能，比不使用NOLOGGING选项大约会节省70%的时间。使用NOLOGGING创建索引的唯一风险就是如果数据库需要做前滚恢复，你将不得不重建索引。

2、空间和结构因素

COMPRESS选项：COMPRESS选项用于在创建非唯一性索引时压缩重复值。对于连接索引（即索引包含多列）来说，COMPRESS选项会使索引的大小减少一半以上。COMPRESS选项也可以设置连接索引的前置长度。

使用大BLOCKSIZE的表空间：将索引建立在大BLOCKSIZE的表空间上，将大大降低索引树的层次。

原文转自：www.ltesting.net

篇10：Oracle索引的学习总结

Oracle索引的学习总结

在Oracle中，索引基本分为以下几种：B*Tree索引，反向索引，降序索引，位图索引，函数索引，interMedia全文索引等，其中最常用的是B*Tree索引和Bitmap索引。

(1)、与索引相关视图

查询DBA_INDEXES视图可得到表中所有索引的列表；访问USER_IND_COLUMNS视图可得到一个给定表中被索引的特定列。

(2)、组合索引概念

当某个索引包含有多个已索引的列时，称这个索引为组合（concatented）索引。

注意：只有在使用到索引的前导索引时才可以使用组合索引

(3)、B*Tree索引

B*Tree索引是最常见的索引结构，默认建立的索引就是这种类型的索引。B*Tree索引在检索高基数数据列（高基数数据列是指该列有很多不同的值）时提供了最好的性能。

DML语句：

Create index indexname on tablename(columnname[columnname...])

B-tree特性：

适合与大量的增、删、改（OLTP）；

不能用包含OR操作符的查询；

适合高基数的列（唯一值多）；

典型的树状结构；

每个结点都是数据块；

大多都是物理上一层、两层或三层不定，逻辑上三层；

叶子块数据是排序的，从左向右递增；

在分支块和根块中放的是索引的范围。

(4)、Bitmap索引

位图索引主要用于决策支持系统或静态数据，不支持行级锁定。位图索引最好用于低cardinality列（即列的唯一值除以行数为一个很小的值，接近零）。

DML语句：

Create BITMAP index indexname on tablename(columnname[columnname...])

Bitmap特性:

适合与决策支持系统；

做UPDATE代价非常高；

非常适合OR操作符的查询；

基数比较少的时候才能建位图索引。

(5)、B*tree和Bitmap的不同

在一颗 B* 树中，通常索引条目和行之间存在一种一对一的关系：一个索引条目就指向一行；而对于位图索引，一个索引条目则使用一个位图同时指向多行。

位图索引适用于高度重复而且通常只读的数据（高度重复是指相对于表中的总行数，数据只有很少的几个不同值）。B*tree索引的话通常在访问小数据量的情况下比较适用，比如你访问不超过表中数据的'5%，适用于一般的情况；bitmap的话在数据仓库中使用较多，用于低基数列，比如性别之类重复值很多的字段，基数越小越好。

(6)、导致索引失效的情况

使用不等于操作符（、!=）

通常把不等于操作符改成OR条件，就可以使用索引，以避免全表扫描

使用IS NULL 或IS NOT NULL

使用IS NULL 或IS NOT NULL同样会限制索引的使用。因为NULL值并没有被定义。在SQL语句中使用NULL会有很多的麻烦。因此建议开发人员在建表时，把需要索引的列设成NOT NULL。如果被索引的列在某些行中存在NULL值，就不会使用这个索引（除非索引是一个位图索引）。

使用函数

如果不使用基于函数的索引，那么在SQL语句的WHERE子句中对存在索引的列使用函数时，会使优化器忽略掉这些索引。

比较不匹配的数据类型

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

复合索引中的前导列没有被作为查询条件

复合索引中，一定要将前导列作为查询条件，索引才会被使用

CBO模式下选择的行数比例过大，优化器采取了全表扫描

这是基于代价的优化考虑

篇11：关于oracle分区技术初了解

一、分区类型

1. 范围分区(Range Partitioning)

适用于连续/按时间排序的数据

2. 散列分区(Hash Partitioning)

适用于不连续/数据记录固定的数据

3. 组合分区 Range-Hash

Range-List

4. 列表分区 List Partitioning

适用于对不连续域的数据分区

更准确的控制数据的分区存储

适用于位置类数据

二、分区表的维护

准备工作

SYS@ORA11G>create tablespace sales_ts012 datafile'/u01/app/oracle/oradata/ORA11G/sales_ts01_01.dbf' size 10m;Tablespace created.SYS@ORA11G>create tablespace sales_ts022 datafile'/u01/app/oracle/oradata/ORA11G/sales_ts02_01.dbf' size 10m;Tablespace created.SYS@ORA11G>create tablespace sales_ts032 datafile'/u01/app/oracle/oradata/ORA11G/sales_ts03_01.dbf' size 10m;Tablespace created.SYS@ORA11G>create tablespace sales_ts042 datafile'/u01/app/oracle/oradata/ORA11G/sales_ts04_01.dbf' size 10m;Tablespace created.SYS@ORA11G>create tablespace sales_ts052 datafile'/u01/app/oracle/oradata/ORA11G/sales_ts05_01.dbf' size 10m;Tablespace created.SYS@ORA11G>create tablespace sales_ts062 datafile'/u01/app/oracle/oradata/ORA11G/sales_ts06_01.dbf' size 10m;Tablespace created.SYS@ORA11G>SYS@ORA11G>SYS@ORA11G>SYS@ORA11G>conn tyger/tygerConnected.TYGER@ORA11G>TYGER@ORA11G>TYGER@ORA11G>CREATE TABLE SALES(PROD_ID NUMBER,CUST_ID NUMBER,TIME_ID DATE,CHANNEL_ID NUMBER,PROMO_ID NUMBER,QUANTITY_SOLD NUMBER(10,2),AMOUNT_SOLD NUMBER(10,2))PARTITION BY RANGE(TIME_ID)(PARTITION sales01 values less than ('01-Feb-2004')TABLESPACE SALES_TS01,PARTITION sales02 values less than ('01-Mar-2004')TABLESPACE SALES_TS02,PARTITION sales03 values less than ('01-Apr-2004')TABLESPACE SALES_TS03,PARTITION sales04 values less than ('01-May-2004')TABLESPACE SALES_TS04,PARTITION sales05 values less than ('01-Jun-2004')TABLESPACE SALES_TS05,PARTITION sales06 values less than ('01-Jul-2004')TABLESPACE SALES_TS06); 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17Table created.TYGER@ORA11G>selectTABLE_NAME,PARTITIONING_TYPE,SUBPARTITIONING_TYPE,STATUS2 from user_part_tables;TABLE_NAME PARTITION SUBPARTIT STATUS------------------------------ --------- -----------------SALES RANGE NONE VALIDTYGER@ORA11G>col table_name for a20TYGER@ORA11G>col tablespace_name for a20TYGER@ORA11G>l1 selectTABLE_NAME,PARTITION_NAME,TABLESPACE_NAME2* fromuser_tab_partitionsTYGER@ORA11G>/TABLE_NAME PARTITION_NAME TABLESPACE_NAME-------------------- --------------------------------------------------SALES SALES01 SALES_TS01SALES SALES02 SALES_TS02SALES SALES03 SALES_TS03SALES SALES04 SALES_TS04SALES SALES05 SALES_TS05SALES SALES06 SALES_TS066 rows selected.语法：ALTERTABLE ALTER INDEXADD PARTITION -DROP PARTITIONCOALESCE PARTITION - MODIFY PARTITIONDROP PARTITION - MODIFY DEFAULT ATTRIBUTESEXCHANGE PARTITION - MODIFY PARTITION COALESCEMERGE PARTITIONS - REBUILD PARTITIONMODIFY PARTITION - RENAME PARTITIONMODIFY DEFAULT -SPLIT PARTITIONATTRIBUTES - UNUSABLEMOVE PARTITIONRENAME PARTITIONSPLIT PARTITION

2.1 删除表分区

Alter table sales droppartition sales01;TYGER@ORA11G>alter table sales drop partitionsales01;Table altered.TYGER@ORA11G>selectTABLE_NAME,PARTITION_NAME,TABLESPACE_NAME2 from user_tab_partitions;TABLE_NAME PARTITION_NAME TABLESPACE_NAME-------------------- --------------------------------------------------SALES SALES02 SALES_TS02SALES SALES03 SALES_TS03SALES SALES04 SALES_TS04SALES SALES05 SALES_TS05SALES SALES06 SALES_TS06

2.2 增加表分区

增加分区的分区范围必须比当前分区的最后一个分区更高

TYGER@ORA11G>alter table sales add partitionsales01 values less than ('01-Feb-2004') tablespace sales_ts01;alter table sales add partition sales01 values lessthan ('01-Feb-2004') tablespace sales_ts01*ERROR at line 1:ORA-14074: partition bound mustcollate higher than that of the last partitionTYGER@ORA11G>alter table sales add partitionsales012 values less than ('01-Aug-2004') tablespacesales_ts01;Table altered.TYGER@ORA11G>selecttable_name,partition_name,tablespace_name2 from user_tab_partitions;TABLE_NAME PARTITION_NAME TABLESPACE_NAME-------------------- --------------------------------------------------SALES SALES02 SALES_TS02SALES SALES03 SALES_TS03SALES SALES04 SALES_TS04SALES SALES05 SALES_TS05SALES SALES06 SALES_TS06SALES SALES01 SALES_TS01

2.3 合并分区

必须是相邻的范围分区

继承最大的范围边界

TYGER@ORA11G>alter table sales2 merge partitions sales06,sales01 intopartition sales07;Table altered.TYGER@ORA11G>select table_name,partition_name,tablespace_name2 from user_tab_partitions;TABLE_NAME PARTITION_NAME TABLESPACE_NAME-------------------- --------------------------------------------------SALES SALES07 USERSSALES SALES02 SALES_TS02SALES SALES03 SALES_TS03SALES SALES04 SALES_TS04SALES SALES05 SALES_TS05

2.4 移动表分区

移动分区数据到另一个表空间

重新整理数据减少碎片

改变物理属性

TYGER@ORA11G>alter table sales move partitionsales012 tablespace sales_ts_move;

2.5 拆分表分区

拆分表分区———一个分区变的太大，导致备份，恢复和分区性能操作花费时间太长，重新分配I/O负载，

关于oracle分区技术初了解

，

TYGER@ORA11G>alter table sales split partition sales072 at ('01-Jul-2004') // 按哪个时间点拆分3 into (partition sales01 tablespacesales_ts01,4 partition sales06 tablespacesales_ts06);Table altered.TYGER@ORA11G>selecttable_name,partition_name,tablespace_name2 from user_tab_partitions;TABLE_NAME PARTITION_NAME TABLESPACE_NAME-------------------- --------------------------------------------------SALES SALES02 SALES_TS02SALES SALES03 SALES_TS03SALES SALES04 SALES_TS04SALES SALES05 SALES_TS05SALES SALES06 SALES_TS06SALES SALES01 SALES_TS016 rows selected.

★ 删除Oracle 9i数据库数据库教程

★ ORACLE NUMBER类型详解数据库教程

★ oracle经典20道笔试题

★ 探讨Oracle中的&号问题

★ Oracle常????}集(三)数据库教程

★ Word入门动画教程99：自动标记索引项