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
扫一扫
1082
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
