Abstract: A mechanism for managing data in relational database management systems (RDBMS). The method includes receiving a structured query language (SQL) query to be executed on a relational database management system (RDBMS). The RDBMS includes a schema of tables divided into a plurality of partitions and the SQL query includes an operation of data to be executed on the RDBMS. The method also includes determining whether the SQL query is a valid SQLT query. The SQL query is a valid SQLT query when the SQL query includes a join operation applied to data from tables in a same partition of the plurality of partitions.

Abstract: System and method embodiments are provided for improving the performance of query processing in a massively parallel processing (MPP) database system by pushing down join query processing to data nodes recursively. An embodiment method includes receiving, at a coordinator process, a join query associated with a plurality of tables of the MPP database system, generating, at the coordinator process, an execution plan tree for the join query, and processing, at each of a plurality of data nodes communicating with the coordinator process, the execution plan tree to obtain join query results. The method further includes, upon detecting a next join operator below a top join operator in the execution plan tree at each of the data nodes, forwarding to the other data nodes a sub-tree for the next join operator, and receiving, at each of the data nodes from the other data nodes, sub-tree processing results.

Abstract: System and method embodiments are provided for improving the performance of query processing in a massively parallel processing (MPP) database system by selecting one or more stationary tables for processing the query plan. In an embodiment method, a join query associated with a plurality of tables of the MPP database system is received at a coordinator process. Stationary tables are then selected from the tables according to table partition keys in the join query. The stationary tables are indicated and the join query are sent to a plurality of data nodes communicating with the coordinator process. At each of the data nodes, an execution plan for the join query is generated. Each of the data nodes then executes the execution plan to obtain query results without sharing information of the stationary tables between the data nodes.

Abstract: A relational database for storing data used to populate form records fields, wherein a database table structure stores both fixed and variable form content, for multiple form types by creating tables to store the fixed form content and variable form content. Also described is a method for structuring a relational database, the data content of which is used to populate form fields in forms; the forms structured as fixed data content interspersed with form fields for receipt and display of variable data content; the variable data content derived from primary tables in said relational database; said method comprising creating one or more reference tables which store information about said primary tables thereby to permit tracking of changes to said form fields.

Abstract: A method and system for providing keyword ranking using a common affix is provided. The method for providing keyword ranking using the common affix includes: extracting a common affix using a keyword set; and providing keyword ranking which includes the extracted common affix.

Abstract: An approach is provided in which a processor receives a scan request to scan data included in a data table. The processor selects a column in the data table corresponding to the scan request and retrieves column data entries from the selected column. In addition, the processor identifies the width of the selected column and selects a scan algorithm based upon the identified column width. In turn, the processor loads the column data entries into column data vectors and computes scan results from the column data vectors using the selected scan algorithm.

Abstract: A method of interrogation or modification of a database having a plurality of tables each with fields and relationships between the fields of various tables, the method including: filling in a single privileged table; filling in at least one field in another table for which data are sought or to be modified; filling in at least one filter pertaining to a field of another table allowing the selection of the data sought or to be modified; identifying in the database, using the input fields and the fields appearing in the input filters, the tables containing these fields; identifying in the database, using the input fields and the fields appearing in the input filters, the relationships between these fields of various tables; reformulating a query to the database by stating in full the fields, the identified tables, joins representative; and applying the query to the database.

Abstract: Systems and methods are provided for performing a database query. After receiving an identification of data item components and associations between the data item components, wherein the data item components include a measure and a category, a query generation system creates and executes a database query that comprises one or more Uqueries, wherein each Uquery comprises a Mx segment subquery and a U0 segment subquery. The Mx segment subquery is associated with a table that contains the measure, a table that contains columns for a calculated measure, or a correlated table, and the U0 segment subquery is associated with a table that contains the category, a table that contains columns for a calculated category, a dependent measure table, or a dependent filter table. The query generation system can generate a virtual results table by aggregating Uquery results from one Uquery with Uquery results from another Uquery.

Abstract: A data item is retrieved from a data store in a data processing system. The data processing system comprises an index, which at least partially indexes the data store, and also a partiality definition store. The partiality definition store indicates a part of the data store which is referenced by the partial index. A request is received for a data item from the data store and responsive to determining that the requested data item is not referenced by the partial index, the data store is searched by using the partiality definition store to avoid searching a part of the data store which is known to be indexed.

Abstract: The described implementations relate to updating views in a database system. One implementation can identify an active view that retrieves records from a database and determine one or more physical tables in the database that are referenced by the active view. The implementation can track one or more columns in the physical tables that are used by the active view and identify a change to the physical tables in a transaction log of the database. If the change does not affect the one or more columns, the change can be treated as an ignorable change for the active view.

Abstract: In one aspect, a method is provided. The method includes: (1) identifying in a query of a database a union command involving data in the database that is mutually exclusive; and (2) changing the union command to a union all command.

Abstract: Mechanisms for performing database queries are provided. With these mechanisms, in response to a query request, a query plan intended for minimum query response time and a query plan intended for minimum query total time for the query request are obtained execution of the minimum query response time query plan and the minimum query total time query plan is started. Before the execution of the minimum query total time query plan reaches a specified point, an initial query result obtained from the execution of the minimum query response time query plan is output. In response to the execution of the minimum query total time query plan reaching the specified point, continuing the execution of the minimum query total time query plan to output remaining query results.

Abstract: There is provided a computer-executable method of executing an outer join on a parallel database management system. An exemplary method comprises receiving an outer skewed values list (SVL). The outer SVL may comprise values that are indicated to be skewed. The exemplary method further comprises receiving an inner SVL. The inner SVL may comprise values that are indicated to be skewed. Additionally, the exemplary method comprises partitioning the outer table and the inner table across a plurality of join instances, based on the outer SVL and the inner SVL. A missing skew value is identified. The missing skewed value may be a value of the inner SVL that is not found in the inner table. The outer join is performed using the plurality of join instances, based on the missing skewed value.

Abstract: Database report generation technology for handling a single logical query that requires data that is physically located in two different sources. The system uses intelligent push-down joins, which move data from one source to the other and join data within that source. The system dynamically determines which direction to move data depending on how the data is used in a query.

Abstract: A usage model and the underlying technology used to provide sequence analysis as part of a relational database system. Included components include the semantic and syntactic integration of the sequence analysis with an existing query language, the storage methods for the sequence data, and the design of a multipart execution scheme that runs the sequence analysis as part of a potentially larger database query, especially using parallel execution techniques.

Abstract: Techniques are provided for hardware-accelerated relational joins. A first table comprising one or more rows is processed through a hardware accelerator. At least one join column in at least one of the one or more rows of the first table is hashed to set at least one bit in at least one bit vector. A second table comprising one or more rows is processed through a hardware accelerator. At least one join column in at least one of the one or more rows of the second table is hashed to generate at least one hash value. At least one bit vector is probed using the at least one hash value. A joined row is constructed responsive to the probing step. The row-construction step is performed in the hardware accelerator.

Abstract: A data storage and query method supporting an agile development and horizontal scaling, in a field of computer applications, includes steps of: transforming data into a 2-tuple (Key, MiniTable) and identifying any MiniTable (having a data set removed) with a unique Key, wherein the MiniTable includes multiple data columns; a structure of each data column is limited by a pre-defined mode; and the data columns are sorted according to a primary key defined by the mode. The data storage and query method obtains a capability of the horizontal scaling via removing dependency among the data sets, and a capability of the agile development via supporting an analogous SQL grammar by saving dependency among the data within the data sets. The data storage and query method can be applied to a data management system which supports the horizontal scaling of big data and the agile development of short development lifecycles.

Abstract: A weight is determined for each of a plurality of join predicates for a join between one or more first database objects and one or more second database objects based on a join selectivity for each of the plurality of join predicates. The plurality of join predicates are sorted based on the determined weights. The join operation is performed joining the one or more first database objects with the one or more second database objects in accordance with an order of the sorted plurality of join predicates.

Abstract: Partitioned join operations are performed between a first database object and a second database object by determining an agent group for an agent in response to the agent receiving rows of the second database object to process; partitioning the rows to determine a target hash table for each row and adding the partitioned rows to work to be performed by the agent group; and distributing the work for the group to agents of the group by assigning to a single agent all the rows associated with a particular hash table to perform a join operation on the assigned rows. Each partition is assigned a first counter value indicating an upper bound of a task id range that is most recently assigned to an agent in the agent group for processing, and a second counter value indicating the highest task id that has been processed for that partition.

Abstract: Embodiments of the invention relate to processing queries that utilize fact and/or dimension tables. In one aspect, a pre-join filtering phase precedes a star join. The necessary conditions for the pre-join filtering are considered for a given SQL query, including an estimated size of the hash table exceeding a threshold and presence of a local predicate either on the fact table or one or more dimension tables that is not a large dimension table. Once the necessary conditions are satisfied, the execution of the query exploits the pre-join filtering to build a pre-join output filter from columns of a reduced fact table that joins with each large dimension table. Thereafter, all the dimension tables and the fact table are joined in a star join while exploiting each pre-join filter. Accordingly, the order of when joins occur is changed in order to reduce the size of the fact table and to work from the fact table to reduce the size of large dimension tables.

Abstract: Various embodiments of systems and methods for join tuple assembly by partial specializations are described herein. The join tuple assembly by partial specializations is a phase of the method for join query evaluation by semi-join reduction. By using partial specializations of the non-join part of the WHERE clause of a join query and matching sets, the join tuple assembly is organized in a manner that all computations are necessary, none are repeated, and failure to complete a partial join tuple to a full tuple is detected as early as possible. The method can be applied to inner and outer joins, and to arbitrary join graphs and non-join conditions in the WHERE clause. It can also be used outside the context of semi-join reductions.

Abstract: Techniques for processing joins on column partitioned tables are provided. A query includes a first-Column Partition (CP) table joined with a second-CP table. The query is decomposed into a three-step process and rewritten and processed.

Abstract: Techniques for processing joins on column partitioned tables are provided. A join operation having a column partitioned table within a query is decomposed into a two-step process. The first process performs the join condition on the column portioned table with optional filtering conditions and a non-column partitioned table and spools the resulting columns to a spooled table. The spooled table is then rowid joined back to the column partitioned table to acquire remaining columns not present in the spooled table. Both the first and second steps can be separately resolved for costs when determining a query execution plan.

Abstract: A method and system of transforming a query at a web server are provided. An abstract syntax tree representing the query can be traversed from the top down, for each node in the abstract syntax tree: labeling the node as being in a conjunct position when the node's parent is a WHERE node; labeling the node as being not in a conjunct position when the node's parent is an OR node; and labeling the node identically to the node's parent node when the node's parent is an AND node. Then any IN node in the abstract syntax tree can be transformed to an INNER JOIN node when the IN node is labeled as being in a conjunct position. The abstract syntax tree can be converted into a Structured Query Language (SQL) query, which can then be passed to a database for processing.

Abstract: 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.

Abstract: The number of iterations or self joins required to execute a recursive database query can be estimated. It will be appreciated that this estimation can be used to plan the execution of the recursive query and can be made in various ways and for various applications. By way of example, an estimated number of iterations or self joins required to execute a recursive database query (e.g., 12) can be used as a basis to determine or plan an optimal execution plan. For example, given an estimated twelve (12) iterations, an execution plan can be determined for executing at least the first three (3) iterations or for executing every there (3) iterations, whereas for an estimated twenty (21) iterations required to complete a recursive database query, an execution plan can be determined for the first five (5) or six (6) iterations, and so on.

Abstract: The present invention extends to methods, systems, and computer program products for optimizing parallel queries using interesting distributions. For each logical operator in an SQL server MEMO, in a top down manner from a root operator to the leaf operators, interesting distributions for the operators can be identified based on the properties of the operators. Identified interesting distributions can be propagated down to lower operators by annotating the lower operators with the interesting distributions. Thus, a SQL server MEMO can be annotated with interesting distributions propagated top down from root to leaf logical operators to generate an annotated SQL server MEMO. Parallel query plans can then be generated from the annotated SQL server MEMO in a bottom up manner from leaf operators to a root operator. Annotated interesting properties can be used to prune operators, thereby facilitating a more tractable search space for a parallel query plan.

Abstract: This document describes various techniques for efficiently sequencing structured data in a particular order to provide a sequence of data elements suitable for storage or communication. The sequence may contain values representing a number of default-value structured data elements omitted from the sequence and/or a byte length of the sequence.

Abstract: An elastic parallel database system where metadata is specified out-of-band during database operations via a set of augmentation rules. The rules are used to augment or modify commands received, and indicate whether they are to be used for specific connections on which they are received, for all client connections, or some other scope.

Abstract: A system includes reception of a request conforming to a modeled query and join filter criteria, the request comprising a first subrequest associated with a search attribute view and a second subrequest associated with an analytical view, definition of a join between the search attribute view and the analytical view based on one or more view attributes of the search attribute view, and filtering of a result set associated with the first subrequest based on the join and on the join filter criteria.

Abstract: A computer-implemented method, system, and/or computer program product optimizes an order of execution of column join operations. A first partitioning of the first data column splits the first data column into first subsets of rows. A second partitioning of the second data column splits the second data column into a second subsets of rows. A first value frequency information indicates a frequency of attribute values within a subset of rows of the first data column processed. A second value frequency information indicates a frequency of attribute values within a subset of rows of the second data column. Cardinalities of sub-tables derived by a respective joining of the subsets of rows of the first and second data columns are estimated, based on the first and second value frequency information. An order of execution of multiple join operations is then optimized based on the estimated cardinalities of the sub-tables.

Abstract: A method, system, and/or computer program product estimate a cardinality of a joined table (T) obtained by joining at least a first data column (R) and a second data column (S), where R and S each comprise attribute values. A first density distribution function f(x) describes a frequency of the attribute values of R. A second density distribution function (g(x)) describes the frequency of the attribute values of S. A first information on values in R is based on a sample of values of R. A second information on values in S is based on a sample of values of S. One or more processors then estimate a cardinality of a joined table (T) based on the first and second density distribution function (f(x), g(x)) and the first and second information on values.

Abstract: Methods and apparatus, including computer program products, are provided for receiving an indication of a defined outer join to enable obtaining a first database table at a first database and a second database table at a second database; sending a first request to the first database to obtain the first database table; sending a second request to the second database to obtain the second database table; receiving, in response to the first and second requests, each of the first and second database tables; performing an outer join on the received first and second database tables, the outer join forming an output database table; and providing the output database table.

Abstract: Detection and use of relationships in tabular data is enhanced. A foreign-key-based relationship between two tables in memory is automatically discovered by a module. A view over table data is computationally augmented by adding an expandable hierarchical column. The expandable hierarchical column includes second table data that is related to rows of a first table by the discovered foreign-key-based relationship. A naming scheme for the added column is described. Sometimes the augmentation places an AddJoinColumn operator in a query expression tree; sometimes the tree is rewritten by moving a query operator or replacing a specified pattern of operators by a flat Join operation between two tables. Column expansion may be followed by reshaping data into a flat table, by getting a homogeneous query, by performing a join, or other operations.

Abstract: A computer implemented program analysis method employing a set of new abstract domains applicable to non-convex invarients. The method analyzes programs statically using abstract interpretation while advantageously considering non-convex structures and in particular those situations in which an internal region of an unreachable state exists within a larger region of reachable states. The method employs a new set of non-convex domains (donut domains) based upon the notion of an outer convex region of reachable states (Domain D1) and an inner region of unreachable states (Domain D2) which advantageously permits capture of non-convex properties by using convex regions and operations.

Abstract: The TABLE function mechanism available in a RDBMS is used to integrate RDF models into SQL queries. The table function invocation takes parameters including an RDF pattern, an RDF model, and an RDF rule base and returns result rows to the SQL query that contain RDF triples resulting from the application of the pattern to the triples of the model and the triples inferred by applying the rule base to the model. The RDBMS includes relational representations of the triples and the rules. Optimizations include indexes and materialized views of the representations of the triples, precomputed inferred triples, and a method associated with the TABLE function that rewrites the part of the SQL query that contains the TABLE function invocation as an equivalent SQL string. The latter technique is generally applicable to TABLE functions.

Abstract: A computer-implemented method for generating online search results includes receiving, over the Internet, referring URL data including a query, and a network site ID for a network site that was visited based on third party search engine analysis of the query; generating indexed query and network site data based on the received referring URL data and network site; receiving a new query from a user; determining a network site relevant to the new query based on the indexed query and network site data; and displaying to the user a link to the network site. Systems for generating online search results are also disclosed.

Abstract: System, method, computer program product embodiments and combinations and sub-combinations thereof for tuning query execution performance in a database management system are described. In an aspect, query optimization in a database management system (DBMS) with awareness of limited resource usage includes identifying limited resource usage for each possible plan being considered for query execution by a query engine. A determination of whether any possible plan has limited resource usage that does not exceed a threshold level is made, and when at least one possible plan does not exceed the threshold level, the identified limited resource usage is included as a consideration during plan selection by the query engine. In an embodiment, temporary database usage is identified for each possible plan.

Abstract: Techniques involving an abstract derived entity. The abstract derived entity is a data object present in an abstract data model that may be referenced by other entities in the abstract data model as though it were a relational table present in a physical data source. The abstract data model provides data access independent of the manner in which the data is physically represented in a set of physical data sources. The abstract derived entity may be used to provide aggregate data joined with other non-aggregate data. The abstract derived entity may describe a relational table comprising a set of aggregate data used to form a column of the relational table joined to other columns formed from the sets of input data specified by the abstract derived entity. The resulting abstract derived table may be queried as though it were an actual relational table stored in a physical data source.

Abstract: Methods and systems for query optimization for a multi-tenant database system are provided. Some embodiments comprise receiving at a network interface of a server in a multi-tenant database system an original query transmitted to the multi-tenant database system by a user associated with a tenant, wherein the original query is associated with data accessible by the tenant, and wherein the multi-tenant database system includes at least a first index and a second index. Metadata associated with the data is retrieved, wherein at least a portion of the data is stored in a common table within the multi-tenant database system. A tenant-selective query syntax is determined by analyzing at least one of metadata generated from information about the tenant or metadata generated from the data accessible by the tenant.

Abstract: A query complier analyzes a query to identify a correlated scalar subquery. The query complier transforms the query having the correlated scalar subquery into a query of AGGREGATION INNER/OUTER JOIN or MAX1ROW INNER/OUTER JOIN depending on a result type of the correlated scalar subquery. The AGGREGATION INNER/OUTER JOIN performs JOIN on the rows of the correlated scalar subquery with the rows of a main query and AGGREGATE on the joined rows and returns a result of the joined rows of the main query and aggregation value thereof. The MAX1ROW INNER/OUTER JOIN performs JOIN on the rows of the correlated scalar subquery with the rows of a main query, raises Error when the number of joined rows of the subquery is two or more and returns a result of the row of the main query and the joined row of the subquery.

Abstract: A mechanism is provided for secure data access in a parallel processing system. A database having two tables is provided. A subset of the tables' primary key attributes is considered sensitive. A first user is authorized to access the primary key's sensitive attribute in an unmasked format, while a second user is authorized to access same data in a masked format. Two security tables are generated having a plurality of entries mapping the values of the primary key attribute in the unmasked format to the values of the primary key attribute in the masked format. A security view is generated joining the two security tables. The security view grants the first user access to the values of the primary key's sensitive attribute in the unmasked format only and grants the second user access to the values of the primary key's sensitive attribute in the masked format only.

Abstract: In accordance with the teachings described herein, systems and methods are provided for performing index joins. A database management application may receive an instruction to perform an index join operation between columns in a first table and a second table, wherein the database management application does not have direct access to an index of the first table or the second table for performing the index join operation. A query may be automatically generated by the database management application, wherein the query includes a where clause equality expression that equates an indexed column of the second table with a parameter or updatable constant. The database management application may substitute a value from a row of the first table may for the parameter or updatable constant, and cause the query to be executed on the index of the second table to fetch any one or more rows of the second table that satisfy the where clause by having an index value that matches the substituted value.

Abstract: A continuous query may be received, the continuous query being identified based at least in part on an archived view. The archived view may be created and/or identified based at least in part on a join query related to two or more archived relations associated with an application, at least one of the two or more archived relations being identified as a dimension relation. A query plan for the continuous query may be generated. A join operator in the query plan may be identified based at least in part on the dimension relation. A state of an operator corresponding to the dimension relation may be initialized. It may be identified if the state of the operator identifies an event that detects a change to the dimension relation. The continuous query may be re-started based at least in part on the event that detects the change to the dimension relation.

Abstract: Described herein is a technology for facilitating optimization of database queries is described herein. In some implementations, a first query is received. At least one scenario for optimization is automatically recognized by detecting specific syntax in the query. A second query is then automatically generated by re-writing the first query using a pre-defined operation corresponding to the recognized scenario.

Abstract: Data query in a share-nothing database includes obtaining a query request and generating an optimized access plan with respect to the query request. The query request relates to external data stored in an external data source and contains a definition for expected distribution of the external data. The data query also includes obtaining data distribution information related to the expected distribution based on the optimized access plan, transmitting the data distribution information to the external data source so that the external data source splits and returns the external data in accordance with the data distribution information, and executing query-related processing of the split external data in accordance with the optimized access plan.

Abstract: Techniques for processing queries are provided. In one approach, an execution plan for a query includes multiple sub-plans, one or more of which are selected at runtime while one or more other sub-plans are not executed during execution of the execution plan. In another approach, data about misestimate is generated and stored persistently for subsequent queries. In another approach, statistics for a database object are generated automatically and efficiently while the database object is created or data items are added thereto. In another approach, a hybrid histogram is created that includes a feature of frequency histograms and a feature of height-balanced histograms. In another approach, computer jobs are executed in such a way to avoid deadlock. In another approach, changes to a database object trigger a hard parse of a query even though an execution plan already exists for the query.

Abstract: A synonym module analyzes a set of names for entities, such as names of streets and cities within a geographic database, and adds synonym names to the entities based on the analysis. More specifically, based on adjacencies of words within the names, the synonym module formulates a set of synonym rules specifying permissible ways of spelling a single word as multiple words (or vice-versa). The synonym module applies the synonym rules to the names, and if one of the rules matches a name, the synonym module adds an associated synonym name based on the name and on the rule matching the name. User queries for named entities in the database may then take into account not only the original names of the entities, but also any added synonym names.

Abstract: A system and/or method are implemented to process queries to a database. In particular, the processing of queries to the database is enhanced by enhancing the determination of join orders of tables implicated in queries. Join orders between relatively large numbers of tables are determined by dividing the set of tables to be ordered into a plurality of subsets of tables, and ordering the individual subsets of tables.

Abstract: A method for joining records from database tables is proposed. Join attributes are ordered into a sequence S0, and a scope C is initialized to empty. Tables including a join attribute A that is first in S0 are identified. A new sequence S? omits A from S0. Iteration through each identified table proceeds in join attribute A order. The iteration utilizes a tree index structure for each identified table where the index key includes all join attributes in the identified table in S0 order. For each matched value v of the join attribute located during the iterating, a new scope Cv is created by appending “A=v” to the scope C. If S? is empty, a tuple corresponding to the scope Cv is output. Otherwise, S0 is set to S? and for each scope Cv, C is set to Cv and the steps beginning with the identifying step are recursively invoked.