Organize tables reasonably in Informix-online under UNIX

With the development of banking business, its database is also growing rapidly. Therefore, effectively controlling the database size and reasonably organizing tables can improve computer operation efficiency and reduce financial risks. As we all know, databases are composed of tables. In actual use, tables change dynamically, and some tables grow rapidly. When a table has more than eight staggered intervals in dbspace, the access efficiency of the table will be affected. As the number of staggered intervals increases, the access to the database may even deteriorate, system bumps often occur. The specific manifestation is slow response time and extended backup time. You can run the onstat-giof command and the glance (UNIX) command to check whether the disk I/O is always high.

Here are two commands to view the table allocation: oncheck-pe and oncheck-pc. The former generates a detailed list of pages in each chunk of dbspace. The latter checks the integrity of the system directory table and makes summary statistics on the situation of each table, including the creation time, lock category, total number of intervals, and interval size. The latter runs for a little longer. Check the results carefully, especially for tables that frequently perform deletion, insertion, and addition of records. The results may surprise you. I have seen a table with 460 MB and 67 intervals, and another 430 MB and 57 intervals, seriously impacting system performance. Ignore tables smaller than megabytes.

Generally, a dbspace has multiple chunks, which can be viewed using onstat-d. However, not all chunks are allocated properly, and some chunks are often useless (because the database is usually allocated ample space when it is initially created ), some chunks are crowded and tables are severely staggered. To balance I/O and improve access efficiency, you can add tables with rapid growth to a separate chunk. However, unfortunately, Informix-online does not provide such a command. Here we will introduce a clever method. Let's take a look at the simple Createtable command. It can be followed by the extentsizeextent-size and nextsizenext-size parameters. The former specifies the initial interval size of the table to be created, the latter specifies the size of the extended interval when the initial interval is full, in KB. Statement:
Create table test
(T1 char (10)
T2 char (20)
)
Extent size 80000
Next size 500.

The initial space of the test table created by this statement is 80 Mb. For example, the initial space allocated for each test is KB after the data is filled.

When creating a table in lnformix, the space allocation policy is to first find a continuous and large enough space for the table. We can use this to solve the problem easily. Assume that there is a dbspace, which has two chunks and the second is idle. Let's put some rapidly growing tables into the second chunk. Step 1: Back up the database. Step 2: delete useless records from tables with fast growth and unload them. Step 3: drop the table to be rebuilt. Step 4: Use onstat-d to check the number of idle pages in chunk1 and set it to n. Step 5: Create a table test and set its extent-size to be slightly less than n * m. m is the size of each page, usually 2 K and 4 K. Step 6: Use onstat-d to check whether chunk2 is still idle. That is, make sure the test table in the previous step is created in chunk1. Otherwise, delete the test table and assign a smaller extent-size to recreate the test table. Step 7: Create the table you want to create and set it to Tb. Set the appropriate extent-size value, which is better than the total space occupied by the Tb table. At this time, Tb must have been created in chunk2! Because Informix cannot find a suitable space in chunk1. Remember to delete the test table. If your database only has one chunk, step 4 and Step 5 are not required. This also helps reduce table interleaved.

In addition, when creating indexes for such tables, you can add the fillfactor10 parameter after Createindex. It decides to reserve 90% of the space for creating indexes, which is good for tables with frequent updates and fast growth, it enables the index to be stored continuously in a certain space, which also improves the access efficiency. The default value is 90. After creating a table and loading data, it is best to disable the log, which helps shorten the time for loading data.