Automatic data compression method, system, electronic device and storage medium

Abstract

The present application provides a method, system, electronic device, and storage medium for automatic data compression. The method comprises obtaining multiple target tables from a database; wherein the target tables are tables that occupy more than a preset amount of database space; obtaining access information for each target table within a first preset time period; screening at least one initial target table from each target table based on the access information for each target table; determining, for each initial target table, whether the initial target table meets automatic compression conditions; and automatically compressing the initial target table if the initial target table meets the automatic compression conditions. The present invention can improve data compression efficiency and avoid production incidents caused by potentially large disparities in the professional and technical expertise of different DBAs.

Description

Technical Field

[0001] The present invention relates to the field of data processing technology, and more specifically, to a data automatic compression method, system, electronic equipment and storage medium. Background Art

[0002] In centralized databases, to ensure operational efficiency, we often use SAN disks or SSD disks as storage media for database servers. These storage media are relatively expensive resources. For example, by introducing the compress table feature, we can reduce the disk space required for data tables.

[0003] In existing technology, database administrators (DBAs) perform table compression to save significant storage space. However, manual table compression is a significant workload, especially when a single DBA may be responsible for managing hundreds of databases. Furthermore, manual compression is inefficient, and the technical expertise of different DBAs can vary widely, leading to potential production incidents. Summary of the Invention

[0004] In view of this, the present application provides a data automatic compression method, system, electronic device and storage medium to improve the efficiency of data compression and avoid production incidents caused by the large gap in professional and technical levels between different DBAs.

[0005] A first aspect of the present invention discloses an automatic data compression method, which is applied to an automatic data compression system. The method comprises:

[0006] Acquire multiple target tables from a database; wherein the target tables are tables that occupy a database space greater than a preset value;

[0007] Obtaining access information of each target table within a first preset time period;

[0008] Filtering at least one initial target table from each of the target tables according to the access information of each of the target tables;

[0009] For each of the initial target tables, determining whether the initial target table meets an automatic compression condition;

[0010] If the initial target table meets the automatic compression condition, the initial target table is automatically compressed.

[0011] Optionally, the method further includes:

[0012] If the initial target table does not meet the automatic compression condition, corresponding reminder information is output to provide a database administrator with a reminder to perform data compression on the initial target table.

[0013] Optionally, the access information includes the number of full table scans and logical reads of the target table, and screening at least one initial target table from each target table based on the access information of each target table includes:

[0014] For each target table, determining whether the number of full table scans of the target table is less than a preset table scan threshold, and whether the number of logical reads of the target table is less than a preset logical read threshold;

[0015] If the number of full table scans of the target table is less than the preset table scan threshold, and the number of logical reads of the target table is greater than the preset logical read threshold, the target table is determined to be the initial target table.

[0016] Optionally, determining, for each of the initial target tables, whether the initial target table meets an automatic compression condition includes:

[0017] For each of the initial target tables, determining whether the database archive space capacity of the initial target table is greater than a preset capacity threshold, whether the initial target table has a global index, and whether the initial target table meets the conditions for online redefinition;

[0018] If the database archive space capacity of the initial target table is greater than a preset capacity threshold, the initial target table has a global index, and the initial target table does not meet the online redefinition condition, it is determined that the initial target table does not meet the automatic compression condition;

[0019] If the database archive space capacity of the initial target table is not greater than a preset capacity threshold, and / or the initial target table does not have a global index, and / or the initial target table meets the conditions for online redefinition, it is determined that the initial target table meets the automatic compression conditions.

[0020] Optionally, if the initial target table meets the automatic compression condition, automatically compressing the initial target table includes:

[0021] If the initial target table meets the automatic compression condition and the current time of the data automatic compression system is within a second preset time period, obtaining the current archive space usage rate of the database;

[0022] If the current archive space usage rate of the database is not less than the preset usage rate, after waiting for a preset time, return to execute to obtain the current archive space usage rate of the database;

[0023] If the current archive space usage rate of the database is less than the preset usage rate, the initial target table meets the archiving condition, all indexes on the initial target table are local indexes, and the initial target table meets the online redefinition condition, obtain the table space capacity and space usage rate of the initial target table;

[0024] If the index space occupancy rate calculated based on the table space capacity and space usage rate of the initial target table is less than the preset occupancy rate, modifying the compression attribute of the initial target table to the target compression attribute, and creating a compressed intermediate table with the target compression attribute;

[0025] Using an online redefinition toolkit to create corresponding permission information for the compressed intermediate table, and synchronizing the data of the initial target table after the compression attribute is modified to the intermediate table; wherein the permission information is the same as the table information of the initial target table;

[0026] The initial target table after the compression attribute is modified is deleted from the database.

[0027] Optionally, the method further includes:

[0028] The default value corresponding to the automatic compression status in the information table corresponding to the initial target table is updated to compression completed; wherein, the information table is generated based on the table information of the initial target table, and the table information at least includes the automatic compression status, archiving conditions, and online redefinition conditions.

[0029] A second aspect of the present invention discloses an automatic data compression system, the system comprising:

[0030] A target table acquisition unit is used to acquire multiple target tables from a database; wherein the target tables are tables that occupy a database space greater than a preset value;

[0031] an access information acquiring unit, configured to acquire access information of each target table within a first preset time period;

[0032] a screening unit, configured to screen out at least one initial target table from each of the target tables according to access information of each of the target tables;

[0033] A first judging unit is configured to judge, for each of the initial target tables, whether the initial target table satisfies an automatic compression condition;

[0034] The data automatic compression unit is used to automatically compress the initial target table if the initial target table meets the automatic compression condition.

[0035] Optionally, the system further includes:

[0036] The information output unit is used to output corresponding reminder information to provide a database administrator with information for performing data compression on the initial target table.

[0037] The third aspect of the present invention discloses an electronic device, which includes a processor and a memory, wherein the memory is used to store program code and data for automatic data compression, and the processor is used to call the program instructions in the memory to execute a data automatic compression method disclosed in the first aspect of the present invention.

[0038] A fourth aspect of the present invention discloses a storage medium, which includes a storage program, wherein when the program is running, the device where the storage medium is located is controlled to execute an automatic data compression method disclosed in the first aspect of the present invention.

[0039] The present application provides a data automatic compression method, system, electronic device and storage medium, which can query and obtain multiple target tables whose space occupied by the database is greater than a preset value through a database, obtain access information of each target table within a first preset time period, and screen out at least one initial target table from each target table based on the access information of each target table; for each initial target table, if the initial target table meets the automatic compression conditions, automatically compress the initial target table, and complete the data table compression without manual intervention, which not only improves the efficiency of data compression, but also avoids the problem of production incidents caused by the large gap in professional and technical levels between different DBAs. BRIEF DESCRIPTION OF THE DRAWINGS

[0040] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the following briefly introduces the drawings required for use in the embodiments or the description of the prior art. Obviously, the drawings described below are merely embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on the provided drawings without paying any creative work.

[0041] Figure 1 A flowchart of a method for automatic data compression provided in an embodiment of the present application;

[0042] Figure 2 A flowchart of a method for automatically compressing an initial target table provided in an embodiment of the present application;

[0043] Figure 3 A schematic diagram of the structure of an automatic data compression system provided in an embodiment of the present application;

[0044] Figure 4 A schematic diagram of the structure of an electronic device provided in an embodiment of the present application. DETAILED DESCRIPTION

[0045] The following will clearly and completely describe the technical solutions in the embodiments of the present invention in conjunction with the accompanying drawings. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without making creative efforts are within the scope of protection of the present invention.

[0046] As used herein, the term "including" and its variations are open-ended, i.e., "including but not limited to." The term "based on" means "based, at least in part, on." The term "one embodiment" means "at least one embodiment," the term "another embodiment" means "at least one additional embodiment," and the term "some embodiments" means "at least some embodiments." Other terms are defined in the following description.

[0047] It should be noted that the concepts of "first" and "second" mentioned in the disclosure of the present invention are only used to distinguish different systems, modules or units, and are not used to limit the order or interdependence of the functions performed by these systems, modules or units.

[0048] It should be noted that the modifications of "one" and "multiple" mentioned in the disclosure of the present invention are illustrative rather than restrictive, and those skilled in the art should understand that unless otherwise clearly indicated in the context, it should be understood as "one or more".

[0049] See also Figure 1 , shows a flow chart of an automatic data compression method provided by an embodiment of the present application, the automatic data compression method is applied to an automatic data compression system, and the automatic data compression method specifically includes the following steps:

[0050] S101: Acquire multiple target tables from a database; wherein the target tables are tables that occupy a database space greater than a preset value.

[0051] In an embodiment of the present application, a scheduled data automatic compression task can be created. For example, a specified data automatic compression task is created to start at 12 o'clock every night. When the scheduled time is reached, the corresponding data automatic compression task is started, and multiple target tables that occupy a space larger than a preset value in the database are queried and obtained from the database.

[0052] It should be noted that the preset value may be 100 GB and may be set according to actual applications, and is not limited in the embodiments of the present application.

[0053] It should also be noted that when querying the database, you can add the following filter conditions to quickly exclude common tables that occupy no more than a preset value of database space, as well as exclude target tables that have undergone data compression.

[0054] The filter condition can be: OWNER not in('SYSTEM','SYS','OUTLN','ORACLE_OCM','DBSNMP','APPQOSSYS','WMSYS','DIP','TSMSYS','EXFSYS','XDB','ANONYMOUS','MDSYS','ORDPLUGINS','ORD SYS', 'SI_INFORMTN_SCHEMA', 'SYSMAN', 'MGMT_VIEW', 'XS$NULL', 'ORDDATA', 'FLOWS_FILES', 'DMSYS', 'CTXSYS', 'OLAPSYS', 'MDDATA', 'SCOTT', 'PUBLIC').

[0055] Filter condition for excluding target tables that have been compressed: (TABLE_NAME or PARTITION_NAME) not in (COMPRESSION = 'ENABLED' AND COMPRESS_FOR <> 'OLTP').

[0056] Furthermore, in the embodiment of the present application, for each target table, table information corresponding to the target table can also be obtained, and an information table corresponding to the target table can be generated according to the table information corresponding to the target table, as shown in Table 1.

[0057] Table 1:

[0058]

[0059] Among them, Table_Owner is the user name of the user to which the target table belongs, Table_Name is the table name corresponding to the target table, Part_Name is the partition name corresponding to the target table, Seg_Size is the archiving space capacity of the target table, when the default value corresponding to Can_Arch is Y, it means that the target table meets the archiving conditions, otherwise it does not meet the archiving conditions; when the default value corresponding to Index_Local is Y, it means that all indexes on the target table are local indexes, otherwise there are indexes on the target table that are not local indexes; when the default value corresponding to Can_Redef is Y, it means that the target table meets the online redefinition conditions, otherwise it does not meet the online redefinition conditions; Compress_Stat is the automatic compression status of the target table. If the default value corresponding to Compress_Stat is N, it means that the target table has not been compressed, otherwise it has been compressed.

[0060] It should be noted that if Part_Name exists in the target table, it means that the target table is a partitioned table.

[0061] S102: Obtain access information of each target table within a preset time period.

[0062] In the embodiment of the present application, after at least one target table is queried from the database, for each target table, corresponding access information of the target table within a preset time period can be obtained.

[0063] It should be noted that the preset time period can be 30 days, which can be set according to actual application and is not limited in the present embodiment.

[0064] It should be noted that the access information of the target table within the preset time period includes the number of logical reads within 30 days (such as the current LOGICAL_READS_TOTAL value - the LOGICAL_READS_TOTAL value 30 days ago) and the number of full table scans within 30 days (such as the current TABLE_SCANS_TOTAL value - the TABLE_SCANS_TOTAL value 30 days ago).

[0065] S103: Filter out at least one initial target table from each target table according to the access information of each target table.

[0066] In an embodiment of the present application, a preset table scan threshold and a preset logical read threshold are pre-set. After obtaining the access information corresponding to each target table, for each target table, it is determined whether the number of full table scans of the target table is less than the preset table scan threshold, and whether the number of logical reads of the target table is less than the preset logical read threshold; if the number of full table scans of the target table is less than the preset table scan threshold, and the number of logical reads of the target table is less than the preset logical read threshold, the target table is determined to be the initial target table.

[0067] If the number of full table scans of the target table is not less than the preset table scan threshold, or the number of logical reads of the target table is not less than the preset logical read threshold, it is determined that the target table is not the initial target table and the target table is deleted, that is, the data of the target table is not automatically compressed.

[0068] S104: for each initial target table, determine whether the initial target table meets the automatic compression condition; if the initial target table meets the automatic compression condition, execute step S105; if the initial target table does not meet the automatic compression condition, execute step S106.

[0069] In an embodiment of the present application, after selecting at least one initial target table from various target tables, for each initial target table, it is determined whether the database archive space capacity of the initial target table is greater than a preset capacity threshold, whether the initial target table has a global index, and whether the initial target table meets the conditions for online redefinition.

[0070] If the database archive space capacity of the initial target table is greater than the preset capacity threshold, the initial target table has a global index, and the initial target table does not meet the conditions for online redefinition, it is determined that the initial target table does not meet the automatic compression conditions; if the database archive space capacity of the initial target table is not greater than the preset capacity threshold, and / or the initial target table does not have a global index, and / or the initial target table meets the conditions for online redefinition, it is determined that the initial target table meets the automatic compression conditions.

[0071] Specifically, the database archive space capacity Seg_Size of the initial target table can be obtained from the information table corresponding to the initial target table; the default value corresponding to the Index_Local item can be obtained from the information table corresponding to the initial target table. If the default value is "Y", it can be determined that all indexes on the initial target table are local indexes, that is, there are no global indexes; the pre-set EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(<Table_Owner> ,<Table_Name> ,dbms_redefinition.cons_use_rowid,<Part_Name> ); Check whether the initial target table meets the conditions for online redefinition. If an error is reported, the conditions for online redefinition are not met, and the default value corresponding to Can_Redef in the initial target is updated to N.

[0072] It should be noted that compressing a table will consume archive space, so it is necessary to evaluate whether compressing the initial target table will not consume too much archive space and cause the archive space to be full. In addition, if there are global indexes on the initial target table, performing data compression will cause the global indexes to become invalid, which has a significant impact.

[0073] S105: Automatically compress the initial target table.

[0074] In the embodiment of the present application, for each initial target table, if it is determined that the initial target table meets the automatic compression condition, the initial target is automatically compressed.

[0075] See also Figure 2 , shows a flow chart of a method for automatically compressing an initial target table provided by an embodiment of the present application, which specifically includes the following steps:

[0076] S201: Determine whether the current time of the automatic data compression system is within a second preset time period; if the current time of the automatic data compression system is within the second preset time period, execute step S202.

[0077] In the specific process of executing step S201, when it is determined that the initial target table meets the automatic compression conditions, the current time of the system is further obtained, and it is determined whether the current time is within the preset time period (for the sake of convenience, the preset time period here is referred to as the second preset time period); if the current time is within the second preset time period, step S202 is executed; if the current time is not within the second preset time period, the current scheduled task is terminated.

[0078] It should be noted that the second preset time period may be from 12:00 PM to 4:00 AM the next day, and may be set according to actual conditions, and is not limited in the present embodiment.

[0079] S202: Obtain the current archive space usage of the database.

[0080] S203: Determine whether the current archive space usage of the database is less than the preset usage; if the current archive space usage of the database is not less than the preset usage, execute step S204; if the current archive space usage of the database is less than the preset usage, execute step S205.

[0081] In the specific process of executing step S203, after obtaining the current archive space usage rate of the database, it is further determined whether the current archive space usage rate of the database is less than the preset usage rate. If it is not less than, after waiting for the preset time, it returns to execute to obtain the current archive space usage rate of the database; if it is less than, execute step S205.

[0082] It should be noted that the preset usage rate may be 20%, which can be set according to actual applications and is not limited in the present embodiment.

[0083] S204: Wait for a preset time.

[0084] During the specific execution of step S204 , if the current archive space usage rate of the database is not less than the preset usage rate, after waiting for a preset time, the process returns to step S201 .

[0085] It should be noted that the preset time may be 100 seconds and may be set according to actual application, and is not limited in the embodiment of the present application.

[0086] S205: Determine whether the initial target table meets the archiving conditions, whether all indexes on the initial target table are local indexes, and whether the initial target table meets the online redefinition conditions; if the initial target table meets the archiving conditions, and all indexes on the initial target table are local indexes, and the initial target table meets the online redefinition conditions, execute step S206.

[0087] In an embodiment of the present application, the default value corresponding to the Index_Local item obtained from the information table corresponding to the initial target table, if the default value is "Y", it can be determined that the indexes on the initial target table are all local indexes, that is, there is no global index; the default value corresponding to the Can_Arch item obtained from the information table corresponding to the initial target table, if the default value is "Y", it can be determined that the initial target table meets the archiving condition; the default value corresponding to the Can_Redef item obtained from the information table corresponding to the initial target table, if the default value is "Y", it can be determined that the initial target table meets the online redefinition condition.

[0088] When it is determined that the initial target table meets the archiving condition, all indexes on the initial target table are local indexes, and the initial target table meets the online redefinition condition, the table space capacity and space usage of the initial target are obtained.

[0089] It should be noted that if the initial target table does not meet the archiving conditions, or there is an index on the initial target table that is not a local index, or the initial target table does not meet the online redefinition conditions, the current scheduled task is terminated.

[0090] S206: Acquire the table space capacity and space usage of the initial target table, and calculate the index space occupancy rate based on the table space capacity and space usage of the initial target table.

[0091] In the specific process of executing step S206, the table space capacity and space usage rate of the initial target table are calculated, and the index space occupied capacity of the initial target table is calculated; the value of the space usage rate of the initial target table plus the table space capacity of the initial target table is divided by the index space occupied capacity of the initial target table to obtain the index space occupancy rate of the initial target table.

[0092] S207: Determine whether the index space occupancy rate is less than a preset occupancy rate; if the index space occupancy rate is less than the preset occupancy rate, execute step S208.

[0093] In the specific process of executing step S207, a preset occupancy rate is preset, which may be 80%. If the index space occupancy rate is less than the preset occupancy rate, step S208 is executed.

[0094] S208: Modify the compression attribute of the initial target table to the target compression attribute, and create a compression intermediate table whose compression attribute is the target compression attribute.

[0095] During the specific execution of step S208, when the index space occupancy rate is less than the preset occupancy rate, the compression attribute of the initial target table is modified to the target compression attribute, and a compression intermediate table with the target compression attribute is created based on the table information of the initial target table.

[0096] It should be noted that the target compression attribute may be OLTP.

[0097] S209: Use the online redefinition toolkit to create corresponding permission information for the compressed intermediate table, and synchronize the data of the initial target table after the compression attribute is modified to it.

[0098] In the specific process of executing step S209, after the compression attribute of the initial target table is modified to the target compression attribute and a compression intermediate table with the compression attribute as the target compression attribute is created, the initial target table after the compression attribute is modified is redefined online using the online redefinition toolkit. Specifically, a copy of the permission information of the initial target table after the compression attribute is modified is created on the compression intermediate table, and the data of the initial target table after the compression attribute is modified is actively synchronized to the compression middleware that creates the permission information.

[0099] It should be noted that the permission information at least includes the permissions, constraints, indexes, and materialized view LOG of the initial target table after the compression attribute is modified.

[0100] S210: The initial target table after the compression attribute is modified is deleted from the database, and the default value corresponding to the automatic compression state in the information table corresponding to the initial target table is updated to complete the compression.

[0101] In an embodiment of the present application, after the default value corresponding to the automatic compression status in the information table corresponding to the initial target table is updated to compression completed, corresponding information is output to prompt the DBA that the initial target has completed data compression.

[0102] S106: Output corresponding reminder information to provide a database administrator with information for performing data compression on the initial target table.

[0103] In an embodiment of the present application, when the initial target table does not meet the automatic compression conditions, corresponding reminder information is output to provide a database administrator with a manual data compression for the initial target table.

[0104] The present application provides an automatic data compression method, which can query and obtain multiple target tables whose database space is greater than a preset value through a database, obtain access information of each target table within a first preset time period, and screen out at least one initial target table from the target tables based on the access information of each target table; for each initial target table, if the initial target table meets the automatic compression conditions, automatically compress the initial target table, and complete the data table compression without manual intervention, which not only improves the efficiency of data compression, but also avoids the problem of production incidents caused by the large gap in professional and technical levels between different DBAs.

[0105] Based on the data automatic compression method disclosed in the embodiment of the present invention, the embodiment of the present invention also discloses a data automatic compression system. Figure 3 As shown, the data automatic compression system includes:

[0106] The target table acquisition unit 31 is used to acquire multiple target tables from the database; wherein the target table is a table that occupies a space in the database greater than a preset value;

[0107] An access information acquisition unit 32 is configured to acquire access information of each target table within a first preset time period;

[0108] a screening unit 33, configured to screen out at least one initial target table from each target table according to the access information of each target table;

[0109] The first judging unit 34 is configured to judge, for each initial target table, whether the initial target table satisfies an automatic compression condition;

[0110] The data automatic compression unit 35 is configured to automatically compress the initial target table if the initial target table meets the automatic compression conditions.

[0111] The specific principles and execution processes of each unit in the data automatic compression system disclosed in the above embodiment of the present invention are the same as those disclosed in the above embodiment of the present invention. Figure 1 The data automatic compression method shown is the same, and reference may be made to the corresponding part of the data automatic compression method disclosed in the above embodiment of the present invention, which will not be described again here.

[0112] The present application provides an automatic data compression system, which can query and obtain multiple target tables whose database space is greater than a preset value through a database, obtain access information of each target table within a first preset time period, and screen out at least one initial target table from the target tables based on the access information of each target table; for each initial target table, if the initial target table meets the automatic compression conditions, the initial target table is automatically compressed, and the data table compression can be completed without manual intervention, which not only improves the efficiency of data compression, but also avoids the problem of production incidents caused by the large gap in professional and technical levels between different DBAs.

[0113] Furthermore, the embodiment of the present application provides an automatic data compression system, further comprising:

[0114] The information output unit is used to output corresponding reminder information to provide a database administrator with information for data compression on the initial target table.

[0115] Optionally, the access information includes the number of full table scans and logical reads of the target table. The filtering unit includes:

[0116] A second judgment unit is configured to judge, for each target table, whether the number of full table scans of the target table is less than a preset table scan threshold, and whether the number of logical reads of the target table is less than a preset logical read threshold;

[0117] The initial target table determining unit is configured to determine that the target table is the initial target table if the number of full table scans of the target table is less than a preset table scan threshold and the number of logical reads of the target table is greater than a preset logical read threshold.

[0118] Optionally, the first judgment unit includes:

[0119] The first judgment subunit is used to judge, for each initial target table, whether the database archive space capacity of the initial target table is greater than a preset capacity threshold, whether the initial target table has a global index, and whether the initial target table meets the conditions for online redefinition;

[0120] A first determining unit is configured to determine that the initial target table does not meet the automatic compression condition if the database archive space capacity of the initial target table is greater than a preset capacity threshold, the initial target table has a global index, and the initial target table does not meet the online redefinition condition;

[0121] The second determination unit is configured to determine that the initial target table meets the automatic compression condition if the database archive space capacity of the initial target table is not greater than a preset capacity threshold, and / or the initial target table does not have a global index, and / or the initial target table meets the conditions for online redefinition.

[0122] Optional, automatic data compression unit, including:

[0123] an archive space usage rate acquisition unit, configured to acquire the current archive space usage rate of the database if the initial target table meets the automatic compression condition and the current time of the data automatic compression system is within a second preset time period;

[0124] The return execution unit is used to return to the execution archive space usage acquisition unit after waiting for a preset time if the current archive space usage of the database is not less than the preset usage;

[0125] a data acquisition unit, configured to acquire the table space capacity and space usage of the initial target table if the current archive space usage of the database is less than a preset usage, the initial target table meets the archiving condition, the indexes on the initial target table are all local indexes, and the initial target table meets the online redefinition condition;

[0126] a creation unit configured to modify the compression attribute of the initial target table to the target compression attribute and create a compressed intermediate table with the same compression attribute if the index space occupancy rate calculated based on the table space capacity and space usage rate of the initial target table is less than a preset occupancy rate;

[0127] A data synchronization unit is used to create corresponding permission information for the compressed intermediate table using an online redefinition toolkit, and synchronize the data of the original target table after the compression attribute is modified to the intermediate table; wherein the permission information is the same as the table information of the original target table;

[0128] The deletion unit is used to delete the initial target table after the compression attribute is modified from the database.

[0129] Furthermore, the automatic data compression system provided in the embodiment of the present application further includes:

[0130] An update unit is used to update the default value corresponding to the automatic compression status in the information table corresponding to the initial target table to completed compression; wherein the information table is generated based on the table information of the initial target table, and the table information includes at least automatic compression status, archiving conditions, and online redefinition conditions.

[0131] The present application embodiment provides an electronic device, such as Figure 4 As shown, the electronic device includes a processor 401 and a memory 402, the memory 402 is used to store program codes and data for automatic data compression, and the processor 401 is used to call program instructions in the memory to execute the steps shown in the automatic data compression method in the above embodiment.

[0132] An embodiment of the present application provides a storage medium, which includes a stored program, wherein when the program is running, the device where the storage medium is located is controlled to execute the data automatic compression method shown in the above embodiment.

[0133] Each embodiment in this specification is described in a progressive manner. The same or similar parts between the embodiments can be referred to each other. Each embodiment focuses on the differences from other embodiments. In particular, for system or system embodiments, since they are basically similar to method embodiments, the description is relatively simple. For relevant parts, refer to the partial description of the method embodiment. The system and system embodiments described above are merely schematic. The units described as separate components may or may not be physically separated, and the components shown as units may or may not be physical units, that is, they may be located in one place or distributed on multiple network units. Some or all of the modules can be selected according to actual needs to achieve the purpose of the solution of this embodiment. A person of ordinary skill in the art can understand and implement it without making any creative efforts.

[0134] Professionals may further appreciate that the units and algorithm steps of each example described in conjunction with the embodiments disclosed herein can be implemented in electronic hardware, computer software, or a combination of the two. In order to clearly illustrate the interchangeability of hardware and software, the above description has generally described the components and steps of each example according to their functions. Whether these functions are performed in hardware or software depends on the specific application and design constraints of the technical solution. Professionals and technicians may use different methods to implement the described functions for each specific application, but such implementation should not be considered beyond the scope of the present invention.

[0135] The above description of the disclosed embodiments is intended to enable one skilled in the art to implement or use the present invention. Various modifications to these embodiments will be readily apparent to one skilled in the art, and the general principles defined herein may be implemented in other embodiments without departing from the spirit or scope of the present invention. Therefore, the present invention is not limited to the embodiments shown herein but is intended to conform to the widest scope consistent with the principles and novel features disclosed herein.

[0136] The above are only preferred embodiments of the present invention. It should be pointed out that for ordinary technicians in this technical field, several improvements and modifications can be made without departing from the principles of the present invention. These improvements and modifications should also be regarded as the scope of protection of the present invention.

Claims

1. A method for automatic data compression, characterized in that: Applied to an automatic data compression system, the method includes: Acquire multiple target tables from a database; wherein the target tables are tables that occupy a database space greater than a preset value; Obtaining access information of each target table within a first preset time period; Filtering at least one initial target table from each of the target tables according to the access information of each of the target tables; For each of the initial target tables, determining whether the initial target table meets an automatic compression condition; If the initial target table meets the automatic compression condition, automatically compress the initial target table; Wherein, if the initial target table satisfies the automatic compression condition, automatically compressing the initial target table includes: If the initial target table meets the automatic compression condition and the current time of the data automatic compression system is within a second preset time period, obtaining the current archive space usage rate of the database; If the current archive space usage rate of the database is not less than the preset usage rate, after waiting for a preset time, return to execute to obtain the current archive space usage rate of the database; If the current archive space usage rate of the database is less than the preset usage rate, the initial target table meets the archiving condition, all indexes on the initial target table are local indexes, and the initial target table meets the online redefinition condition, obtain the table space capacity and space usage rate of the initial target table; If the index space occupancy rate calculated based on the table space capacity and space usage rate of the initial target table is less than the preset occupancy rate, modifying the compression attribute of the initial target table to the target compression attribute, and creating a compressed intermediate table with the target compression attribute; Using an online redefinition toolkit to create corresponding permission information for the compressed intermediate table, and synchronizing the data of the initial target table after the compression attribute is modified to the intermediate table; wherein the permission information is the same as the table information of the initial target table; The initial target table after the compression attribute is modified is deleted from the database.

2. The method according to claim 1, characterized in that The method further comprises: If the initial target table does not meet the automatic compression condition, corresponding reminder information is output to provide a database administrator with a reminder to perform data compression on the initial target table.

3. The method according to claim 1, characterized in that The access information includes the number of full table scans and logical reads performed on the target table. The selecting at least one initial target table from each target table based on the access information of each target table includes: For each target table, determining whether the number of full table scans of the target table is less than a preset table scan threshold, and whether the number of logical reads of the target table is less than a preset logical read threshold; If the number of full table scans of the target table is less than the preset table scan threshold, and the number of logical reads of the target table is greater than the preset logical read threshold, the target table is determined to be the initial target table.

4. The method according to claim 1, wherein The step of determining, for each of the initial target tables, whether the initial target table satisfies an automatic compression condition includes: For each of the initial target tables, determining whether the database archive space capacity of the initial target table is greater than a preset capacity threshold, whether the initial target table has a global index, and whether the initial target table meets the conditions for online redefinition; If the database archive space capacity of the initial target table is greater than a preset capacity threshold, the initial target table has a global index, and the initial target table does not meet the online redefinition condition, it is determined that the initial target table does not meet the automatic compression condition; If the database archive space capacity of the initial target table is not greater than a preset capacity threshold, and / or the initial target table does not have a global index, and / or the initial target table meets the conditions for online redefinition, it is determined that the initial target table meets the automatic compression conditions.

5. The method according to claim 1, characterized in that The method further comprises: The default value corresponding to the automatic compression status in the information table corresponding to the initial target table is updated to compression completed; wherein, the information table is generated based on the table information of the initial target table, and the table information at least includes the automatic compression status, archiving conditions, and online redefinition conditions.

6. A data automatic compression system, characterized in that: The system comprises: A target table acquisition unit is used to acquire multiple target tables from a database; wherein the target tables are tables that occupy a database space greater than a preset value; an access information acquiring unit, configured to acquire access information of each target table within a first preset time period; a screening unit, configured to screen out at least one initial target table from each of the target tables according to access information of each of the target tables; A first judging unit is configured to judge, for each of the initial target tables, whether the initial target table satisfies an automatic compression condition; a data automatic compression unit, configured to automatically compress the initial target table if the initial target table meets the automatic compression condition; Wherein, the automatic data compression unit is further used for: If the initial target table meets the automatic compression condition and the current time of the data automatic compression system is within a second preset time period, obtaining the current archive space usage rate of the database; If the current archive space usage rate of the database is not less than the preset usage rate, after waiting for a preset time, return to execute to obtain the current archive space usage rate of the database; If the current archive space usage rate of the database is less than the preset usage rate, the initial target table meets the archiving condition, all indexes on the initial target table are local indexes, and the initial target table meets the online redefinition condition, obtain the table space capacity and space usage rate of the initial target table; If the index space occupancy rate calculated based on the table space capacity and space usage rate of the initial target table is less than the preset occupancy rate, modifying the compression attribute of the initial target table to the target compression attribute, and creating a compressed intermediate table with the target compression attribute; Using an online redefinition toolkit to create corresponding permission information for the compressed intermediate table, and synchronizing the data of the initial target table after the compression attribute is modified to the intermediate table; wherein the permission information is the same as the table information of the initial target table; The initial target table after the compression attribute is modified is deleted from the database.

7. The system according to claim 6, characterized in that The system further comprises: The information output unit is used to output corresponding reminder information to provide a database administrator with information for performing data compression on the initial target table.

8. An electronic device, characterized in that: The electronic device includes a processor and a memory, the memory is used to store program code and data for automatic data compression, and the processor is used to call program instructions in the memory to execute a data automatic compression method according to any one of claims 1-5.

9. A storage medium, characterized in that: The storage medium includes a storage program, wherein when the program is running, the device where the storage medium is located is controlled to execute a data automatic compression method according to any one of claims 1 to 5.

Images