Database storage object processing method, storage medium and computer device

Abstract

The application provides a database storage object processing method, a storage medium and a computer device. The method comprises the following steps: obtaining a storage object reference information table from a system table of a database, wherein the storage object reference information table is used for recording reference data of a set type storage object; obtaining an update event of the storage object reference information table; judging whether the updated reference data meets a deletion condition; and if yes, deleting a database storage object corresponding to the updated reference data. Through the method, the database storage object without a reference relationship can be deleted in time without affecting the execution of a database operation statement, the problem of transient table space expansion is avoided, and the performance of the database is improved.

Description

Technical Field

[0001] This invention relates to database technology, and in particular to a method for processing database storage objects, a storage medium, and a computer device. Background Technology

[0002] In existing database technologies, there is an increasing need to store content such as music and videos. There are generally two methods for handling such database storage objects: In the database, the vacuumlo module (object handling module) is used to implement an asynchronous deletion mechanism, the operating principle of which is as follows:

[0003] 1. Retrieve all object identifiers from the object metadata table and store them in a temporary table.

[0004] 2. Scan all database tables, and if these object identifiers are used in the tables, delete them from the temporary tables.

[0005] 3. After step 2 is completed, if there is still data in the temporary table, it means that the objects in these temporary tables are not related to the database table and can be deleted.

[0006] This solution implements timed deletion of object instances, which avoids the long-term table bloat problem to some extent, but it cannot avoid the instantaneous tablespace bloat problem.

[0007] The second approach utilizes the database's `lo` module (data processing module) to provide a management mechanism for such database objects. The `lo_manage` mechanism (data management mechanism) avoids persistent storage of temporary database objects during creation. The specific process is as follows:

[0008] 1. Syntax parsing and SQL statement detection.

[0009] 2. If a temporary database object is created in step 1, the identifier of the temporary database object is recorded in memory.

[0010] 3. At the end of the statement, check if the temporary database object has any associated database tables. If there are any associated tables, no operation is performed; otherwise, it means that this database object is an orphan database object and needs to be deleted.

[0011] This approach avoids the creation of orphan database objects to some extent, thus mitigating the problem of sudden database object table bloat. However, this solution frequently scans the database system tables during SQL statement execution, reducing the efficiency of SQL statement execution. Summary of the Invention

[0012] One objective of this invention is to ensure the efficiency of database operation statement execution while avoiding the problem of instantaneous tablespace expansion.

[0013] A further objective of this invention is to enable the timely deletion of database objects that have no reference relationships.

[0014] A further objective of this invention is to improve database performance.

[0015] Specifically, the present invention provides a method for processing database storage objects, comprising:

[0016] Retrieve the storage object reference information table from the system tables in the database. The storage object reference information table is used to record reference data of storage objects of a specified type.

[0017] Retrieve update events from the storage object reference information table and determine whether the updated reference data meets the deletion criteria;

[0018] If so, delete the database storage object corresponding to the updated reference data.

[0019] Optionally, the step of determining whether the updated reference data meets the deletion conditions includes: determining whether the reference count value corresponding to the updated reference data triggers the action of a preset trigger;

[0020] The trigger condition is: the reference count value is less than the preset deletion threshold.

[0021] Optionally, the steps for obtaining update events of the stored object reference information table include:

[0022] Get the deletion event of the database stored object; and / or

[0023] Retrieves the insertion event of a database stored object.

[0024] Optionally, the step of obtaining the deletion event of the database stored object may further include:

[0025] Determine whether the database stored object information already exists in the stored object reference information table;

[0026] If so, subtract a specific value from the reference data in the stored object reference information table.

[0027] Optionally, after determining whether the information of the database stored object already exists in the stored object reference information table, the step further includes:

[0028] If the database storage object is not found in the storage object reference information table, an error message will be returned.

[0029] Optionally, after the step of obtaining the insertion event of the database storage object, the method further includes:

[0030] Determine whether the database stored object information already exists in the stored object reference information table;

[0031] If so, then determine whether the information corresponding to the database stored object is valid;

[0032] If the information corresponding to the stored object in the database is valid, increment the reference data by a specific value in the stored object reference information table.

[0033] Optionally, after determining whether the information of the database storage object already exists in the storage object reference information table, the method further includes: if the information of the database storage object does not exist in the storage object reference information table, adding a new record to the storage object reference information table and updating the information of the database storage object to the new record;

[0034] After determining whether the information corresponding to the database stored object is valid, the process also includes: if the information corresponding to the database stored object is invalid, regenerating the reference information row corresponding to the stored object in the stored object reference information table.

[0035] Optionally, the steps for deleting the database storage object corresponding to the updated reference information include:

[0036] Delete an instance of a database storage object;

[0037] Delete the reference data of the database stored object in the stored object reference information table.

[0038] According to another aspect of the present invention, a machine-readable storage medium is also provided, on which a machine-executable program is stored, wherein the machine-executable program, when executed by a processor, implements the processing method for any of the above-described database storage objects.

[0039] According to another aspect of the present invention, a computer device is also provided, including a memory, a processor, and a machine-executable program stored in the memory and running on the processor, wherein the processor executes the machine-executable program to implement the processing method of any of the above-described database storage objects.

[0040] The database storage object processing method of the present invention retrieves a storage object reference information table from the system tables of the database. This storage object reference information table records reference data for storage objects of a specified type. Then, upon receiving a delete or insert event for a deleted database storage object, the corresponding storage object reference information in the storage object reference information table is updated. The updated storage object reference information is then determined to meet the deletion conditions. If so, the database storage object corresponding to the updated reference data is deleted. This method allows for the timely deletion of database storage objects without reference relationships without affecting the execution of database operation statements, avoiding the problem of instantaneous tablespace expansion and improving database performance.

[0041] The database storage object processing method of the present invention, when performing a deletion operation on a database storage object, checks whether the information of the database storage object already exists in the storage object reference information table. If so, a specific value is decremented from the reference data in the storage object reference information table. When performing an insertion operation on a database storage object, it also checks whether the information of the database storage object already exists in the storage object reference information table. If so, it further checks whether the information corresponding to the database storage object is valid. If so, a specific value is added to the reference data in the storage object reference information table. When the reference data is less than a preset deletion threshold, a preset trigger is triggered, and the trigger then deletes the database storage object corresponding to the reference data. Through this method, the information in the storage object reference information table can be updated in a timely manner when database operation statements are executed. When the reference data reaches the trigger condition, the trigger will automatically delete the database storage object corresponding to the reference data. This allows for the timely deletion of database storage objects without reference relationships without affecting the execution of database operation statements, avoiding the problem of instantaneous tablespace expansion.

[0042] The above and other objects, advantages and features of the present invention will become more apparent to those skilled in the art from the following detailed description of specific embodiments of the invention in conjunction with the accompanying drawings. Attached Figure Description

[0043] The following sections will describe some specific embodiments of the invention in detail by way of example and not limitation, with reference to the accompanying drawings. The same reference numerals in the drawings denote the same or similar parts or portions. Those skilled in the art should understand that these drawings are not necessarily drawn to scale. In the drawings:

[0044] Figure 1 This is a schematic diagram illustrating the overall relationship of a database storage object processing method according to an embodiment of the present invention;

[0045] Figure 2This is a flowchart illustrating a method for processing database storage objects according to an embodiment of the present invention;

[0046] Figure 3 This is a flowchart illustrating the insertion operation of a database storage object processing method according to an embodiment of the present invention;

[0047] Figure 4 This is a flowchart illustrating the deletion operation of a database storage object processing method according to an embodiment of the present invention.

[0048] Figure 5 This is a schematic diagram of a machine-readable storage medium in a database storage object processing method according to an embodiment of the present invention; and

[0049] Figure 6 This is a schematic diagram of a computer device in a database storage object processing method according to an embodiment of the present invention. Detailed Implementation

[0050] Those skilled in the art should understand that the embodiments described below are merely a part of the embodiments of the present invention, and not all of the embodiments of the present invention. These partial embodiments are intended to explain the technical principles of the present invention and are not intended to limit the scope of protection of the present invention. Based on the embodiments provided by the present invention, all other embodiments obtained by those skilled in the art without creative effort should still fall within the scope of protection of the present invention.

[0051] In existing database technologies, there is an increasing need to store content such as music and videos. There are generally two methods for handling such database storage objects: In the database, the vacuumlo module (object handling module) is used to implement an asynchronous deletion mechanism, the operating principle of which is as follows:

[0052] 1. Retrieve all object identifiers from the object metadata table and store them in a temporary table.

[0053] 2. Scan all database tables, and if these object identifiers are used in the tables, delete them from the temporary tables.

[0054] 3. After step 2 is completed, if there is still data in the temporary table, it means that the objects in these temporary tables are not related to the database table and can be deleted.

[0055] This solution implements timed deletion of object instances, which avoids the long-term table bloat problem to some extent, but it cannot avoid the instantaneous tablespace bloat problem.

[0056] The second approach utilizes the database's `lo` module (data processing module) to provide a management mechanism for such database objects. The `lo_manage` mechanism (data management mechanism) avoids persistent storage of temporary database objects during creation. The specific process is as follows:

[0057] 1. Syntax parsing and SQL statement detection.

[0058] 2. If a temporary database object is created in step 1, the identifier of the temporary database object is recorded in memory.

[0059] 3. At the end of the statement, check if the temporary database object has any associated database tables. If there are any associated tables, no operation is performed; otherwise, it means that this database object is an orphan database object and needs to be deleted.

[0060] This approach avoids the creation of orphan database objects to some extent, thus mitigating the problem of sudden database object table bloat. However, this solution frequently scans the database system tables during SQL statement execution, reducing the efficiency of SQL statement execution.

[0061] To address the aforementioned issues, this method provides a way to promptly delete database storage objects without reference relationships without affecting the execution efficiency of database operation statements, thereby avoiding the problem of instantaneous tablespace expansion and improving database performance.

[0062] Figure 1 This is a schematic diagram illustrating the overall relationship of a database storage object processing method according to an embodiment of the present invention.

[0063] It should be noted that this diagram, for the purpose of more clearly illustrating the method, selects a specific database storage object as an example, such as a large object (a large object is a (LOB) data type that allows the storage and manipulation of unstructured and semi-structured data, such as documents, graphics, video clips, audio files, and XML files). Those skilled in the art can utilize this method to operate on other types of database storage objects according to their actual needs. This diagram includes:

[0064] In step S101, when the insertion module 11 identifies a large object involved in the insertion operation, it performs a query operation in the large object reference count table 13 based on the information of this large object.

[0065] The information in the large object reference count table 13 includes: oid 14 (object identifier value), ref count 15 (reference count), and is valid 16 (validity flag).

[0066] In step S102, when the deletion module 12 identifies a large object involved in the deletion operation, it performs a query operation in the large object reference count table 13 based on the information of this large object.

[0067] In step S103, when the ref count 15 in the large object reference count table 13 is less than the preset threshold, the trigger 17 will be triggered.

[0068] In step S104, after trigger 17 is triggered, trigger 17 will delete the corresponding large object metadata 18.

[0069] In this method, a large object reference count table 13 records the reference information of all large objects. When an insert or delete operation is performed, the insert module 11 and the delete module 12 update the large object information designed for this operation in the large object reference count table 13. When the trigger condition is met, the trigger deletes the corresponding large object. This achieves the goal of timely deleting large objects with no reference relationships without affecting the execution of database operation statements, thereby improving database performance.

[0070] Figure 2 This is a flowchart illustrating a method for processing database storage objects according to an embodiment of the present invention. The method includes the following steps:

[0071] Step S201: Retrieve the storage object reference information table from the system tables of the database. The storage object reference information table records reference data for storage objects of a specified type.

[0072] Step S202: Obtain update events from the storage object reference information table. This step includes obtaining delete events for database storage objects and / or insert events for database storage objects. When a database operation statement performs an insert or delete operation on a database storage object, the information of that storage object will be updated in this storage object reference information table.

[0073] Step S203: Determine whether the updated reference data meets the deletion conditions. This step includes obtaining the information row of the stored object in the stored object reference information table and determining whether the reference count value in the updated reference information is less than a preset threshold.

[0074] Step S204: If the determination in step S203 is yes, delete the database storage object corresponding to the updated reference data. When the reference count value in the updated reference information is less than a preset threshold, the trigger will be activated, and then the trigger will delete the information of the corresponding database storage object.

[0075] In this method, a reference count table is used to record the reference information of all stored objects. When an insert or delete operation is performed, the reference information of the object involved in the operation is updated in the reference count table. When the trigger condition is met, the trigger deletes the corresponding large object. This ensures the performance of database operation statements while avoiding the problem of instantaneous tablespace expansion.

[0076] Figure 3 This is a flowchart illustrating the insertion operation of a database storage object processing method according to an embodiment of the present invention.

[0077] It should be noted that this diagram uses a specific database storage object as an example to more clearly illustrate the method, such as a large object (a Large Object is a Level of Entrance (LOB) data type that allows the storage and manipulation of unstructured and semi-structured data, such as documents, graphics, video clips, audio files, and XML files). Those skilled in the art can utilize this method to operate on other types of database storage objects according to their specific needs. The method generally includes the following steps:

[0078] Step S301: Query the large object reference count table. This step includes: after obtaining the trigger event for the database operation statement to insert a large object, first querying the information corresponding to the large object in the preset large object reference count table based on the information of the large object.

[0079] Step S302: Determine whether the object exists in the reference count table.

[0080] Step S303: If the result of step S302 is negative, add a new row to the reference count table and update the information in this row. This step includes: if the reference count information corresponding to this large object does not exist in the large object reference count table, then the reference count information of this large object needs to be newly inserted into the large object reference count table. Therefore, add a new row of data to the object reference count table and update the reference count information of this large object to this row.

[0081] Step S304: If the result of step S302 is yes, determine whether it is valid. This step includes: if the large object reference count table contains the corresponding count information for this large object, then continue to determine whether the valid flag corresponding to the reference count information displays a valid status.

[0082] Step S305: If the determination result in step S304 is yes, update this row of information. This step includes: if it is determined that there is a corresponding count information for this large object in the large object reference count table and it is displayed as valid, then increment the reference count information of this large object by a specific value. An optional example is: incrementing the reference count of the large object by one, which means that the database has added one reference to this large object.

[0083] Step S306: If the result of step S304 is negative, regenerate the reference count row.

[0084] Through the above process, after obtaining the database insert operation statement, the database system updates the reference information of this stored object to the large object reference count table during the execution of this statement. Triggers can then monitor this table to determine their triggering behavior. This approach records the reference information of stored objects without affecting the execution efficiency of the insert operation statement, thus improving database performance.

[0085] Figure 4 This is a flowchart illustrating the deletion operation of a database storage object processing method according to an embodiment of the present invention.

[0086] It should be noted that this diagram uses a specific database storage object as an example to more clearly illustrate the method, such as a large object (a Large Object is a Level of Entrance (LOB) data type that allows the storage and manipulation of unstructured and semi-structured data, such as documents, graphics, video clips, audio files, and XML files). Those skilled in the art can utilize this method to operate on other types of database storage objects according to their specific needs. The method generally includes the following steps:

[0087] Step S401: Query the large object reference count table. This step includes: after obtaining the trigger event for the database operation statement to delete a large object, first querying the information corresponding to the large object in the preset large object reference count table based on the information of the large object.

[0088] Step S402: Determine whether the object exists in the reference count table.

[0089] Step S403: If the result of step S402 is negative, output an error message. This step includes: if the large object reference count table does not contain the corresponding count information for this large object, it indicates that the large object to be deleted by the database operation statement does not exist in this database, therefore an error message is returned.

[0090] Step S404: If the result of step S402 is yes, determine whether the reference count is less than the deletion threshold. This step includes: if the reference count information corresponding to this large object exists in the large object reference count table, then continue to determine whether the reference count in the reference count information is less than the deletion threshold. The deletion threshold is a threshold preset in the database. When the reference count is less than this threshold, it means that this large object has no reference relationship in the database and needs to be deleted.

[0091] Step S405: If the result of step S404 is negative, update the reference count. This step includes: if it is determined that there is a corresponding reference count for this large object in the large object reference count table and the reference count is greater than a preset threshold, then the reference count for this large object is reduced by a specific value. An optional example is: reducing the reference count for the large object by one means that the database reduces the number of references to this large object by one.

[0092] Step S406: If the result of step S404 is yes, delete the large object instance. This step includes: if the result of step S404 is yes, it means that the large object no longer has a reference relationship in the database, so the deletion operation is started to delete the instance of this large object.

[0093] Step S407: Delete the large object reference count information. That is, delete the data row corresponding to this large object in the large object reference count table.

[0094] Through the above process, after obtaining the database's delete operation statement for a stored object, the database system updates the reference information of this stored object to the large object reference count table during the execution of this statement. Triggers can then monitor this table to determine their triggering status. This allows for recording the reference information of stored objects without affecting the execution efficiency of the delete operation statement, and for deleting large objects with no reference relationships in real time. This avoids the problem of instantaneous tablespace expansion and improves database performance.

[0095] This embodiment also provides a machine-readable storage medium and a computer device. Figure 5 This is a schematic diagram of a machine-readable storage medium 501 according to an embodiment of the present invention. Figure 6 This is a schematic diagram of a computer device 603 according to an embodiment of the present invention.

[0096] The machine-readable storage medium 501 stores a machine-executable program 502 thereon, which, when executed by a processor, implements the database storage object processing method of any of the above embodiments.

[0097] Computer device 603 may include memory 601, processor 602, and machine-executable program 502 stored on memory 601 and running on processor 602. When processor 602 executes machine-executable program 502, it implements the database storage object processing method of any of the above embodiments.

[0098] It should be noted that the logic and / or steps represented in the flowchart or otherwise described herein, such as deleting a large object instance, may be specifically implemented in any machine-readable storage medium for use by, or in conjunction with, an instruction execution system, apparatus or device (such as a computer-based system, a processor-based system or other system that can fetch and execute instructions from, an instruction execution system, apparatus or device).

[0099] For the purposes of this embodiment, the machine-readable storage medium 501 can be any means capable of containing, storing, communicating, propagating, or transmitting a program for use by or in conjunction with an instruction execution system, apparatus, or device. More specific examples (a non-exhaustive list) of the machine-readable storage medium 501 include: an electrical connection (electronic device) having one or more wires, a portable computer disk drive (magnetic device), random access memory (RAM), read-only memory (ROM), erasable and editable read-only memory (EPROM or flash memory), fiber optic devices, and portable optical disc read-only memory (CDROM). Furthermore, the machine-readable storage medium 501 can even be paper or other suitable media on which the program can be printed, since the program can be obtained electronically, for example, by optically scanning the paper or other medium, followed by editing, interpreting, or otherwise processing as necessary, and then stored in a computer memory.

[0100] It should be understood that various parts of the present invention can be implemented using hardware, software, firmware, or a combination thereof. In the above embodiments, multiple steps or methods can be implemented using software or firmware stored in memory and executed by a suitable instruction execution system.

[0101] Computer device 603 can be, for example, a server, desktop computer, laptop computer, tablet computer, or smartphone. In some examples, computer device 603 can be a cloud computing node. Computer device 603 can be described in the general context of computer system executable instructions (such as program modules) executed by a computer system. Typically, program modules can include routines, programs, object programs, components, logic, data structures, etc., that perform specific tasks or implement specific abstract data types. Computer device 603 can be implemented in a distributed cloud computing environment where tasks are performed by remote processing devices linked via a communication network. In a distributed cloud computing environment, program modules can reside on local or remote computing system storage media, including storage devices.

[0102] Computer device 603 may include a processor 602 adapted to execute stored instructions and a memory 601 that provides temporary storage space for the operation of said instructions during operation. The processor 602 may be a single-core processor, a multi-core processor, a computing cluster, or any other configuration. The memory 601 may include random access memory (RAM), read-only memory, flash memory, or any other suitable storage system.

[0103] The processor 602 can be connected via a system interconnect (e.g., PCI, PCI-Express, etc.) to an I / O interface (input / output interface) suitable for connecting the computer device 603 to one or more I / O devices (input / output devices). I / O devices may include, for example, a keyboard and indicating devices, where indicating devices may include a touchpad or touchscreen, etc. I / O devices may be built into the computer device 703 or may be external devices connected to the computing device.

[0104] The processor 602 may also be linked via a system interconnect to a display interface suitable for connecting the computer device 603 to a display device. The display device may include a display screen as a built-in component of the computer device 603. The display device may also include a computer monitor, television, or projector, etc., externally connected to the computer device 603. Furthermore, a network interface controller (NIC) may be adapted to connect the computer device 603 to a network via a system interconnect. In some embodiments, the NIC may use any suitable interface or protocol (such as an Internet Minicomputer System Interface) to transmit data. The network may be a cellular network, a radio network, a wide area network (WAN), a local area network (LAN), or the Internet, etc. Remote devices may connect to the computing device via the network.

[0105] The flowchart provided in this embodiment is not intended to indicate that the operations of the method will be performed in any particular order, or that all operations of the method are included in every case. Furthermore, the method may include additional operations. Within the scope of the technical concept provided by the method in this embodiment, additional variations can be made to the above method.

[0106] Therefore, those skilled in the art should recognize that although numerous exemplary embodiments of the present invention have been shown and described in detail herein, many other variations or modifications conforming to the principles of the present invention can be directly determined or derived from the disclosure of the present invention without departing from the spirit and scope of the invention. Thus, the scope of the present invention should be understood and construed as covering all such other variations or modifications.

Claims

1. A method for processing database stored objects, comprising: The storage object reference information table is obtained from the system table of the database. The storage object reference information table is used to record reference data of a specified type of storage object. The reference data includes a valid marker of the specified type of storage object, and the specified type of storage object includes large objects. Obtain the update event of the storage object reference information table, and determine whether the updated reference data meets the deletion conditions; If so, delete the database storage object corresponding to the updated reference data; The step of obtaining the update event of the storage object reference information table includes: obtaining the insertion event of the database storage object; After the step of obtaining the insertion event of the database storage object, the method further includes: determining whether the information of the database storage object already exists in the storage object reference information table; if so, determining whether the information corresponding to the database storage object is valid; if the information corresponding to the database storage object is valid, adding a specific value to the reference data in the storage object reference information table.

2. The method for processing database storage objects according to claim 1, wherein, The step of determining whether the updated reference data meets the deletion conditions includes: determining whether the reference count value corresponding to the updated reference data triggers the action of a preset trigger; The trigger condition is that the reference count value is less than a preset deletion threshold.

3. The method for processing database storage objects according to claim 1, wherein, The step of obtaining the update event of the storage object reference information table includes: Obtain the deletion event of the database stored object.

4. The method for processing database storage objects according to claim 3, wherein, Following the step of obtaining the deletion event of the database storage object, the method further includes: Determine whether the information of the database stored object already exists in the storage object reference information table; If so, subtract a specific value from the reference data in the stored object reference information table.

5. The method for processing database storage objects according to claim 4, wherein, After the step of determining whether the information of the database stored object already exists in the storage object reference information table, the method further includes: If the database storage object does not exist in the storage object reference information table, an error message will be returned.

6. The method for processing database storage objects according to claim 1, wherein, After the step of determining whether the information of the database storage object already exists in the storage object reference information table, the method further includes: if the information of the database storage object does not exist in the storage object reference information table, adding a new record to the storage object reference information table and updating the information of the database storage object to the new record; After the step of determining whether the information corresponding to the database storage object is valid, the method further includes: if the information corresponding to the database storage object is invalid, regenerating the reference information row corresponding to the storage object in the storage object reference information table.

7. The method for processing database storage objects according to claim 1, wherein, The step of deleting the database storage object corresponding to the updated reference information includes: Delete the instance of the database storage object; Delete the reference data of the database storage object in the storage object reference information table.

8. A machine-readable storage medium having a machine-executable program stored thereon, wherein the machine-executable program, when executed by a processor, implements the method for processing a database storage object according to any one of claims 1 to 7.

9. A computer device comprising a memory, a processor, and a machine-executable program stored in the memory and running on the processor, wherein the processor, when executing the machine-executable program, implements a method for processing database storage objects according to any one of claims 1 to 7.

Images