[20140920]oracle cluster index (11g)(2)
[20140920]oracle cluster index (11g)(補充).txt
--上個星期簡單研究了一下cluster表.
--應用中除了堆表,很少使用cluser表,也就僅僅在生產系統使用IOT索引組織表.
--實際上系統表中許多都是cluster表.比如SYS.TAB$,SYS.COL$等都建立在cluster中.
--沒事,簡單研究一下其儲存結構.
1.建立測試環境:
連結
http://blog.itpub.net/267265/viewspace-1266411/
SCOTT@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
--create cluster cluster_dept (deptno NUMBER(2)) index ;
create cluster cluster_dept (deptno NUMBER(2)) ;
create index i_cluster_deptno on cluster cluster_dept;
create table dept1 cluster cluster_dept(deptno) as select * from dept;
create table emp1 cluster cluster_dept(deptno) as select * from emp;
SCOTT@test> alter system dump datafile 4 block 1983 ;
System altered.
2.以下是資料塊轉儲:
Block header dump: 0x010007bf
Object id on Block? Y
seg/obj: 0x470c8 csc: 0x02.a63f479c itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x10007b8 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0001.021.000044c0 0x00c005b5.1c5b.0d C--- 0 scn 0x0002.a63f4788
0x02 0x0005.008.0000725e 0x00c007a0.2518.1b --U- 3 fsc 0x0000.a63f47a3
bdba: 0x010007bf
data_block_dump,data header at 0x2a9752e064
===============
tsiz: 0x1f98
hsiz: 0x24
pbl: 0x2a9752e064
76543210
flag=--------
ntab=3
nrow=5
frre=-1
fsbo=0x24
fseo=0x1efd
avsp=0x1ed9
tosp=0x1ed9
0xe:pti[0] nrow=1 offs=0
0x12:pti[1] nrow=1 offs=1
0x16:pti[2] nrow=3 offs=2
0x1a:pri[0] offs=0x1f82
0x1c:pri[1] offs=0x1f6a
0x1e:pri[2] offs=0x1f44
0x20:pri[3] offs=0x1f21
0x22:pri[4] offs=0x1efd
block_row_dump:
tab 0, row 0, @0x1f82
tl: 22 fb: K-H-FL-- lb: 0x0 cc: 1
curc: 4 comc: 4 pk: 0x010007bf.0 nk: 0x010007bf.0
col 0: [ 2] c1 0b
tab 1, row 0, @0x1f6a
tl: 24 fb: -CH-FL-- lb: 0x0 cc: 2 cki: 0
col 0: [10] 41 43 43 4f 55 4e 54 49 4e 47
col 1: [ 8] 4e 45 57 20 59 4f 52 4b
tab 2, row 0, @0x1f44
tl: 38 fb: -CH-FL-- lb: 0x2 cc: 6 cki: 0
col 0: [ 3] c2 4e 53
col 1: [ 5] 43 4c 41 52 4b
col 2: [ 7] 4d 41 4e 41 47 45 52
col 3: [ 3] c2 4f 28
col 4: [ 7] 77 b5 06 09 01 01 01
col 5: [ 3] c2 19 33
tab 2, row 1, @0x1f21
tl: 35 fb: -CH-FL-- lb: 0x2 cc: 6 cki: 0
col 0: [ 3] c2 4f 28
col 1: [ 4] 4b 49 4e 47
col 2: [ 9] 50 52 45 53 49 44 45 4e 54
col 3: *NULL*
col 4: [ 7] 77 b5 0b 11 01 01 01
col 5: [ 2] c2 33
tab 2, row 2, @0x1efd
tl: 36 fb: -CH-FL-- lb: 0x2 cc: 6 cki: 0
col 0: [ 3] c2 50 23
col 1: [ 6] 4d 49 4c 4c 45 52
col 2: [ 5] 43 4c 45 52 4b
col 3: [ 3] c2 4e 53
col 4: [ 7] 77 b6 01 17 01 01 01
col 5: [ 2] c2 0e
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 1983 maxblk 1983
--說明:
ntab=3 =>表示有3個表. nrow=5 有5條記錄.
tab 0, row 0, @0x1f82
tl: 22 fb: K-H-FL-- lb: 0x0 cc: 1
curc: 4 comc: 4 pk: 0x010007bf.0 nk: 0x010007bf.0
col 0: [ 2] c1 0b
--tab 0 實際上cluster主鍵.
SCOTT@test01p> select dump(10,16) from dual ;
DUMP(10,16)
-----------------
Typ=2 Len=2: c1,b
--tab 1 實際上dept1表.
tab 1, row 0, @0x1f6a
tl: 24 fb: -CH-FL-- lb: 0x0 cc: 2 cki: 0
col 0: [10] 41 43 43 4f 55 4e 54 49 4e 47
col 1: [ 8] 4e 45 57 20 59 4f 52 4b
SCOTT@test01p> select dump(dname,16) c50 ,dump(loc,16) c40 from dept1 where deptno=10 ;
C50 C40
-------------------------------------------------- ----------------------------------------
Typ=1 Len=10: 41,43,43,4f,55,4e,54,49,4e,47 Typ=1 Len=8: 4e,45,57,20,59,4f,52,4b
--tab 2 實際上emp1表.
tab 2, row 0, @0x1f44
tl: 38 fb: -CH-FL-- lb: 0x2 cc: 6 cki: 0
col 0: [ 3] c2 4e 53
col 1: [ 5] 43 4c 41 52 4b
col 2: [ 7] 4d 41 4e 41 47 45 52
col 3: [ 3] c2 4f 28
col 4: [ 7] 77 b5 06 09 01 01 01
col 5: [ 3] c2 19 33
SCOTT@test01p> select dump(empno,16) c30 ,dump(ename,16) c30 from emp1 where empno=7782;
C30 C30
------------------------------ ------------------------------
Typ=2 Len=3: c2,4e,53 Typ=1 Len=5: 43,4c,41,52,4b
--可以發現資訊是一致的.
--有點奇怪的是oracle如何知道tab1 對應的就是dept1.tab 2 對應的就是emp1呢?
--自己遇到一個小問題oracle如何知道tab1 對應的就是dept1.tab 2 對應的就是emp1呢? 我建立按照順序
--先建立dept1,然後emp1,自己當然很清楚.但是oracle內部如何知道這些呢?
3.昨天檢視tab檢視時,無意中發現.
SCOTT@test01p> @desc tab
Name Null? Type
----------------------- -------- ----------------
TNAME NOT NULL VARCHAR2(128)
TABTYPE VARCHAR2(7)
CLUSTERID NUMBER
--可以發現包含clusterid欄位.
SCOTT@test01p> select * from tab where clusterid is not null order by 3;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT1 TABLE 1
EMP1 TABLE 2
--clusterid的數字正好對上!
SCOTT@test01p> select text_vc from dba_views where owner='SYS' and view_name='TAB';
TEXT_VC
------------------------------------------------------------------------------------
select o.name,
decode(o.type#, 2, 'TABLE', 3, 'CLUSTER',
4, 'VIEW', 5, 'SYNONYM'), t.tab#
from sys.tab$ t, sys."_CURRENT_EDITION_OBJ" o
where o.owner# = userenv('SCHEMAID')
and o.type# >=2
and o.type# <=5
and o.linkname is null
and o.obj# = t.obj# (+)
SELECT object_id, data_object_id, object_name
FROM dba_objects
WHERE owner = USER AND object_name IN ('EMP1', 'DEPT1', 'DEPT')
OBJECT_ID DATA_OBJECT_ID OBJECT_NAME
---------- -------------- -------------
96173 96170 EMP1
96172 96170 DEPT1
92285 92285 DEPT
SELECT a.obj#,
a.dataobj#,
b.object_name,
a.bobj#,
a.tab#,
a.cols,
a.clucols
FROM sys.tab$ a, dba_objects b
WHERE a.obj# = b.object_id
AND a.dataobj# = b.data_object_id
AND b.owner = USER
AND b.object_name IN ('EMP1', 'DEPT1', 'DEPT')
ORDER BY 1
OBJ# DATAOBJ# OBJECT_NAME BOBJ# TAB# COLS CLUCOLS
---------- ---------- ------------------------------ ---------- ---------- ---------- ----------
92285 92285 DEPT 3
96172 96170 DEPT1 96170 1 3 1
96173 96170 EMP1 96170 2 8 1
--可以從這裡看出一些線索,CLUCOLS=1表示cluster表,TAB#表示順序.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-1276613/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- [20140909]oracle cluster index (11g).txtOracleIndex
- 11g R2新特徵:oracle cluster health moniter (CHM) 簡介特徵Oracle
- Oracle的Index-2(轉)OracleIndex
- Oracle RAC(Cluster)的重構(整理)(2)Oracle
- oracle 11g r2 cluster啟動順序及啟動異常可能原因Oracle
- index clusterring cluster的一點測試!Index
- oracle簇clusterOracle
- [20120730]11g下Oracle Index rebuild online之2.txtOracleIndexRebuild
- Oracle 11g r2 racOracle
- Oracle Cluster Time ManagementOracle
- oracle 11GR2新特性 Cluster Time Synchronization Service 配置Oracle
- oracle index unusableOracleIndex
- oracle document indexOracleIndex
- Oracle Index InternalsOracleIndex
- oracle 11g r2 installOracle
- DataGuard on Oracle 11g初步介紹(2)Oracle
- 【Oracle】global index & local index的區別OracleIndex
- oracle中的cluster表Oracle
- Oracle釋出Super ClusterOracle
- 11g新特性--invisible indexIndex
- oracle index索引原理OracleIndex索引
- zt_oracle indexOracleIndex
- oracle hint_no_indexOracleIndex
- oracle index monitoringOracleIndex
- oracle index 聚集因子OracleIndex
- oracle hints index格式OracleIndex
- Oracle 11g R2刪除EMOracle
- Oracle Database 11g Release 2 RAC On LinuxOracleDatabaseLinux
- oracle 11g R2安裝RACOracle
- The Oracle Clusterware Voting Disk and Oracle Cluster RegistryOracle
- 【RAC】11gR2 新特性:Oracle Cluster Health Monitor(CHM)簡介Oracle
- RedHat 4 as 下安裝Oracle11gR2,Cluster(ASM)和DatabaseRedhatOracleASMDatabase
- oracle hint之full,index,index_asc,index_desc,index_combile示例OracleIndex
- Oracle ASM Cluster File Systems (ACOracleASM
- 聊聊Oracle聚簇Cluster(上)Oracle
- Oracle ACFS ( ASM Cluster File System )OracleASM
- Oracle11gr2新增提示CHANGE_DUPKEY_ERROR_INDEXOracleErrorIndex
- oracle hint之hint_index_ffs,index_joinOracleIndex