Abstract: The present invention provides the ability to alternatively update or insert a row into a table. This functionality is accomplished by merging a source table with a destination table such that the rows in the combined table are classified as an update row or an insert row. An identifier is inserted into each row to indicate its class. Examination of the identifier allows the row to be correctly inserted into the destination table or correctly updated in the destination table.

Abstract: Techniques are provided for evenly distributing data items of a particular set of data to a plurality of buckets. The buckets of data items may then be assigned to processes to perform operations on the data items in parallel with the other processes. In one embodiment, the set of data (which may come from tables or be the result set of a previous operation) is divided into a plurality of subsets. For each subset of the plurality of subsets, a sample of data items is randomly selected. The sampling itself may be performed in parallel, with each sampling process using a different seed to randomize its selection of samples. The sampled data items are sorted and ranges are determined based on distribution keys of the sampled data items. The ranges are assigned to buckets, and the data items are then distributed to the buckets assigned to the range into which their distribution key falls.

Abstract: Techniques are provided for executing query statements, or portions thereof, in parallel without using slave SQL to communicate to each slave the operations to be performed by the slave. Instead of generating one cursor for the query coordinator (QC) based on the original statement, and separate cursors for each DFO based on the slave SQL, the techniques involve sharing the cursor that is generated from the original statement among the various participants involved in the parallel execution of the operations specified in the original statement.

Abstract: A query coordinator handles a multiple-server dynamic performance query by sending remote query slaves (1) first information for generating a complete plan for the query, and (2) second information for participating in the dynamic performance view portion of the query. If the slaves on the remote servers are unable to use the first information to generate an equivalent query (for example, if they reside in a database server that has closed the database), then the slaves on the remote servers use the second information to participate in the dynamic performance view portion of the query.

Abstract: A cursor that is generated from an original database statement includes a master plan that is shared among the various participants involved in the parallel execution of the operations specified in the original statement. For participants that cannot share the same cursor, such as slave processes on remote servers, a remote cursor is generated. Signatures are generated for both the master plan and the remote plans, and compared to ensure that the remote plans are equivalent to the master plans. Various parameters and statistics are provided to remote servers to increase the likelihood that the remote plans will be equivalent to the master plan.

Abstract: Techniques are provided for processing, within a database server, a database statement, where at least one subtask required by the database statement is to be executed in parallel by a slave set. The database server determines whether the database statement includes an external expression, relative to the subtask, that (a) produces information that is required by slave processes in the slave set in order to perform the subtask, and (b) is not to be evaluated by each slave process in the slave set. During execution of the statement, if the database statement includes an external expression, then one or more external values are generated by causing the external expression to be evaluated, and the one or more external values are communicated to slaves of the slave set to allow the slave processes to perform respective portions of the subtask in parallel, without requiring each slave of the slave set to evaluate the external expression.

Abstract: Techniques are provided for generating execution plans for, and executing, database statements that contain filter subqueries. Upon receiving a database statement that contains a filter subquery, the database server builds an execution plan that includes a filter operation that is fed by a subtree that corresponds to the subquery. The database server performs a cost analysis to determine whether (1) the filter operation should be parallelized, and (2) the subquery tree should be parallelized. Techniques are also described for generating plans and executing queries where (1) both the filter operation and the subquery subtree are parallelized, (2) the filter operation is parallelized but the subquery subtree operation is performed serially, and (3) the filter operation is performed serially but the subquery subtree operation is parallelized.

Abstract: Techniques are provided for processing a database statement that includes a table function capable of parallel execution, where the input of the table function is specified in a subquery. During compilation of the database statement, a global cursor is generated for the database statement. The global cursor includes a first plan component for executing the table function and a second plan component for generating input for the table function. A “dummy” cursor is then generated. When slaves execute the table function, they call the dummy cursor for input. When a slave calls the dummy cursor for input, the dummy cursor causes a context switch back to the global cursor, so that the second plan component within the first cursor generates the input for the slave.

Abstract: Techniques are provided for executing database statements, or portions thereof, in parallel without using slave SQL to communicate to each slave the operations to be performed by the slave. Techniques are provided for incorporating within a shared cursor the code fragments that govern both sides of the interaction between a query coordinator (QC) and remotely-located slaves. Further, techniques are provided for the QC to communicate with each slave on how and which portions of the execution plan to execute and when. A state-transition engine for slave execution under the control of the query-coordinator is also provided.

Abstract: Techniques are described for optimizing the computation of OLAP ranking functions. The techniques involve push-down of the filtering operation into the window sort operation corresponding to a target ranking function. The push-down technique may be employed when a predetermined set of push-down conditions are met.

Abstract: A method and apparatus is disclosed for allowing access to one or more previously generated rows of data. A server receives a statement that requires delivery of one or more rows of data to a client, and designates a particular row of data as a current row within a generation sequence. As the rows of data are being delivered to the client, the server accesses select previously generated rows of data that has already been delivered to the client without designating a new particular row of data as the current row. Once the previously generated row is accessed, data is retrieved from predetermined columns. The data retrieved may be used to perform various calculations that will insert values into the current row prior to delivery of the current row to the client. The statement transmitted by the client may include various parameters, including a default parameter, that specifies a default value to be used by the server.