Oracle数据库中索引的维护ORACLE教程Word格式.docx
- 文档编号:22136213
- 上传时间:2023-02-02
- 格式:DOCX
- 页数:8
- 大小:17.43KB
Oracle数据库中索引的维护ORACLE教程Word格式.docx
《Oracle数据库中索引的维护ORACLE教程Word格式.docx》由会员分享,可在线阅读,更多相关《Oracle数据库中索引的维护ORACLE教程Word格式.docx(8页珍藏版)》请在冰豆网上搜索。
Oracle为数据库中的全部数据安排逻辑构造空间。
数据库空间的单位是数据块〔block〕、范围〔extent〕和段〔segment〕。
Oracle数据块〔block〕是Oracle运用和安排的最小存储单位。
它是由数据库建立时设置的DB_BLOCK_SIZE确定的。
一旦数据库生成了,数据块的大小不能变更。
要想变更只能重新建立数据库。
〔在Oracle9i中有一些不同,不过这不在本文探讨的范围内。
〕
Extent是由一组连续的block组成的。
一个或多个extent组成一个segment。
当一个segment中的全部空间被用完时,Oracle为它安排一个新的extent。
Segment是由一个或多个extent组成的。
它包含某表空间中特定逻辑存储构造的全部数据。
一个段中的extent可以是不连续的,甚至可以在不同的数据文件中。
一个object只能对应于一个逻辑存储的segment,我们通过查看该segment中的extent,可以看出相应object的存储状况。
〔1〕查看索引段中extent的数量:
selectsegment_name,count(*)
fromdba_extents
wheresegment_type='
INDEX'
andowner=UPPER('
owner'
groupbysegment_name
〔2〕查看表空间内的索引的扩展状况:
select
substr(segment_name,1,20)"
SEGMENTNAME"
bytes,
count(bytes)
wheresegment_namein
(selectindex_name
wheretablespace_name=UPPER('
表空间'
))
groupbysegment_name,bytes
orderbysegment_name
[1][2][3]下一页
三.索引的选择性
索引的选择性是指索引列中不同值的数目与表中记录数的比。
假如一个表中有2000条记录,表索引列有11010个不同的值,那么这个索引的选择性就是11010/2000=0.101。
一个索引的选择性越接近于1,这个索引的效率就越高。
假如是运用基于cost的最优化,优化器不应当运用选择性不好的索引。
假如是运用基于rule的最优化,优化器在确定执行路径时不会考虑索引的选择性〔除非是唯一性索引〕,并且不得不手工优化查询以幸免运用非选择性的索引。
确定索引的选择性,可以有两种方法:
手工测量和自动测量。
〔1〕手工测量索引的选择性
假如要依据一个表的两列创立两列并置索引,可以用以下方法测量索引的选择性:
列的选择性=不同值的数目/行的总数/*越接近1越好*/
selectcount(distinct第一列||'
%'
||其次列)/count(*)
from表名
假如我们知道其中一列索引的选择性〔例如其中一列是主键〕,那么我们就可以知道另一列索引的选择性。
手工方法的优点是在创立索引前就能评估索引的选择性。
〔2〕自动测量索引的选择性
假如分析一个表,也会自动分析全部表的索引。
第一,为了确定一个表确实定性,就要分析表。
analyzetable表名
computestatistics
其次,确定索引里不同关键字的数目:
selectdistinct_keys
fromuser_indexes
wheretable_name='
表名'
andindex_name='
索引名'
第三,确定表中行的总数:
selectnum_rows
fromuser_tables
第四,索引的选择性=索引里不同关键字的数目/表中行的总数:
selecti.distinct_keys/t.num_rows
from
user_indexesi,
user_tablest
wherei.table_name='
andi.index_name='
andi.table_name=t.table_name
第五,可以查询USER_TAB_COLUMNS以了解每个列的选择性。
表中全部行在该列的不同值的数目:
column_name,
num_distinct
fromuser_tab_columns
列的选择性=NUM_DISTINCT/表中全部行的总数,查询USER_TAB_COLUMNS有助测量每个列的选择性,但它并不能准确地测量列的并置组合的选择性。
要想测量一组列的选择性,须要采纳手工方法或者依据这组列创立一个索引并重新分析表。
四.确定索引的实际碎片
随着数据库的运用,不行幸免地对根本表进展插入,更新和删除,这样导致叶子行在索引中被删除,使该索引产生碎片。
插入删除越常见的表,索引碎片的程度也越高。
碎片的产生使访问和运用该索引的I/O本钱增加。
碎片较高的索引必需重建以保持最正确性能。
〔1〕利用验证索引吩咐对索引进展验证。
这将有价值的索引信息填入index_stats表。
validateindex用户名.索引名
〔2〕查询index_stats表以确定索引中删除的、未填满的叶子行的百分比。
name,
del_lf_rows,
lf_rows,
round((del_lf_rows/(lf_rows+0.0000000001))*101)"
FragPercent"
fromindex_stats
〔3〕假如索引的叶子行的碎片超过10%,考虑对索引进展重建。
alterindex用户名.索引名rebuild
tablespace表空间名
storage(initial初始值next扩展值)
nologging
〔4〕假如出于空间或其他考虑,不能重建索引,可以整理索引。
alterindex用户名.索引名coalesce
〔5〕去除分析信息
analyzeindex用户名.索引名
deletestatistics
上一页[1][2][3]下一页
五.重建索引
〔1〕检查须要重建的索引。
依据以下几方面进展检查,确定须要重建的索引。
第一,查看SYSTEM表空间中的用户索引。
为了幸免数据字典的碎片出现,要尽量幸免在SYSTEM表空间出现用户的表和索引。
selectindex_name
wheretablespace_name='
其次,确保用户的表和索引不在同一表空间内。
表和索引对象的第一个规那么是把表和索引分别。
把表和相应的索引建立在不同的表空间中,最好在不同的磁盘上。
这样可以幸免在数据管理和查询时出现的很多I/O冲突。
setlinesize120
col"
OWNER"
formata20
INDEX"
formata30
TABLE"
TABLESPACE"
i.owner"
i.index_name"
t.table_name"
i.tablespace_name"
dba_indexesi,
dba_tablest
wherei.owner=t.owner
andi.tablespace_name=t.tablespace_name
andi.ownernotin('
第三,查看数据表空间里有哪些索引
用户的默认表空间应当不是SYSTEM表空间,而是数据表空间。
在建立索引时,假如不指定相应的索引表空间名,那么,该索引就会建立在数据表空间中。
这是程序员经常忽视的一个问题。
应当在建索引时,明确的指明相应的索引表空间。
colsegment_nameformata30
owner,
segment_name,
sum(bytes)
fromdba_segments
数据表空间名'
andsegment_type='
groupbyowner,segment_name
第四,查看哪个索引被扩展了超过10次
随着表记录的增加,相应的索引也要增加。
假如一个索引的nextextent值设置不合理〔太小〕,索引段的扩展变得很频繁。
索引的extent太多,检索时的速度和效率就会降低。
setlinesize101
colownerformata10
coltablespace_nameformata30
count(*),
tablespace_name
groupbyowner,segment_name,tablespace_name
havingcount(*)10
orderbycount(*)desc
〔2〕找出须要重建的索引后,须要确定索引的大小,以设置合理的索引存储参数。
owner"
segment_name"
tablespace_name"
bytes"
BYTES/COUNT"
sum(bytes)"
TOTALBYTES"
round(sum(bytes)/(1024*1024),0)"
TOTALM"
count(bytes)"
TOTALCOUNT"
andsegment_namein
(
'
索引名1'
索引名2'
......
groupbyowner,segment_name,segment_type,tablespace_name,bytes
orderbyowner,segment_name
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle 数据库 索引 维护 教程