PowerPoint

2003 03

# (1)1. 1.1 1.2

2. 2.1 2.1.1 2.1.2 2.1.3 2.1.4 ERD 2.2 2.2.1 2.2.2 2.2.3 2.2.4 2.2.5 # (2)2. 2.3 2.4 2.4.1 2.4.2 2.4.3 2.4.4 2.4.5 2.4.6 2.4.7 2.4.8 2.4.9 2.4.10 Partition 2.4.11 2.5

3. 3.1 3.2 #1.1 1. . , . DBMS DB #1.2 1. PhaseTaskActivity

ERD Naming Rule (, , , ) Entity . ( ) Entity ( ) #1.2 1. PhaseTaskActivity Entity (3) .DDL . .

#2.1.2 2.1 : : : , , .

#2.1.3 2.1 , , 1:1, 1:M, M:N . (ER_win) M:N 1:M . Mandatory Optional . Identifying . Non-Identifying non-key Null . , , . .#2.1.4 2.1 : , ATTRIBUTE UID ENTITY UID ATTRIBUTE . (mandatory) (Main) (Action) UID . UID UID ATTRIBUTE (: ) (:) ATTRIBUTE UID bar ATTRIBUTE UID UID RELATIONSHIP #2.1.5 ERD 2.1 . ERD . Cardinality Sub Area . .#2.2 2.2.1 : . : . . : : (, ) .# 1 .() 2 UID ( ) 2 Identifier non-Identifier 3 UID . 2.2.2 2.2 # (FK) (FK)2.2 M:N 1:1 Subtype

2.2.2 1:1, M:N #132.2 2.2.2 ? ? Relationship ? ? (version) ? : ), : ), : ), . . ( ) 9999-12-31 .#142.2.3 ,

. 2~4 . . . 2.2 #2.2.4 2.2 (?) . Not Null, Check, Default SQL ( ) #162.2.4 - 2.2 . ) , ->, -> ) , , -> , ) , -> Sub Type . #17

: 2.2.4 - 2.2 #18 BOM -

2.2.4 - 2.2 #192.2.4 - Subtype (1) 2.2 SUB-TYPE SUPER-TYPE SUB-TYPE SUB-TYPE SUPER-TYPE SUB-TYPE SUPER-TYPE SUB-TYPE SUPER-TYPE FK SUB-TYPE FK SUB-TYPE SUB-TYPE SUB-TYPE SUB-TYPE SUB-TYPE SUPER-TYPE SUB-TYPE FK SUPER-TYPE FK #202.2.4 - Subtype (2) 2.2 VIEW SUB-TYPE SUB-TYPE SUB-TYPE (JOIN) SQL SUB-TYPE NOT NULL SUB-TYPE (TYPE) (INDEX) SUB-TYPE SUB-TYPE , SUB-TYPE UNION SQL VIEW UID

#212.2.4 - 2.2 Null Erwin Role

#222.2.5 2.2 Crud Matrix CRUD ? C ? R ? . ? ? #232.2.5 2.2 Primary Key ? Primary Key Primary Key Primary Key ? Primary Key ? Primary Key (Identify Non Identify ) ? (, , -> ) ? ? . ? . Primary Key ? Primary Key Flag Primary Key Primary Key #242.2.5 2.2 ? . ? ? . ? ? , ? #252.2.5 2.2 M:N ? M:N All or nothing 1:N M:N ? Mandatory Optional . FK ? ? Primary key #26 M:N . 1:M, 1:N . 1:M mandatory optional . 2.3 2 Constraint Storage

/ Partition #272.4.1 2.4 OSCPUMemoryDisk System TablespaceRollback SegmentData Buffer Cache

#282.4.2 2.4 : Crud matrix : : I/O .#292.4.3 2.4 DBMS Row Row Row #302.4.4 2.4 . 2~4 . (Disk ) . Storage . OS Striping . (Partition) . Extents Size . Locally Management tablespace . . #312.4.5 2.4 , Partitioning #322.4.5 2.4 1:1 1:M Super type Sub Type ( ) ( ) Locking SQL

#332.4.5 2.4

#342.4.6 (1)2.4 Entity table TABLE ENTITY ENTITY (instance chart) TABLE SUPER-TYPE SUB-TYPE Attribute Column Primary Key Foreign Key SQL (reserved word) SQL Nulls/Unique NN DATE DATE LOGGING, Null * ( ) Default .#352.4.6 (2)2.4 Constraint / . Rule Constraint Not Null Unique Check Check Primary Key Foreign Key#362.4.7 (1)2.4 Primary Key Foreign Key SQL MULTI BLOCK READ MULTI BLOCK READ 16 16 MULTI_BLOCK_READ_COUNT 8 (BLOCK SIZE 8K) 64k . = / * 100 10 ~ 15% (ORDER BY, GROUP BY, UNION, DISTINCT)#372.4.7 (2)2.4 (NULL ) DML 5 CHAR VARCHAR2 Date = Substr, Like = #382.4.8 2.4 Crud Matrix SQL #392.4.9 2.4 , , ( ) 6 Union, Distinct, Order by, Group by #402.4.10 Partition Partition : , , DB Data Performance Data Disk Striping I/O Performance (Disk ) Partition Range Partitioning () : (Oracle8) Hash Partitioning () : (Oracle8i) Composite Partitioning () : (Oracle8i)2.4 #

(CRM) - + - 2.4.10 Partition Partition Key "I/O ?" Partitioning Key 2.4 #42 : 2.4.10 Partition 2.4 Local prefixed partitioned index Index Column Column Partition Key Column Index Partition (Partitioning Key) Index Local non-prefixed partitioned index Index Column Partition Key Column Local Index Partition Key Column Unique Index Global Index Global prefixed partitioned index Drop Index Rebuild Partition Key Column Unique Index Global prefixed non-partitioned index Partitioned Primary Key#432.4.11 2.4 1 2 3 4 42 Oracle executablesIndex data filesRedo logsExport filesControl fileData data filesRollback segment fileTemporary user data filesArchive log filesControl file3 Oracle executablesRedo logsRollback segment fileExport filesControl fileData data filesTemporary user data filesControl fileArchive log filesIndex data filesControl file

4 Oracle executablesIndex data filesRedo logsExport filesControl fileData data filesTemporary user filesControl file

Index data filesControl file

Archive log filesRollback segment file

5 Oracle executablesRedo logsSYSTEM tablespace data filesControl fileData data filesTemporary user filesControl file

Index data filesControl file

Rollback segment fileExport filesArchive log files

#442.5 2. #453.1 3. #463.2 (1)3. , , , , , , , , , , , , #473.2 (2)3. NumberVarchar2(20)NumberNumberVarchar2(20)NumberNumberNumberVarchar2(20)Varchar2(13)Varchar2(6)-

-

- - - Varchar2(2)Varchar2(2)Varchar2(2)Varchar2(2)Varchar2(2)Varchar2(2)(V)(D)Varchar2(8)DateNumber(3)#483.2 (3)3. () (?), , , , (?), , , - , , - , , , , , , , - , , , , , account acct: acct_no Amountamt #493.2 (4)3. SCOTT TSTEST01 IDEMPPctused70Pctfree50IDNNPKFKEMPNOVARCHAR26VVEMPNMVARCHAR240VJUMINNOVARCHAR213VDEPTNOVARCHAR22VV#503.2 (5)3. EP CRUD 1,R12002, , C12003, , C51,0004, , R51,000 V_EMP EMPEMPNOEMPNMHIREDATEVarchar2(6)Varchar2(40)DATEV_ORDERITEM ORDERORDERNOORDERNMORDERDATEVarchar2(6)Varchar2(40)DATEORDERITEMITEMNO PRICEVarchar2(6)Number(10) #513.2 (6)3. DEPTI_DEPT01DEPTNONUMBER(2)ISTEST01UNIQUEASCPKEMPI_EMP01EMPNOVARCHAR2(6)ISTEST01UNIQUEASCPKI_EMP02EMPNOVARCHAR2(6)ISTEST01NOT UNIQUEDESCINDEXHIREDATEVARCHAR2(8) Row (Byte)()()CODE44102,5001005EMP120108, 0218050DEPT44102,3301010ITEM26053,211,8743,700200#523.2 (7)3. (40% ) TABLE1TABLE2TABLE330M20M100MTS01150M + 60M = 210MDF001.DBFTABLE5TABLE6TABLE710M5M100MTS02115M + 46M = 161MDF002.DBF01DF002.DBF02 /DISK1/DISK1/ORADATA/ORA8/DB1DF001.DBF01320MTS001210MTS002100MDF002.DBF01500MTS002861MDF002,DF01 /DISK2/DISK2/ORADATA/ORA8/DB1DF001.DBF02110MTS003100MDF002.DBF02500MTS002861M#533.2 (8)3. /DISK1/DISK1/ORADATA/ORA8/DB12G820M41%DF001.DBF001320MDF002.DBF001500M/DISK2/DISK2/ORADATA/ORA8/DB12G1510M75%DF001.DBF02500MDF001.DBF02500MDF001.DBF02110MDF001.DBF02900M#54