Teradata Us, Inc.

Salesforce, Inc.

Salesforce Com, Inc.

Rama Krishna Korlapati

Punit B Shah

Haiyan Chen

Rui Zhang

Grace Kwan-On Au

Tri Minh Tran

Paul Laurence Sinclair

Mohammed Yassin Al-Kateb

Sung Jin Kim

Yi Xia

Sanjib Kumar Mishra

Grace K Au

Sanjay Nair

Douglas Doole

Serge Philippe Rielau

Colm McHugh

Lu Ma

Prateek Swamy

Prashasthi Prabhakar

Vijay Sarathy

Bin Cao

Chengyang Zhang

Dominique Jeremy Evans

William J McKenna

John Francis O'Brien

David Gershuni

Mikhail Lychagin

Techniques are disclosed relating to updating a database schema. A computer system may store database schema versions for a database schema. A given schema version may have an active state or one of multiple non-active states. One of the schema versions may be a first schema version that is in the active state and the remaining schema versions may be in the non-active states. The computer system may receive updates to apply to the database schema. The computer system may transition a second schema version from a first non-active state that does not permit structural or content updates to a second non-active state that permits structural and content updates. The computer system may apply the updates to the second database schema version and then transition the second database schema version into the active state and the first database schema version into the first non-active state.

Database object lifecycle states

Techniques are disclosed relating to implementing multiple index scans. A computer system may store a database table comprising fields and indexes corresponding to those fields. The computer system may receive a request to access records based on a Boolean expression that affects a selection of records from the database table and that comprises clauses, at least two of which are joined by an AND operation. The computer system may access the requested records. The accessing may include, for a given one of the at least two clauses, performing an index scan on an index that corresponds to the given clause to identify records that satisfy that clause. The computer system may update a hash table based on the identified records and then identify the requested records based on the hash table. The Boolean expression may include clauses joined by an OR operation that are processed using multiple index scans.

Multiple index scans

Techniques are disclosed that relate to manipulating a chain of database objects without locking the chain. A computer system may maintain a chain that orders a set of database objects stored in a cache of the computer system. The computer system may receive a set of requests to perform database transactions. Based on those received set of requests, the computer system may determine to perform a plurality of chain operations that involve modifying the chain. The computer system may perform two or more of the plurality of chain operations at least partially in parallel using a set of atomic operations without acquiring a lock on the chain.

Mechanisms for maintaining chains without locks

Techniques are disclosed relating to database query optimizers. In some embodiments, a query parser of a database system receives a query requesting data from a multi-tenant database. The query includes a first constraint that restricts a query optimizer's selection of a set of execution plans available to implement the query. The database system determines whether an override has been specified for a particular tenant associated with the received query, the override indicating that the first constraint is to be replaced with a second constraint for the particular tenant. In response to determining that the override has been specified for the particular tenant, the database system replaces the first constraint in the query with the second constraint and provides the query with the second constraint to the query optimizer for selection of an execution plan in accordance with the second constraint.

Query plan overrides

Methods, computer readable media, and devices for identifying and preventing invalid memory access. A method may include defining a dynamic scope for an operation, receiving a request to allocate a portion of the range of shared memory, allocating a monotonically increasing portion of the range of shared memory such that a subsequent request to allocate memory is allocated a different portion of the range of shared memory, receiving a request to deallocate the allocated portion of the range of shared memory, deallocating the allocated portion of the range of shared memory by protecting the deallocated portion of the range of shared memory from any subsequent access, and in response to an access of the protected deallocated portion of the range of shared memory by one of the one or more threads or processes of the operation, trapping and terminating the one thread or process.

Identifying and preventing invalid memory access

Techniques for improving complex database queries are provided. A determination is made whether to adopt a static or dynamic query execution plan for a received database query based on metrics. When a dynamic query execution plan is adopted, the database query is separated into query fragments. A plan fragment is generated for each query fragment and executed to generate feedback for the plan fragment. The feedback from the execution of each plan fragment is used to initiate query rewrite rules to simplify the corresponding query fragments. The rewritten query fragments are combined to generate the dynamic query plan.

Incremental simplification and optimization of complex queries using dynamic result feedback

Caching runtime plan data that is determined not to change for different invocations of a query plan. In some embodiments, a computing system accesses information that specifies a query plan generated for a first database query and generates a first runtime plan for the first database query based on the query plan. In some embodiments, the system caches information generated for the first runtime plan that is determined not to change for different invocations of the query plan. For example, transformation code may include separate functions for mutable and immutable state. In some embodiments, the system retrieves and uses the cached information to generate a second runtime plan for a second database query. Disclosed techniques may improve performance of query plan transformations that hit in the runtime plan cache.

Caching techniques for query runtime state

A query having a Union All view is identified. A logical join between Union AH view/derived table and other tables is broken down into multiple physical joins. The physical joins are pushed to the branches. Cost-based processing statistics are obtained for the branches. An optimal plan for the joins is selected based on the statistics; representing an optimal query execution for the query. The optimal query execution plan is provided to a database engine for executing the optimal query execution plan against a data warehouse.

Cost-based optimizations for join queries with union all views and derived tables

Disclosed are systems and methods for dynamic joining of database tables. The systems and methods can include: selecting a first table from a plurality of tables; selecting a second table from the plurality of tables; joining the first table and the second table to create a temporary table; and storing, in a memory of a computing device, the temporary table and the temporary skew value. The first table can have a first skew value. The second table can have a second skew value. The second skew value can be similar to the first skew value. The temporary table can have a temporary skew value.

Dynamic joining of database tables

Techniques for improving complex database queries are provided. A determination is made whether to adopt a static or dynamic query execution plan based on metrics. When the dynamic query execution plan is used, a request fragment of the request is planned and the corresponding plan fragment is executed. The processed fragment provides feedback related to its processing to the remaining request and the process is repeated on the remaining request until the request is completed.

Techniques for improving the performance of complex queries

A database system may include a storage device configured to store a plurality of database tables. The database system may further include a processor in communication with the storage device. The processor may determine a first sampling percentage to be used on a column of a database table. The first sampling percentage may be based on a respective frequency of each column value in the column. The processor may determine a second sampling percentage to be used on the column in generation of the plan to respond to the database query. The second sampling percentage may be based on size of the database table. The processor may select the maximum of the first sampling percentage and the second sampling percentage. The selected sampling percentage may be used to collect statistics on the column. The collected statistics may be used to generate at least one database query response plan associated with the column. A method and computer-readable medium may also be implemented.

Database-table sampling-percentage selection

Techniques for query statistics inheritance are provided. Statistics for a database are used to determine selectivity estimates for sparse joins and tables being joined together within a given query. These statistics are inherited up to the given query along with the selectivity estimates and provided to a database optimizer to use when developing query plans and selecting an optimal query plan for the given query.

Techniques for query statistics inheritance

A system may include a storage device configured to store a data store comprising data. The database system may further include a processor in communication with the storage device. The processor may receive a query that includes at least one predicate associated with the data. The processor may further determine that the at least one predicate is eligible to be converted to a range set predicate. The processor may further convert the at least one predicate to a range set predicate. The processor may further process the query with the range set predicate. A method and computer-readable medium may also be implemented.

Predicate-based range set generation

A database system may include an array of storage devices configured to store a plurality of database tables and a plurality of histograms. Each histogram may correspond to one or more columns of a database table. The database system may further include a processor in communication with the storage device. The processor may receive an indication that a histogram is to be rebuilt, wherein the histogram has a first number of intervals. The processor may determine at least one condition associated with a distribution of column values of at least one column in the histogram. The processor may determine a second number of histogram intervals based on the at least one condition. The processor may rebuild the histogram to include the second number of intervals. A method and computer-readable medium may also be implemented.

Histogram interval selection

A method for estimating the selectivity of a database base table predicate, the cardinality of a join, and the cardinality of an aggregation. The method includes receiving a database query, the query comprising one or more query predicates and referencing one or more database tables. One or more join indexes are identified, the join index(es) defined on respective database tables referenced by the database query. The join index(es) comprises one or more join index predicates, and includes one or more join columns in its select list. The row count selected by the query predicates is calculated at least partly using the row count or statistics of the one or more join indexes. The selectivity of the base table predicate is calculated at least partly from the calculated row count. The cardinality of the join is estimated at least partly from the row count and statistics of the identified join index(es).

Growing community of inventors

El Segundo, CA, United States of America

Rama Krishna Korlapati