High performance index creation
High performance index creation using parallel query plans with repartitioning scan and vector-based repartitioning scan is described. An example method includes extracting index key columns from data rows of the database table to build a set of index rows, wherein the index on the database table is defined by a plurality of index key columns including a first index key column and a second index key column. Partition boundary values are generated to divide up the index rows into range-partitioned sets, and the index rows are sorted based on values of the index key columns. A repartitioning scan, including a SARG-based or a vector-based partitioning scan is performed on the index rows, using a plurality of worker threads executing in parallel to build sub-indexes. Subsequently, each range-partitioned set of index rows are assigned to a worker thread in the plurality of worker threads. Accordingly, the sub-indexes generated from the plurality of work threads are merged to build the index for the database table.
1. A computer-implemented method for creating an index on a database table, comprising:
building a set of index rows by extracting an index key column from data rows of the database table;
sorting the set of index rows based on the values of the index key column;
generating partition boundary values to divide up the index rows into range-partitioned sets;
performing a search argument-based repartitioning scan on the index rows of the database table upon determination that there are no duplicated values in the partition boundary values of the index key column;
assigning each range-partitioned set of index rows to one of a plurality worker threads in the plurality of worker threads;
executing at least some of the worker threads in parallel to build a plurality of sub-indexes based on the range-partitioned sets; and
merging the plurality sub-indexes from the plurality of work threads to generate the index for the database table.
2. The method of claim 1 , further comprising performing a vector based repartitioning scan performed upon determination that there are duplicated values in the partition boundary values of the index key column.
3. The method of claim 2 , wherein the vector-based repartitioning scan is associated with a repartition of the index rows based on a columns vector built upon partition boundary values of at least the index key column.
4. The method of claim 1 , wherein the search argument-based repartitioning scan is associated with a repartition of the index rows based on partition boundary values of the first index key column only.
5. The method of claim 1 , further comprising sampling data rows in the database table to determine if the database table is qualified for parallel processing.
6. The method of claim 1 , further comprising performing a vector based repartitioning scan performed upon determination that duplicated values in the partition boundary values of the index key column exceed a predefined threshold.
7. The method of claim 1 , further comprising sampling data rows in the database table to generate a distribution map including partition boundary rows, which are defined by the partition boundary values of the index key column.
8. The method of claim 7 , further comprising performing a vector-based repartitioning scan that includes a vector comparison of an index row to be processed with the partition boundary rows.
9. A system for creating an index on a database table, comprising:
a column extractor, implemented by one or more computing devices, configured to:
extract an index key column from data rows of the database table, and
sort the set of index rows based on the values of the index key column;
a value generator, implemented by one or more computing devices, configured to:
generate partition boundary values to divide up the index rows into range-partitioned sets;
a repartitioning scanner, implemented by one or more computing devices, configured to:
perform a search argument-based repartitioning scan on the index rows of the database table upon determination that there are no duplicated values in the partition boundary values of the index key column,
assign each range-partitioned set of index rows to one of a plurality of worker threads in the plurality of worker threads, and
execute at least some of the worker threads in parallel to build a plurality of sub-indexes based on the range-partitioned sets; and
an index merger, implemented by one or more computing devices, configured to merge the plurality of sub-indexes from the plurality of work threads to generate the index for the database table.
10. The system of claim 9 , wherein the repartitioning scanner is further configured to perform a vector-based repartitioning scan upon determination that there are duplicated values in the partition boundary values of the index key column.
11. The system of claim 10 , wherein the vector-based repartitioning scan is associated with a repartition of the index rows based on a columns vector built upon partition boundary values of at least the index key column.
12. The system of claim 9 , wherein the search argument-based repartitioning scan is associated with a repartition of the index rows based on partition boundary values of the first index key column only.
13. The system of claim 9 , further comprising: a data sampler, implemented by one or more computing devices, configured to sample data rows in the database table to determine if the database table is qualified for parallel processing.
14. The method of claim 9 , wherein the repartitioning scanner is further configured to perform a vector-based repartitioning scan upon determination that duplicated values in the partition boundary values of the index key column exceed a predefined threshold.
15. The method of claim 9 , further comprising sampling data rows in the database table to generate a distribution map including partition boundary rows, which are defined by the partition boundary values of the index key column.
16. The method of claim 15 , wherein the vector-based repartitioning scan includes a vector comparison of an index row to be processed with the partition boundary rows.
17. A computer program product comprising a computer readable storage medium having instructions encoded thereon that, when executed by a processor, cause the processor to perform operations comprising:
building a set of index rows by extracting an index key column from data rows of the database table;
sorting the set of index rows based on the values of the index key column;
generating partition boundary values to divide up the index rows into range-partitioned sets;
performing a search argument-based repartitioning scan on the index rows of the database table upon determination that there are no duplicated values in the partition boundary values of the index key column;
assigning each range-partitioned set of index rows are assigned to one of a plurality worker threads in the plurality of worker threads;
executing at least some of the worker threads in parallel to build a plurality of sub-indexes based on the range-partitioned sets; and
merging the plurality of sub-indexes from the plurality of work threads to generate the index for the database table.
18. A computer-implemented method for creating an index on a database table, comprising:
building a set of index rows by extracting an index key column from data rows of the database table;
generating partition boundary values to divide up the index rows into range-partitioned sets;
sorting the index rows based on values in the index key column;
determining that there are no duplicated values in the partition boundary values of the index key column;
performing a search argument based repartitioning scan on the index rows of the database table using a plurality of worker threads executing in parallel to build sub-indexes wherein each range-partitioned set of index rows are assigned to a worker thread in the plurality of worker threads, wherein the a search argument-based repartitioning scan is performed upon determination that there are no duplicated values in the partition boundary values of the first index key column; and
merging the sub-indexes from the plurality of work threads to generate the index for the database table.