Shulou(Shulou.com)05/31 Report--

This article mainly introduces the example analysis of hash join bitmap construction, which has certain reference value. Interested friends can refer to it. I hope you will gain a lot after reading this article. Let Xiaobian take you to understand it together.

Logic of hash join

--Scan the small table in the whole table, and take out the values of the associated fields, and build a hash table in PGA.

--Traverse the large table, execute the same hash function according to the scan result according to the associated field, get the hash value, and find the matching record in the hash table of pga.

The smaller row source is used to build the hash table and bitmap, and the second row source is used to be hansed and matched to the hash table generated by the first row source for further concatenation. Bitmap is used as a faster lookup method to check whether there are matching rows in the hash table. This lookup method is especially useful when the hash table is too large to fit in memory. This connection method also has the concept of so-called driver table in NL connection. The table constructed as hash table and bitmap is the driver table. When the hash table and bitmap constructed can be accommodated in memory, this connection method is extremely efficient.

Understanding bitmap index

Bitmap index: The index has as many bits as there are rows in the table. For example, the id field of the table establishes bitmap index (only 5 (the first row), 10 (the second row) and 15 (all the remaining rows)). If the table has 100 rows, the index has 100 bits. If the first row of the table is 5, the first row corresponds to the first index value. After the first index value in the index block is 5, it corresponds to a bitmap identifier such as 100000000 (99 zeros) instead of a rowid of the traditional B-tree index property. The second row of the table is 10, so the second row corresponds to the second index value. After the second index value in the index block is 10, it corresponds to 010000000 (98 zeros). All rows after the third row correspond to the third index value 00111111 (98 ones)

The index blocks are as follows

5 1000000000000

10 0100000000000

15 0011111111111

From the above 1, it can be seen that only 1 row of 5 is the first row, only 1 row of 10 is the second row, and 15 corresponds to the next 98 rows. If every four rows is 5 as follows, it means that 5 has a total of 20 values. It is found that there is only one 1 in each column, which is actually only one reason for each row.

5 1000010000100

10 0100000000000

15 0011101111011

The following are some personal understandings of hash bitmaps

Bitmaps are similar to a[1][0], a[1][1], a[1][2],... a[1][9]、.. a[10][0]

1、2、3、4、..、100

1、2、3、4、..、1000

1、2、3、4、..、10000

How to quickly find 28

a[10][10]

a[100][10]

a[1000][10]

mod(10,10)

a[1][0]=1

a[1][1]=11

..

a[1][9]=91

a[2][0]=2

a[2][1]=12

..

a[2][9]=92

...

a[8][?]

[8][2]->28

The above a[10][0]=10, a[10][1]=20 (because ten bits are 1, that is 10. Add the 10 bits before, that is 20). a[10][9]=100

1、11、21、31、... 91

2、12、22、32、... 92

3、13、23、33、... 93

...

8、18、28、38、... 98

..

10、20、30、40、... 100

mod(100,10)

a[1][0]=1

a[1][1]=11

..

a[1][9]=91

a[2][0]=2

a[2][1]=12

..

a[2][9]=92

...

a[28][?]

[28][0]->28

mod(1000,10)

a[1][0]=1

a[1][1]=11

..

a[1][9]=91

a[2][0]=2

a[2][1]=12

..

a[2][9]=92

...

a[28][?]

[28][0]->28

Thank you for reading this article carefully. I hope that Xiaobian's shared "example analysis of hash join bitmap construction" will help everyone. At the same time, I hope everyone will support you a lot and pay attention to the industry information channel. More relevant knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.