Browse Prior Art Database

Asynchronized real-time cardinality collection

IP.com Disclosure Number: IPCOM000240164D
Publication Date: 2015-Jan-08
Document File: 8 page(s) / 181K

Publishing Venue

The IP.com Prior Art Database

Abstract

In our invention, we have raised an idea to collect the cardinality of a table real-time and asynchronously. It is able to provide the optimizer the real time data of the cardinality with little overhead on the system, meanwhile, the optimizer will generate the best optimized access plan according to cardinality. In our invention, when the first application access one table (SELECT, UPDATE, INSERT OR DELETE), the RDBMS will compute the cardinality of the table at once and write the number into one variable in a memory and a queue will be created attached on the table. When other applications access the table and commit the transaction which lead to record changed in the transaction (insert or delete operation), the changed number will be pushed into the queue . Along with other applications accessing, the queue of the table may become longer. Then the system will schedule a task (thread) for the table to compute (aggregate) all the elements in the queue and summarize the value with the initial variable and update the variable with new value and clean the queue.

This text was extracted from a PDF file.
This is the abbreviated version, containing approximately 41% of the total text.

Page 01 of 8

Asynchronized real

Asynchronized real-

Statistical information collecting for a table or index is an important feature in RDBMS, which is used for optimizer to generate the optimized access plan. In the statistical information, the cardinality of a table is the most essential element of them. Currently, most of the RDBMS collect statistical information by manual which is synchronously and time-consuming or by a scheduled task running in the background periodically; neither could get the real time statistical information which is too outdated for optimizer to get a accurate evaluation.

In our invention, we have raised an idea to collect the cardinality of a table real-time and asynchronously. It is able to provide the optimizer the real time data of the cardinality with little overhead on the system, meanwhile, the optimizer will generate the best optimized access plan according to cardinality. In our invention, when the first application access one table (SELECT, UPDATE, INSERT OR DELETE), the RDBMS will compute the cardinality of the table at once and write the number into one variable in a memory and a queue will be created attached on the table. When other applications access the table and commit the transaction which lead to record changed in the transaction (insert or delete operation), the changed number will be pushed into the queue (e.g +10 for inserting 10 rows, or -30 for deleting 30 rows). Along with other applications accessing, the queue of the table may become longer. Then the system will schedule a task (thread) for the table (e.g every 10ms to trigger such a thread) to compute (aggregate) all the elements in the queue and summarize the value with the initial variable and update the variable with new value and clean the queue. The new value would be the latest cardinality of the table. Meanwhile any requests to calculate the row count of the table will trigger the compute thread and return the new value immediately avoiding table or index scan.(the select count(*) operation will be completed instantaneously´╝ë.

As described in Figure 1, when the first application access the table (TABLE1 ), the RDBMS will calculate the cardinality of the table at once and record the number into one variable(TABLECOUNT) in memory. When the table is kicked out of memory, and the variable(TABLECOUNT) will be released then.

1

--time cardinality collection

time cardinality collection



Page 02 of 8

Figure

Figure

111 Record table count in a varible

Record table count in a varible

After the variable TABLECOUNT is recorded, if many transactions want to update the same variable at the same time, there will be lockwait happened. So we can not just put one variable for the table here.(Shown in Figure 2)

2



Page 03 of 8

Figure

Figure

222 What will happen when many application update the variable

What will happen when many application update the variable?

As described in Figure 3,in our invention RDBMS will assign a queue for each variable(each ta...