Discussion on the modeling and design of Oracle database
- 2020-06-01 11:12:33
- OfStack
The ORACLE tutorial you are looking at is a brief introduction to the modeling and design of the Oracle database. To develop a database-based application system, one of the most critical steps is the database modeling design based on the whole system, from the logical to the physical, a link is not designed, the entire application system is like building on a dangerous house, with the development process, it will face all kinds of difficulties at any time
At the expected risk, developers pay an unpredictable price for modifying or redesigning poorly designed database systems. Therefore, a good database design is necessary for an efficient system.
1. Logical modeling
Database design method for specific database varies, but the modeling phase of the same, so you can use 1 some common tools, such as Rational rose, PowerDesigner etc., this one phase is mainly based on the demand of the system, and analysis in order to realize the application of system information, analysis of data of the internal and external relations, so as to effectively create the data structure of the whole system (usually in a relational database called table structure), and on this basis to the amount of data in a database, data flow, and estimate analysis of response speed, this data model is created. Concrete operating rules of the database, the user's specific requirements and analysis several paradigm of experience, from the performance of the database, security, convenient management, easy to develop, the specific method because of the analysts preferences and habits of different, can need not tools, it is best to use tools, make the analysis process is simple, the most main is can generate 1 figure, such as E - R figure, let know 1 analysis process.
2. Physical design
This step design and system database of concrete use, also relates to the hardware and software platform of database runs, the purpose is to reasonably allocated physical space to the database, the step 1 in database design is very important, related to the performance of the database data security and database, specific, this step 1 contains a list of the corresponding space of the distribution of the data files on disk, and according to the size of the volume of data to determine redolog file, rollback section size, and then, the distribution of these files should follow some principles, 1 for backup, conducive to the principle of optimization, The principles are as follows (take the ORACLE database as an example) :
1. Create different table Spaces for tables and indexes. Prohibit non-core oracle system component objects from the system table Spaces, and ensure that the data table Spaces and index table Spaces are located on different disk drives.
2. Understand how end users access data and, if possible, place frequently queried and frequently queried objects on different physical disks.
3. When a database contains large objects that allow users to access different data elements in parallel, it is beneficial to split the objects on multiple disks. Partitions can be used. You need to be even more careful when defining a table with millions of rows on an operating system platform, because the size of the database file is limited, which is caused by the operating system, not oracle.
4. Create at least two user-defined rollback tablespaces on separate disks to hold the user's own rollback segment. Arrange the order of the rollback segments in the initialization file so that they switch between multiple disks.
5. Put the redo log files on a disk with fewer reads and writes. More than two redo log groups are established for each oracle instance, with two members of the same group placed on different devices.
6. Determine the size of the tables and indexes, which determines the size of the table Spaces needed to hold them, which table Spaces are physically on which disks, and which table Spaces can be combined in 1. The specific estimation method can be based on some formulas of oracle 1, and the storage parameters of each table should be defined according to its specific characteristics and purposes, such as (pctfree,pctused).
3. Design of database parameters
Every database set, the default parameter Settings, but for specific application requirements parameter Settings may be different, the default parameter Settings often need according to the characteristics of the application system and need to change, such as the number of operating system platform, instance each database, all kinds of memory size Settings, adopt the mode of the thread and the way of backup, specific parameters is 1 set to be the most appropriate modifications, this step is very important for database performance, and ensure that the required database application system functions implemented important step 1.
4. Interface problems with development software
The last thing to be considered in database design is the interface problem with the development software to be selected. Interface programs should be prepared, some of which are already prepared by the third party software, some of which are required by the database itself, such as jdbc,bde,ado, etc., and the interface with the database, mainly considering the usability and efficiency of the interface. This 1 step mainly starts from the experience, because this kind of product comes out ceaselessly, and it is through each businessman's adulation, want to decide which is the most suitable in the practice.
Above is the database modeling design of several important steps of approximate analysis, the whole design process is constantly improved, is the database administrator, designers, developers do together, just each have different emphasis, database administrators and so on the design of step 2, 3, designers focused on the design of the step 1, and the developer focused on the design of the four steps, because some of the application system programming environment and the practical application environment is different, so to do two sets of design, and pay attention to the compatibility of two sets of design portability.
The following combination of a practical development of labor management system database design, the above steps to do a step forward to explain.
The system is applied to large enterprise groups, including several 10 branch factories. The basic data are all from each branch factory. Each branch factory has a set of access database, which stores its own talent file information. This is a partial database.
1. Logical modeling
First is the amount of data to the database, data flow, and estimate analysis of response speed, the step 1 to the physical design is also necessary, industrial system is the main data of the personnel file, there are about 100 million or so, and personnel information, salary level and type of work sequence, factories workshop and so on all kinds of coding, the total amount of data about 300 million or so in some ways. Data input and maintenance are mostly carried out in various factories and mines, and then concentrated everywhere, so the data flow is large and the response speed is as required as 1.
On the tool of logical modeling, Rantional rose 2000 is adopted. Various use case view are designed according to the needs of users. In logic view, the database entities and the E-R diagram between the entities are generated, and then Data modeler is generated.
The detailed diagram is very tedious and I won't show it here.
2. Physical design
1, because the staff file information base is very large, there are photos, so it is divided into two tables, the photos alone in a table, and the use of partition technology, in the file table, the establishment of a photo table partition, different partitions are placed in different table space.
2. Since there are many codes in the database of employee files, the operation of read shall be conducted from the code library 1 when querying, so the information table of employee files and the code library shall not be placed in one table space.
3. Set up two rollback table Spaces and 10 rollback segments, divide them into two table Spaces, and set up a relatively large rollback segment, because the employee file information table is often modified and added in batches.
4. Put the redo log files on a disk with fewer reads and writes. Create three redo log groups, with two members of the same group placed on two plates. Because the backup mode of ARCHIVELOG is chosen.
5, according to the nature of each table, determine its storage parameters, such as the staff file information table modification, deletion, insert are more, so the size of its Pctfree 10,Pctused 80 fixed, estimated the number of block it needs roughly, calculate the size of the data file stored in the table space of these tables.
6. Establish a set of database security system according to user needs. That is, you summarize several levels of users, create those users, and assign those users a corresponding limit for developers to use when programming.
3. Parameter setting
From the default initialization parameters file, select large. Because there are a lot of users, about 100.
SHARED_POOL_SIZE, depending on the size of the database as well as the application. This is set to 1.5 times the default.
DB_BLOCK_BUFFERS is set to 25 megabytes, depending on the memory of the server used and the number of transactions.
SORT_AREA_SIZE is set to 10 megabytes, depending on the memory of the server used
LOG_BUFFER USES 1.5 times the default value.
DBWR_IO_SLAVES set to 1
ROLLBACK_SEGMENTS is set to 10, depending on the volume of transactions. On two separate disks.
PROCESS 80
MTS_SERVERS 3
MTS_DISPATCHERS tcp. 3
ARCHIVE_LOG_START TRUE is backed up by ARCHIVE.
The determination of these parameters is temporary, no system can be run before the parameters are precisely determined, need to be adjusted after the application run.
4. To open
Since the development software of our system USES Borland c++builder 5, we use the relatively mature BDE interface, which is provided by Borland company with good performance.
The above is based on many years of database management, development practice summary and out of the database modeling and design of the general idea, I hope to help you.
At the expected risk, developers pay an unpredictable price for modifying or redesigning poorly designed database systems. Therefore, a good database design is necessary for an efficient system.
1. Logical modeling
Database design method for specific database varies, but the modeling phase of the same, so you can use 1 some common tools, such as Rational rose, PowerDesigner etc., this one phase is mainly based on the demand of the system, and analysis in order to realize the application of system information, analysis of data of the internal and external relations, so as to effectively create the data structure of the whole system (usually in a relational database called table structure), and on this basis to the amount of data in a database, data flow, and estimate analysis of response speed, this data model is created. Concrete operating rules of the database, the user's specific requirements and analysis several paradigm of experience, from the performance of the database, security, convenient management, easy to develop, the specific method because of the analysts preferences and habits of different, can need not tools, it is best to use tools, make the analysis process is simple, the most main is can generate 1 figure, such as E - R figure, let know 1 analysis process.
2. Physical design
This step design and system database of concrete use, also relates to the hardware and software platform of database runs, the purpose is to reasonably allocated physical space to the database, the step 1 in database design is very important, related to the performance of the database data security and database, specific, this step 1 contains a list of the corresponding space of the distribution of the data files on disk, and according to the size of the volume of data to determine redolog file, rollback section size, and then, the distribution of these files should follow some principles, 1 for backup, conducive to the principle of optimization, The principles are as follows (take the ORACLE database as an example) :
1. Create different table Spaces for tables and indexes. Prohibit non-core oracle system component objects from the system table Spaces, and ensure that the data table Spaces and index table Spaces are located on different disk drives.
2. Understand how end users access data and, if possible, place frequently queried and frequently queried objects on different physical disks.
3. When a database contains large objects that allow users to access different data elements in parallel, it is beneficial to split the objects on multiple disks. Partitions can be used. You need to be even more careful when defining a table with millions of rows on an operating system platform, because the size of the database file is limited, which is caused by the operating system, not oracle.
4. Create at least two user-defined rollback tablespaces on separate disks to hold the user's own rollback segment. Arrange the order of the rollback segments in the initialization file so that they switch between multiple disks.
5. Put the redo log files on a disk with fewer reads and writes. More than two redo log groups are established for each oracle instance, with two members of the same group placed on different devices.
6. Determine the size of the tables and indexes, which determines the size of the table Spaces needed to hold them, which table Spaces are physically on which disks, and which table Spaces can be combined in 1. The specific estimation method can be based on some formulas of oracle 1, and the storage parameters of each table should be defined according to its specific characteristics and purposes, such as (pctfree,pctused).
3. Design of database parameters
Every database set, the default parameter Settings, but for specific application requirements parameter Settings may be different, the default parameter Settings often need according to the characteristics of the application system and need to change, such as the number of operating system platform, instance each database, all kinds of memory size Settings, adopt the mode of the thread and the way of backup, specific parameters is 1 set to be the most appropriate modifications, this step is very important for database performance, and ensure that the required database application system functions implemented important step 1.
4. Interface problems with development software
The last thing to be considered in database design is the interface problem with the development software to be selected. Interface programs should be prepared, some of which are already prepared by the third party software, some of which are required by the database itself, such as jdbc,bde,ado, etc., and the interface with the database, mainly considering the usability and efficiency of the interface. This 1 step mainly starts from the experience, because this kind of product comes out ceaselessly, and it is through each businessman's adulation, want to decide which is the most suitable in the practice.
Above is the database modeling design of several important steps of approximate analysis, the whole design process is constantly improved, is the database administrator, designers, developers do together, just each have different emphasis, database administrators and so on the design of step 2, 3, designers focused on the design of the step 1, and the developer focused on the design of the four steps, because some of the application system programming environment and the practical application environment is different, so to do two sets of design, and pay attention to the compatibility of two sets of design portability.
The following combination of a practical development of labor management system database design, the above steps to do a step forward to explain.
The system is applied to large enterprise groups, including several 10 branch factories. The basic data are all from each branch factory. Each branch factory has a set of access database, which stores its own talent file information. This is a partial database.
1. Logical modeling
First is the amount of data to the database, data flow, and estimate analysis of response speed, the step 1 to the physical design is also necessary, industrial system is the main data of the personnel file, there are about 100 million or so, and personnel information, salary level and type of work sequence, factories workshop and so on all kinds of coding, the total amount of data about 300 million or so in some ways. Data input and maintenance are mostly carried out in various factories and mines, and then concentrated everywhere, so the data flow is large and the response speed is as required as 1.
On the tool of logical modeling, Rantional rose 2000 is adopted. Various use case view are designed according to the needs of users. In logic view, the database entities and the E-R diagram between the entities are generated, and then Data modeler is generated.
The detailed diagram is very tedious and I won't show it here.
2. Physical design
1, because the staff file information base is very large, there are photos, so it is divided into two tables, the photos alone in a table, and the use of partition technology, in the file table, the establishment of a photo table partition, different partitions are placed in different table space.
2. Since there are many codes in the database of employee files, the operation of read shall be conducted from the code library 1 when querying, so the information table of employee files and the code library shall not be placed in one table space.
3. Set up two rollback table Spaces and 10 rollback segments, divide them into two table Spaces, and set up a relatively large rollback segment, because the employee file information table is often modified and added in batches.
4. Put the redo log files on a disk with fewer reads and writes. Create three redo log groups, with two members of the same group placed on two plates. Because the backup mode of ARCHIVELOG is chosen.
5, according to the nature of each table, determine its storage parameters, such as the staff file information table modification, deletion, insert are more, so the size of its Pctfree 10,Pctused 80 fixed, estimated the number of block it needs roughly, calculate the size of the data file stored in the table space of these tables.
6. Establish a set of database security system according to user needs. That is, you summarize several levels of users, create those users, and assign those users a corresponding limit for developers to use when programming.
3. Parameter setting
From the default initialization parameters file, select large. Because there are a lot of users, about 100.
SHARED_POOL_SIZE, depending on the size of the database as well as the application. This is set to 1.5 times the default.
DB_BLOCK_BUFFERS is set to 25 megabytes, depending on the memory of the server used and the number of transactions.
SORT_AREA_SIZE is set to 10 megabytes, depending on the memory of the server used
LOG_BUFFER USES 1.5 times the default value.
DBWR_IO_SLAVES set to 1
ROLLBACK_SEGMENTS is set to 10, depending on the volume of transactions. On two separate disks.
PROCESS 80
MTS_SERVERS 3
MTS_DISPATCHERS tcp. 3
ARCHIVE_LOG_START TRUE is backed up by ARCHIVE.
The determination of these parameters is temporary, no system can be run before the parameters are precisely determined, need to be adjusted after the application run.
4. To open
[1] [2] next page
The ORACLE tutorial you are looking at is a brief introduction to the modeling and design of the Oracle database. Software interface issuesSince the development software of our system USES Borland c++builder 5, we use the relatively mature BDE interface, which is provided by Borland company with good performance.
The above is based on many years of database management, development practice summary and out of the database modeling and design of the general idea, I hope to help you.
Previous page [1] [2]