Recommended Oracle tablespace

In Oracle databases, tables are the most basic content. It can be said that the quality of table design is directly relatedDatabasePerformance. Therefore, when designing a table, in addition to complying with its inherent database principles, you also need to look at your own databaseManagementExperience. I will share my experiences here, which may be helpful to you.

1. Where should the table be stored?

We all know that in Oracle databases, the concept of space is used to manage table objects. Some tablespaces have been created in the database during database creation. So when we create a new table, where should we place the new table? This is like sitting at a meal. Generally, we should follow the following suggestions when creating a table:

First, a system tablespace already exists in the database during database creation. Generally, this tablespace only contains data dictionaries and ORACLE System Objects. If we create our tables in this space, we need to reduce the database performance. Therefore, we do not recommend that you create a table in this space. However, if more than one person maintains the database, how can we ensure that other users do not create database tables in the system tablespace when eight people design the database system together? The best way is to use permission control. For example, we can specify a default tablespace for each Database Designer so that they can only create tables in the tablespace. In this way, it can prevent them from creating their own data tables in the system tablespace, thus adversely affecting the running performance of the database. Therefore, if you set the default tablespace for each user, you do not need to specify the tablespace when creating a specific table.

Second, when we design a database for an application, we 'd better plan the table space first. Generally, do not randomly distribute data tables to different tablespaces. For example, when we design a database for an ERP system, it is unwise to put the tables related to the purchasing department and the sales department into two different tablespaces. This will reduce the efficiency of some database management and maintenance operations, such as dataBackupIn addition, data of a specific application cannot be centrally managed. Therefore, we generally recommend that you place tables of the same application in the same tablespace during database tablespace planning. If you want to differentiate tables of different departments or modules, you can enter the table name. For example, when designing an ERP system database, we can add a prefix to the database for identification based on the different application modules. For example, for tables related to the basic configuration of the system, we can use ad as the prefix. For tables related to the sales department, we can add the SA prefix. In this case, the specific modules of these tables are clear. There is no need to set different tablespaces for this purpose. This is a common mistake for beginners of Oracle databases. This is mainly caused by a lack of familiarity with Oracle tablespace definitions.

2. pay extra attention to tables with a relatively large storage volume.

Some tables are very large. What we are talking about here is big. It doesn't necessarily mean that the structure is complex. Instead, it means that a large amount of data is expected to be stored in this table. In order to improve the processing efficiency of this table, we must make certain arrangements in advance. Otherwise, the query and insert operations on these large tables will be slow. Therefore, it is necessary for us to estimate the data storage capacity of tables in advance during database design, and make some additional settings for tables with large data volumes. For example, in the database settings of ERP software, the data volume of the two tables, product data and material list data, will be relatively large. In the long run, the amount of data in such document-related tables as sales orders, purchase orders, production orders, and accounting creden。 is also large. A year or two may not feel it, but after ten years, the number of records will be very large. Such as the ERP systemInformatizationManaging projects is a normal thing for decades. We do not recommend that you delete the previous data to ensure the integrity of the record. Therefore, when designing databases for such applications, we should fully consider the performance of these large tables.

Specifically, you can consider following the following suggestions when designing a large table.

　　First, do not set storage restrictions for large tables. In Oracle databases, you can set storage quota limits for each table. In this case, the maximum table capacity cannot exceed this limit. For tables with small data capacity, this setting can improve the space utilization. However, if the data volume is large, it is not recommended to set the table storage space in advance. For example, the sales order table of the ERP system may have a small record volume at the beginning. The first year is expected to have only 1 GB of record capacity. However, it is estimated that the record capacity will reach 10 Gb in ten years. In this case, how can we set a bucket for it? It is unreasonable to set up 10 Gb space at the very beginning. In addition, setting a bucket means that storage fragments may be generated, which affects the efficiency of data query. Therefore, we recommend that you do not limit the storage space of tables of certain applications when designing database tables.

The second isAllocate enough temporary space for large tables. For example, we need to query product information when using the ERP system. We all know that there are someEnterpriseThe number of records is very large. We also perform sorting operations frequently during queries. For example, query data is sorted by product code. This sorting action requires a large temporary storage space.Therefore, when a table is expected to have a large number of records, we need to allocate enough temporary space for it. The storage parameter settings of the temporary space depend on the default storage parameter settings of the temporary tablespace. We can change these parameters to meet our requirements..If you do not allocate enough temporary space to a large table, the sorting will be slow and may fail..
Third, yesConsider separating a table from a table's index. The index of a large table is usually relatively large. In general, the number of indexes increases as the number of table records increases. The two indexes are close to a proportional relationship. Therefore, when the record capacity of a table is large, the number of indexes is also large. In this case, we consider breaking through the table space rule definition described above. Consider storing the table and its indexes in different tablespaces, or even separately stored on different hard disks when conditions permit. What are the benefits of doing so?The biggest advantage is that the index can easily obtain the required continuous storage space, thus improving the input efficiency.In other words, data query efficiency can be improved. If this is not the case, it may take 30 seconds for the database to query a large-capacity record. If this is the case, the time may be reduced to 10 seconds. This is a significant performance improvement.

3. How to name a table?

I have discussed how to allocate buckets to tables. Next, I will describe this issue in detail to help the database administrator master a set of good database naming rules.

First of all, there is no doubt that the naming rules of the Oracle database should be followed when it is named. For example, you cannot name a table starting with a number. For example, you cannot name a table using the database keywords, for example, the table name cannot be repeated. These are the most basic requirements. In addition to following these basic naming rules, we still need to follow some additional rules for the sake of subsequent database maintenance in actual work. These rules are different from those defined by Oracle. The rules we are talking about are not binding. It can be said that they are only consensus in the industry. If you do not handle this problem, the Oracle database won't say you have an error, but it will be troublesome for subsequent maintenance.

First, when naming databases, it is best to reflect the classification relationship of tables. For example, when designing a database, tables are differentiated by specific modules of the system. For example, if the requirements of the front-end system are different, database tables can be roughly divided into basic system configuration tables, sales module tables, procurement module tables,ReportsModule tables. We can distinguish different prefixes based on different modules. The benefits of doing so are obvious. If you see the maximum name of a table, you can know which application and module the table belongs to. This will undoubtedly improve the database design and front-end.SoftwareDevelopment efficiency. At the same time, the default sorting rules in the database are sorted by name. Therefore, if you set a category prefix for the table, you can sort the tables of the same category together for us to check.

Second, you should consider readability when naming tables, rather than randomly naming them. The most common is that those who are new to learning databases, such as creating severalTestTables, such as test1 and Test2. Although this is only a test, it does not comply with our naming rules. To perform a test, start with test and add the specific content to be tested. In this case, we can know the specific purpose of the table through the table name. You do not need to open the table to view the specific structure or comment in it to know the information we need. Therefore, when designing a table name, you need to pay attention to its readability.