Execution cost reduction of sampled queries in a database
AssigneeInternational Business Machines Corporation
InventorPaul Calisto Zuzarte
Life of this patent
Estimating the term…
Abstract
A method, system and computer program product for reducing the execution cost of sampled queries of tables in a database. A subset of entries comprising row identifiers or key values based on an input query of data in the tables of the database is generated. A sampling predicate to reduce the number of entries in the subset is generated. The sampling predicate is applied to the subset to reduce the number of entries in the subset. The data in the database corresponding to the reduced subset is retrieved from the database.
Claims33
A method of establishing a sampled query of tables in a database, the method comprising:
- a) receiving a query referencing a fact table and at least one other joined table in the database stored in a computer-readable medium, a sampling specification for the query, and an index on fact table keys of the fact table;
- b) generating, using the index, a subset of records of the fact table keys after receiving the query, wherein the index includes keys of the at least one other joined table;
- c) generating, based on the received sampling specification for the query, a sampling predicate to sample the generated subset of the fact table keys;
- d) sampling, based on the generated sampling predicate, the generated subset to obtain a sampled subset;
- e) retrieving selected records from the at least one other joined table, which records correspond to records of the sampled subset of the fact table keys;
- and
- f) executing, using a processor, the query based on the sampled subset and the selected records from the at least one other joined table.
A computer readable storage medium storing a program to execute functions of a method for establishing a sampled query of tables in a database, the functions comprising:
- a) receiving a query referencing a fact table and at least one other joined table in the database, a sampling specification for the query, and an index on fact table keys of the fact table;
- b) generating, using the index, a subset of records of the fact table keys after receiving the query, wherein the index includes keys of the at least one other joined table;
- c) generating, based on the received sampling specification for the query, a sampling predicate to sample the generated subset of the fact table keys;
- d) sampling, based on the generated sampling predicate, the generated subset to obtain a sampled subset;
- e) retrieving selected records from the at least one other joined table, which records correspond to records of the sampled subset of the fact table keys;
- and
- f) executing the query based on the sampled subset and the selected records from the at least one other joined table.
A data processing system for performing a sampled query of tables in a database, the system comprising:
- a processor which receives a query referencing a fact table and at least one other joined table in the database, a sampling specification for the query, and an index on fact table keys of the fact table;
- a subset generator which generates, using the index, a subset of records of the fact table keys after receiving the query, wherein the index includes keys of the at least one other joined table;
- a predicate generator which generates, based on the received sampling specification for the query, a sampling predicate to sample the generated subset of the fact table keys;
- a sampling predicate applier which samples, based on the generated sampling predicate, the generated subset to obtain a sampled subset, wherein the processor retrieves selected records from the at least one other joined table, which records correspond to records of the sampled subset of the fact table keys;
- and
- a query processor which executes the query based on the sampled subset and the selected records from the at least one other joined table.
Dependencies are inferred from the claim text.