Database skills-Oracle tablespace Design Concept

The greatest difference between an SQL Server database and an Oracle database is the tablespace design. Oracle Database pioneered the design concept of tablespace, which makes an indelible contribution to the high performance of Oracle Database. In this case, many Oracle optimizations are implemented based on the tablespace design concept.

Typical application 1: control the tablespaces occupied by users.

In some large database applications, we need to control the disk space occupied by a user or a group of users. This is like setting a disk quota for each user on the file server to prevent hard disk space depletion. Therefore, in the database, we also need to limit the disk space available to users. To achieve this goal, we can implement it through the tablespace.

We can create different tablespaces in the Oracle database, set the maximum storage capacity for them, and then assign users to the tablespaces. In this case, the user's storage capacity is limited by the size of the tablespace.

Typical application 2: control the disk space occupied by the database.

Sometimes, there may be more than one service running on the Oracle Database Server. In addition to database servers, there may also be application system servers such as email servers. Therefore, you must first plan the disk space of the Oracle database. Otherwise, when the disk space occupied by multiple application services increases infinitely, in the end, each service may be stopped due to the exhaustion of hard disk space. Therefore, to use multiple application services on the same server, we often need to plan and allocate disk space for them first. Each service cannot exceed the maximum limit we allocate to it, or remind us immediately after it is exceeded. Only in this way can we avoid the crash of various application services due to the depletion of disk space.

Typical application 3: flexibly place tablespaces to improve database input/output performance.

The database administrator can also store different types of data in different tablespaces, which can significantly improve the database input and output performance and facilitate data backup and recovery management. Because our database administrator can back up data by tablespace when backing up or recovering data. For example, when designing a large distribution system background database, we can create tablespaces by province. Data Files related to Zhejiang Province are stored in the tablespace of Zhejiang Province. Business records in Beijing are recorded in the tablespace of Beijing. In this way, when the business data in Zhejiang Province is incorrect, the tablespace in Zhejiang province can be restored directly. Obviously, in this design, when data in a tablespace is wrong and needs to be restored, the impact on other tablespaces can be avoided.

In addition, the table space can be backed up independently. When the database capacity is relatively large, if you back up the entire database at once, it will obviously take a lot of time. Although Oracle Database supports hot backup, it occupies a large amount of system resources during the backup process, resulting in a decline in database performance. Therefore, when the database capacity is relatively large, we need to set multiple tablespaces, and then plan the backup time of each tablespace to improve the backup efficiency of the entire database, reduce the impact of backup on the normal operation of the database.

Typical application 4: Sorting of large tables.

We all know that when there are many records in the table, the query speed will be slow. After the first query is successful, it still takes so much time to re-Sort it for the second time. For this reason, when we design a database, we usually place such a large table object in an independent tablespace to improve the database performance.

Typical application 5: open log files and data files to improve database security.

• 2 pages in total:
• Previous Page
• 1
• 2
• Next Page