JP6758252B2 - Histogram generation method, histogram generator and histogram generation program - Google Patents

Description

The present invention relates to a histogram generation method, a histogram generation device, and a histogram generation program that generate a histogram showing the distribution of data contained in a table stored in a database.

In the database system, the execution time of the processing corresponding to the query statement can be shortened by using the processing cost of the query statement in creating the execution plan such as the optimization of the query statement that operates the database. Conventionally, a method of using a histogram showing the distribution of data contained in a table stored in a database is known for estimating a processing cost (see, for example, Patent Document 1).

JP-A-2016-095561

The histogram contains one or more buckets, corresponding to each of the plurality of columns contained in the table. The bucket contains information that indicates the number of data and the number of values that the data represents in the data interval. The database system can create a histogram (intermediate histogram) after the arithmetic processing by applying the arithmetic processing in the query statement to the histogram, and estimate the size of the intermediate result of the query.

However, when type conversion occurs for a column in a query statement, the data after type conversion and the histogram do not correspond. Therefore, there arises a problem that the estimation result of the size of the intermediate result using the histogram deviates from the size of the intermediate result in the actual arithmetic processing. If the query processing is continued in a state where the estimation result of the size of the intermediate result deviates from the size of the intermediate result in the actual arithmetic processing, the resources available in the database system may be exhausted and the processing may not be completed. ..

Therefore, the present invention has been made in view of these points, and an object of the present invention is to provide a histogram generation method, a histogram generation device, and a histogram generation program capable of generating a histogram corresponding to the data after type conversion. To do.

The histogram generation method according to the first aspect of the present invention is specified as a specific step of specifying a column to be type-converted to a different type in a query statement executed by a computer and executed in a database system. The frequency distribution of the data before the type conversion corresponding to the column is shown, and based on the histogram stored in the storage unit in advance, a histogram having a coarser frequency distribution than the histogram is used in the query statement. It includes a generation step included in the intermediate results, which is generated as an intermediate result histogram of the data after the type conversion corresponding to the column is performed.

In the specific step, the computer identifies the column in which the type conversion from the type corresponding to the numerical value to the type corresponding to the character string is performed in the query statement, and in the generation step, the computer is specified. When the data before the type conversion corresponding to the column includes data having a different number of digits, the maximum value and the minimum value before the type conversion of all the data corresponding to the column are included. You may generate a bucket containing information indicating the number of the data and the number of values indicated by the data in the data interval, and generate the intermediate result histogram based on the generated bucket.

In the specific step, the computer identifies the column in which the type conversion from the type corresponding to the numerical value to the type corresponding to the character string is performed in the query statement, and in the generation step, the computer is specified. When the data before the type conversion corresponding to the column includes data showing a positive value and data showing a negative value, the first data corresponding to all the data having a positive value. One bucket may be generated and a second bucket corresponding to all data having a negative value may be generated.

In the generation step, the computer sets the maximum and minimum values of the data contained in the first bucket and the second bucket to the maximum and minimum values of the data contained in the respective buckets. You may set it.

In the generation step, the computer sets the maximum value of the first bucket to the maximum value in the data included in the intermediate result histogram, and sets the minimum value of the second bucket to the maximum value of the data included in the intermediate result histogram. It may be set to the minimum value.

In the generation step, when the maximum value of the data included in the histogram is a positive value, the computer sets the maximum value in the first bucket, which is one or more buckets corresponding to the data having a positive value. Even if the maximum value of the data included in the histogram is set and the minimum value of the second bucket, which is one or more buckets corresponding to the data having a negative value, is set to the minimum value of the data included in the histogram. Good.

In the generation step, when the maximum value of the data included in the histogram is a negative value, the computer sets the maximum value of the data to the minimum value of the data included in the histogram and the data. The minimum value of may be set to the maximum value of the data included in the histogram.

In the generation step, when the data corresponding to the specified column includes data indicating a positive value and data indicating a negative value, the type in the data corresponding to the column. The first, which is one or more buckets corresponding to data having a positive value, based on the ratio of the maximum value before conversion to the minimum value before type conversion and the number of all data corresponding to the column. The number of data contained in each of the bucket and the second bucket, which is one or more buckets corresponding to the data having a negative value, is estimated, and the first bucket and the second bucket including information indicating the estimated number of data are included. You may generate a bucket.

In the generation step, the computer estimates the number of data after being narrowed down by the narrowing conditions corresponding to the columns included in the query statement based on the generated intermediate result histogram and stores it in the storage unit. The query is performed by updating the number of data corresponding to each of the plurality of buckets included in the stored histogram based on the estimated number of data and the number of data before being narrowed down by the narrowing condition. Further histograms of said columns may be generated after the statement has been executed.

The histogram generator according to the second aspect of the present invention corresponds to a specific part that specifies a column that undergoes type conversion to a different type in a query statement executed in a database system, and the specified column. The intermediate result of the query statement includes a histogram showing the frequency distribution of the data before the type conversion is performed, and based on the histogram stored in the storage unit in advance, the granularity of the frequency distribution is coarser than that of the histogram. A generation unit that generates an intermediate result histogram of the data after the type conversion corresponding to the column is provided.

The histogram generation program according to the third aspect of the present invention is a specific part that specifies a column in which a computer is subjected to type conversion to a different type in a query statement executed in a database system, and the specified unit. The frequency distribution of the data before the type conversion corresponding to the column is shown, and based on the histogram stored in the storage unit in advance, a histogram having a coarser frequency distribution than the histogram is obtained in the middle of the query statement. It functions as a generation unit that is included in the result and is generated as an intermediate result histogram of the data after the type conversion corresponding to the column is performed.

According to the present invention, there is an effect that a histogram corresponding to the data after type conversion can be generated.

It is a figure which shows the outline of the database system which concerns on this embodiment. It is a figure which shows the structure of the database system which concerns on this embodiment. It is a figure which shows an example of the histogram which concerns on this embodiment. It is a flowchart which shows the flow of processing in the database system which concerns on this embodiment. It is a flowchart which shows the process flow in the digit difference correspondence processing which concerns on this embodiment. It is a flowchart which shows the process flow in the negative value correspondence processing which concerns on this embodiment. It is a flowchart which shows the flow of processing in the type return process which concerns on this embodiment.

[Overview of database system 1]
FIG. 1 is a diagram showing an outline of the database system 1 according to the present embodiment. The database system 1 is a system that executes a query statement acquired from the user terminal 2. Here, the query statement is a character string for operating the database, and is a statement written in SQL (Structured Query Language). In the following explanation, the query statement is called a query.

The database system 1 is composed of one or more computers that can communicate with each other, and functions as a histogram generator. The database system 1 is communicably connected to the user terminal 2 via a communication network such as a LAN or the Internet.

The database system 1 acquires a query from the user terminal 2 ((1) in FIG. 1). The database system 1 analyzes the acquired query to identify the column to which type conversion is performed ((2) in FIG. 1), and corresponds to the column after type conversion based on the histogram before type conversion of the identified column. Generate a bucket to be used ((3) in FIG. 1). Here, the bucket is information contained in the histogram showing the frequency distribution of the data corresponding to the column. The bucket contains information that indicates the number of data in the data interval and information that indicates the number of values that the data indicates.

The database system 1 generates an intermediate result histogram, which is a histogram corresponding to the intermediate result of the query, based on the generated bucket ((4) in FIG. 1). Here, the database system 1 generates a histogram as an intermediate result histogram corresponding to the specified column and having a coarser frequency distribution than the histogram based on the histogram before the type conversion is performed. By doing so, the database system 1 can generate a histogram corresponding to the data after type conversion.

The database system 1 calculates the processing cost of the query based on the intermediate result histogram ((5) in FIG. 1), and executes the query based on the processing cost ((6) in FIG. 1). Since the intermediate result histogram corresponds to the processing corresponding to the query, the database system 1 can calculate the processing cost of the query with high accuracy.
The configuration of the database system 1 will be described below.

[Configuration example of database system 1]
FIG. 2 is a diagram showing a configuration of a database system 1 according to the present embodiment.
The database system 1 includes a storage unit 11 and a control unit 12.

The storage unit 11 is, for example, a ROM (Read Only Memory), a RAM (Random Access Memory), or the like. The storage unit 11 stores various programs for operating the database system 1. The storage unit 11 stores a database management program that causes the control unit 12 of the database system 1 to function as an acquisition unit 121, a specific unit 122, a generation unit 123, a cost calculation unit 124, and an execution unit 125, which will be described later. Here, the database management program is a histogram generation program that causes the control unit 12 to function as the acquisition unit 121, the specific unit 122, and the generation unit 123, and a query that causes the control unit 12 to function as the cost calculation unit 124 and the execution unit 125. It may include an executable program.

In addition, the storage unit 11 stores the database 111. One or more tables are stored in the database 111. Also, each of the one or more tables contains one or more columns. Each of the one or more columns is provided with a histogram showing the frequency distribution of the data corresponding to the columns.

FIG. 3 is a diagram showing an example of a histogram according to the present embodiment. The example shown in FIG. 3 is a histogram corresponding to the column “A” included in a predetermined table. As shown in FIG. 3, the histogram contains information indicating the minimum and maximum values of the data corresponding to the columns, and includes one or more buckets. In the example shown in FIG. 3, it can be confirmed that two buckets "A1" and "A2" are included.

The bucket contains the bounds, the number of records, and the number of domains. Bound is information indicating the range that data can take. The number of records is the number of data. The number of domains is the number of value types. In the example shown in FIG. 3, data is shown in the bucket, but this data is shown to explain the number of bounds, the number of records, and the number of domains, and is not actually included. And.

The control unit 12 is, for example, a CPU (Central Processing Unit). The control unit 12 controls the functions related to the database system 1 by executing various programs stored in the storage unit 11. By executing the database management program, the control unit 12 functions as an acquisition unit 121, a specific unit 122, a generation unit 123, a cost calculation unit 124, and an execution unit 125.

The acquisition unit 121 acquires the query corresponding to the database 111 from the user terminal 2.
The identification unit 122 analyzes the query acquired by the acquisition unit 121 and identifies a column in which type conversion is performed to a different type. Specifically, the identification unit 122 identifies a column in which type conversion is performed to a different type by specifying a character string corresponding to a cast operation, which is an operation indicating type conversion, in a character string indicating a query. To do. Here, the different types of types refer to types in which the order of the magnitude relation of the data is different. The identification unit 122 specifies, for example, a column in which type conversion from a type corresponding to a numerical value to a type corresponding to a character string is performed.

The generation unit 123 has a frequency distribution particle size smaller than that of the histogram based on the histogram before the type conversion, which is stored in the storage unit 11 corresponding to the column in which the type conversion is performed to a different type. A rough histogram is generated as an intermediate result histogram of the column included in the intermediate result of the query.

Specifically, first, the generation unit 123 generates the same histogram as the histogram before the type conversion is performed as the intermediate result histogram. Subsequently, the generation unit 123 executes the digit difference handling process to be executed when the data corresponding to the column contains data having a different number of digits, and is executed when the data corresponding to the column contains data having a negative value. The bucket included in the intermediate result histogram is updated by executing the negative value correspondence process. Hereinafter, the details of the digit difference handling process and the negative value handling process will be described.

[Processing for orders of magnitude]
The generation unit 123 determines whether or not the data corresponding to the specified column includes data having a different number of digits. For example, the generation unit 123 includes data having a different number of digits in the data corresponding to the specified column based on the information indicating the maximum value and the minimum value included in the histogram generated as the intermediate result histogram. Judge whether or not.

When the generation unit 123 determines that the data having a different number of digits is included, the generation unit 123 executes the digit difference correspondence process and changes the bucket of the histogram generated as the intermediate result histogram.

The generation unit 123 contains information indicating the number of data and the number of values indicated by the data in the data interval including the maximum value and the minimum value before type conversion of all the data corresponding to the specified column. To generate. Then, the generation unit 123 erases the bucket included in the histogram generated in advance as the intermediate result histogram. By doing so, the generation unit 123 can generate a histogram corresponding to the data after type conversion, although the granularity of the histogram becomes coarse.

When the data corresponding to the specified column includes data showing a positive value and data showing a negative value, the generation unit 123 corresponds to all the data having a positive value. The first bucket is generated, and the second bucket corresponding to all the data having a negative value is generated.

For example, the generation unit 123 adds positive and negative values to the data corresponding to the specified column based on the information indicating the maximum value and the minimum value included in the histogram generated as the intermediate result histogram. It is determined whether or not the data indicating the value is included.

When the generation unit 123 determines that the data indicating a positive value and the data indicating a negative value are included, the first bucket is generated and the second bucket is generated. For example, when the histogram generated as the intermediate result histogram contains a plurality of buckets, the generation unit 123 aggregates the buckets corresponding to the data having a positive value among the plurality of buckets. Create a first bucket. In addition, the generation unit 123 aggregates the buckets corresponding to the data having a negative value among the plurality of buckets to generate the second bucket. Here, the generation unit 123 decomposes the bucket into the first bucket and the second bucket when there is a bucket containing both positive data and negative data among the plurality of buckets. To do.

Then, the generation unit 123 updates the bound, the number of records, and the number of domains of the data included in each of the first bucket and the second bucket. For example, the generation unit 123 sets the maximum value and the minimum value of the data contained in each of the first bucket and the second bucket to the maximum value and the minimum value of the values that the data contained in each bucket can take, and these Set the bucket bound. For example, if the minimum value of positive data (numeric type data) before type conversion from numeric type to character string type is 4 and the maximum value is 500, the first value after type conversion to character string type The minimum value of the bucket is 10 and the maximum value is 99. Also, if the minimum value of negative data (numeric type data) before type conversion from numeric type to character string type is -500 and the maximum value is -4, after type conversion to character string type. The minimum value of the second bucket is -99, and the maximum value is -10.

Further, the generation unit 123 sets the maximum value of the first bucket corresponding to all the data having a positive value to the maximum value in the data included in the intermediate result histogram. Further, the generation unit 123 sets the minimum value of the second bucket corresponding to all the data having a negative value to the minimum value in the data included in the intermediate result histogram. For example, the minimum value of positive data (numeric type data) before type conversion is 4, the maximum value is 500, and the minimum value of negative data (numerical type data) before type conversion is -500, maximum value. When is -4, the minimum value and the maximum value of the data included in the intermediate result histogram are -99 and 99.

Further, when the generation unit 123 aggregates the buckets corresponding to the data having a positive value among the plurality of buckets, the generation unit 123 totals the number of records and the number of domains of all the buckets corresponding to the data having a positive value. By calculating, the number of records and the number of domains in the first bucket are set. Further, when the generation unit 123 aggregates the buckets corresponding to the data having a negative value among the plurality of buckets, the generation unit 123 totals the number of records and the number of domains of all the buckets corresponding to the data having a negative value. By calculating, the number of records and the number of domains in the second bucket are set.

In addition, when the data showing a positive value and the data showing a negative value are included, the generation unit 123 determines the ratio of the maximum value of the data before type conversion to the minimum value before type conversion. The number of domains in each of the first and second buckets is estimated based on the total number of domains (the number of data) in the bucket before update, and the first bucket and the second bucket include information indicating the estimated number of domains. You may generate a bucket.

For example, suppose that the bucket before update contains 100 pieces of data, and the maximum value of the bucket is 9 and the minimum value is -3. In this case, the ratio of the maximum value to the minimum value is 3: 1. Therefore, when the number of domains of the first bucket and the second bucket generated from the bucket before the update is estimated based on the ratio, the number of domains in the first bucket is 75, and the number of domains in the second bucket is 75. Is 25 pieces. By doing so, the generation unit 123 can easily estimate the number of domains in the first bucket and the second bucket.

[Negative value handling processing]
The generation unit 123 determines whether or not the data corresponding to the specified column includes data showing a negative value. When the generation unit 123 determines that the data indicating a negative value is included, the generation unit 123 executes the negative value correspondence process and changes the bucket of the histogram generated as the intermediate result histogram.

First, when the generation unit 123 determines that a bucket containing data showing a positive value and data showing a negative value exists, the bucket is divided into a first bucket having a positive value and a negative value. Disassemble into a second bucket having.

Subsequently, the generation unit 123 determines whether or not the maximum value of the data included in the already generated intermediate result histogram is a positive value. When the maximum value of the data included in the intermediate result histogram is a positive value, the generation unit 123 includes the maximum value in one or more first buckets corresponding to the data having a positive value in the intermediate result histogram. Update to the maximum value in the data. Further, when the minimum value of the data included in the intermediate result histogram is a negative value, the generation unit 123 converts the maximum value in one or more second buckets corresponding to the data having a negative value into the intermediate result histogram. Update to the minimum value of the contained data.

Further, when the maximum value of the data included in the intermediate result histogram is a negative value, the generation unit 123 sets the maximum value of the data to the minimum value of the data included in the histogram and sets the minimum value of the data. Set the value to the maximum value of the data contained in the histogram.

Further, the generation unit 123 replaces the minimum value and the maximum value in the second bucket. For example, when the minimum value of the second bucket is -99 and the maximum value is -10 due to the execution of the digit difference handling process, the generation unit 123 replaces these minimum and maximum values. , The minimum value of the second bucket is -10, and the maximum value is -99.

Further, in the generation unit 123, when the intermediate result histogram is not updated by the digit difference processing and is in the same state as the histogram before the type conversion, and there are a plurality of second buckets, the plurality of second buckets Reverse the order.

[Estimation of the number of domains after the filtering conditions are applied]
The generation unit 123 estimates the number of domains (the number of data) after being narrowed down by the narrowing conditions corresponding to the columns to be type-converted included in the query, based on the generated intermediate result histogram.

The column for which type conversion has been performed in the query is retained as data of the type before the type conversion is performed again after the filtering process during query execution is completed. Therefore, the histogram of the data after the filtering process is completed needs to correspond to the histogram corresponding to the data of the type before the type conversion is performed, that is, the histogram stored in the storage unit 11 in advance. ..

Therefore, the generation unit 123 estimates the number of data corresponding to each of the plurality of buckets stored in the storage unit 11 in the histogram before type conversion corresponding to the column, based on the intermediate result histogram. By updating based on the number of data and the number of data before being narrowed down by the narrowing conditions, a histogram of the column after the narrowing down process is completed is further generated.

The cost calculation unit 124 calculates the processing cost of the query based on the intermediate result histogram generated by the generation unit 123. For example, the cost calculation unit 124 estimates the size of the intermediate data when the query is executed based on the intermediate result histogram generated by the generation unit 123.

The execution unit 125 executes the query based on the processing cost calculated by the cost calculation unit 124. Specifically, first, when the execution unit 125 completes the creation of histograms and cost estimation of all arithmetic processing corresponding to the query, it optimizes the execution plan including query optimization based on the estimated processing cost. .. Then, the execution unit 125 executes the query based on the optimized execution plan.

[Process flow in database system 1]
Subsequently, the processing flow in the database system 1 will be described. FIG. 4 is a flowchart showing a processing flow in the database system 1 according to the present embodiment.

First, the query corresponding to the database 111 is acquired from the user terminal 2 (S10).
Subsequently, the identification unit 122 identifies the column in which the type conversion is performed based on the acquired query (S20).

Subsequently, the generation unit 123 generates the same histogram as the histogram before the type conversion is performed as the intermediate result histogram (S30).
Subsequently, the generation unit 123 determines whether or not the data in the specified column contains data of an order of magnitude (S40). When the generation unit 123 determines that the data contains an order of magnitude, the generation unit 123 shifts the process to S50 and executes the process of dealing with the order of magnitude. The details of the digit difference processing will be described later. If the generation unit 123 determines that the data does not include an order of magnitude of data, the generation unit 123 shifts the processing to S60.

Subsequently, the generation unit 123 determines whether or not the data in the specified column contains data having a negative value (S60). When the generation unit 123 determines that the data having a negative value is included, the generation unit 123 shifts the process to S70 and executes the negative value correspondence process. The details of the negative value handling process will be described later. If the generation unit 123 determines that the data does not contain negative value data, the process is transferred to S80.

Subsequently, the generation unit 123 corrects the column type (S80). Subsequently, the generation unit 123 determines whether or not the types of all the columns specified by the specific unit 122 have been corrected (S90). When the generation unit 123 determines that the types of all columns have been modified, the process is transferred to S100, and when it is determined that the types of all columns have not been modified, the process is transferred to S30.

Subsequently, the processing cost of the query is calculated based on the intermediate result histogram generated by the cost calculation unit 124 and the generation unit 123 (S100).
Subsequently, the generation unit 123 executes the type return process (S110). The details of the mold reversion process will be described later.
Subsequently, the execution unit 125 executes the query based on the processing cost calculated by the cost calculation unit 124 (S120).

[Process flow in digit difference processing]
Next, the processing flow in the digit difference processing will be described. FIG. 5 is a flowchart showing a processing flow in the digit difference handling processing according to the present embodiment.

First, the generation unit 123 has a bucket corresponding to positive and negative values, that is, a bucket containing positive value data and negative value data in the histogram corresponding to the column specified by the specific unit 122. Whether or not it is determined (S51).

When the generation unit 123 determines that there are buckets corresponding to positive and negative values, it shifts processing to S52, and shifts the bucket to the first bucket corresponding to the positive value data and the negative value data. Disassemble into the corresponding second bucket. When the generation unit 123 determines that there is no bucket corresponding to the positive value and the negative value, the process is transferred to S53.

Subsequently, the generation unit 123 aggregates the positive value data in the first bucket (S53) and aggregates the negative value data in the second bucket (S54).
Subsequently, the generation unit 123 updates the maximum value and the minimum value of the column (S55). Specifically, the generation unit 123 sets the maximum value of the first bucket to the maximum value of the column, and sets the maximum value of the second bucket to the minimum value of the column.

[Processing flow in negative value handling processing]
Next, the processing flow in the negative value correspondence processing will be described. FIG. 6 is a flowchart showing a processing flow in the negative value correspondence processing according to the present embodiment.

First, the generation unit 123 has a bucket corresponding to positive and negative values, that is, a bucket containing positive value data and negative value data in the histogram corresponding to the column specified by the specific unit 122. Whether or not it is determined (S71).

When the generation unit 123 determines that there are buckets corresponding to positive and negative values, it shifts processing to S72, and shifts the bucket to the first bucket corresponding to the positive value data and the negative value data. Disassemble into the corresponding second bucket. When the generation unit 123 determines that there is no bucket corresponding to the positive value and the negative value, the process is transferred to S73.

Subsequently, the generation unit 123 determines whether or not the data having a positive value exists in the data corresponding to the column specified by the specific unit 122 (S73). When the generation unit 123 determines that the positive value data exists, the process is transferred to S74, and the maximum value of the negative value data before the type conversion is set to the minimum value of the column data. When the generation unit 123 determines that the data having a positive value does not exist, the process is transferred to S75, and the maximum value and the minimum value of the data corresponding to the column are exchanged.

Subsequently, the generation unit 123 replaces the maximum value and the minimum value of the second bucket corresponding to the negative value data (S76).
Subsequently, the generation unit 123 reverses the order of the plurality of second buckets when there are a plurality of second buckets (S77).

[Process flow in type return processing]
Next, the flow of processing in the mold return processing will be described. FIG. 7 is a flowchart showing a processing flow in the mold return processing according to the present embodiment.

First, the generation unit 123 determines whether or not the conditional expression included in the query includes the type conversion process (S111). When the conditional expression does not include the type conversion process, the generation unit 123 updates the histogram by transferring the process to S112 and applying the process corresponding to the conditional expression to the histogram before the type conversion.

When the conditional expression includes the type conversion process, the generation unit 123 executes the type conversion for the column to be type-converted (S113).
Subsequently, the generation unit 123 executes the operation indicated by the conditional expression on the column after the type conversion (S114).

Subsequently, the generation unit 123 calculates the selection rate of the data corresponding to the column based on the number of data before and after the execution of the operation (S115), and updates the histogram before type conversion based on the selection rate (S115). S116).

Subsequently, the generation unit 123 determines whether or not an unprocessed conditional expression exists (S117). If the unprocessed conditional expression exists, the generation unit 123 shifts the process to S111, and if the unprocessed conditional expression does not exist, the generation unit 123 ends the process of this flowchart.

[Effect in this embodiment]
As described above, the database system 1 according to the present embodiment shows the frequency distribution of the data before the type conversion is performed corresponding to the column in which the type conversion is performed to different types in the acquired query, and the storage unit is stored in advance. Based on the histogram stored in 11, a histogram having a coarser frequency distribution than the histogram is generated as an intermediate result histogram of the columns included in the intermediate result of the query. As a result, the database system 1 generates an intermediate result histogram corresponding to the data after the type conversion by converting the maximum value and the minimum value of the histogram and the bucket in response to the type conversion, although the grain size is coarse. The query processing cost can be calculated accurately based on the intermediate result histogram.

Further, the database system 1 executes the type return processing after calculating the processing cost of the query based on the intermediate result histogram, and further generates the histogram of the column after the narrowing condition is applied. By doing so, the database system 1 can improve the estimation accuracy of the processing cost corresponding to the processing that occurs after the narrowing-down processing is performed.

Although the present invention has been described above using the embodiments, the technical scope of the present invention is not limited to the scope described in the above embodiments. It will be apparent to those skilled in the art that various changes or improvements can be made to the above embodiments. For example, the above-mentioned plurality of embodiments may be combined. Further, in particular, the specific embodiment of the distribution / integration of the apparatus is not limited to the one shown above, and all or a part thereof may be arbitrarily added according to various additions or functional loads. It can be functionally or physically distributed / integrated in units.

1 ... database system, 11 ... storage unit, 12 ... control unit, 121 ... acquisition unit, 122 ... specific unit, 123 ... generation unit, 124 ... cost calculation unit, 125 ... Execution unit, 2 ... User terminal

Claims (11)

Computer runs,
In a query statement executed in a database system, a specific step to identify a column that undergoes type conversion to a different type, and
The frequency distribution of the data before the type conversion corresponding to the specified column is shown, and a histogram having a coarser frequency distribution than the histogram is obtained based on the histogram stored in the storage unit in advance. A generation step that is included in the intermediate result of the query statement and is generated as an intermediate result histogram of the data after the type conversion corresponding to the column is performed.
Histogram generation method including. In the specific step, the computer identifies, in the query statement, the column in which the type conversion from the type corresponding to the numerical value to the type corresponding to the character string is performed.
In the generation step, when the data before the type conversion corresponding to the specified column includes data having a different number of digits, the computer of all the data corresponding to the column. Generate a bucket containing information indicating the number of the data and the number of values indicated by the data in the data interval including the maximum value and the minimum value before the type conversion, and the intermediate result based on the generated bucket. Generate a histogram,
The histogram generation method according to claim 1. In the specific step, the computer identifies, in the query statement, the column in which the type conversion from the type corresponding to the numerical value to the type corresponding to the character string is performed.
In the generation step, when the computer includes data showing a positive value and data showing a negative value in the data before the type conversion corresponding to the specified column is performed. A first bucket corresponding to all data having a positive value is generated, and a second bucket corresponding to all data having a negative value is generated.
The histogram generation method according to claim 1 or 2. In the generation step, the computer sets the maximum and minimum values of the data contained in the first bucket and the second bucket to the maximum and minimum values of the data contained in the respective buckets. Set,
The histogram generation method according to claim 3. In the generation step, the computer sets the maximum value of the first bucket to the maximum value in the data included in the intermediate result histogram, and sets the minimum value of the second bucket to the maximum value of the data included in the intermediate result histogram. Set to the minimum value,
The histogram generation method according to claim 4. In the generation step, when the maximum value of the data included in the histogram is a positive value, the computer sets the maximum value in the first bucket, which is one or more buckets corresponding to the data having a positive value. The maximum value in the data included in the histogram is set, and the minimum value in the second bucket, which is one or more buckets corresponding to the data having a negative value, is set as the minimum value in the data included in the histogram.
The histogram generation method according to any one of claims 1 to 5. In the generation step, when the maximum value of the data included in the histogram is a negative value, the computer sets the maximum value of the data to the minimum value of the data included in the histogram and the data. Set the minimum value of to the maximum value of the data contained in the histogram,
The histogram generation method according to any one of claims 1 to 6. In the generation step, when the data corresponding to the specified column includes data indicating a positive value and data indicating a negative value, the type in the data corresponding to the column. The first, which is one or more buckets corresponding to data having a positive value, based on the ratio of the maximum value before conversion to the minimum value before type conversion and the number of all data corresponding to the column. The number of data contained in each of the bucket and the second bucket, which is one or more buckets corresponding to the data having a negative value, is estimated, and the first bucket and the second bucket including information indicating the estimated number of data are included. Generate a bucket,
The histogram generation method according to any one of claims 3 to 7. In the generation step, the computer estimates the number of data after being narrowed down by the narrowing conditions corresponding to the columns included in the query statement based on the generated intermediate result histogram and stores it in the storage unit. The query is performed by updating the number of data corresponding to each of the plurality of buckets included in the stored histogram based on the estimated number of data and the number of data before being narrowed down by the narrowing condition. Further generate a histogram of the column after the statement is executed,
The histogram generation method according to any one of claims 1 to 8. In the query statement executed in the database system, a specific part that identifies the column that undergoes type conversion to a different type, and
The frequency distribution of the data before the type conversion corresponding to the specified column is shown, and a histogram having a coarser frequency distribution than the histogram is obtained based on the histogram stored in the storage unit in advance. A generator included in the intermediate result of the query statement and generated as an intermediate result histogram of the data after the type conversion corresponding to the column is performed.
Histogram generator with. Computer,
In the query statement executed in the database system, the specific part that identifies the column that undergoes type conversion to a different type, and
The frequency distribution of the data before the type conversion corresponding to the specified column is shown, and a histogram having a coarser frequency distribution than the histogram is obtained based on the histogram stored in the storage unit in advance. A generator that is included in the intermediate result of the query statement and is generated as an intermediate result histogram of the data after the type conversion corresponding to the column is performed.
Histogram generation program that functions as.

Images