US 12,282,478 B2
Iterative multi-attribute index selection for large database systems
Rainer Schlosser, Berlin (DE); Jan Kossmann, Berlin (DE); Martin Boissier, Berlin (DE); Matthias Uflacker, Berlin (DE); and Hasso Plattner, Schriesheim (DE)
Assigned to Hasso-Plattner-Institut für Digital Engineering gGmbH, Potsdam (DE)
Filed by Hasso-Plattner-Institut für Digital Engineering gGmbH, Potsdam (DE)
Filed on Apr. 2, 2020, as Appl. No. 16/838,830.
Claims priority of application No. 19167157 (EP), filed on Apr. 3, 2019.
Prior Publication US 2020/0320070 A1, Oct. 8, 2020
Int. Cl. G06F 16/00 (2019.01); G06F 9/50 (2006.01); G06F 11/30 (2006.01); G06F 16/21 (2019.01); G06F 16/22 (2019.01); G06F 16/242 (2019.01); G06F 16/2453 (2019.01); G06F 16/2455 (2019.01); G06F 16/28 (2019.01)
CPC G06F 16/2453 (2019.01) [G06F 9/5016 (2013.01); G06F 9/5027 (2013.01); G06F 11/3034 (2013.01); G06F 16/211 (2019.01); G06F 16/22 (2019.01); G06F 16/2282 (2019.01); G06F 16/2425 (2019.01); G06F 16/24542 (2019.01); G06F 16/24549 (2019.01); G06F 16/2455 (2019.01); G06F 16/24565 (2019.01); G06F 16/284 (2019.01)] 15 Claims
OG exemplary drawing
 
1. A method implemented in a computer system with a physical processing unit and a physical storage device, the method for executing queries by the physical processing unit on a database using an index set I, the computer system storing the database with N attributes in the physical storage device, wherein the index set I is a set of multi-attribute index candidates having an ordered set of attributes k={i1, . . . , iK} where iu∈{1, . . . , N}, u=1, . . . , K, the method comprising the following steps:
(1) setting, by the physical processing unit of the computer, the working index set I=Ø;
(2) determining, by the physical processing unit, a single-attribute index {i} (i=1, . . . , N) that minimizes the ratio between associated costs reduction of the processing performed by the physical processing unit and associated additional memory consumption of the physical storage device;
(3) adding, by the physical processing unit, the determined single-attribute index from step (2) to the working index set I;
(4) for each elementary index element {i} (i=1, . . . , N) and for each index k∈I,
when I∩{i}=Ø,
(a) creating, by the physical processing unit, a new index set Ĩ by adding {i} as new single-attribute index to the working index set I; and
when I∩{i}≠Ø,
(b) creating, by the physical processing unit, a new index set Ĩ by appending {i} at the end of index k of the working index set I;
(5) from the new index sets Ĩ created in step (4), selecting, by the physical processing unit, the new index set Ĩ* that has a best ratio between associated costs reduction of the processing performed by the physical processing unit and associated additional memory consumption of the physical storage device;
(6) setting, by the physical processing unit, the working index set I=Ĩ*; and
(7) executing, by the physical processing unit, a query using the working index set determined in step (6),
wherein in the ratio according to step (2), the associated costs are based on workload costs and reconfiguration costs of changing a current index set to a working index set stored by the physical storage device, and the associated memory consumption is based on an occupied memory of the single-attribute index {i}, and
wherein in the ratio according to step (5), the associated costs are based on workload costs and reconfiguration costs of changing a current index set to a working index set stored by the physical storage device, and the associated memory consumption is based on a difference between an occupied memory of the new index sets Ĩ and an occupied memory of the working index set I.