JP4285770B2 - Index specification for relational databases - Google Patents

Description

Background of the Invention
Field of Invention
The present invention relates to specifying indexes for relational databases. The invention also relates to a relational database that includes a process for specifying an index.
Description of prior art
Data processing environments are known in which executable instructions are configured to obtain a set of data from data contained in a database in response to a data inquiry. Data may be accessed directly from the data table, where it is necessary to search all entries in the table to obtain the requested information. Alternatively, an index may be used in the search process to substantially increase the speed of the search process.
The design of a large and frequently used index structure for a database is currently very difficult to execute and is subject to errors. The problem is that human database administrators detect requests to index generally hundreds or thousands of different structured query language (SQL) statements that run against the database on a daily basis, and then This arises because of the technical demands and constraints that it is impossible to translate these requests into a set of preferred indexes defined throughout the database. However, the poorly specified index design results in the SQL statement consuming significantly more processor equipment and running longer than it should, and as a result, the device is overloaded. It will be loaded.
For a long time, there has been a need for a method for globally specifying an index structure defined for a given database design for a typical SQL workload called a target workload. However, this technical problem is solved when it is difficult to realize a technical solution by utilizing the processing power that can be used without requiring an intuitive mental process in the human operator's organ. Not.
Summary of the Invention
According to a first aspect of the invention, a plurality of statements supplied to the database are parsed to specify a set of indexes for the database stored in the machine in a readable form, and a table of the database is obtained. To identify an index derived from, evaluate a level of improved behavior achievable when the index is available, and specify a set of indexes (tuples, groups) to the database A method is provided that includes processing the evaluated level.
In a preferred embodiment, the improved level of behavior is assessed by generating a reduced model of the database table derived from information related to the nature of the table. In general, the reduced model may include 5000 data entries in the region for each table. The model database is preferably filled with representative data entries obtained from the modeled live database, and the model is filled by considering the cardinality of the current index of the live database. Furthermore, the database model can be fulfilled by considering the distribution of entries in the current index of the live database.
In the preferred embodiment, database statistics are copied from the live database to the database model. The base level cost is preferably calculated to execute the statement without an additional index. The cost level is preferably obtained by evaluating the execution time. Further, the cost level may be assessed by assessing index maintenance overhead.
According to a second aspect of the invention, an analysis means configured to specify a set of indexes for a relational database and analyzing a plurality of statements supplied to the database, and obtained from a table of the database Identifying means for identifying the index, evaluation means for assessing the level of improved behavior achievable when the index is usable, and specifying the set of indexes for the database Means for specifying a set of indexes is provided comprising processing means for processing the evaluated levels.
In a preferred embodiment, the potential index is identified from the set of predicates defined by the statement.
According to a third aspect of the invention, the data storage means, the data processing means, and the program instructions readable from the data storage means are configured to designate a set of indexes for the database. In response to the instructions, the processing means parses a plurality of statements supplied to the database, identifies an index obtained from a table of the database, and To provide a means for evaluating the level of improved behavior achievable when an index is available and for processing the evaluated level to specify a preferred set of indexes for the database It is configured.
In the preferred embodiment, cost savings are calculated by processing the cost value of the cost of the old SQL statement and the cost value of the cost of the new SQL statement by possible indexes. Cost savings may be calculated by subtracting the new cost from the old cost. Cost savings are preferably calculated for the table by considering each new possible index with respect to its respective table.
In the preferred embodiment, the possible indexes are ordered in terms of the possibility of being designated as the preferred index. Index combinations are identified by randomly combining currently possible indexes and processing the evaluated levels to specify a preferred set of indexes.
According to a fourth aspect of the invention, a plurality of data tables stored in a machine readable form and an index to process the data tables in response to statements and facilitate processing of the data tables A relational database comprising instructions executable by the processing means to specify a preferred set of indexes, wherein the instructions are provided to the database. Parses multiple statements, identifies the index obtained from the database table, evaluates the level of improved behavior achievable when the index is available, and determines the preferred index for the database Configured to process the evaluated levels to specify a set That.
[Brief description of the drawings]
FIG. 1 illustrates a telecommunications environment that includes a data analysis system.
FIG. 2 shows details of the data analysis system shown in FIG. 1 and having a plurality of large capacity disk storage devices configured to store data tables.
3A, 3B, 4A and 4B show examples of data tables of the type stored on the data storage device shown in FIG.
5 and 6 show examples of indexes obtained from the data included in the table shown in FIG. 3A.
FIG. 7 shows a database structure installed in the environment shown in FIG. 2 and includes a process of designating an index.
FIG. 8 shows processing executed in the environment shown in FIG. 7 and includes processing for designating a set of indexes.
FIG. 9 illustrates the process of specifying the set of indexes shown in FIG. 8, the process of modeling a live database, the process of analyzing a typical SQL statement, and the base cost calculation. Processing, processing for identifying possible indexes, and processing for designating a preferred index.
FIG. 10 shows in detail the process of modeling the live database shown in FIG.
FIG. 11 shows in detail the process of parsing the SQL statement shown in FIG.
FIG. 12 shows a table of data generated using the process shown in FIG.
FIG. 13 shows a process for calculating the base cost shown in FIG.
FIG. 14 details the process of identifying and ordering the possible indexes shown in FIG.
FIG. 15 details the process of specifying a preferred set of indexes shown in FIG.
FIG. 16 shows in detail an example of the data generated during the process shown in FIG.
FIG. 17 illustrates the generation of a new index combination using the process detailed in FIG.
FIG. 18 shows a procedure for setting an index in the live database.
Example
The present invention will now be described by way of example only with reference to the accompanying drawings shown above.
A telecommunications environment is shown in FIG. 1, in which a plurality of communication user equipments 101 such as telephone handsets, fax machines and modems are connected to a local exchange 102 via respective local analog lines 103. . The analog signal is digitized at the local switch 102, followed by switching and rerouting within the digital domain. This results in a large number of calls being routed through the digital time division multiplexed channel 104 to the trunk communication network 105.
The local exchange 102 and the trunk network 105 perform normal communication switching and allow calls to be connected in the normal manner. In addition, advanced services are provided by the advanced service node 106 to allow customers access to advanced services such as storage and forward forwarding and identification of personal numbers. The customer accesses the advanced service node 106 via a digital multiplexer 107 connected to the trunk network 105. Therefore, the call is routed to the advanced service node 106 via the trunk network 105, and then information is sent back to the calling customer, and the call is routed again to the terminal device 101 or the like via the trunk network 105. Alternatively, the functionality of the advanced service node 106 may be distributed through the trunk telecommunications network 105.
When a call is made, the billing details for the call are stored at the associated local exchange 102. Subsequently, this call information representing the use for which the connected customer bears the cost is supplied to the central management unit 108 via the communication channel 109. In this manner, all fee information is routed to a central management unit 108 that manages the generation and distribution of customer accounts.
In operation, a system composed of the terminal device 101, the local exchange 102, the trunk network 105, and the advanced service node 106 connects a very large number of calls, and as a result, a collection of operation data is generated. First, this data identifies details regarding the nature of the call being made, the nature of the destination call and the type of call. The call type information may identify the call as a direct local call, or the call is a long distance or international call, including use of services provided by the advanced service node 106 May be identified. Analysis of this data provides at least two significant advantages. First, the manner in which the system operates can be changed in response to the collected data representing the operating characteristics of the system. Therefore, if certain areas are found to use substantially more advanced services than other areas, reroute these service assignments to optimize the availability of these services. Is possible. Similarly, assessment of network usage may be made available to designers of marketing strategies, particularly when efforts are made to successfully use the capabilities available during off-peak periods.
In fact, almost similar queries are executed at regular intervals on the database. The division of actions has particular interests and requires that those interests be updated on a regular basis. A very large number of users can access the database, and over a period of time hundreds or thousands of SQL queries are performed on the data contained in the database, and most of these queries are updated as new data is included It is executed a number of times to produce the rendered result.
The system shown in FIG. 1 includes a data analysis device 110, an advanced service node 106, and a central management device 108 that are configured to receive data from the trunk network 105. In turn, the data analysis device returns the data to the trunk network 105, the advanced service node 106, and the central management device 108. Within the trunk network 105 and the advanced service node 106, modifications are made to the technical operation of these systems in response to data received back from the data analyzer. Similarly, the data returned to the central management device 108 results in a change in the way the customer is invoiced (ie, billed), i.e., generally modifies the way the customer uses the network.
Data is collected in the data analyzer 110 and stored in the form specified by the original system designer. These designers strive to predict the type of query that will be required later, but it is impossible to anticipate every query that matters. Therefore, data tends to be stored in the data analysis device in relational database terms, thereby facilitating subsequent operations in response to specific queries. As a result of these queries, the procedure of the trunk network 105, the advanced service node 106, or the central management device 108 is modified. In addition, the data generated in response to a particular query is verified in the data verification unit 111 for reference or subsequent use.
A data analysis device 110 is shown in detail in FIG. 2, which has 10 processors substantially located around a mainframe computer 201 such as the IBM ES9000 and capable of operating at 50 MIPS (million instructions per second). Configured. A user can access the database system via a user terminal 202 connected by a plurality of networks, and the data is stored in the disk drive 203 in the form of a data table and stores data in units of terabytes. can do.
An operational data source such as the trunk network 105, the advanced service node 106, and the central management device 108 are shown as the operational data source 204. Further, the data may be received from another external source, such as shown by external data source 205. The operation control signal stream returning to the trunk network 105, advanced service node 106, or central management unit is indicated by data supplied to the operation control unit 206, and data verification and printing is indicated at 207. Data is stored in the system on disk storage 203 and output data is obtained in response to the query and is initiated by the user using network user terminal 202. Data held on disk storage 203 is continuously collected in response to the operation of a trunk network or another device as shown in FIG. In addition, management data may also be maintained on the storage device and a query may be initiated to relate the operational data to the management data.
Examples of types of database tables stored on the disk storage device 203 are shown in FIGS. 3A, 3B, 3C and 3D. Information representing each communication event is received from the central management device 108. Each event is given a unique sequence number, thereby creating a new record in the database as shown in FIG. 3A. The recording is completed by identifying the date, start time, end time of the event, the customer's phone number that initiated the event, and the type of call. Therefore, at 0:01 am on December 01, 1995, a customer whose telephone number was 404 7241 made a type A call and ended at 0:25 am. In this example, an arbitrary designation is given for the type of call, where a type A call represents a local call, a type B call represents a long distance call, and the type The C call is a long distance call made by an operator, and the type G call represents the use of advanced services. The event identified above is recorded under event number 12345, and the next event is recorded under event number 12346. This was initiated by a customer whose telephone number was 386 4851 at 0:01 am on December 01, 1995, again identified as a type A call.
The database in data analysis system 110 also includes management data as shown in FIG. 3B. The table shown in FIG. 3B maps customer identifiers onto customer phone numbers. It will be appreciated that a particular customer may have multiple phone lines with different phone numbers, whereby a particular phone number is required to identify the associated customer. Thus, in the example shown, the customer whose telephone number is 404 7241 is assigned customer identification number 0074895 in record 303. Similarly, record 304 indicates that telephone number 404 7242 is assigned to the customer identified by customer identification number 057896.
The table shown in FIG. 4A associates customer identification numbers with customer addresses. Thus, it can be seen from record 305 that a customer with identification number 0074895 lives in Hyholborn 52 in London.
In general, if a particular city is always located within a particular geographic area, it is not necessary to provide extensive geographic data. However, the geographic area may be adjusted by a particular operator to reflect changes in the commercial environment. A separate table is provided that maps towns and cities to specific regions. Thus, as identified by record 306, London is mapped to the southeast region and Loughborough is mapped to eastern inland region in record 307. The regional boundaries can be adjusted to reflect the location of the regional office, so that, for example, the eastern inland region is combined with the western inland region to become an inland region. Under such circumstances, it is not necessary to change the table shown in FIG. 4A, only the table shown in FIG. 4B needs to be changed, and the table shown in FIG. It has substantially fewer records than the table shown in FIG. 4A.
It will be appreciated that when queried with reference to event numbers, the data record is read very quickly by the data table shown in FIG. 3A. The data table shown in FIG. 3A is ordered with respect to event numbers, where each event number is specific to a particular record. Thus, given the event number 12345, the database can quickly respond to a customer whose phone number is 404 7247 making a Type A call. Similarly, by referring to the table shown in FIG. 3B, this telephone number can be associated with the customer identifier, thereby addressing and querying for the tables shown in FIGS. 4A and 4B, respectively. A region is identified.
However, problems arise when a query is made regarding another field in the record shown in the table of FIG. 3A. For example, the query may require that a list is generated for all events initiated by a particular phone number. Alternatively, the inquiry may be made for all events of a particular call type. More complex queries may be made using this type of entry, for example, a query may be made requesting details of all calls initiated from the southeast region and may last for more than 10 minutes.
According to the previous example, a simple query is made requesting details of all events initiated by a particular phone number. The table shown in FIG. 3A is not indexed under the telephone number entry, so the processor needs to search the telephone number data field for all records in the table. Obviously, this requires substantially more processor resources than reading the record for the event number. The table shown in FIG. 3A is ordered by event number so that given an event number, a particular record is identified very quickly. However, if not indexed under any other field, a search must be performed to identify the particular field of interest. According to the more complex example identified above, a particular query needs to be satisfied by searching different fields several times through all the records contained in the table.
In order to improve the speed at which the search is performed, it is possible to generate an index for a particular table so that a quick search is also performed on other data fields. As shown in FIG. 5, the data contained in Table 3A has been used to generate an index based on telephone numbers. Each phone number is considered one at a time and an index is generated to identify each particular event initiated by a particular phone number. Thus, record 301 is identified for telephone number 404 7241 and event 12345 is recorded for this telephone number. Subsequently, another event 14876,15739,15928 and 16047 was initiated by this phone number. The index continues until all events for the phone number being considered are recorded. Subsequently, the index continued to the next telephone number, in this example 404 7242, for which events 13728, 14937, 15821, 14723, etc. were recorded.
Thereafter, phone number 404 7243 is considered along with the event recorded for this phone number, and so on, until all phone number entries in the data table shown in FIG. 3 have been considered. In the index shown in FIG. 5, the number of records given is equal to the number of records in the original table shown in FIG. 3A. However, the table of FIG. 5 is merely an index, so for a particular telephone number, the index will point back to the specific event number in the main table shown in FIG. 3A. Thus, the index can quickly perform a search based on the telephone number, after which the remaining data contained in the table record shown in FIG. 3A is obtained.
An index similar to that shown in FIG. 5 is shown in FIG. 6, where the data contained in the table shown in FIG. 3A is indexed with respect to the type of event. The record 301 is reproduced as the record 601 in the index shown in FIG. The event shown as event number 12345 was generated by a subsequent type A call, which is listed for the entry for event type A. Event numbers 13856, 14024, 15752, and 14831 were events that produced type A calls.
Thereafter, as shown in FIG. 6, events for type B calls are recorded including event 13728 and placed in record 602, and events are sequentially recorded for type B events. Once all events for a type B event have been recorded, the table continues to the type C event initiated by event number 12350.
Once the index for the phone number shown in FIG. 5 and the index for the type of event shown in FIG. 6 are generated, the table shown in FIG. 3A is based on the event number, phone number and event type. These indexes can be used for quick access to records. Clearly, it is desirable to make these indexes available when responding to queries. However, the benefits of index availability must be compared to the costs involved for index generation and more important index updates, along with additional demands on storage space. Thus, in many practical implementations, it is impossible to generate all possible indexes if there is insufficient disk storage space available. Sometimes an increase in disk storage space is possible, but in most practical cases, there is an upper bound on the total storage space and the total amount of storage space allocated for index generation.
The system shown in FIG. 2 is configured to store a very large amount of data. In addition, this data is fairly strongly requested by the user who is querying to generate a new set of data. Some of these queries have a one-time nature, but in order to assess changes to the requested set of data in response to additions and changes made to the source data, the queries are A high percentage is recursively matched at relatively regular intervals. Under these circumstances, it is virtually impossible for a database administrator to receive an indexing request correctly to achieve optimal behavior in response to hundreds or thousands of different SQL statements made against the database. Impossible. However, if the optimal index is not provided, the query places excessive demands on the central processing unit. In addition, such queries tend to run longer than necessary, thereby causing delays. Because these issues remain, the machine is heavily overloaded and limits the number of queries that can be run per unit time. This results in less machine usage and actually increases the cost of the system per query.
The system of the present invention is configured to specify an indexing structure in an attempt to overcome the technical problems described above. The system is configured to parse SQL statements provided to the database to specify a preferred set of possible indexes and can be used to improve the execution of SQL statements that reference the database. An estimate is made of the level of improved behavior that can be achieved if the potential index is actually available in the database system. From these evaluations, a preferred list of indexes is specified. Thus, subjective restrictions on the database administrator are removed by the machine, the numerical display is calculated, and a specific index may be included in the system before allocating resources to actually generate the index. The desired degree is indicated. These numerical indications are actually derived from improved behavioral evaluation when the index is placed.
The database hardware shown in FIG. 2 is schematically shown in FIG. 7 along with related processes performed by the hardware. The database system may be configured in accordance with database instructions permitted under the trademark “DB2” of IBM Corporation. The resulting DB2 environment is shown in FIG. 7 as 710, which includes data storage 702 and SQL execution process 703.
In the illustrated embodiment, three tables are defined in the data storage device, which are shown as a first table 704, a second table 705, and a third table 706. Within the illustrated database system, all six indexes are generated for each table (which is variable depending on the configuration), which are indicated by the dashed area 707. Thus, each table may have an associated set of indexes, and the actual index contained within the set of indexes is specified by the index set as defined by the preferred features of the present invention. Determined according to the method performed by the device. The database also includes a catalog 709 configured to store database definitions and catalog statistics.
In DB2, “RUNSTATS” configured to obtain statistics on tables, columns, and indexes in the database is provided as a utility. The catalog provides detailed information about the dimensions of the table and allows an empty table to be created before data is transferred from a live data store to a copy of a similar data store. In addition, the SQL execution process includes a process known as an “optimizer”, which is configured to analyze catalog statistics to optimize the execution of a particular SQL statement.
In addition to defining the table dimensions, the catalog statistics also record column cardinality, second highest and second lowest values, cluster ratios and data distribution.
Column cardinality defines the number of different values in a particular column, while full key cardinality identifies the total number of different values in the index defined for a table. The customer table may have a cardinality greater than 1 million rows, each of which is a different customer, but the column that identifies the gender of these customers only has two cardinality. Similarly, the cardinality of an index that identifies a geographical region may be the lower of the hundreds.
The second highest value shown as HIGH2KEY represents the second highest value in the particular column. Similarly, the value identified as LOW2KEY represents the second lowest value for a particular column, and this information can be used to determine the potential in the column, especially when considered in combination with the cardinality of the column. A range of values can be displayed.
The cluster ratio shows how well the ordering of the data in the index follows the ordering of the data in the original table. When an index for a cluster is defined for a table and the data in that table is reconstructed, all the rows in the table are put into a clustered sequence and the cluster ratio is considered to be 100 percent. Another index with different columns and the order of the columns relative to the index for the cluster tends to have a cluster ratio with a low value because their data may not be well clustered with respect to the index for the cluster. As rows are inserted into or deleted from the table, the cluster ratio of the index gradually decreases, and more rows become out of sequence for the cluster. Thus, the cluster ratio provides an indication of how well the data in this embodiment is ordered within a particular data index and generated from a sample of live data within each table. Data distribution statistics define the 10 most frequently occurring values in the column along with their percentage of occurrences.
The input information is supplied to substantially two forms of live database systems. First, as indicated by input line 715, new data is provided to the system, and second, as indicated by input line 716, a query for SQL statements is provided to the database. The entered query on line 716 is executed by the SQL execution process 703 and produces output as indicated by output line 717. The new data received on the input line 715 results in the table in the data store 702 being updated, and this update process is performed according to the SQL command. Accordingly, changes, deletions, and queries of both entered data and SQL are fed to the SQL execution process 703, both executed under the control of the process.
In general, SQL statements in the form of queries or queries provided on input line 716 are executed more quickly when SQL processor 703 accesses a number of indexes in addition to the main data from each table. As a result, if a database system is required to primarily respond to this type of query, it is desirable to include multiple indexes in the system. However, when a table is requested to be updated in response to new data received on input line 715 or in response to changed or deleted data, SQL may be used if multiple indexes exist. A large processing overhead is placed on the execution process 703. Therefore, if the database system is primarily required as a data repository and queries to the system are minimal, it is desirable to minimize the number of indexes in the system so as to reduce housekeeping overhead. In most practical systems, both types of input are received, and therefore the number of indexes in the system is minimized to reduce housekeeping overhead, but optimal if there is storage space Current index selection should be optimized to provide good performance.
The degree to which the table index specification approaches the ideal solution depends on the nature of the SQL query provided on input line 716. Although the system is supplied with a large number of indexes, these indexes have little benefit if they are not related to the predicates specified in a typical SQL query. Similarly, if an index is configured to satisfy regularly occurring SQL queries over infrequent SQL queries, the greatest benefit is derived from the available indexes. However, although not particularly common, some SQL queries for relevant operations (in the justification of existing systems) say that high priority must be given to this type of query. It must be recognized that can be very important. Therefore, it can be seen that many competing restrictions are imposed on the index set specifier 708, which recognizes that it attempts to provide an optimal set of table indexes. It is done. The SQL execution process 706 includes an optimizer process configured to evaluate the best method for obtaining and manipulating the data contained in the data storage device 702 to satisfy the SQL statement. The optimizer examines each SQL statement executed against the database and evaluates each of a number of access paths that can satisfy the statement. Each potential access path is assigned a cost that represents the amount of processing required for the particular path being executed as well as the amount of disk access. The optimizer is then configured to select the access path with the lowest cost as the actual path for performing the function requested in the SQL statement.
Statement optimization may occur during execution, known as dynamic SQL, where the content of each SQL statement typically varies from one execution to the next. . Instead, the optimization may be performed once before the statement is actually executed, and the result is stored in the DB2 plan. This type of optimization is known as static SQL, and is used when SQL statements are known prior to execution. Static SQL is more effective than system features if the optimization process is performed only once for the SQL statement and the result is stored for later iteration execution.
A preferred index set specifier (708) prepares for optimization and before the optimizer makes an online decision within the SQL execution process 703 as to which index of the available index to use. Process stages that are configured to specify which indexes are actually to be generated. However, in addition to performing optimization, the designator 708 must take into account index housekeeping, execution frequency, and statement priority.
Designator 708 designates a preferred set of indexes in response to a sample of typical SQL statements executed by the system. To obtain this information, the SQL trace (trace) process 718 tracks the queries provided on the input line 716 so that after an appropriate period of time, a representative sample of SQL statements has passed through the line 719. Is supplied to the designation device 708. Designator 708 reads the catalog statistics as well as a sample of the tabular data from the data store, so that the tabular data is provided through line 720. After the index set designation process is executed, an output signal is supplied through the output line 721 so that the designation data can be generated in a format that can be read by the printer 722 visually. In addition, an SQL indication is provided via line 723 to the SQL execution processor 703, resulting in a preferred set of indexes being generated in the data store. Further, the information generated by the printer 722 can inform the operator that additional storage is required in the data storage in order for a preferred set of indexes to be constructed.
An overview of the process by the optimal index set designator 708 is shown in FIG. Initially, the SQL tracker 718 is activated at step 801 so that the SQL statement used to access the database over a period of time is recorded by the SQL tracker. Finally, a sample SQL statement is collected and it is determined that the table index is again specified.
At this stage, the database can effectively be taken off line, so that no further queries can be made until a new table index is specified. Under such circumstances, the index set designation process is preferably performed on a hardware platform common to the database itself. Instead, in another embodiment, the index set designation process steps may be performed independently on a separate platform using data sent to and from the database platform.
The instruction to specify the set of indexes is supplied to the external platform using an appropriate data carrier medium such as a magnetic disk, an optical disk, or a magneto-optical disk. Alternatively, the instructions may be supplied to additional platforms via network capabilities. The instruction load on the index set designator 708 is indicated by a removable disk 724.
In step 802, the catalog statistics for each table space in the live database are updated using RUNSTATS so that the updated data from the catalog is used when information is provided to the index set designator 708. It is ensured that it becomes possible.
In step 803, an index set designation process is performed to designate an index set. In step 804, a question is asked as to whether more disk space is provided, and if the answer is affirmative, the disk storage allocation for generating the index is increased. Instead, if the question made in step 804 is negative, then control is directed to step 806.
In step 806, a question is asked as to whether the details of the specified set will be printed, and if the answer is affirmative, a print signal is supplied to printer 722, possibly in the form of a normal parallel interface connection. Is done. Instead, if the question made in step 806 is negative, then control is directed to step 808.
In step 808, a question is asked as to whether the designation made in step 803 is performed for the live system, and if the answer is affirmative, it is performed in step 809, subject to limited disk space. The Instead, when control is directed to step 810, the database is eventually put back on the line.
A preferred index set designation method 803 is shown in FIG. In step 901, the live database is modeled in the process of the designated device 708 according to the procedure detailed in FIG. Thereafter, the SQL statement tracked by the SQL tracking process 718 is analyzed by the process of the designated device 708 according to the procedure detailed in FIG.
Live database modeling results in a table that is similar to the table shown in FIG. 7, but is substantially smaller than a table in an online live system. It is possible to run the catalog statistics procedure within a designated process, thereby generating catalog statistics that reflect the size of the entries in the modeled table. However, the process by the designation device 708 is related to the effective operation of the live system, and therefore it is even more related to catalog statistics as contained in the live system and stored in the live catalog 702. . Thus, in step 903, the live statistics from the catalog are copied to the index set designator, which forms a primary key index with a default (default) set of live indexes, and for each table an index is clustered. To do.
In step 904, a base level cost estimate is calculated as shown in detail in FIG. 12, whereby a cost improvement is estimated when a potentially optimal index is added. This cost difference provides an objective function for performing the following processing related to the specification of the set of indexes.
Most of the calculations performed to identify the set of indexes are basically done on a per-table basis, after which the table determines which specific index when disk space is available for index generation. The combination is again taken into account when an assessment is made as to whether is generated on the live system. As a result, a table is selected in step 905, candidate indexes are identified in step 906, qualified indexes are ordered according to their objective functions in step 907, and an optimal set of indexes is generated in step 908. . Thereafter, a question is made at step 909 as to whether another table is available, and if the answer is affirmative, control is returned to step 905. If the answer to the question at step 909 is negative, then control is directed to step 910 where an optimal set of indexes that are likely to be applicable to the live system is specified.
A procedure 901 for modeling a live database is shown in FIG. 10 to generate a reduced model of the database within the process of specifying a set of indexes. In step 1001, catalog statistics are read from catalog 702, after which an empty table is generated in the model, copying the properties of live tables 704, 705, 706 as described by the respective catalog definition. The size of each table is limited to 5000 rows, which is substantially less than the number of rows in the live database table.
In order to ensure that the reduced model of the table within the index set designator 708 accurately reflects the live table in the data storage device 702, each of them in the empty table generated in step 1002 Need to be filled with a representative sample of data entries read from the live table. In step 1003, a live table is selected along with its respective catalog.
In step 1004, an index associated with the already selected table and operable in the live database is considered to identify the particular index indicated as HF1 with the highest primary key card value. In step 1005, HIGH2KEY, LOW2KEY and COLCARD for the first column of HF1 are identified, and in step 1006, the distribution of data for the first column is determined.
Thereafter, in step 1007, a set of random values is generated for the first column of HF1 within the range defined by LOW2KEY and HIGH2KEY. The usefulness of the value is weighted according to the frequency distribution as determined at step 1006, so that when the model table is filled with up to 5000 entries obtained from the live table, The distribution of values is sufficient for the process to be performed on the model with respect to the requirements at the time of processing, which substantially reflects the similar requirements made when executed on each live table . The randomly selected values identified in step 1007 are read from the live table entries in step 1008, and the entries read in step 1008 are sequentially written to the model table in step 1009.
In step 1010, first, the model data is processed so that the entries in the data table are reorganized according to the cluster key. Potential indexes are generated for each table, and statistics are gathered on the nature of these indexes. The resulting index statistics are expanded to production dimensions, the expanded values are stored, and the original table data is deleted.
The answer to the question made in step 1011 is affirmative until all of the modeled tables are randomly selected from live tables held in the data store 702 and formed with entries weighted according to frequency. is there.
A procedure 902 for parsing the captured SQL statement is shown in detail in FIG. In step 1101, the SQL statement is processed to determine and determine whether it is the first specific statement that occurred or whether the statement was seen before. Thus, each unique SQL statement is given a unique label, and if the same SQL statement is identified again, the number of occurrences is recorded in the frequency column.
A table is selected in step 1102, and then the SQL statement labeled in step 1101 is identified in step 1103. Thus, steps 1103 through 1106 are only performed for each unique occurrence of the captured statement.
A question is asked in step 1104 as to whether the statement selected in step 1103 uses the table selected in step 1102. If the answer to the question is affirmative, then in step 1105 the statement label is added to the appropriate list of tables. Instead, if the answer to the question at step 1104 is negative, step 1105 is bypassed and control is directed to step 1106.
In step 1106, a question is asked as to whether another statement is considered, and if the answer is affirmative, control returns to step 1103 to allow the next labeled statement to be selected. To. Instead, if the answer is negative, another statement is not available, the statement identifying the pointer is reset, and control is directed to step 1107. In step 1107, a question is asked as to whether another table exists, and if the answer is affirmative, control returns to step 1102 and the next table is selected.
Eventually all tables are considered, and as a result the answer to the question made in step 1107 is negative.
The list of tables generated in step 1105 is shown in detail in FIG. The list was tracked with a first column 1201 identifying the original table, a second column 1202 identifying the SQL statement with respect to those labels as specified in step 1101, and the frequency of the statements, ie It consists of a third column 1203 that identifies the number of times a particular SQL statement occurs in the set.
As shown in FIG. 12, first, Table 1 is selected at step 1102, so that in response to repeated operations at step 1005, SQL statements A, B, C, through SQL Z are Added to the list. Table 2 is then selected, and consequently the SQL label is identified in this table, and finally Table 3 is selected, resulting in the statement label associated with that table. Although there are three tables in an embodiment of the present invention, it should be appreciated that any number of tables may exist when used in a large relational database.
In the third column 1203, the frequency of occurrence is recorded, which is typically measured thousands of times. Thus, x occurrences are recorded for statement A, y occurrences are recorded for statement B, and z occurrences are recorded for statement C.
A procedure 904 for assessing base level costs is shown in FIG. A table is selected at step 1301, and an SQL statement is selected at step 1302. In step 1303, the cost for executing the SQL statement selected in step 1302 when applied to the table selected in step 1301 is evaluated. The cost calculation is accomplished using a timer on value obtained from an optimizer in the SQL execution processor 703. However, the timer on value only considers the use of the index and does not take into account index maintenance. Consequently, in the preferred embodiment, instructions developed under the name "QCF" (TM) by Innovation Management Solutions of Florida, USA are executed, thereby executing CPU usage and SQL statements. For the elapsed time, the index cost value is given in combination with the index maintenance evaluation. These cost values do not represent absolute measurement results, but the associated cost values can be obtained by performing similar methods when additional indexes are present, which adds additional disk space. It provides an objective function that can select a specific index over a more expensive index when compared to a request for.
In step 1304, for each statement, the cost value calculated in step 1303 is multiplied by an execution frequency factor, and in step 1305, the resulting product is multiplied by a priority factor. Then, in step 1306, the cost is added to the base cost sum for the particular table, and in step 1307 a question is asked as to whether there is another statement. If the answer to the question is affirmative, control returns to step 1302 so that the next SQL statement is selected and the cost calculation procedure is repeated.
Finally, if the answer to the question in step 1307 is negative, a question is asked in step 1308 as to whether there is another table. If the answer is affirmative, another table sum is generated in step 1309 and control is returned to step 1301, thereby allowing the next table to be selected. Eventually, the answer to the question made at step 1308 is negative, which leads control to step 905.
The procedure for calculating the cost of a candidate index to identify a qualified index is shown in detail in FIG. In step 1401, candidate indexes are identified from the set of predicates obtained from the SQL statement associated with the table under consideration, which is determined by being selected in step 905 according to the list shown in FIG. Thus, indexable predicates can be identified by analyzing the SQL statements associated with the table under consideration. Identified indexable predicates refer to specific columns that are grouped by table and SQL statement to form a set of predicates. These sets of predicates provide a starting point for identifying candidate indices (ie, candidate indices are constructed) that may benefit when satisfying the associated SQL statement. In addition, catalog statistics are generated for each identified index.
Candidate indexes are selected in step 1402, and the indexes selected in step 1302 in step 1403 are generated as part of the table model held in the index set designator 708. After a new index is created in step 1403, the associated catalog entry in the model is updated in step 1404, thereby making the index full size.
Candidate indexes are generated against the model database. The DB2 recovering index utility is run against the table to feed the index, and the DB2 Runstats utility is run against the database to gather statistics. Statistics are gathered for each index, stored in a database, and expanded to live capacity for use throughout the process.
The possible indexes identified in step 1401 include all index combinations that may use a particular column entry. Thus, the columns are arranged in a different order and include possible orders. Similarly, entries in each column may rise and fall sequentially, again once all these possible combinations exist. Not all of these combinations are actually required as candidate indexes, so a selection process is performed at step 1402 to identify candidate indexes. The column position is called the sequence of columns, and the possibility of rising and falling is called ordering. Indexes that share the same column ordering are grouped together, showing only the differences associated with their ordering. All indexes defined within the group, i.e. all indexes with the same sequence of columns, are generated in the model. Catalog statistics for each of these indexes are also generated, after which all trapped SQL that references the table is targeted on the index. Thereafter, the description function in the database is trained to identify the specific index within the group that was actually used. These indexes then become the selected candidate indexes and the remaining indexes from the potential set are eliminated. After that, the generated index is deleted, the next group is taken into account until all of its groups have been considered, and finally, before control is directed to the loop started in step 1402. The generated index is deleted again.
Accordingly, as described above, a candidate index is selected in step 1402, a candidate index is generated in step 1403, and the catalog is updated in step 1404 in response to the newly generated index.
Since a new index has been generated in the model, the SQL statements that reference the respective tables are cost calculated according to a method that is substantially similar to the method for calculating the base cost level shown in detail in FIG. After the SQL cost is calculated with the appropriate new index in step 1405, the new cost value is stored in step 1406 and then the index generated in step 1303 is deleted in step 1407.
In step 1408, a question is asked as to whether another index exists and if the answer is affirmative, control is returned to step 1402, resulting in the next possible index being selected. Eventually, the cost of all SQL statements is calculated for all possible indexes, resulting in a negative answer to the question made in step 1408.
The cost value calculated in step 1406 and stored in the table defines a new cost for each index that may have been identified in step 1401. For each of these indexes, a cost saving is calculated by subtracting the new cost from the base cost calculated in step 904. This cost saving value represents an objective function, where an index with a low cost saving value is considered more suitable than an index with a high cost saving value. This cost savings value is stored for each index in step 1410 and a question is asked in step 1411 as to whether another index exists. If the answer is affirmative, a cost savings value is calculated for the next index in step 1409, and then a cost savings value is calculated for all possible indexes, resulting in step 1411. The answer to the question is negative.
As a result of storing the cost savings in step 1410, a list of indexes is generated with the cost savings assigned to each. This represents the objective function, so the appropriate index is ordered according to this objective function in step 907, thereby placing the index with the highest cost savings near the top of the list of eligibility.
The process 908 shown in FIG. 9 for processing possibly ordered indexes is shown in detail in FIG. 15, and an example of operations performed according to the procedure of FIG. 15 is shown in FIGS. It is shown.
In FIG. 16, eleven possible indexes are shown, which are represented by the unique identification numbers 1625, 1616, 1604, 1673, 1612, 1646, 1635, 1691, 1622, 1683 and 1617. The cost savings identified earlier for these indexes and the procedures defined represent the priority for inclusion in the specified set of optimal indexes. Accordingly, the appropriately qualified indexes shown in FIG. 16 are ordered based on the priority specified for the associated cost savings recorded for each index. These cost savings have no absolute meaning and give instructions related to the cost savings calculated according to the procedure described above. That is, index 1625 is identified as a cost saving of 73, index 1616 is a cost saving of 72, index 1604 is a cost saving of 68, and finally index 1617 is a cost saving of 4. Have been identified as performing. Thus, the information required to order the indexes 1625-1617 in terms of their cost saving eligibility is calculated according to the procedure detailed in step 803.
In step 1501 of FIG. 15, cost savings are considered and standardized to facilitate subsequent processing. Standardization allows cost savings to be considered within a predetermined range, and in this example is selected within the range of 0-9999. The overall cost savings are calculated as shown at 1681 in FIG. 16, which is 500 in this example. The entire range is divided by this overall cost savings 1681 to give a unit range value, which is then multiplied by the cost savings value, thereby giving a distribution value. The calculation of the distribution value is performed in step 1502, resulting in a standardized cost saving. Thus, according to the procedure performed in step 1502, the cost savings for index 1625 are normalized to a value of 1660, and index 1616 is normalized (from 72 cost savings) to a standardized value of 1440. Similarly, standardized values are calculated for all indexes under consideration, so that when summed, the standardized values are equal to the full range of values in the distribution, which in this example is 9999. It is. In step 1503, a question is asked as to whether another genetic iteration is required and the answer is affirmative in the first iteration. A pre-selection is made regarding the number of required genetic repeats and a counting operation is performed at step 1503.
If the answer to the question is affirmative, a random number in the range of 0 to 9999 is generated in step 1504. This random number is used in step 1505 to select a particular index. Accordingly, the selection of a random index is made at step 1505 and is weighted in terms of cost savings provided by a particular index. Thus, an index 1617 is selected by a number in the range 0 to 80, an index 1683 is selected by a number in the range 81 to 400, and an index 1622 is selected by a number in the range 401 to 820. Finally, the index 1625 is selected by a number in the range 8540-9999. The number of distributions assigned to a particular index is proportional to its associated cost savings, whereby an index with higher cost savings is selected over many iterations over an index with an average lower cost savings. However, low cost saving indexes still remain in the pool and can be selected according to genetic procedures.
A new combination of indexes is generated at each iteration, and the combination of these indexes gives a specific cost saving. This allows the index combination to give its own index identification, and the composite index is ordered based on the objective function so that it is included in the table shown in FIG.
Accordingly, a specific index is selected in step 1505, and the selected index in step 1506 is written into the index buffer. An index buffer 1791 is shown in FIG. 17, which contains six buffer locations that represent the maximum number of indexes allowed for the particular table under consideration. Referring to FIG. 17, each table is shown with a maximum of 6 indexes within a particular embodiment, but this form may be adjusted to meet a particular local operating condition. . Thus, buffer 1791 has six positions, and the identification of the selected index, such as index 1625, can be placed at any of these positions and selected on a random basis. Accordingly, as shown in FIG. 17, the identification of index 1625 is located at the second buffer position of index buffer 1791.
In step 1507, a second random number is generated in the range of 0 to 9999, so that the second parent index is selected in step 1508. The indication of the selected index is written into the second index buffer as shown at 1792 in FIG. In this example, the index 1604 is consequently selected by the random number generated in step 1507 and randomly positioned at the fourth position in the buffer 1792.
In step 1510, a buffer disconnect location is randomly selected at any interface between locations within a particular buffer. In this example, the cutting position is located between the second position and the third position, as indicated by arrow 1793 in FIG. By this cutting position, the buffer 1791 which is the first parent and the buffer 1792 which may be regarded as the second parent can be combined. The result of this exchange is shown in buffers 1794 and 1795. In buffer 1794, index 1625 is obtained from first parent 1791 and placed in the second position, and index identifier 1604 is obtained from parent 1792 and placed in the fourth position. As shown in buffer 1795, another descendant of this join does not contain an index identifier, so it is considered as a void child and is not considered further. Thus, in step 1511 of FIG. 15, two parents are “bred”, resulting in the creation of a child 1794 that includes indexes 1625 and 1604.
In order to make the availability of a potentially optimal index even more interesting, a stage of genetic manipulation is provided in which a child defined by the contents of buffer 1794 is mutated. Another random number is generated and consequently another index is selected. As a result of this mutation process in step 1512, buffer 1796 is loaded with the index indication obtained from child 1794, and index 1635 is randomly added at the sixth position. In step 1513, the child cost savings generated in step 1511 and the mutant cost savings generated in step 1512 are evaluated.
In step 1514, new indexes 1794 and 1796 are added to the pool of potential indexes in order of eligibility. Cost savings are calculated for each index with reference to the table under consideration so that the index can be added to the list of FIG. 16 ranked according to the resulting cost savings. The overall cost savings are recalculated and then the new standardized cost savings are recalculated for all indexes including the newly added index. From this standardized distribution of cost savings, a value is calculated for each index in step 1502 and the question is again asked whether there is a need for genetic repeats.
If the answer to the question made in step 1503 is affirmative again, a random number in the range of 0 to 9999 is generated, a new index is selected in step 1505, and then an indication of this index is added in step 1506. 1 is written to the parent buffer 1791. A second random number is also generated at step 1507, whereby a second parent is selected at step 1508, after which the parent's indication is written to buffer 1792 at step 1509.
In step 1510, the cutting position is again randomly selected, the parent is raised in step 1511, and their offspring are written to buffers 1794 and 1795, after which mutants are generated from valid children. Thus, each iteration adds up to four new indexes to the index join pool.
Finally, if the answer to the question made at step 1503 is negative, then control is directed to step 805 of FIG. While the process shown in FIG. 15 is repeated, new index calculations are identified in a substantially random manner. However, each new index is tested to determine and determine the resulting cost savings, and the index with the highest cost saving value is placed near the top of the list shown in FIG. In addition, having a relatively high cost savings value also increases the range of number distributions to select, thereby increasing the likelihood that these indexes will be selected for combining. However, it is possible that such an index could be selected because relatively unlikely indexes remain in the pool. When repeated sufficiently, index combinations with very high cost savings are identified, and these indexes are placed near the top of the list shown in FIG. Finally, when the process shown in FIG. 15 is completed, the indexes including the newly raised index are listed in the list for generation at the time of specification in order of decreasing cost.
Details of the method identified in step 910 of FIG. 9 for configuring the database to include the specified index are shown in FIG. Objective functions that specify associated cost savings are considered for indexes only with respect to their associated tables. However, in an operating database system, multiple tables must function together. Therefore, if a given storage space is available, the storage space must be allocated for indexes associated with all existing tables.
In step 1801, the total amount of disk space used by each table present in the live database is calculated, and in step 1802, the values calculated in step 1801 are summed to obtain the total required for all tables. Disk space is given. In step 1803, the disk space required for each table is divided by the total disk space to give a percentage of the base disk space allocation for each table. This percentage allocation allocates space to the index as shown in step 1804, so that the relative amount of storage allocated for index generation is approximately equal to the relative allocation of storage space to the table. Will be equal. Thus, if a table occupies a large amount of disk space, a large amount of disk space is similarly allocated to indexes operating over this table.
A table is selected at step 1805 and the most appropriate index obtained for that table is selected at step 1806. In step 1807, a question is asked as to whether sufficient disk space is allocated for the preferred index selected in step 1806 to be generated on the live system. If this answer is affirmative, the index selected in step 1806 is designated for generation in step 1808. Instead, if sufficient disk space is not available, the answer to the question made in step 1807 results in a negative result, step 1808 is bypassed, and control is directed to step 1809.
In step 1809, a question is asked as to whether another table exists, and if the answer is affirmative, control is returned to step 1805, resulting in the next table being selected and the appropriate index in this table. Considered in steps 1806 and 1807. Finally, when all the indexes for the selected table are considered, the answer queried in step 1809 results in a negative result.
If the answer to the question at step 808 is affirmative, the procedure detailed in FIG. 18 is performed. Thus, a new index structure is created in the live database, after which the database is placed online at step 810 with the new index placed.
Referring to FIG. 14, the process performed in step 1401 becomes very time consuming if the set of predicates results in the identification of an index that requires more than four columns. Under these circumstances, the first four preferred columns are selected and the remaining columns are provisionally excluded. Selection is made on the basis of filter coefficients, and four columns with low filter coefficients are selected. These four columns are processed with all possible ordering possibilities, thereby selecting a candidate index as described above.
After all appropriate indexes have been determined, the tentatively excluded large number of indexes are assembled by adding the fifth preferred column, the sixth preferred column, the seventh preferred column, etc., thereby creating a new A fifth column index, a new sixth column index, a new seventh column index, etc. are generated. These indexes are created for the most cost effective candidate index containing the four columns needed. These columns are only added to the four column candidate indexes in increasing order, and catalog statistics are calculated for these new indexes, after which these new indexes are costed and qualified. It is added to the ordered list and placed in the order of eligibility (relevance) of the index previously costed.
Referring to FIG. 15, the number of appropriate indexes considered for the genetic process initiated in step 1505 is important, resulting in a relatively long processing time. Under these circumstances, it is preferable to place an upper limit on the size of the “binding pool” before the genetic process is performed. In general, the size of the binding pool may be limited to a maximum of 30 suitable indexes before performing the genetic process.
Basically, the purpose of the genetic process is to find a composite index that significantly reduces processing overhead when constructing a set of general SQL queries. To reduce processing time, it is preferable to “prepare the pool” by adding a set of indexes that may be particularly advantageous.
The first stage of pool preparation involves examining existing live database systems and thereby determining which indexes are actually used in the live system. These sets of indexes are then added to the appropriate set of indexes as described above.
The second stage of pool preparation is to re-evaluate eligibility ranking after it is assumed that the most eligible index is in the live system. Thus, the cost factor is re-considered from the starting position where the most appropriate index as calculated above is placed as belonging to the live system. When this live index is added to the system, some cost savings of the remaining eligible indexes will change significantly, thereby effectively re-ordering the indexes within the list of eligibility. Again, the most cost effective remaining eligible index is added to the system and the cost savings are recalculated based on this new index present. This process is repeated and iteratively performed up to a set of 6 new indexes. Each of these sets of indexes is added to the combined pool at the appropriate number of times.

Claims (6)

In a computerized database system, a set of indexes for use in combination with a database table stored in a machine readable format is determined using its hardware resources based on computer software. In the method
The computerized database system is configured to be able to present to a user a data set identified from data contained in a data table in response to a data query;
Analyzing a plurality of data queries submitted to a computerized database system to identify terminology that can be indexed correspondingly;
A plurality of candidate indexes that are candidates for an index that can reduce the amount of processing required by the computerized database system in response to each data query can be indexed as identified in the analysis step. Identifying from the terminology;
A representative of the actual data query received by the computerized database system for a table of actual data stored in the computerized database system formed using the identified candidate index Evaluating a cost saving for each candidate index indicative of a reduction in throughput required by the computerized database system in response to the sample for each of the plurality of candidate indexes identified in the identifying step When,
Determining a preferred set of indexes to be used in a computerized database system based on the estimated cost savings for each of a plurality of candidate indexes ;
Cost savings use the candidate index and the amount of work done by the computerized database system in response to a representative sample of actual data queries received by the computerized database system without using the candidate index A throughput that represents the difference from the throughput performed by the computerized database system in response to a representative sample of actual data queries received by the computerized database system;
In the step of evaluating the cost savings, the processing performed by the computerized database system in response to a representative sample of actual data queries received by the computerized database system without using candidate indexes The base cost, which is the amount of processing, is calculated and the processing performed by the computerized database system in response to a representative sample of actual data queries received by the computerized database system using each candidate index the throughput is measured, that determine a set of indexes evaluation is performed by subtracting the measured throughput from the base cost method. In the cost savings assessment, a reduced data table is generated that includes only samples selected as representative from entries read from the actual data table that contain more entries than the maximum allowed. , the actual data table for the generation of the reduced data table, the processing performed by the representative sample pairs to computerized database system of the actual data queries received by computerized database system The method of claim 1, wherein the throughput is measured. The method of claim 2, wherein said database statistics about the actual data table is also used in the reduced data table. The method of claim 1, wherein the evaluation of cost savings includes an evaluation of overhead storing an index. In computerized database system, in equipment that determine the set of indexes used in combination with a database table stored in a machine readable form, the computerized database system, in response to the data query Configured to be able to present to the user a data set derived from the data contained in the data table,
An analysis means for analyzing a plurality of data queries submitted to a computerized database system to identify terms that can be indexed correspondingly;
A plurality of candidate indexes that are candidates for the index to help reduce the amount of processing required by the computerized database system in response to each data query can be indexed identified in the analysis step An identification means for identifying from a term,
Representative of actual data queries received by the computerized database system against actual data tables stored in the realized computerized database system formed using the identified candidate index Evaluation means for evaluating a cost saving for each candidate index indicative of a reduction in throughput required by the computerized database system in response to the sample for each of the plurality of candidate indexes identified in the identifying step When,
Determining means for determining a preferred set of indexes to be used in the computerized database system based on the estimated cost savings for each of the plurality of candidate indexes ;
The evaluation means for evaluating the cost saving is evaluated for each of the plurality of candidate indexes identified in the identifying step, and the cost saving is received by a computerized database system without using the candidate index. The amount of processing performed by a computerized database system in response to a representative sample of actual data queries and a representative of actual data queries received by a computerized database system using candidate indexes The amount of processing that represents the difference from the amount of processing performed by a computerized database system in response to a sample,
The evaluation means for evaluating the cost savings is a process performed by the computerized database system in response to a representative sample of actual data queries received by the computerized database system without using candidate indexes. Processing performed by the computerized database system in response to a representative sample of actual data queries received by the computerized database system using each candidate index the processing amount was measured, that determine a set of indices that is configured to perform evaluation by subtracting the measured throughput from the base cost device. 6. A data set derived from data contained in the actual data table comprising the apparatus of claim 5 and stored in a machine readable form in response to a data query is presented to a user. A computerized database system that is configured as follows.

Images