oracle存储研究方法

最新推荐文章于 2021-04-14 21:54:02 发布

cuanwei9356

最新推荐文章于 2021-04-14 21:54:02 发布

阅读量62

点赞数

一、表

/*
先介绍一下rowid的定义规则：
第7～9位是表示的是数据文件，而10～15位表示的是在这个数据文件中的第几个BLOCK。rowid编码相当于64进制。
用A~Z a~z 0~9 + /共64个字符表示。A表示0，B表示1，……，a表示26，……，0表示52，……，+表示62，/表示63。）

接下来看几个基本概念：
一个table至少有一个segment，如果分区表，则每个分区是一个segment。
segment由一个或多个extents组成，segment不可以跨表空间但可以跨数据文件。
extent由多个连续的blocks组成，不可以跨数据文件。
block由1-多个os块组成，是oracle i/o的最小存储单位。
*/

--创建测试表空间：
create tablespace test datafile 'E:oracleoradatacsltjtest.dbf' size 100M autoextend on next 10M
extent management local;

--创建测试用户：
create user test identified by test default tablespace test temporary tablespace temp;
grant dba to test;

conn test/test
SELECT file_name,tablespace_name,bytes,blocks FROM DBA_DATA_FILES
where tablespace_name='TEST';

FILE_NAME TABLESPACE_NAME BYTES BLOCKS
E:ORACLEORADATACSLTJTEST.DBF TEST 104857600 12800

SELECT * FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME='TEST';

TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
TEST 8 9 104792064 12792 8
12800 - 12792文件头占用8block

--创建测试表：
create table test(id varchar2(20) not null primary key,rq date);

--查看视图：

SELECT table_name,tablespace_name,num_rows FROM DBA_TABLES WHERE OWNER='TEST';

TABLE_NAME TABLESPACE_NAME NUM_ROWS
TEST TEST

*/

--插入数据
insert into test values('0',sysdate);
commit;

select t.rowid,t.* from test t;

ROWID ID RQ
------------------ -------------------- -----------
AAABynAAIAAAAAKAAA 0 2006-8-21 1

从ROWID的"AAI"或者利用数据字典SELECT * FROM DBA_DATA_FILES可以看出，datafile号为8，

alter system dump datafile 1 block 10;

看到其中的col 0 和col 1就是数据库中的最终存储格式
data_block_dump,data header at 0x2fd105c
===============
tsiz: 0x1fa0
hsiz: 0x14
pbl: 0x02fd105c
bdba: 0x0200000a
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f93
avsp=0x1f7f
tosp=0x1f7f
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f93
block_row_dump:
tab 0, row 0, @0x1f93
tl: 13 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 1] 30
col 1: [ 7] 78 6a 08 15 12 32 27
end_of_block_dump
End dump data blocks tsn: 8 file#: 8 minblk 10 maxblk 10

select dump(id),dump(rq) from test;

利用dump函数可以查出数据文件的内部格式，比较方便清晰，但是不能看大字段类型
DUMP(ID) DUMP(RQ)
----------------------- -------------------------------------------
Typ=1 Len=1: 48 Typ=12 Len=7: 120,106,8,21,18,50,39

select SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,HEADER_BLOCK,BYTES,BLOCKS
from dba_segments where owner='TEST';

数据块是从第9块开始的，前8块做为文件头
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME HEADER_BLOCK BYTES BLOCKS
TEST TABLE TEST 9 65536 8
SYS_C002512 INDEX TEST 17 65536 8

select SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,BLOCK_id,BYTES,BLOCKS
from dba_extents where owner='TEST';

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BLOCK_ID BYTES BLOCKS
TEST TABLE TEST 9 65536 8
SYS_C002512 INDEX TEST 17 65536 8

begin
for i in 1..100000 loop
insert into test values(i,sysdate);
end loop;
commit;
end;
/

select SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,HEADER_BLOCK,BYTES,BLOCKS
from dba_segments where owner='TEST';

DBA_SEGMENTS记录数不变，BYTES和BLOCKS增加
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME HEADER_BLOCK BYTES BLOCKS
TEST TABLE ZHOUWF 9 3145728 384
SYS_C002512 INDEX ZHOUWF 17 3145728 384

select SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,BLOCK_id,BYTES,BLOCKS
from dba_extents where owner='TEST';

前16条数据8块一扩展；从第17条数据开始，128块一扩展
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BLOCK_ID BYTES BLOCKS
TEST TABLE TEST 9 65536 8
TEST TABLE TEST 41 65536 8
TEST TABLE TEST 57 65536 8
TEST TABLE TEST 81 65536 8
TEST TABLE TEST 97 65536 8
TEST TABLE TEST 113 65536 8
TEST TABLE TEST 137 65536 8
TEST TABLE TEST 153 65536 8
TEST TABLE TEST 177 65536 8
TEST TABLE TEST 193 65536 8
TEST TABLE TEST 217 65536 8
TEST TABLE TEST 233 65536 8
TEST TABLE TEST 241 65536 8
TEST TABLE TEST 249 65536 8
TEST TABLE TEST 257 65536 8
TEST TABLE TEST 393 65536 8
TEST TABLE TEST 521 1048576 128
TEST TABLE TEST 777 1048576 128
SYS_C002512 INDEX TEST 17 65536 8
SYS_C002512 INDEX TEST 33 65536 8
SYS_C002512 INDEX TEST 49 65536 8
SYS_C002512 INDEX TEST 65 65536 8
SYS_C002512 INDEX TEST 73 65536 8
SYS_C002512 INDEX TEST 89 65536 8
SYS_C002512 INDEX TEST 105 65536 8
SYS_C002512 INDEX TEST 121 65536 8
SYS_C002512 INDEX TEST 129 65536 8
SYS_C002512 INDEX TEST 145 65536 8
SYS_C002512 INDEX TEST 161 65536 8
SYS_C002512 INDEX TEST 169 65536 8
SYS_C002512 INDEX TEST 185 65536 8
SYS_C002512 INDEX TEST 201 65536 8
SYS_C002512 INDEX TEST 209 65536 8
SYS_C002512 INDEX TEST 225 65536 8
SYS_C002512 INDEX TEST 265 1048576 128
SYS_C002512 INDEX TEST 649 1048576 128

二、索引

可以看出索引空间和数据空间几乎一致，因为索引空间除了包含id字段内容还包含了rowid做为到表中查找的映射，
所以对于这种简单表索引查询应该比全表扫描要慢，以前测试过表中只有一列，索引空间大约是表空间的2倍。
对大表创建索引可以使用nologging来减少重做日志；
节省重做日志文件的空间；
缩短创建索引的时间；
改善了并行创建大索引时的性能。
create index idx_test on test(id) nologging compute statistics;
对于现有索引可以修改为nologging：
alter index idx_test rebuild nologging;
对于分区索引：
　alter index idx_test rebuild partition partiton_name nologging;
创建基于函数的索引
常用与UPPER、LOWER、TO_CHAR(date)等函数分类上，例：
create index idx_func on test (UPPER(id)) tablespace tablespace_name;
创建位图索引
对基数较小，且基数相对稳定的列建立索引时，首先应该考虑位图索引，例：
create bitmap index idx_bitm on test (id) tablespace tablespace_name;
创建局部分区索引
基础表必须是分区表；
分区数量与基础表相同；
每个索引分区的子分区数量与相应的基础表分区相同；
基础表的子分区中的行的索引项，被存储在该索引的相应的子分区中,例如:
　　Create Index TG_CDR04_SERV_ID_IDX On TG_CDR04(SERV_ID)
　　Pctfree 5
　　Tablespace TBS_AK01_IDX
　　Storage (
　　MaxExtents 32768
　　PctIncrease 0
　　FreeLists 1
　　FreeList Groups 1
　　)
　　local
　　/
创建范围分区的全局索引
基础表可以是全局表和分区表。
　　create index idx_start_date on tg_cdr01(start_date)
　　global partition by range(start_date)
　　(partition p01_idx vlaues less than (‘0106’)
　　partition p01_idx vlaues less than (‘0111’)
　　…
　　partition p01_idx vlaues less than (‘0401’ ))
　　/
基于规则的优化器总是使用索引，用rule方式总是从驱动表开始（from子句最右边的表），所以要把大表放在前边，小表放在后。
查询并行化
　　select /*+ full(test) parallel(test,8)*/ * from emp;
永久并行化（不推荐）
　　alter table test parallel degree 8;
屏蔽索引
数值型：在索引字段上加0，例如
　　select * from test where id+0 = var;
字符型：在索引字段上加''，例如
　　select * from test where id||''=var;
oracle9i开始可以监视索引使用情况：
alter index pk_dept nomonitoring usage;
select * from v$object_usage;
alter index index_name nomonitoring usage;
更多索引原理可以参考http://zhouwf0726.itpub.net/post/9689/197331

select name,lf_rows from index_stats;

NAME LF_ROWS
------------------------------ ----------
SYS_C002512 100001

select object_id from dba_objects where object_name='SYS_C002512';

OBJECT_ID
----------
7336

*/

ALTER SESSION SET EVENTS 'immediate trace name TREEDUMP level 7336';

根据trace内容我们可以深入研究oracle的索引结构，待续......

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18921899/viewspace-1016974/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/18921899/viewspace-1016974/

cuanwei9356

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle存储研究方法

一、表/*先介绍一下rowid的定义规则：第7～9位是表示的是数据文件，而10～15位表示的是在这个数据文件中的第几个BLOCK。rowid编码相当于64进制。用A~Z a~z 0~9 + /共64个字符表示。A表示0，B表示1...
复制链接

扫一扫