oracle儲存研究方法

一、表

/*
先介紹一下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@]