oracle index blevel 降低,Oracle reverse函数

reverse函数功能就是反向排列给定的数组元素

SQL>      create     or     replace     function     Re(s     string)     return     varchar2     is

Result     varchar2(1000);

v_s     string(1000);

begin

for     i     in     1..length(s)

loop

Result:=Result||substr(s,length(s)-i+1,1);

end     loop;

return(Result);

end     Re;

1.3.3 索引

Oracle 9i中有6种可以使用的索引：B-Tree索引(不指定任何选项的创建方法所创建)、压缩B树索引、Bitmap(位图)索引、基于函数的索引、 Reverse Key Index(RKI，反向键索引)、Index Organized Table(IOT，索引组织表)。

1.3.3.1 检查索引信息

 dba_indexes

index_type (normal-B树，normal/rev-反向键，function-based normal,bitmap,iot-top,cluster,lob)

compression

funcidx_status

 dba_segments

segment_name

segment_type

tablespace_name

 dba_tables

SQL> select table_name,iot_name,iot_type,tablespace_name from dba_tables where iot_type is not null;

就可以观察出IOT益出的情况。 Tablespace_name 为空，说明为一个索引而不是一个表。

 监视索引使用情况

如果一个索引永远得不到，应该删除：

1)消除了对被索引表DML操作时的索引维护系统开销。

2)释放表空间。

执行SQL>alter index c_unicom.idx_ne_cell monitoring usage;

一段时间后执行SQL> alter index unicom.idx_ne_cell nomonitoring usage;

此时查询v$object_usage(Oracle 9i)的used列，YES表示监视期间至少得到过一次访问。NO表示未得到访问。

1.3.3.2 B-Tree索引

 基础概念

也叫平衡树索引。

是按升序对被索引的列进行排序，同时存储一个行ID，用来指出该行的其他数据的物理存储地，Oracle以一种树型结构来存储这些数。

查询时，首先搜索该多级树结构形式组织的索引，然后按ID访问需要的表数据。

1)如果表数据行很多，只返回一个表中的少数行(Oracle建议是5%)。

2)具有高基数的列。且这些列经常被用在where条件中。

此时Oracle建议创建一个B树索引。

如：Create index idx_ne_cell on ne_cell(ne_id) tablespace unicom_dbs;

 判断是否需要重建索引

B树索引设计为总是平衡的，Oracle随着insert操作而连续的分裂索引块，但是，随着insert和delete操作，该索引会产生许多级，增加索引从根遍历的时间，降低了效率，此时需要重建索引。

Oracle建议：

1)当级别深度到4或超过4时。(增加了从根遍历的时间)

索引被分析后，执行select index_name,blevel from dba_indexes where blevel >=4;检查

2)被删除数占总数的20%以上时。(增加了访问某项所访问的index块数)

- 执行analyze index idx_ne_cell validate structure;填充表index_stats数据。

- 执行select (del_lf_rows_len/lf_rows_len)*100 “Wasted Space”

from index_stats where name=’IDX_NE_CELL’;

Column Description

LF_ROWS Number of values currently in the index

LF_ROWS_LEN Sum in bytes of the length of all values

DEL_LF_ROWS Number of values deleted from the index

DEL_LF_ROWS_LEN Length of all deleted values

 重建索引

在一个不稳定的表上建索引会影响性能，一个索引block只有完全空时才能进入free list，即使一个索引block里只含有一个条目，它也必须被维护，因此索引需要进行阶段性的重建。

- 删除该index并执行create index ...开始创建。

是最耗时的方法，也是资源密集型。但7.3前版本只能这么做。

- alter index .. rebuild tablespace ..;

使用现有索引项来重建索引，很快。同时能把索引转移到其他表空间。但创建的过程中，新旧索引同时存在，要求有额外的临时空间可用。

SQL>alter index idx_ne_cell rebuild online;

使用online来尽可能减少创建过程中出现的任何加锁问题。

- alter index .. coalesce;

只是将处于同一个索引分支内的叶块拼合起来，很快，不要求额外的磁盘空间，最大限度的减少了创建过程中出现的任何加锁问题。但此方法不能转移索引到另外一个表空间。

更多关于alter index 参考 9.0.1 A901177-01的16章。

1.3.3.3 压缩B树索引

如果B树索引非常大，此时就建议使用压缩B树索引了。

压缩B树索引：在索引列存在重复值时，只记录一个值和不同的row id，减少了重复的列值的存储(同理，基数非常大或列值都唯一的，压缩就没多少必要了)。最大限度的减少了B树索引占用的磁盘空间。同时索引扫描也会访问更少的数据库块，减少I/O。

压缩语句：

SQL>create index idx_alarminfo on alarminfo(ALARMOBJECT) tablespace unicom_dbs compress;

或：

SQL>alter index idx_alarminfo rebuild compress;

1.3.3.4 Bitmap(位图)索引

创建表行的一个二进制映像，比如是存储1，不是存储0。所以占用更少的存储空间。

1)位图索引检索、匹配的行会更快。

2)对于底基数列，工作的更好。(高基数，B树好)

3)被索引表要DML操作少、长度大、有极少不同值的表列上极有用。(DML操作会引起位图级的加锁发生，而且要求重建所有可能值的整个位图；即使发生很多行被操作，但只按一次DML进行更新)

4)适合在决策支持系统或数据仓库使用。

SQL>create bitmap index idx_ne_cell on ne_cell(related_bsc);

//加大sort_area_size和pga_aggregate_target通常会加快bitmap index的创建和操作。

1.3.3.5 基于函数的索引

函数索引适用于B树索引和bitmap索引。但必须把init.ora的query_rewrite_enabled参数设置成true才可使用。(默认是false)

在SQL语句的WHERE字句中有任一函数或操作被运用于一个索引列时，执行计划将使用全表扫描而不会使用非函数索引。

如：select last_name,first_name from employee

where upper(first_name) = ‘REGINALD’;

或：select product_id,units,price,price*units from sales

where (price*units) > 10000;

都不会使用first_name或price或units列上的索引。

如下创建函数索引后优化程序才会考虑使用索引：

SQL>create index idx_first_name_employee on employee (upper(first_name));

SQL>create index idx_sales_total on sales (price*units) tablespace ind_dbs;

1.3.3.6 Reverse Key Index

属于B树索引。

RKI：首先反向每个列键值的字节(如123，就被反为321，新数值在范围上，比原来那些列值会分布的更均匀)，然后在反向后的数据上进行索引。

非常适用于含有序数的列。(因传统的B树，此时往往会产生很多级，而超过4级性能就会很低)

注意：RKI只能用于等于、不等于判断。其他如>、

SQL>create index idx_employee_iem_id on employee(emp_id)

Reverse

Tablespace idx_dbs;

或

SQL>alter index idx_employee_iem_id rebuild reverse;

1.3.3.7 Index Organized Table

B树、位图、反向等索引，直接指向索引基础表中对应数据行的ID，这是因为表行以一种或多或少的随机方式被分配给表内的块，因Oracle在存储行时，并不考虑行的内容。是一种堆叠方式存储的数据，叫Heap Table(堆表)。

如果希望按一种指定顺序来存储一个表数据，就要使用IOT(索引组织表)。此时把行数据全部存储在索引本身内。

好处：

1)表行按索引顺序存储。若用主键来访问表，IOT比传统表更快。

2)使用B树时，只需要读取索引，不需要读取表，减少了一次I/O。

注意：

1)IOT在将要做索引基础的那一列上必须有一个主键约束。

2)IOT不能含有唯一性约束，或被cluster。

SQL>create table employee_history

(employee_id number primary key,

last_name varchar2(20),

first_name varchar2(20),

title varchar2(30),

hire_date date,

departure_date date)

ORGANIZATION INDEX TABLESPACE idx_dbs --指定表为IOT

PCTTHRESHOLD 25 --指定真个数据块保持打开的百分比，必须在0-50之间

INCLUDEING first_name --指定行超过PCTTHRESHOLD时，按那一列把行分解成两段

OVERFLOW TABLESPACE app_of – 指定分解的行的一半存储的表空间

MAPPING TABLE; -- 在创建IOT上的位图所有时所必须的一个关联映像表的创建

位图索引可以以堆表(此时以行ID关联数据行)或IOT(利用映像表来定位被索引的IOT行)做为基础表。

映像表：把索引的物理行ID映像到索引组织表的对应的逻辑行ID，每个IOT只有一个。

物理行ID会随着数据的insert和delete而发生变化(因为排序了，IOT叶会在它们变满时像B树索引一样发生分裂；如果没有映像表记录逻辑ID，这种物理分裂会使相关位图索引不能使用)。

随着时间的推移，bitmap index可能会和IOT不一致，查找不一致范围：

SQL> select owner,index_name,pct_direct_access from dba_indexes

where pct_direct_access is not null;

Oracle建议，超过30%的bitmap index应该rebuild。

1.3.4 分区partition

分区：将表数据划分成更小的子集。

好处：

1)提高可用性。

如100GB的表，如果划分成2G的50个分区，只要当前查询的数据所处的分区online，而其他的分取offline，数据库依然工作正常。

2)减轻管理负担。

对分区数据的维护，比如删除、更新、分析、数据恢复等，可以以分区为单位进行维护。

3)提高DML和查询操作性能。

利用CBO的分区排除机制，跳过未含有相关数据的表分区，减少I/O。

分区表使用CBO，CBO具有很好的“分区意识能力”，直接排除不属于查询结果的分区。(分区键列上同样不能应用函数，否则不会使用索引)

对于分区的表，应尽可能的利用分区键作为查询条件，不然会执行full table scan，会比不分区的全表扫描花费更多的时间，失去分区的意义。

分区信息可查询：select * from user_tab_partitions;

(比如小区表，可以按小时分区，小时里再按hash分区防止分区不均匀，此时再对各分区进行truncate等维护，就快了)

1.3.4.1 创建分区

分区可分为如下几种：(范围分区，hash分区，列表分区，范围-hash分区，范围-列表分区(oracle 9i relase 2以后才提供))

 范围分区

使用列值的范围来确定一个数据行被插入到那个分区中。

1)分区键最多可16列，一个表最多可65636个分区。

2)分区范围中不能有任何空隙。

3)每个分区中，等于上限的值都是存储在上一个分区中的。

4)less then子句指定的值，必须是一个用RPAD函数填充过的值、日期(必须包含世纪)或常数。

5)分区表不能包含带有long,long raw的数据列。

6)试图插入一个无法放入任一分区的数据，都导致ORA-14400错误。

7)通过更新数据使数据在分区间移动不被允许，除非创建表的时候指定了enable row movement子局。

如果插入的数据导致分区不均匀，应考虑使用散列分区。

SQL>create table student_history

(student_id number(10),

degree varchar2(3),

gradution_date date)

PARTITION BY RANGE (graduation_date)

(PARTION p_2000 VALUES LESS THEN

(to_date(‘2000-1-1’,’yyyy-mm-dd’)) TABLESPACE hist_tab01,

PARTION p_2001 VALUES LESS THEN

(to_date(‘2001-1-1’,’yyyy-mm-dd’)) TABLESPACE hist_tab02,

PARTION p_error VALUES LESS THEN

(MAXVALUE) TABLESPACE hist_tab03

);

SQL> select * from student_history (p_2000);

 列表分区

类似范围分区，区别是它基于一组指定的值。范围分区是基于一个范围。

试图插入一个无法放入任一分区的数据，都导致ORA-14400错误。

SQL>create table student_history

(student_id number(10),

degree varchar2(3),

gradution_date date)

PARTITION BY LIST (degree)

(PARTION p_undergrad VALUES (‘BS’,’BA’,’BBA’,’BFA’)

TABLESPACE hist_tab01,

PARTION p_graduate VALUES (‘MA’,’MBA’,’MFA’,’MS’)

TABLESPACE hist_tab02,

PARTION p_doctorate VALUES (‘PHD’)

TABLESPACE hist_tab03

);

 散列分区

使用一个散列算法来分配数据被插入到那个分区中。一般分配很均匀，对于序数的列很适合。

1)分区键应具有很高基数。

2)如果是唯一键分区，效果最好。如果是范围查找，不会有性能提升。

3)通过更新数据使数据在分区间移动不被允许。

4)总分区数，应该是2的整数倍。

SQL> create table student_history

(student_id number(10),

degree varchar2(3),

gradution_date date)

PARTITION BY HASH (student_id)

PARTITION 3

STORE IN (hist_tab01, hist_tab02, hist_tab03);

drop table part_ALARMTEXTDATA;

create table part_ALARMTEXTDATA

( ALARMNUMBER VARCHAR2(16) NOT NULL ,

ALARMTEXT VARCHAR2(255) NOT NULL ,

SERIAL NUMBER(38) NOT NULL)

PARTITION BY HASH (ALARMNUMBER)

(part_ALARMTEXTDATA_01 tablespace hxj_partion01,

part_ALARMTEXTDATA_02 tablespace hxj_partion02,

part_ALARMTEXTDATA_03 tablespace hxj_partion03,

part_ALARMTEXTDATA_04 tablespace hxj_partion04);

 组合分区

创建范围分区，在该分区内又再创建散列分区。(一般用在需要范围分区，而范围分区又不均匀的情况下)

1)分区是逻辑结构，表数据被存储在子分区级上。

2)组合分区对：分区级的、历史的、日期的，查询十分好使。

3)组合分区对子分区级的并行操作也十分有用。

4)分区级的连接操作通过使用组合的局部索引支持。

SQL> create table student_history

(student_id number(10),

degree varchar2(3),

gradution_date date)

PARTITION BY RANGE (graduation_date)

SUBPARTITION BY HASH (student_id)

SUBPARTITIONS 2

STORE IN (hist_tab01, hist_tab02)

(

PARTION p_2000 VALUES LESS THEN

(to_date(‘2000-1-1’,’yyyy-mm-dd’)) TABLESPACE hist_tab01,

PARTION p_2001 VALUES LESS THEN

(to_date(‘2001-1-1’,’yyyy-mm-dd’)) TABLESPACE hist_tab02,

PARTION p_error VALUES LESS THEN

(MAXVALUE) TABLESPACE hist_tab03

);

1.3.4.2 索引分区

唯一所以必须是全局的。

分两类，全局与局部索引，前缀与非前缀索引。

 全局与局部索引：与该索引的分区结构是否与被索引基础表结构匹配有关系。

局部索引：索引中的分区与基础表的分区逐个匹配。

- 可以人任何分区上创建局部分区索引。

- 创建后，Oracle自动维护表分区与索引分区之间的关系。

- 分区上的位图索引必须是局部的索引。

SQL> create index dinya_idx_t on dinya_test(item_id)

local

(

partition idx_1 tablespace dinya_space01,

partition idx_2 tablespace dinya_space02,

partition idx_3 tablespace dinya_space03

);

Index created.

SQL>

全局索引：索引中的分区与基础表的分区数不逐个匹配。

- 可以人任何分区上创建全局分区索引，但它自身必须是范围的。

- 最高分区必须以MAXVALUE参数来定义。

- 对分区表的维护操作(drop ,truncate操作等)，会导致全局索引无效。此时必须重建。

- 使用global创建与表分区一样多的索引，Oracle也不会认为是局部索引。

SQL> create index dinya_idx_t on dinya_test(item_id)

global partition by range(item_id)

(

partition idx_1 values less than (1000) tablespace dinya_space01,

partition idx_2 values less than (10000) tablespace dinya_space02,

partition idx_3 values less than (maxvalue) tablespace dinya_space03

);

Index created.

global 子句允许指定索引的范围值，这个范围值为索引字段的范围值.

SQL>

或

SQL> create index dinya_idx_t on dinya_test(item_id);

Index created.

SQL>

 前缀与非前缀索引：与该索引是否有分区键及分区键出现在索引结构内的位置有关系。

前缀索引：索引的最左列与分区键相同。可以是唯一的，也可以不是唯一的。

前缀局部索引：

SQL>create index student_history_lp_idx on student_history (graduation_date) local;

此时默认与基础表分区数一样。以graduation_date范围分区。

前缀全局索引：

略。

非前缀索引：索引的最左列与分区键不相同。可以是唯一的(唯一时分区键必须是索引的子集)，也可以不是唯一的。

非前缀局部索引：

SQL>create index student_history_lp_idx on student_history (graduation_date) local;

此时默认与基础表分区数一样。以graduation_date范围分区。

非前缀全局索引：

略。

分区索引不能作为整体一次重建。

alter index local2_alarminfo rebuild partition l_ind2_alarminfo_050915;

1.3.4.3 管理分区

1.1.1.1.1 收集分区的统计数据

可以针对段、分区或子分区级上收集。

例(收集分区表和分区索引)：

SQL>execute dbms_stats.gather_table_stats(ownname=>’APPS’,tabname=>’STUDENT_HISTORY’,partname=>’P_1998’,granularity=>’PARTITION’);

SQL>execute dbms_stats.gather_index_stats(ownname=>’SYS’,indname=>’STUDENT_HISTORY_GP_IDX’,partname=>’P_200n’);

from:http://hi.baidu.com/zting0103/blog/item/0b42b029885097fd98250a63.html