Method and apparatus for determining distinct cardinality dual hash bitmaps

• US 5,802,521 A
• Filed: 10/07/1996
• Issued: 09/01/1998
• Est. Priority Date: 10/07/1996
• Status: Expired due to Term

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle Corporation
• Inventors: Ong, Michael T., Ziauddin, Mohamed
• Primary Examiner(s): Black, Thomas G.
• Assistant Examiner(s): JUNG, DAVID YIUK
• Application Number:
  US08/726,957
• Time in Patent Office: 694 Days
• Field of Search: 707/1-2, 707/6-10, 707/100-104, 707/200-206, 707/500
• US Class Current: 1/1
• CPC Class Codes: G06F 16/9014 hash tables
• CPC Class Codes: Y10S 707/99931 Database or file accessing
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99937 Sorting
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface

Abstract

A method and apparatus for determining distinct cardinality of a data sample using dual hash bitmaps. Two different bitmaps determine the distinct cardinality of the data sample (e.g., a column of data within a data table of a database). A small sized bitmap, M*sqrt(C*K), is used where M is a programmable value that reduces collision error, C is the size of the column ("data sample size"), and K is a key density value. Once selected, both M and K are constant. Sample entry values are hashed by a hash function and a modulo function determines an entry into the first bitmap. Based on the bitmap'"'"'s bit entries, a first counter is updated, or not, to maintain a first distinct cardinality value. A large bitmap is used having a size, M*C, but only a small fraction is actually used, M*sqrt(C*K). Only hashed column entries falling inside the fraction are processed as above to maintain a second counter. At the end of the data sample entry processing, the second counter is extrapolated to the large bitmap size. Expected collision error compensation is computed and compensated for the first and second counters. Distribution error is computed for the second counter and added with its compensated collision error. The total errors of the first and second counters are compared to select an output distinct cardinality. The distinct cardinality measurement requires sub-linear increase in memory for each linear increase in sample size.

Claims

1. In a computer system having a processor coupled to a bus and a computer readable memory unit coupled to said bus, a method for determining distinct cardinality of a data sample, said method comprising the steps of:

(a) receiving said data sample, said data sample containing C entries;

(b) determining a first distinct cardinality value of said data sample using a hashing function and a first bitmap, said first bitmap comprising x entries;

(c) determining a second distinct cardinality value of said data sample using a hashing function and a fractional bitmap that is a fraction of a second bitmap, said second bitmap comprising y logical entries and said fractional bitmap comprising z entries wherein y is larger than x; and

(d) selecting between said first distinct cardinality value and said second distinct cardinality value as said distinct cardinality of said data sample, wherein said first bitmap and said fractional bitmap are separate bitmaps and are stored within said computer readable memory unit.

2. A method as described in claim 1 wherein x is M* sqrt(C*K)!, z is M* sqrt(C*K)!, M is a multiplier constant value, and K is a key density constant value and wherein y is M*C.

3. A method as described in claim 1 further comprising the steps of:

determining a first probability error factor of said first distinct cardinality value; and

determining a second probability error factor of said second distinct cardinality value; and

wherein said step of selecting between said first distinct cardinality value and said second distinct cardinality value comprises the steps of;

d1) reporting said first distinct cardinality value as said distinct cardinality of said data sample provided said first probability error factor is less than said second probability error factor; and

d2) reporting said second distinct cardinality value as said distinct cardinality of said data sample provided said first probability error factor is not less than said second probability error factor.

4. A method as described in claim 3 wherein

said step of determining a first probability error factor of said first distinct cardinality value comprises the step of determining said first probability error factor based on a collision error of said first distinct cardinality value and wherein

said step of determining a second probability error factor of said second distinct cardinality value comprises the step of determining said second probability error factor based on a collision error and a distribution error of said second distinct cardinality value.

5. A method as described in claim 1 wherein said step of determining a first distinct cardinality value of said data sample comprises the steps of:

(b1) for each entry of said C entries performing the steps of;

determining a first hash value from said hash function of said step of determining a first distinct cardinality value of said data sample;

determining a first modulo value based on the size of said first bitmap and said first hash value;

indexing said first bitmap based on said first modulo value to access a first bit value; and

provided said first bit value is zero, setting said first bit value to 1 and incrementing said first distinct cardinality value; and

(b2) compensating said first cardinality value for collision error.

6. A method as described in claim 1 wherein said step of determining a second distinct cardinality value of said data sample comprises the steps of:

(c1) for each entry of said C entries performing the steps of;

determining a second hash value from said hash function of said step of determining a second distinct cardinality value of said data sample;

determining a second modulo value based on the size of said second bitmap and said second hash value;

provided said second modulo value is within said fractional bitmap, indexing said fractional bitmap based on said second modulo value to access a second bit value; and

provided said second bit value is zero, and provided further said second modulo value is within said fractional bitmap, setting said second bit value to 1 and incrementing said second distinct cardinality value;

(c2) extrapolating said second distinct cardinality value over said second bitmap from said fractional bitmap; and

(c3) compensating said second cardinality value for collision error.

7. A method as described in claim 5 wherein said step of determining a second distinct cardinality value of said data sample comprises the steps of:

(c1) for each entry of said C entries performing the steps of;

determining a second hash value from said hash function of said step of determining a second distinct cardinality value of said data sample;

determining a second modulo value based on the size of said second bitmap and said second hash value;

provided said second modulo value is within said fractional bitmap, indexing said fractional bitmap based on said second modulo value to access a second bit value; and

provided said second bit value is zero, and provided further said second modulo value is within said fractional bitmap, setting said second bit value to 1 and incrementing said second distinct cardinality value;

(c2) extrapolating said second distinct cardinality value over said second bitmap from said fractional bitmap; and

(c3) compensating said second cardinality value for collision error.

8. A method as described in claim 1 wherein said step of determining a first distinct cardinality value of said data sample and said step of determining a second distinct cardinality value of said data sample are performed simultaneously.

9. In a computer system having a processor coupled to a bus and a computer readable memory unit coupled to said bus, a method for determining distinct cardinality of a data sample, said method comprising the steps of:

(a) receiving said data sample, said data sample containing C entries;

(b) determining a first distinct cardinality value of said data sample using a hashing function and a first bitmap, said first bitmap comprising M* sqrt(C*K) ! entries where M is a multiplier constant value and K is a key density constant value;

(c) determining a second distinct cardinality value of said data sample using a hashing function and a fractional bitmap that is a fraction of a second bitmap, said second bitmap comprising M*C logical entries and said fractional bitmap containing M* sqrt(C*K)! entries; and

(d) selecting between said first distinct cardinality value and said second distinct cardinality value as said distinct cardinality of said data sample, wherein said first bitmap and said fractional bitmap are separate bitmaps stored within said computer readable memory unit.

10. A method as described in claim 9 further comprising the steps of:

determining a first error value based on a collision error of said first distinct cardinality value; and

determining a second error value based on a collision error and a distribution error of said second distinct cardinality value; and

wherein said step of selecting between said first distinct cardinality value and said second distinct cardinality value comprises the steps of;

d1) reporting said first distinct cardinality value as said distinct cardinality of said data sample provided said first error value is less than said second error value; and

d2) reporting said second distinct cardinality value as said distinct cardinality of said data sample provided said first error value is not less than said second error value.

11. A method as described in claim 9 wherein said step of determining a first distinct cardinality value of said data sample comprises the steps of:

(b1) for each entry of said C entries performing the steps of;

determining a first hash value from said hash function of said step of determining a first distinct cardinality value of said data sample;

determining a first modulo value based on the size of said first bitmap and said first hash value;

indexing said first bitmap based on said first modulo value to access a first bit value; and

provided said first bit value is zero, setting said first bit value to 1 and incrementing said first distinct cardinality value; and

(b2) compensating said first cardinality value for collision error.

12. A method as described in claim 11 wherein said step of compensating said first cardinality value for collision error is dependent upon said value M.

13. A method as described in claim 10 wherein said step of determining a second distinct cardinality value of said data sample comprises the steps of:

(c1) for each entry of said C entries performing the steps of;

determining a second hash value from said hash function of said step of determining a second distinct cardinality value of said data sample;

determining a second modulo value based on the size of said second bitmap and said second hash value;

provided said second modulo value is within said fractional bitmap, indexing said fractional bitmap based on said second modulo value to access a second bit value; and

provided said second bit value is zero, and provided further said second modulo value is within said fractional bitmap, setting said second bit value to 1 and incrementing said second distinct cardinality value;

(c2) extrapolating said second distinct cardinality value over said second bitmap from said fractional bitmap; and

(c3) compensating said second cardinality value for collision error.

14. A method as described in claim 13 wherein said step of extrapolating said second distinct cardinality value over said second bitmap from said fractional bitmap is dependent upon said value M and wherein said step of compensating said second cardinality value for collision error is dependent upon said value K.

15. A method as described in claim 11 wherein said step of determining a second distinct cardinality value of said data sample comprises the steps of:

(c1) for each entry of said C entries performing the steps of;

determining a second hash value from said hash function of said step of determining a second distinct cardinality value of said data sample;

determining a second modulo value based on the size of said second bitmap and said second hash value;

provided said second modulo value is within said fractional bitmap, indexing said fractional bitmap based on said second modulo value to access a second bit value; and

provided said second bit value is zero, and provided further said second modulo value is within said fractional bitmap, setting said second bit value to 1 and incrementing said second distinct cardinality value;

(c2) extrapolating said second distinct cardinality value over said second bitmap from said fractional bitmap; and

(c3) compensating said second cardinality value for collision error.

16. A method as described in claim 9 wherein said step of determining a first distinct cardinality value of said data sample and said step of determining a second distinct cardinality value of said data sample are performed simultaneously.

17. A computer system comprising a processor coupled to a bus and a computer readable memory unit coupled to said bus, said computer readable memory unit containing a set of instructions that when executed by said processor causing said computer system to implement a method for determining distinct cardinality of a data sample, said method comprising the steps of:

(a) receiving said data sample, said data sample containing C entries;

(b) determining a first distinct cardinality value of said data sample using a hash function and a first bitmap, said first bitmap comprising x entries;

(c) determining a second distinct cardinality value of said data sample using a hash function and a fractional bitmap that is a fraction of a second bitmap, said second bitmap comprising y logical entries and said fractional bitmap comprising x entries wherein y is larger than x; and

(d) selecting between said first distinct cardinality value and said second distinct cardinality value as said distinct cardinality of said data sample, wherein said first bitmap and said fractional bitmap are separate bitmaps and are stored within said computer readable memory unit using different memory locations.

18. A computer system as described in claim 17 wherein x is M* sqrt(C*K)!, where M is a multiplier constant value and K is a key density constant value and wherein y is M*C.

19. A computer system as described in claim 18 wherein said method further comprises the steps of:

determining a first error value based on a collision error of said first distinct cardinality value; and

determining a second error value based on a collision error and a distribution error of said second distinct cardinality value; and

wherein said step of selecting between said first distinct cardinality value and said second distinct cardinality value comprises the steps of;

d1) reporting said first distinct cardinality value as said distinct cardinality of said data sample provided said first error value is less than said second error value; and

d2) reporting said second distinct cardinality value as said distinct cardinality of said data sample provided said first error value is not less than said second error value.

20. A computer system as described in claim 18 wherein said step of determining a first distinct cardinality value of said data sample comprises the steps of:

(b1) for each entry of said C entries performing the steps of;

determining a first hash value from said hash function of said step of determining a first distinct cardinality value of said data sample;

determining a first modulo value based on the size of said first bitmap and said first hash value;

indexing said first bitmap based on said first modulo value to access a first bit value; and

provided said first bit value is zero, setting said first bit value to 1 and incrementing said first distinct cardinality value; and

(b2) compensating said first cardinality value for collision error.

21. A computer system as described in claim 18 wherein said step of determining a second distinct cardinality value of said data sample comprises the steps of:

(c1) for each entry of said C entries performing the steps of;

determining a second hash value from said hash function of said step of determining a second distinct cardinality value of said data sample;

determining a second modulo value based on the size of said second bitmap and said second hash value;

provided said second modulo value is within said fractional bitmap, indexing said fractional bitmap based on said second modulo value to access a second bit value; and

provided said second bit value is zero, and provided further said second modulo value is within said fractional bitmap, setting said second bit value to 1 and incrementing said second distinct cardinality value;

(c2) extrapolating said second distinct cardinality value over said second bitmap from said fractional bitmap; and

(c3) compensating said second cardinality value for collision error.

22. A computer system as described in claim 20 wherein said step of determining a second distinct cardinality value of said data sample comprises the steps of:

(c1) for each entry of said C entries performing the steps of;

determining a second hash value from said hash function of said step of determining a second distinct cardinality value of said data sample;

determining a second modulo value based on the size of said second bitmap and said second hash value;

provided said second modulo value is within said fractional bitmap, indexing said fractional bitmap based on said second modulo value to access a second bit value; and

provided said second bit value is zero, and provided further said second modulo value is within said fractional bitmap, setting said second bit value to 1 and incrementing said second distinct cardinality value;

(c2) extrapolating said second distinct cardinality value over said second bitmap from said fractional bitmap; and

(c3) compensating said second cardinality value for collision error.

23. A computer system as described in claim 18 wherein said step of determining a first distinct cardinality value of said data sample and said step of determining a second distinct cardinality value of said data sample are performed simultaneously.