Sybase, Inc.

Peter J Schneider

Xiaobin Ma

Srinivasan Mottupalli

Xun Cheng

Hui Zhang

Santosh Pendap

Sudipto Rai Chowdhuri

Ghazi Nourdine Benadjaoud

Leon Xiong

Ming-li Rui

A system and method for processing a database query is described. In response to detection that a database query involves a star or snowflake join operation, a join operator in a preliminary query plan can be split into a build operator and a probe operator. The probe operator can be placed in a final query plan in the same place as the join operator in the preliminary query plan, while the build operator can be placed beneath the probe operator in the final query plan, between an exchange operator and the exchange operator's child from the preliminary query plan.

Splitting of a join operation to allow parallelization

Disclosed herein are system, method, and computer program product embodiments for optimizing a query plan reuse in a database server system accessible by a plurality of client connections. An embodiment comprises determining if a query plan in a global cache storage is reserved by a client connection of a plurality of client connections, generating a cloned query plan from the query plan based on the determining, and associating the cloned query plan with a second client connection of the plurality of client connections.

Query plan optimization for prepared SQL statements

A method can include receiving a request to execute a database command identifying a target table; identifying a plurality of rows to insert into the target table based in part on the database command; writing rows, from the plurality of rows, into a data page until the data page is full; determining, by an index thread manager, a number of threads to use for updating indexes defined for the target table; and upon determining the data page is full, updating, in parallel, the indexes defined for the target table using the number of threads.

Index updates using parallel and hybrid execution

Creation of an index for a table of sorted data for use by a data storage application is initiated. Thereafter, N+1 logical partition of rows of the table are defined so that each logical partition has a corresponding worker process. Each worker process then builds a sub-index based on the corresponding logical partition which are later merged to form the index. Related apparatus, systems, techniques and articles are also described.

High performance index creation on sorted data using parallel query plans

A system and method for processing a database query is described. The method can, in response to detection that a database query involves a star or snowflake join operation, determine a selectivity ratio for each of a plurality of dimension tables. The selectivity ratio having a lower value can correspond to a more restrictive dimension table. Thereafter, a table ordering can be created beginning with a fact table and continuing with each of the dimension tables in ascending order of their corresponding selectivity ratios. Then a query plan involving join operations between successive tables in the table ordering can be created.

Star and snowflake join query performance

A method can include initiating execution of a database command, the database command associated with a base table with at least one row to copy to a target table, the database command associated with a non-bulk insert mode; making a run-time decision on whether to automatically convert the insert mode from the non-bulk insert mode to a BULK insert mode based on the number of row buffers filled with rows from the base table during execution of the database command; and inserting at least one row into the target table using an insert mode based on the run-time decision.

Run-time decision of bulk insert for massive data loading

A method for inserting rows into a target table can include receiving a database command, the database command associated with a base table with at least one row to copy to a target table; receiving an indication that use of a BULK insert mode is feasible for the database command; based on the indication, and determining that an insert mode for the database command has been converted from a non-bulk insert mode to the BULK insert mode: reading a row from the base table; building the row read from the base table into an allocated row buffer; inserting the row into the target table in the BULK insert mode; and if it is determined that the allocated row buffer is full, updating at least one index in parallel with the inserting.

Convert command into a BULK load operation

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.

High performance index creation

System, methods and articles of manufacture for optimizing a query plan reuse in a database server system accessible by a plurality of client connections. An embodiment comprises providing at least one global cache storage and a private cache storage to a plurality of client connections, and coordinating utilization of the at least one global cache storage and the private cache storage to share light weight stored procedures and query plans for prepared SQL statements across the plurality of client connections via the at least one global cache storage while avoiding a spinlock access for executing the prepared SQL statements.

Embodiments include a method, apparatus and computer program product for providing instructions from a database client to a database server. The method includes the steps of receiving from a database a representation of a first instruction and an associated identifier. The representation of the first instruction and the associated identifier are stored in a cache. A second instruction, from an application, is matched with the representation of the first instruction stored in the cache. The associated identifier is retrieved from the cache based on the matching and the associated identifier is sent to the database server. Upon receipt of the associated identifier, the database server executes the first instruction.

Client-side statement cache

System, methods and articles of manufacture for joining data in the database tables comprising, performing an index scan on a global index of a first database table, determining rows in the first database table that may be joined with a second database table based on a needed partitioning, wherein the needed partitioning is determined using an index scan, determining a number of partitions in the second database table, and joining each of the corresponding partitions in the first database table with a corresponding partitions in the second database table.

Parallel partitioning index scan

Database system and methods are described for improving execution speed of database queries (e.g., for decision support) by optimizing execution of nested queries or 'subqueries,' such as are commonly used in client/server database environments. In particular, a subquery cache is provided having a size which can be dynamically adjusted by the system during execution of the query, for achieving an optimal cache size. During execution of the subquery, the system keeps statistics on cache 'hits' and 'misses.' Specifically, the system stores a count of the number of times a row is found in the cache (i.e., a 'hit') as well as a count of the number of times a scan of the cache yields no 'hit' (i.e., a 'miss'). Also, a maximum LRU (least-recently used) depth of the deepest hit in the cache is also tracked. This tracks how deep (i.e., the deepest row) into the cache there has been a 'hit.' Based on the tracking of these cache statistics, the system of the present invention can dynamically adjust subquery cache size to an optimal size at execution time based on actual data encountered during execution of the query.

Growing community of inventors

Dublin, CA, United States of America

Peter J Schneider