Virtual column query method and device compatible with oracle database and medium

Abstract

The embodiment of the invention discloses a virtual column query method and device compatible with an oracle database and a medium, relates to the technical field of data migration and is used for solving the problem that an existing PostgreSQL database does not support a virtual column. The method comprises the following steps: identifying a grammar expression of a table column to be migrated in an oracle database, identifying a virtual column of the table column to be migrated, inserting column information of the virtual column into a specified system table of a PostgreSQL database according to an identifier, and storing an expression of a virtual column value into the specified system table; receiving a query statement to compare the to-be-queried column information with the column information of the specified system table, and determining whether the current to-be-queried column is a virtual column or not; if yes, obtaining the expression of the virtual column value according to the specified system table, and replacing the column name of the current to-be-queried column with the expression of the virtual column value so as to obtain the virtual column value of the current to-be-queried column.

Description

Technical Field

[0001] The present invention relates to the field of data migration technology, and more particularly to a virtual column query method, device and medium compatible with an Oracle database. Background Art

[0002] Oracle database and PostgreSQL database are both widely used relational database management systems. Oracle database generally provides higher performance and better hardware compatibility, and performs well in handling a large number of concurrent transactions and massive data, while PostgreSQL database may be faster in processing certain types of queries, especially queries involving complex JOINs and subqueries, and PostgreSQL supports more parallel processing and distributed processing. Currently, Oracle databases usually require expensive licensing fees and maintenance costs, which is a considerable burden for many companies. In contrast, PostgreSQL is open source and PostgreSQL's generated columns can dynamically calculate the value of expressions during queries without taking up additional storage space. Therefore, some companies will migrate Oracle database data to PostgreSQL databases.

[0003] However, the data migration process involves virtual column migration. As a special column in the database, the value of a virtual column is not directly stored in the database, but is calculated by expressions based on the values ​​of other columns. When Oracle's virtual columns are migrated to PostgreSQL, PostgreSQL does not support virtual columns and only supports the generation of storage columns, that is, the value of the column expression is calculated and stored in the tablespace. However, when the expression is changed, the stored generated column value will not change. If the generated column in PostgreSQL is used to replace the virtual column in Oracle, its column expression will be different from that of the Oracle virtual column. In addition, the Oracle virtual column syntax is incompatible with PostgreSQL, which makes it very difficult to migrate the Oracle virtual column function to PostgreSQL, and the query value of the virtual column is inconsistent with Oracle. Summary of the invention

[0004] In order to solve the above technical problems, one or more embodiments of this specification provide a virtual column query method, device and medium compatible with an Oracle database.

[0005] One or more embodiments of this specification adopt the following technical solutions:

[0006] One or more embodiments of the present specification provide a virtual column query method compatible with an Oracle database, the method comprising:

[0007] Based on the syntax layer of the PostgreSQL database, identify the syntax expression of the table column to be migrated in the Oracle database to identify the virtual column of the table column to be migrated;

[0008] Inserting the column information of the virtual column into the designated system table of the PostgreSQL database according to the identifier, and storing the expression of the virtual column value into the designated system table of the PostgreSQL database;

[0009] receiving a query statement, determining the column information to be queried according to the query statement, and comparing the column information to be queried with the column information of the specified system table to determine whether the current column to be queried is a virtual column;

[0010] If so, the expression of the virtual column value corresponding to the current column to be queried is obtained according to the specified system table, and the column name of the current column to be queried is replaced with the expression of the virtual column value to obtain the virtual column value of the current column to be queried.

[0011] Optionally, in one or more embodiments of the present specification, before identifying the syntax expression of the table column to be migrated in the Oracle database based on the syntax layer of the PostgreSQL database to identify the virtual column of the table column to be migrated, the method further includes:

[0012] The PostgreSQL database creates a corresponding new virtual column syntax expression based on the syntax expression of the table column to be migrated in the Oracle database, and adds the new virtual column syntax expression to the syntax layer.

[0013] Optionally, in one or more embodiments of the present specification, the PostgreSQL database creates a corresponding new virtual column syntax expression based on the syntax expression of the table column to be migrated in the Oracle database, and adds the new virtual column syntax expression to the syntax layer, specifically including:

[0014] According to the specific keywords of the Oracle database, query and obtain the syntax expression of the table column to be migrated in the Oracle database;

[0015] Determine whether the syntax expression of the table column to be migrated contains a data type, and if so, add a virtual column syntax for matching the omitted and displayed specified options; wherein the specified options include: a generated always option and a virtual option;

[0016] If the syntax expression of the table column to be migrated defaults the data type, a virtual column expression syntax of the default data type is added.

[0017] Optionally, in one or more embodiments of the present specification, inserting the column information of the virtual column into a specified system table of the PostgreSQL database according to the identifier, and storing the expression of the virtual column value in the specified system table of the PostgreSQL database specifically includes:

[0018] Identify the column information of the virtual column according to the syntax layer, and insert the column information into a designated system table of the PostgreSQL database; wherein the designated system table is a pg_attribute system table;

[0019] The generated column of the specified system table is marked according to the specified character of the preset data type, so as to store the expression of the virtual column value in the marked generated column.

[0020] Optionally, in one or more embodiments of the present specification, after inserting the column information of the virtual column into the designated system table of the PostgreSQL database according to the identifier and storing the expression of the virtual column value in the designated system table of the PostgreSQL database, the method further comprises:

[0021] Monitoring the insert data statement of the PostgreSQL database to parse the insert data statement to determine the specified insert column information;

[0022] Comparing the designated insert column information with the column information of the designated system table to determine whether the designated insert column information corresponds to a virtual column;

[0023] If so, an error handling operation is triggered.

[0024] Optionally, in one or more embodiments of the present specification, receiving a query statement, determining the column information to be queried according to the query statement, and comparing the column information to be queried with the column information of the specified system table to determine whether the current column to be queried is a virtual column, specifically includes:

[0025] Receive query statements and assign the query statements to query tasks of corresponding processors, so as to execute the query tasks in sequence and obtain the to-be-queried column information corresponding to the query statements;

[0026] Comparing the to-be-queried column information with the column information of the designated system table, obtaining the column information of the designated system table corresponding to the query statement;

[0027] It is determined whether the column information of the specified system table has a mark of the specified character, and if so, it is determined that the current column to be queried is a virtual column.

[0028] Optionally, in one or more embodiments of the present specification, obtaining an expression of a virtual column value corresponding to the current column to be queried according to the specified system table, and replacing the column name of the current column to be queried with the expression of the virtual column value, so as to obtain the virtual column value of the current column to be queried, specifically includes:

[0029] Determine whether there is an expression of a virtual column value corresponding to the current column to be queried in the system cache corresponding to the PostgreSQL database;

[0030] If so, obtaining an expression of a virtual column value corresponding to the current column to be queried based on the system cache;

[0031] If not, obtaining an expression of the virtual column value corresponding to the current column to be queried based on the specified system table;

[0032] The column name of the current column to be queried is replaced with the expression of the virtual column value to execute the expression of the virtual column value, and the virtual column value of the current column to be queried is obtained and returned.

[0033] Optionally, in one or more embodiments of the present specification, receiving a query statement and assigning the query statement to a query task of a corresponding processor to sequentially execute each query task and obtain the to-be-queried column information corresponding to each query statement specifically includes:

[0034] Determine the performance indicators of multiple processors corresponding to the PostgreSQL database to obtain historical performance data of each of the processors; wherein the performance indicators include: computing power data, memory occupancy, I / O throughput and query response time;

[0035] The historical performance data is normalized based on a maximum-minimum normalization algorithm to determine a standard deviation of each performance indicator in the plurality of processors according to the historical performance data corresponding to each performance indicator after normalization;

[0036] Determine a correlation coefficient between any two of the performance indicators according to the standardized historical performance data corresponding to any two of the performance indicators on each of the processors, and determine a conflict measure of each of the performance indicators according to the correlation coefficient;

[0037] Determining a weight value corresponding to each of the performance indicators according to the standard deviation and the conflict metric;

[0038] Obtaining a difference between a current performance index of each processor of the PostgreSQL database and a preset index threshold, so as to determine an allocation priority of each processor according to the difference and a weight value corresponding to each performance index;

[0039] The query statements are allocated in sequence according to the priorities, so that the query tasks corresponding to the query statements are cached in a preset query task queue, and the query tasks in the preset query task queue are executed in sequence.

[0040] One or more embodiments of the present specification provide a virtual column query device compatible with an Oracle database, the device comprising:

[0041] at least one processor; and,

[0042] a memory communicatively connected to the at least one processor; wherein,

[0043] The memory stores instructions that can be executed by the at least one processor, and the instructions are executed by the at least one processor to enable the at least one processor to: execute any of the above-mentioned methods.

[0044] One or more embodiments of the present specification provide a non-volatile computer storage medium storing computer executable instructions, wherein the computer executable instructions are configured to be able to: execute any of the above-described methods.

[0045] At least one of the above technical solutions adopted in the embodiments of this specification can achieve the following beneficial effects:

[0046] By identifying the syntax expression of the table columns to be migrated in the Oracle database, the process can automatically identify the virtual columns and migrate their column information and expressions to the specified system table of the PostgreSQL database. Since the system table can obtain the virtual column expression value through the system cache, the efficiency of subsequent query acquisition is improved. When receiving a query statement, the process can quickly determine whether the column to be queried is a virtual column, and dynamically calculate the value of the virtual column according to the expression stored in the system table. In addition, by converting the specified virtual column name into its corresponding expression, the problem of querying virtual columns without stored values ​​is avoided. The newly added virtual column syntax expression based on the PostgreSQL database makes the PostgreSQL database compatible with the syntax of Oracle virtual columns, allowing PostgreSQL to support the virtual column features that were originally common in databases such as Oracle, making it convenient to directly create the same table structure in PostgreSQL using the syntax of Oracle to create virtual tables, and migrate Oracle data to PostgreSQL, which helps to keep the query value of the virtual column consistent with the Oracle database. BRIEF DESCRIPTION OF THE DRAWINGS

[0047] In order to more clearly illustrate the technical solutions in the embodiments of this specification or the prior art, the following briefly introduces the drawings required for use in the embodiments or the prior art description. Obviously, the drawings described below are only some embodiments recorded in this specification. For ordinary technicians in this field, other drawings can be obtained based on these drawings without creative labor. In the drawings:

[0048] Figure 1 A flowchart of a virtual column query method compatible with an Oracle database provided in an embodiment of this specification;

[0049] Figure 2 A schematic diagram showing a newly added virtual column syntax expression provided in an embodiment of this specification;

[0050] Figure 3 A schematic diagram showing the insertion of a virtual column into a specified system table provided in an embodiment of this specification;

[0051] Figure 4 A schematic diagram of the structure of a virtual column query device compatible with an Oracle database provided in an embodiment of this specification;

[0052] Figure 5 A schematic diagram of the structure of a non-volatile storage medium provided in an embodiment of this specification. DETAILED DESCRIPTION

[0053] The embodiments of the present specification provide a virtual column query method, device and medium compatible with an Oracle database.

[0054] In order to enable those skilled in the art to better understand the technical solutions in this specification, the technical solutions in the embodiments of this specification will be clearly and completely described below in conjunction with the drawings in the embodiments of this specification. Obviously, the described embodiments are only part of the embodiments of this specification, not all of the embodiments. Based on the embodiments of this specification, all other embodiments obtained by ordinary technicians in this field without creative work should fall within the scope of protection of this specification.

[0055] like Figure 1 As shown, the embodiment of this specification provides a flowchart of a virtual column query method compatible with an Oracle database, which comprises Figure 1 It can be seen that in one or more embodiments of this specification, a virtual column query method compatible with an Oracle database includes the following steps:

[0056] S101: Based on the syntax layer of the PostgreSQL database, a syntax expression of a table column to be migrated in the Oracle database is identified to mark a virtual column of the table column to be migrated.

[0057] In order to solve the problem that the oracle virtual column syntax is incompatible with PostgreSQL, the embodiment of this specification will identify the syntax expression of the table column to be migrated in the oracle database according to the syntax layer of the PostgreSQL database, so as to identify the virtual column of the table column to be migrated, so as to solve the problem of not supporting virtual columns in PostgreSQL based on subsequent steps after identifying the virtual column. In this process, by accurately identifying the virtual column syntax expression in the Oracle database and mapping it to the corresponding syntax of PostgreSQL, it can be ensured that the definition and behavior of the virtual column remain consistent during the migration process. This reduces errors and omissions in the migration process and improves the accuracy and completeness of the migration. Virtual columns are generally used to store derived data or calculation results without taking up additional storage space. Correctly identifying and rebuilding these virtual columns in PostgreSQL can optimize query performance because queries can directly use these derived data without calculating them at runtime.

[0058] Furthermore, in one or more embodiments of the present specification, before identifying the syntax expression of the table column to be migrated in the Oracle database based on the syntax layer of the PostgreSQL database to identify the virtual column of the table column to be migrated, the method further includes the following process:

[0059] First, the PostgreSQL database creates the corresponding syntax expression for the newly added virtual column based on the syntax expression of the table column to be migrated in the Oracle database, and adds the syntax expression for the newly added virtual column to the syntax layer. Pre-creating and adding the syntax expression for the newly added virtual column helps ensure that the definition and behavior of the virtual column are consistent between Oracle and PostgreSQL during the migration process, which helps maintain data consistency and integrity.

[0060] Specifically, in one or more embodiments of this specification, Figure 2 The PostgreSQL database shown creates a corresponding new virtual column syntax expression based on the syntax expression of the table column to be migrated in the Oracle database, and adds the new virtual column syntax expression to the syntax layer, which specifically includes the following process:

[0061] According to the specific keywords of the Oracle database, query and obtain the syntax expression of the table column to be migrated in the Oracle database. Then, determine whether the syntax expression of the table column to be migrated contains the data type. If it contains the data type, add a virtual column syntax for matching the omitted and displayed specified options; it should be noted that the specified options include: generatedalways option and virtual option. If the syntax expression of the table column to be migrated defaults to the data type, add a virtual column expression syntax of the default data type. Based on this process, by accurately analyzing the syntax expression of the Oracle database and adding the corresponding virtual column syntax according to the data type and option information, it is helpful to directly create the same table structure in PostgreSQL based on the syntax of creating a virtual table in Oracle, which facilitates the migration of data from the Oracle database to the PostgreSQL database.

[0062] That is, when a certain application scenario involves creating a virtual column, such as the following syntax snippet "column[datatype][GENERATED ALWAYS]AS(column_expression)[VIRTUAL]", this syntax only identifies the modified column as a virtual column. From the above syntax, it can be seen that the expression of a virtual column can be expressed in many ways, such as: create tablet1(a int,b int generated always as a*2virtual), column b of table t1 is a virtual column, and the expression of the column value is a*2. The data type int, GENERATED ALWAYS, and VIRTUAL options of column b can all be defaulted, and the default syntax also recognizes column b as a virtual column. Therefore, in order to support virtual column syntax in PostgreSQL, the following syntax is first added to the syntax layer in PostgreSQL: Figure 2 The syntax rules of virtual columns shown in the figure, first, if the syntax of the created table column contains data types, two new rules are added. These two syntax rules can omit and explicitly specify the generatedalways and virtual options. If these two syntaxes are matched, the column expression is a virtual column. If the specified table column has a default data type, a new virtual column expression syntax with a default data type is added. The virtual keyword in this expression can also be omitted. When an invalid virtual column expression is entered, there is no suitable matching rule in the syntax rules, and PostgreSQL will automatically report a syntax error.

[0063] S102: inserting the column information of the virtual column into a designated system table of the PostgreSQL database according to the identifier, and storing the expression of the virtual column value into the designated system table of the PostgreSQL database.

[0064] In order to improve the query efficiency of virtual column data after data migration. In the embodiment of this specification, the column information of the virtual column will be inserted into the specified system table pg_attribute of the PostgreSQL database based on the identifier determined in the above step S101, and the expression of the virtual column value will also be stored in the specified system table of the PostgreSQL database. That is to say, after the virtual column is identified by the syntax layer in step S101, after the syntax parsing of the table creation, if it is recognized that the created table column is a virtual column, the column information of the virtual column will be inserted into the pg_attribute system table, and the expression of the virtual column value will be stored in the pg_attrdef system table.

[0065] Specifically, Figure 3 In one or more embodiments of the present specification, inserting the column information of the virtual column into the specified system table of the PostgreSQL database according to the identifier, and storing the expression of the virtual column value in the specified system table of the PostgreSQL database specifically includes:

[0066] According to the syntax layer, the column information of the virtual column is identified, and the column information is inserted into the designated system table of the PostgreSQL database. It should be noted that the designated system table is the pg_attribute system table, and the virtual column expression value can be obtained efficiently through the system cache. Then, the generated column of the designated system table is marked according to the specified character of the preset data type, so that the expression of the virtual column value is stored in the marked generated column. That is, the column information is inserted into the pg_attribute system table, and a char symbol 'v' is added to the attgenerated column of the pg_attribute system table to indicate that the column is a virtual column, and the expression of the virtual column value is stored in the pg_attrdef system table. In this process, the difference between virtual columns and ordinary columns is identified without adding columns to the pg_attribute system table, which facilitates the subsequent identification of virtual columns.

[0067] Furthermore, in one or more embodiments of the present specification, after inserting the column information of the virtual column into the designated system table of the PostgreSQL database according to the identifier and storing the expression of the virtual column value in the designated system table of the PostgreSQL database, the method further includes the following process:

[0068] First, monitor the insert data statements of the PostgreSQL database to parse the insert data statements to determine the specified insert column information. Then compare the specified insert column information with the column information of the specified system table to determine whether the specified insert column information corresponds to a virtual column. If so, trigger an error handling operation to restrict inserting and updating data into the virtual column. That is, when inserting data into a virtual column, such as insert into t1(a,b)values(1,2). When column b is a virtual column, in the query rewrite phase, check the information of the specified insert column. If the specified insert data column is a virtual column, directly report an error. Similarly, update the value of the virtual column and perform an error handling here. By triggering the error handling operation, data can be prevented from being inserted or updated into the virtual column due to misoperation, thereby enhancing data security.

[0069] S103: receiving a query statement, determining the column information to be queried according to the query statement, and comparing the column information to be queried with the column information of the designated system table to determine whether the current column to be queried is a virtual column.

[0070] Specifically, in one or more embodiments of the present specification, receiving a query statement, determining the column information to be queried according to the query statement, and comparing the column information to be queried with the column information of the specified system table to determine whether the current column to be queried is a virtual column, specifically includes the following process:

[0071] First, receive the query statement and assign the query statement to the query task of the corresponding processor to execute each query task in turn and obtain the column information to be queried corresponding to each query statement. Then compare the column information to be queried with the column information of the specified system table to obtain the column information of the specified system table corresponding to the query statement. Determine whether the column information of the specified system table has a mark of the specified character. If so, it is determined that the current column to be queried is a virtual column. Since the virtual column data is not stored in the table, when we query the virtual column value, we first obtain and calculate the value of the expression where the virtual column is located. When specifying the query column or querying all the table column data of the table, the client cannot perceive whether the column is a storage or virtual column. When the query statement is sent to the PostgreSQL database backend, the database backend identifies the column information to be queried according to the query statement sent by the front end. Since the column information of the table has been stored in the pg_attribute system table when the table is created, including whether the column is a virtual column. Therefore, when querying the table column information, the PostgreSQL database backend can determine whether the queried column is a virtual column based on the table column information stored in the pg_attribute system table. That is to say, in the embodiments of this specification, a query statement is received, and the column information to be queried is determined according to the query statement, and the column information to be queried is compared with the column information of the specified system table to determine whether the current column to be queried is a virtual column. By determining whether the current column to be queried is a virtual column, it is helpful to take corresponding measures for processing the virtual column, thereby improving the search efficiency of the virtual expression.

[0072] Further, in order to improve query efficiency and balanced allocation of query tasks, in one or more embodiments of the present specification, receiving a query statement and allocating the query statement to a query task of a corresponding processor, so as to sequentially execute each query task and obtain the to-be-queried column information corresponding to each query statement, specifically includes the following process:

[0073] First, the performance indicators of multiple processors corresponding to the PostgreSQL database are determined to obtain the historical performance data of each processor. It should be noted that the performance indicators include: computing power data, memory occupancy, I / O throughput and query response time. Then, the historical performance data is standardized based on the maximum and minimum value standardization algorithm, so that the standard deviation of each performance indicator in multiple processors is determined according to the historical performance data corresponding to each performance indicator after standardization. By standardizing the historical performance data based on the maximum and minimum value standardization algorithm, the dimensional difference between different performance indicators is eliminated, so that the performance data is comparable. Then, according to the historical performance data corresponding to any two performance indicators after standardization on each processor, the correlation coefficient between any two performance indicators is determined, so as to determine the conflict measurement of each performance indicator according to the correlation coefficient. Then, according to the standard deviation and the conflict measurement, the weight value corresponding to each performance indicator is determined. By obtaining the difference between the current performance indicator of each processor of the PostgreSQL database and the preset indicator threshold, the allocation priority of each processor is determined according to the difference and the weight value corresponding to each of the performance indicators. By calculating the difference between the current performance index of each processor and the preset index threshold, and determining the allocation priority based on the difference and the weight value, it can accurately reflect the performance differences of each processor and the allocation requirements of the query task, which helps to allocate the query task to the processor with better performance, improve the query efficiency and the overall performance of the system. Then, the query statements are allocated in sequence according to the priority, so that the query tasks corresponding to the query statements are cached in the preset query task queue, and the query tasks of the preset query task queue are executed in sequence. This process can automatically make dynamic adjustments based on the performance data of the processor and the requirements of the query task, avoiding the tediousness and errors of manual intervention. By caching the query tasks in the preset query task queue and executing them in sequence, the orderly scheduling and execution of the query tasks are achieved, and the response speed and stability of the system are improved.

[0074] S104: If yes, then obtaining an expression of a virtual column value corresponding to the current column to be queried according to the designated system table, and replacing the column name of the current column to be queried with the expression of the virtual column value, so as to obtain the virtual column value of the current column to be queried.

[0075] In the process of database query optimization and processing, when it is determined that the current column to be queried is a virtual column, the expression of the virtual column value is obtained according to the specified system table, and the column name of the current column to be queried is replaced with the expression, so that the value of the virtual column is dynamically calculated when the query is executed. By converting the specified virtual column name into its corresponding expression, the problem of querying virtual columns without stored values ​​is avoided, because the values ​​of virtual columns are not stored in the table space.

[0076] Specifically, in one or more embodiments of the present specification, obtaining an expression of a virtual column value corresponding to the current column to be queried according to a specified system table, and replacing the column name of the current column to be queried with the expression of the virtual column value, so as to obtain the virtual column value of the current column to be queried, specifically includes:

[0077] Determine whether there is an expression of the virtual column value corresponding to the current column to be queried in the system cache corresponding to the PostgreSQL database. If so, then obtain the expression of the virtual column value corresponding to the current column to be queried according to the system cache. If not, then obtain the expression of the virtual column value corresponding to the current column to be queried according to the specified system table. Then, replace the column name of the current column to be queried with the expression of the virtual column value to execute the expression of the virtual column value and obtain the virtual column value of the current column to be queried. That is, when querying a table with a virtual column, for example, select a,b from t1. When parsing the query table column information in the semantic phase, it can be determined that the queried column is a virtual column according to the identification in the above steps, and then obtain the expression of the column stored in the pg_attredf system table, and expand the virtual column b specified by the source query to the expression. The value of the virtual column queried at last is the value calculated by the expression. That is, it is equivalent to converting select a,b fromt1 into select a,a*2as b from t1 inside the database. Since the virtual column expression is stored in the pg_attredf system table, the information of the system table is searched in the syscache cache. When the column name is replaced with an expression, the virtual column expression can be directly searched in the cache, so the efficiency of replacing the virtual column expression is relatively high. In addition, it should be noted that the process of expanding the virtual column b specified by the source query into the expression in the above steps is as follows: when the column is judged as a virtual column through attgenerated in the pg_attribute system table, the expression of the virtual column stored in the pg_attrdef system table is obtained according to the table information and the column number information of the column in the table. After obtaining the expression corresponding to the virtual column, for example, the virtual column expression of column b is a*2, then the original query content is select a,b from t1; since column b is a virtual column and the expression is a*2, the information of column b can be directly modified to the expression of column b a*2 at the semantic analysis layer. The PostgreSQL database will calculate the value of column b according to the expression.

[0078] like Figure 4 As shown, the embodiment of this specification provides a structural diagram of a virtual column query device compatible with an Oracle database. Figure 4 It can be seen that in one or more embodiments of this specification, a virtual column query device compatible with an Oracle database includes:

[0079] at least one processor; and,

[0080] a memory communicatively connected to the at least one processor; wherein,

[0081] The memory stores instructions that can be executed by the at least one processor, and the instructions are executed by the at least one processor to enable the at least one processor to: execute any of the above-mentioned exemplary methods.

[0082] like Figure 5 As shown, the present specification provides a schematic diagram of the structure of a non-volatile storage medium. Figure 5 It can be seen that in one or more embodiments of the present specification, a non-volatile storage medium stores computer-executable instructions 501, and the computer-executable instructions 501 can: execute any of the methods described above.

[0083] Each embodiment in this specification is described in a progressive manner, and the same or similar parts between the embodiments can be referred to each other, and each embodiment focuses on the differences from other embodiments. In particular, for the device, equipment, and non-volatile computer storage medium embodiments, since they are basically similar to the method embodiments, the description is relatively simple, and the relevant parts can be referred to the partial description of the method embodiment.

[0084] The above is a description of a specific embodiment of the specification. Other embodiments are within the scope of the appended claims. In some cases, the actions or steps recorded in the claims can be performed in an order different from that in the embodiments and still achieve the desired results. In addition, the processes depicted in the drawings do not necessarily require the specific order or continuous order shown to achieve the desired results. In some embodiments, multitasking and parallel processing are also possible or may be advantageous.

[0085] The above description is only one or more embodiments of this specification and is not intended to limit this specification. For those skilled in the art, one or more embodiments of this specification may have various changes and variations. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of one or more embodiments of this specification shall be included in the scope of the claims of this specification.

Claims

1. A virtual column query method compatible with Oracle database, characterized in that: The method comprises: Based on the syntax layer of the PostgreSQL database, identify the syntax expression of the table column to be migrated in the Oracle database to identify the virtual column of the table column to be migrated; Inserting the column information of the virtual column into the designated system table of the PostgreSQL database according to the identifier, and storing the expression of the virtual column value into the designated system table of the PostgreSQL database; receiving a query statement, determining the column information to be queried according to the query statement, and comparing the column information to be queried with the column information of the specified system table to determine whether the current column to be queried is a virtual column; If so, the expression of the virtual column value corresponding to the current column to be queried is obtained according to the specified system table, and the column name of the current column to be queried is replaced with the expression of the virtual column value to obtain the virtual column value of the current column to be queried.

2. The method for querying a virtual column compatible with an Oracle database according to claim 1, characterized in that: Before identifying the syntax expression of the table column to be migrated in the Oracle database based on the syntax layer of the PostgreSQL database to mark the virtual column of the table column to be migrated, the method further includes: The PostgreSQL database creates a corresponding new virtual column syntax expression based on the syntax expression of the table column to be migrated in the Oracle database, and adds the new virtual column syntax expression to the syntax layer.

3. The method for querying a virtual column compatible with an Oracle database according to claim 2, characterized in that: The PostgreSQL database creates a corresponding new virtual column syntax expression based on the syntax expression of the table column to be migrated in the Oracle database, and adds the new virtual column syntax expression to the syntax layer, specifically including: According to the specific keywords of the Oracle database, query and obtain the syntax expression of the table column to be migrated in the Oracle database; Determine whether the syntax expression of the table column to be migrated contains a data type, and if so, add a virtual column syntax for matching the omitted and displayed specified options; wherein the specified options include: a generated always option and a virtual option; If the syntax expression of the table column to be migrated defaults the data type, a virtual column expression syntax of the default data type is added.

4. The method for querying a virtual column compatible with an Oracle database according to claim 1, characterized in that: Inserting the column information of the virtual column into the designated system table of the PostgreSQL database according to the identifier, and storing the expression of the virtual column value into the designated system table of the PostgreSQL database, specifically includes: Identify the column information of the virtual column according to the syntax layer, and insert the column information into a designated system table of the PostgreSQL database; wherein the designated system table is a pg_attribute system table; The generated column of the specified system table is marked according to the specified character of the preset data type, so as to store the expression of the virtual column value in the marked generated column.

5. The method for querying a virtual column compatible with an Oracle database according to claim 1, characterized in that: After inserting the column information of the virtual column into the designated system table of the PostgreSQL database according to the identifier and storing the expression of the virtual column value in the designated system table of the PostgreSQL database, the method further comprises: Monitoring the insert data statement of the PostgreSQL database to parse the insert data statement to determine the specified insert column information; Comparing the designated insert column information with the column information of the designated system table to determine whether the designated insert column information corresponds to a virtual column; If so, an error handling operation is triggered.

6. The method for querying a virtual column compatible with an Oracle database according to claim 4, characterized in that: Receiving a query statement, determining the column information to be queried according to the query statement, and comparing the column information to be queried with the column information of the specified system table to determine whether the current column to be queried is a virtual column, specifically including: Receive query statements and assign the query statements to query tasks of corresponding processors, so as to execute the query tasks in sequence and obtain the to-be-queried column information corresponding to the query statements; Comparing the to-be-queried column information with the column information of the designated system table, obtaining the column information of the designated system table corresponding to the query statement; It is determined whether the column information of the specified system table has a mark of the specified character, and if so, it is determined that the current column to be queried is a virtual column.

7. The method for querying a virtual column compatible with an Oracle database according to claim 1, characterized in that: Acquiring an expression of a virtual column value corresponding to the current column to be queried according to the designated system table, and replacing the column name of the current column to be queried with the expression of the virtual column value, so as to obtain the virtual column value of the current column to be queried, specifically includes: Determine whether there is an expression of a virtual column value corresponding to the current column to be queried in the system cache corresponding to the PostgreSQL database; If so, obtaining an expression of a virtual column value corresponding to the current column to be queried based on the system cache; If not, obtaining an expression of the virtual column value corresponding to the current column to be queried based on the specified system table; The column name of the current column to be queried is replaced with the expression of the virtual column value to execute the expression of the virtual column value, and the virtual column value of the current column to be queried is obtained and returned.

8. The method for querying a virtual column compatible with an Oracle database according to claim 6, characterized in that: Receiving a query statement and assigning the query statement to a query task of a corresponding processor to execute each query task in sequence and obtaining the to-be-queried column information corresponding to each query statement, specifically includes: Determine the performance indicators of multiple processors corresponding to the PostgreSQL database to obtain historical performance data of each of the processors; wherein the performance indicators include: computing power data, memory occupancy, I / O throughput and query response time; The historical performance data is normalized based on a maximum-minimum normalization algorithm to determine a standard deviation of each performance indicator in the plurality of processors according to the historical performance data corresponding to each performance indicator after normalization; Determine a correlation coefficient between any two of the performance indicators according to the standardized historical performance data corresponding to any two of the performance indicators on each of the processors, and determine a conflict measure of each of the performance indicators according to the correlation coefficient; Determining a weight value corresponding to each of the performance indicators according to the standard deviation and the conflict metric; Obtaining a difference between a current performance index of each processor of the PostgreSQL database and a preset index threshold, so as to determine an allocation priority of each processor according to the difference and a weight value corresponding to each performance index; The query statements are allocated in sequence according to the priorities, so that the query tasks corresponding to the query statements are cached in a preset query task queue, and the query tasks in the preset query task queue are executed in sequence.

9. A virtual column query device compatible with Oracle database, characterized in that: The device comprises: at least one processor; and, a memory communicatively connected to the at least one processor; wherein, The memory stores instructions that can be executed by the at least one processor, and the instructions are executed by the at least one processor so that the at least one processor can: execute any of the methods described in claims 1-8.

10. A non-volatile storage medium storing computer executable instructions, characterized in that: The computer executable instructions can: execute the method described in any one of claims 1 to 8.

Images