Everyone is inaccurate for the understanding of Cluster in Oracle, often confused with Cluster Index in SQL Server. Cluster is a way to store a set of Table, which share some of the same column in the same data block, and stores different tables on this shared Column value on the same block on the same block. The Cluster INDEX in SQL Server is stored in the store sequence according to the Index Key, and this is similar to IOT in Oracle.
From the figure below, we can clearly see the difference between the CLUSTER and a set of Table of the non-Cluster. In Cluster, the data on a single block may come from multiple TABLE, and the "pre-connection" data can be stored conceptually. Single Table can also use Cluster, which is stored in groups according to certain column. As in the figure, all department IDs 20 and 110 department information and data of employee information will be stored in the same block. Note that the stored data is not sorted (That is the task of the IoT), which is stored in the sectoral ID packet set, is stored in HEAP. Therefore, the department 20 is just adjacent to the department 110, and the department 99 and the department 100 are far from the distance (the physical location of the hard disk).
When a single Block can't put down, additional Block will link to the initial block, tolerate overflowed data, which is very similar to the IOT in overflowing Block very similar. Now let's see how to create a cluster. Creating a series of tables in the Cluster is very simple, object storage defines such as PCTFree, PCTUSED, INITIAL are related to Cluster, not Table. This is because a number of tables are stored in the Cluster, and each table has different PCTFREEs in the same block. SQL> CREATE Cluster E_D_Cluster 2 (DeptID Number (2)) 3 Size 1024 4 /
Cluster Created Here first created an Index Cluster. This cluster's key is DeptID, which can be named DEPTID in Table, but the data type Number (2) must match. The Size option is used to tell Oracle expect 1024 byte data and each Cluser Key. Oracle will use this information to calculate the maximum number of Cluster Key that Block can accommodate. So Size is too high, which will get very few Key, and will use more space than need; set capacity is too low, which will get too much data connection, which will deviate from the purpose of using Cluster. SIZE is an important parameter for Cluster. Now let's create Cluster INDEX. An index cluster needs to be indexed before putting the data. The role of Cluster Index is to store a Cluster Key and return the address of the Block containing the key.
SQL> CREATE INDEX E_D_CLUSTER_IDX 2 On Cluster E_D_Cluster 3 /
Index Created Cluster Key's INDEX can use index all normal storage parameters and can be in another TableSpace. It is a normal index that can index to a Cluster and also contain a complete NULL entry. We created Table: SQL> Create Table Department 2 (DeptId Number (2) Primary KEY, 3 DNAME VARCHAR2 (14), 4 LOC VARCHAR2 (13)) 5 Cluster E_D_CLUSTER (DEPTID); TABLE CREATED
SQL> CREATE TABLE Employee 2 (Empid Number Primary Key, 3 Ename Varchar2 (10), 4 Job Varchar2 (10), 5 Mgr Number, 6 Hiredate Date, 7 Sal Number, 8 Comm number, 9 DeptId Number (2) References Department 10 Cluster E_D_CLUSTER (DEPTID);
Table created
Here, create Table and ordinary Table is the only difference in using Cluster keywords, we load data towards Table: SQL> Begin 2 for x in (Select * from scott.dept) 3 Loop 4 Insert Into Department 5 Values (x. Deptno, x.dname, x.loc); 6 INSERT INTO EMPLOYEE 7 Select * from scott.emp 8 where deptno = x.deptno; 9 end loop; 10 end; 11 /
Pl / SQL Procedure SuccessFully Completed We can see the following data in Table: SQL> Select * from department;
DEPTID DNAME LOC ------------------------------- 10 Accounting
New York
20 Research
Dallas
30 Sales
Chicago
40 Operations
Boston
SQL> Select * from Employee;
Empid Ename Job Mgr Hiredate Sal Comm DeptID
---------- -------------------------------------- - - ---------- ---------- ------
7782
Clark
Manager 7839 1981-6-9 2450 10
7839 King President 1981-11-17 5000 10
7934 Miller Clerk 7782 1982-1-23 1300 10
7369 Smith Clerk 7902 1980-12-17 800 20
7566 Jones Manager 7839 1981-4-2 2975 207788 SCOTT Analyst 7566 1982-12-9 3000 20
7876
ADAMS
Clerk 7788 1983-1-12 1100 20
7902 Ford Analyst 7566 1981-12-3 3000 20
7499 Allen Salesman 7698 1981-2-20 1600 300 30
7521 Ward Salesman 7698 1981-2-22 1250 500 30
7654 Martin Salesman 7698 1981-9-28 1250 1400 30
7698 Blake Manager 7839 1981-5-1 2850 30
7844 Turner Salesman 7698 1981-9-8 1500 0 30
7900 James Clerk 7698 1981-12-3 950 30
Now let's take a look at the location of these two Table data stores:
SQL> select dbms_rowid.rowid_block_number (department.rowid) dept_rid, 2 dbms_rowid.rowid_block_number (employee.rowid) emp_rid, department.deptid 3 from department, employee 4 where employee.deptid = department.deptid;
DEPT_RID EMP_RID DEPTID ---------- ---------- ------ 5587 5587 10 5587 5587 10 5587 5587 10 5587 5587 20 5587 5587 20 5587 5587 20 5587 5587 20 5587 5587 20 5587 5587 30 5587 5587 30 5587 5587 30 5587 5587 30 5587 5587 30
You can see the same data as the department ID is stored on a block. Why do we recommend the above method to the original loading of Cluster? This ensures that if some Cluster Key's related data exceeds size, most of the data can be gathered on a block. This only applies to the initial data loading, after which transaction can use the Table Insert data in the Cluster.
Due to the special data storage mode in the Cluster, the RowID has repeated, and now the RowID can only identify a row of data in a table (another RowID repeatedly after the TRANSPORT TABLESPACE operation). SQL> select rowid from department 2 intersect 3 select rowid from employee; ROWID ------------------ AAAGWQAADAAABXTAAA AAAGWQAADAAABXTAAB AAAGWQAADAAABXTAAC AAAGWQAADAAABXTAAD
We have also found that the number of repetitive RowIDs is the same as the number of records in the primary table, namely the number of records in the Cluster. Let's take this Blick dump, see what is the reason:
SQL> ALTER SYSTEM DUMP DATAFILE 3 BLOCK 5587;
SYSTEM altered
Dump file d: /database/oracle/admin/ora817/udump/ora02628.trcwed dec 31 12:31:10 2003Oracle v8.1.7.4.1 - Production vsnsta = 0VSNSQL = f vsnXTR = 3WINDOWS 2000 Version 5.0 Service Pack 4, CPU Type 586 Oracle8i Enterprise Edition Release 8.1.7.4.1 - Productionwith The Partitioning OptionjServer Release 8.1.7.4.1 - ProductionWindows 2000 Version 5.0 Service Pack 4, CPU Type 586instance Name: ORA817
Redo thread mounted by this instance: 1
Oracle Process Number: 17
Windows Thread ID: 2628, Image: Oracle.exe
*** session ID: (17.1471) 2003-12-31 12: 31: 10.159Start Dump Data Blocks TSN: 2 File #: 3 minblk 4275 MaxBLK 4275Buffer TSN: 2 RDBA: 0x00c010B3 (3/4275) SCN: 0x0000.00142980 seq: 0x01 flg: 0x02 tail: 0x29800601frmt: 0x02 chkval: 0x0000 type: 0x06 = trans data Block header dump: 0x00c010b3 Object id on Block Y seg / obj:? 0x657d csc: 0x00.14297c itc: 2 flg: O typ: 1 - Data FSL: 0 fnx: 0x0 Ver: 0x01 ITL XID UBA FLAG LCK SCN / FSC0X01 XID: 0x0003.029.000002D2 UBA: 0x00800466.006E.35 C --- 0 SCN 0x0000.0014297C --- 1. These two Transaction What does Flag and LCK mean? 0x02 xid: 0x0003.023.000002d2 UBA: 0x00800467.006E.37 --U- 18 FSC 0x0000.00142980 DATA_BLOCK_DUMP =============== Tsiz: 0x1fa0Hsiz: 0x46PBL: 0x11Ab745cbdba: 0x00c010b3flag = - ----- K ---- NTAB = 3 - This is 3 tables on behalf of 5587 (E_D_Cluster, Department, EMPLOYEE)
NROW = 22FRRE = -1FSBO = 0x46fseo = 0x1CE3AVSP = 0x1c9dtosp = 0x1c9d0xe: PTI [0] nrow = 4 OFFS = 0 - This is the number of lines of three Table and starting offset 0x12: PTI [1] nrow = 4 OFFS = 40x16: PTI [2] nrow = 14 OFFS = 80x1a: Pri [0] OFFS = 0x1f8a 0x1c: pri [1] OFFS = 0x1c: Pri [1] OFFS = 0x1eef0x1e: Pri [2] OFFS = 0x1e100x20: Pri [3] OFFS = 0x1CF90X22: PRI [ 4] OFFS = 0x1f720x24: pri [5] OFFS = 0x1edb0x26: Pri [6] OFFS = 0x1dfe0x28: Pri [7] OFFE0X28: PRI [7] OFFS = 0x1CE30X2A: PRI [8] OFFS = 0x1f4c0x2c: pri [9] OFFS = 0x1f290x2e: pri [10] OFFS = 0x1f050x30: pri [11] OFFS = 0x1eb80x32: pri [12] OFFS = 0x1e920x34: pri [13] OFFS = 0x1e6d0x36: pri [14] OFFS = 0x1e4a0x38: pri [15] OFFS = 0x1e260x3a: PRI [16] OFFS = 0x1dd50x3c: pri [17] offs = 0x1dac0x3e: pri [18] offs = 0x1d810x40: pri [19] offs = 0x1d5b0x42: pri [20] offs = 0x1d320x44: pri [21] offs = 0x1d0fblock_row_dump: tab 0, row 0, @ 0x1f8a - From this start is EMP_DEPT_CLUSTERTL: 22 FB: KH-FL - LB: 0x0 CC: 1CURC: 4 COMC: 4 PK: 0x00c010B3.0 NK: 0x00c010b3.0col 0: [2] C1 0BTAB 0, ROW 1, @ 0x1eefTL: 22 FB: KH-FL - LB: 0x0 CC: 1CURC: 6 COMC: 6 PK: 0x00c010B3.1 NK: 0x00c010B 3.1col 0: [2] C1 15TAB 0, ROW 2, @ 0x1e10tl: 22 FB: KH-FL - LB: 0x0 CC: 1CURC: 7 COMC: 7 PK: 0x00c010B3.2 NK: 0x00c010b3.2col 0: [2 ] C1 1FTAB 0, ROW 3, @ 0x1cf9tl: 22 FB: KH-FL - LB: 0x0 CC: 1CURC: 1 COMC: 1 PK: 0x00c010B3.3 NK: 0x00c010B3.3COL 0: [2] C1 29TAB 1, ROW 0, @ 0x1f72 - From this is departmenttl: 24 fb: -ch-fl - lb: 0x2 cc: 2 CKI: 0COL 0: [10] 41 43 43 4F 55 4E 54 49 4E 47COL 1: [8] 4E 45 57 20 59 4F 52 4BTAB 1, ROW 1, @
0x1edbtl: 20 fb: -ch-fl - lb: 0x2 cc: 2 CKI: 1col 0: [8] 52 45 48 45 41 52 43 48COL 1: [6] 44 41 4C 4C 41 53TAB 1, ROW 2, @ 0x1DFETL: 18 FB: -CH-FL - LB: 0x2 CC: 2 CKI: 2Col 0: [5] 53 41 4C 45 53COL 1: [7] 43 48 49 43 41 47 4FTAB 1, ROW 3, @ 0x1ce3TL: 22 FB: -CH-FL - LB: 0x2 CC: 2 CKI: 3COL 0: [10] 4F 50 45 52 41 54 49 4F 4e 53Col 1: [6] 42 4F 53 54 4F 4ETAB 2, ROW 0, @ 0x1f4c --2 From this starting is Employee, because department and Employee start from ROW 0, so RowID will repeat
TL: 38 FB: -CH-FL - LB: 0x2 CC: 6 CKI: 0 --3. Why is this TRAN information?
COL 0: [3] C2 4E 53COL 1: [5] 43 4C 41 52 4BCOL 2: [7] 4D 41 4E 41 47 45 52COL 3: [3] C2 4F 28COL 4: [7] 77 B5 06 09 01 01 01COL 5: [3] C2 19 33TAB 2, ROW 1, @ 0x1f29tl: 35 fb: -1-fl - lb: 0x2 cc: 6 CKI: 0COL 0: [3] C2 4F 28COL 1: [4] 4B 49 4E 47COL 2: [9] 50 52 45 53 49 44 45 4E 54COL 3: * Null * Col 4: [7] 77 B5 0B 11 01 01 01COL 5: [2] C2 33TAB 2, ROW 2, @ 0x1f05tl: 36 FB: -CH-FL - LB: 0x2 CC: 6 CKI: 0COL 0: [3] C2 50 23COL 1: [6] 4D 49 4C 4C 45 52COL 2: [5] 43 4C 45 52 4BCOL 3: [3 ] C2 4E 53COL 4: [7] 77 B6 01 17 01 01 01COL 5: [2] C2 0ETAB 2, ROW 3, @ 0x1eb8tl: 35 fb: -Ch-fl - lb: 0x2 cc: 6 CKI: 1col 0 : [3] C2 4A 46Col 1: [5] 53 4D 49 54 48COL 2: [5] 43 4C 45 52 4BCOL 3: [3] C2 50 03COL 4: [7] 77 B4 0C 11 01 01 01COL 5: [ 2] C2 09TAB 2, ROW 4, @ 0x1e92tl: 38 FB: -CH-FL - LB: 0x2 CC: 6 CKI: 1col 0: [3] C2 4C 43COL 1: [5] 4A 4F 4E 45 53COL 2: [7] 4D 41 4E 41 47 45 52COL 3: [3] C2 4F 28COL 4: [7] 77 B5 04 02 01 01 01COL 5: [3] C2 1e 4CTAB 2, ROW 5, @ 0x1e6dtl: 37 fb: -1-fl - lb: 0x2 cc: 6 CKI: 1COL 0: [3] C2 4E 59COL 1: [5] 53 43 4F 54 54COL 2: [7] 41 4E 41 4C 59 53 54COL 3: [3] C2 4C 43COL 4: [7] 77 B6 0C 09 01 01 01COL 5: [2] C2 1ftab 2, ROW 6, @ @ @ @] 0x1e4atl: 35 fb: -ch-fl - lb: 0x2 cc: 6 CKI: 1col 0: [3] C2 4F 4DCOL 1: [5] 41 44 41 4D 53COL 2: [5] 43 4C 45 52 4BCOL 3: [3] C2 4E 59COL 4: [7] 77 B7 01 0C 01 01 01COL 5: [2] C2 0CTAB 2, ROW 7, @ 0x1e26tl: 36 fb: -Ch-fl - lb: 0x2 CC: 6 CKI: 1col 0: [3] C2 50 03
COL 1: [4] 46 4F 52 44COL 2: [7] 41 4E 41 4C 59 53 54COL 3: [3] C2 4C 43COL 4: [7] 77 B5 0C 03 01 01 01COL 5: [2] C2 1ftab 2 , Row 8, @ 0x1dd5tl: 41 fb: -ch-fl - lb: 0x2 cc: 7 CKI: 2COL 0: [3] C2 4B 64 Col 1: [5] 41 4C 4C 45 4ECOL 2: [8] 53 41 4C 45 53 4D 41 4ECOL 3: [3] C2 4D 63COL 4: [7] 77 B5 02 14 01 01 01COL 5: [2] C2 11COL 6: [2] C2 04TAB 2, ROW 9, @ 0x1dactl: 41 FB: -CH-FL - LB: 0x2 CC: 7 CKI: 2COL 0: [3] C2 4C 16COL 1: [4] 57 41 52 44COL 2: [8] 53 41 4C 45 53 4D 41 4ECOL 3: [ 3] C2 4D 63COL 4: [7] 77 B5 02 16 01 01 01COL 5: [3] C2 0D 33COL 6: [2] C2 06TAB 2, ROW 10, @ 0x1d81tl: 43 fb: -Ch-fl - lb : 0x2 cc: 7 CKI: 2COL 0: [3] C2 4D 37Col 1: [6] 4D 41 52 54 49 4ECOL 2: [8] 53 41 4C 45 53 4D 41 4ECOL 3: [3] C2 4D 63col 4: [7] 77 B5 09 1C 01 01 01COL 5: [3] C2 0D 33COL 6: [2] C2 0FTAB 2, ROW 11, @ 0x1d5btl: 38 FB: -CH-FL - LB: 0x2 CC: 6 CKI: 2Col 0: [3] C2 4D 63COL 1: [5] 42 4C 41 4B 45COL 2: [7] 4D 41 4E 41 47 45 52COL 3: [3] C2 4F 28COL 4: [7] 77 B5 05 01 01 01 01COL 5: [3] C2 1D 33TAB 2, ROW 12, @ 0x1d32tl: 41 fb: -ch-fl - lb: 0x2 cc: 7 CKI: 2col 0: [3] C2 4F 2DCOL 1: [6] 54 55 52 4E 45 52COL 2: [8] 53 41 4C 45 53 4D 41 4ECOL 3: [3] C2 4D 63COL 4: [7] 77 B5 09 08 01 01 01COL 5: [2] C2 10col 6: [1] 80TAB 2, ROW 13, @ 0x1d0ftl: 35 fb: -CH- FL - lb: 0x2 cc: 6 CKI: 2col 0: [2] C2 50COL 1: [5] 4A 41 4D 45 53COL 2: [5] 43 4C 45 52 4BCOL 3: [3] C2 4D 63COL 4: [ 7] 77 B5 0C 03 01 01 01COL 5: [3] C2 0A 33nd_of_block_dumpend dump data blocks TSN: 2 File #: 3 minblk 5587 maxblk 5587 Let's explain a few questions in this block:
1. What does these two Transaction's FLAG and LCK mean? Here is the information of Block's ITL, where there are two transactions here, the transaction trough is available in blocks with spaces.
FALG ------ sequentially, CBUT C indicates that the ITL transaction has been submitted B: When reading this ITL, the block needs Rollback (unanimously reading) caused ITL from Rollback Segment, that is, the ITL transaction is Submitted many times to tell the earliest part u indicate that the ITL transaction has been submitted, but the SCN submitted SCN is "guess" on the basis of data validity, it may not be a true commit scn, this SCN is SCN / fsc. T: The LOCK information on the data line involved after the transaction is submitted. The LB will be lb. When the next DML is operated to the block data, the LB will be cleared, indicating that the LOCK (of course, even if the ITL is not cleared Submit also believes that the lock does not exist), in this cleaning process, the corresponding FLAG position is set to T. LCK indicates the number of rows that the ITL affects it on the block 2. Why is RowID repeat?
We want to know why ROWID repeats, first understand the meaning of ROWID. RowID requires 10 bytes of storage on disk and use 18 characters to display it contains the following components.
¨ Data object number The number of each data object is allocated when you are created, and this number is unique in the database;
¨ Related File Number This number is unique to each file in a table space;
¨ The block number indicates the location of the block containing this line in the file;
¨ Row number identifies the position of the line of row of the block;
The internal data object number requires 32 bits, the relevant file number requires 10 digits, the block number requires 22, the bit line number requires 16 bits, add up to 80 bits or 10 bytes, and RowID uses 64-based coding scheme. Displaying this scenario for data objects, numbered three locations for related file numbers Six positions for block numbers Three locations for row numbers with 64 coding scheme using characters AZ AZ 0-9 And / a total of 64 characters as shown in the example
Aaagwq Aad Aaabxt AAA
In this example
AAAGWQ is the data object number
AAD is the relevant file number
AAABXT is the block number
AAA is the number
In block 5587, DelParment and Employee is the same as starting from 0, file numbers, and block numbers, let's take a look at the data object number:
SQL> Select Object_name, Object_ID, DATA_OBJECT_ID FROM User_Objects
2 WHERE Object_name in ('Department', 'Employee', 'E_D_CLUSTER');
Object_name object_id data_Object_id
-------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
E_d_cluster 26000 26000
EMPLOYEE 26004 26000
Department 26002 26000
From here you can see that the object number is different, but in a CLUSTER data object number is the same. Since the components constituting RowID are the same, then ROWID is naturally the same. 3. Why is this information about TRAN?
TL: 38 FB: -CH-
FL--
LB: 0x2 CC: 6 CKI: 0
LB: 0x2 indicates that there is a transaction 0x02 on the change, which is the row of Oracle, if there is no transaction on the line, LB: 0x0, no lock. But whether the data is locked and relying on this business is active. Although LB: 0x2, we can see the business 0x02 status in ITL -, indicating that transactions have been submitted, ie non-active transactions, So this line data is not locked.
