Oracle reverse函数

Oracle reverse函数
2007-10-25 14:06
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只能用于等于、不等于判断。其他如>、<、between等会导致无法使用该索引，可能会导致全表扫描。

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’);


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