Universal search for data stored in RDBMS

Abstract

Techniques are provided for creating a "universal search index" that allow full-text search and value range-based search across all columns from multiple database tables, multiple user-defined non-materialized views, and external sources. In one embodiment, the data is indexed in a specially constructed pattern-based JSON format without duplicating the data. The techniques maintain final consistency with normalized sources of factual database tables and do not significantly affect the performance of transactional data manipulation language (DML) operations.

Description

Technical Field

[0001] This invention relates to database systems, and more particularly to techniques for performing multi-objective search operations on data that may originate from heterogeneous external and / or internal sources. Background Technology

[0002] An inverted index is a data structure that maps content (such as words or numbers) to their location in a table, document, or document set. A general description of inverted indexes can be found at en.wikipedia.org / wiki / Inverted_index, the content of which is incorporated herein by reference.

[0003] Inverted indexes are a key indexing structure for achieving efficient information retrieval. Therefore, inverted indexes are supported and available as text indexes in many relational database management system (RDBMS) products. Specifically, many RDBMS systems have some form of inverted index building capability to allow users to perform full-text searches on semi-structured data (e.g., XML, JSON, etc.) and unstructured data (e.g., text documents, Doc files, PDFs, Web URL content, etc.).

[0004] For relational value-based columns, typical RDBMS systems have the capability to create various index types, such as B-trees and bitmap indexes. For efficient Data Manipulation Language (DML) operations and to maintain a source of accuracy, many database applications normalize data into separate tables. However, search needs often span multiple tables.

[0005] In traditional scenarios, when searches involve multiple columns or tables (e.g., "multi-target searches"), it's necessary to construct SQL queries that include complex joins and multiple conditional clauses. This article refers to such queries as "multi-target queries." Typically, for each table access in a multi-target query, the query optimizer within the RDBMS can only use a single index to retrieve data from one target at most, and treats the other conditional clauses of the multi-target query as post-processing steps to retrieve data from the other targets row by row. This significantly degrades performance.

[0006] To avoid this scenario, some database applications transform their data by encapsulating joins across multiple tables they wish to search into a single materialized view. Unfortunately, this results in data duplication, with each data item having one copy in one of the base tables and another copy in the materialized view. Furthermore, for many materialized views, the overhead associated with keeping the copies synchronized is considerable.

[0007] Another option is to export data from multiple targets to an external search tool. Unfortunately, this also leads to data duplication and synchronization overhead. Both the materialized view approach and the external search tool approach involve significant storage overhead and the hassle of maintaining additional application logic to keep the two data copies synchronized with each other, which may span different heterogeneous sources.

[0008] Another challenge facing RDBMS systems is indexing remote data (data outside the RDBMS) such as remote files and URLs. Current methods involve bringing data into the RDBMS and creating indexes on top of a database structure (e.g., tables) that contains the external data. Unfortunately, this approach also involves data duplication. While databases do provide mechanisms like external tables to allow searching remote data without duplication, current implementations of external tables do not support index creation, and query performance for data from external tables is extremely poor. Similarly, there is no mechanism for searching text-based data from external tables.

[0009] The methods described in this section are possible methods, but not necessarily methods that have been previously conceived or adopted. Therefore, unless otherwise stated, they should not be considered as prior art simply by virtue of the inclusion of any method described in this section. Attached Figure Description

[0010] In the attached diagram:

[0011] Figure 1 This is a block diagram of a database system according to one embodiment, the database system including components for enabling the database system to be used for ubiquitous searches across internal and external sources;

[0012] Figure 2 It is a block diagram of various types of indexed sources according to the implementation method;

[0013] Figure 3 This is a block diagram of a database system with a universal search index according to an implementation method, wherein each source has a different partition of the universal index table, a different JSON search index, and a different synchronization job; and

[0014] Figure 4 It is a block diagram of a computer system on which the techniques described herein can be implemented. Detailed Implementation

[0015] In the following description, numerous specific details are set forth for purposes of explanation in order to provide a thorough understanding of the invention. However, it will be apparent to you that the invention can be practiced without these specific details. In other instances, well-known structures and devices are shown in block diagram form to avoid unnecessarily obscuring the invention.

[0016] General Overview

[0017] This article describes a technique for creating a "universal search index" that allows full-text search and value-range-based searches across all columns from multiple database tables and multiple user-defined views. These techniques involve indexing heterogeneous sources (e.g., relational tables, materialized and non-materialized views, and external sources) as if each source were a collection of JSON objects. Specifically:

[0018] • Each source is viewed as a collection of source rows.

[0019] • To build the index, each source row is converted into a JSON object, and

[0020] • Each JSON object created in this way is indexed and then discarded.

[0021] Therefore, while an index appears to be an index of a collection of JSON objects, the nature of the actual source used to index the JSON can be significantly different from and distinct from the JSON itself. Indexes can be used to identify source rows that satisfy specified search criteria in many types of queries. The metadata used for the source rows can be used to retrieve elements from the underlying source that match the search criteria, or to construct and manipulate JSON objects created based on elements retrieved from the underlying source that match the search criteria.

[0022] In one implementation, the data is indexed in a specially constructed schema-based JSON format, with no duplicate data. Additionally, these techniques allow users to add their external tables to a general index and perform arbitrary search operations on data from remote, non-database sources (e.g., Hadoop files, remote URLs, etc.) without persisting the entire data to a database.

[0023] The general search techniques described below avoid heterogeneous product interactions, do not involve data duplication or denormalization, and efficiently index data in real time in a searchable, transformed format. These techniques maintain eventual consistency with the normalized source of the fact database tables and do not significantly impact the performance of Transactional Data Manipulation Language (DML) operations.

[0024] In one implementation, searches can be performed in several ways, including: (a) executing traditional SQL queries directly on top of a general index; and (b) through an interface (e.g., implemented as a PL / SQL wrapper) that accepts queries in a user-friendly No-SQL style JSON query language (referred to herein as "OracleS ODA"). Thus, users gain the advantages of both: the ACID properties and optimal minimal normalization of data within the RDBMS, and the highly feature-rich No-SQL style query capabilities inherent in the RDBMS itself.

[0025] The general search system described below transforms data in real time and indexes it into the search index, directly into the index structure, persisting only minimal metadata: primary keys and data source name information (the names of the tables or views whose rows are indexed). Similarly, this system does not require application developers to maintain separate application logic for synchronizing the search index (which may reside outside the database as part of a separate search solution) with the original database tables, because the system autonomously tracks DML on the original tables and maintains an eventual consistency model with the general index within the database itself, without any separate search solution.

[0026] In one implementation, virtually any data source can be indexed in the database system by transforming the data into a searchable format (JSON business objects) and creating flexible, non-materialized views, which are then added to a single universal search index. In some implementations, a single index (which can index any number of columns / fields) can be used instead of having multiple indexes directly on the database table, which significantly slows down DML operations.

[0027] Using the techniques described in this paper, a single universal index, decoupled from the database table itself, is combined with queuing logic and background jobs to track DML operations and update the universal index asynchronously, thereby improving the performance of DML operations and accelerating the data ingestion rate.

[0028] In one implementation, within a general index, the indexed data for each data source resides in a separate partition. Therefore, applications can perform fast, localized searches within a single data source using a single partition of the index, while simultaneously performing parallelized global searches across the database by searching across all partitions.

[0029] Using these techniques, documents are transformed into JSON that includes the data source name as the key, and the object contains a list of each column from the data source, preserving its data type. Each field of this JSON is then indexed into a text inverted index and other indexes that facilitate value-based and range-based searches. Therefore, under a single index, all data, along with its preserved data types, can be used flexibly for both text-based and range-based searches simultaneously, significantly improving the ability to answer highly complex analytical and transactional application questions, as this data is transformed into SQL or PL / SQL queries accessible through a single index.

[0030] In one implementation, the PL / SQL API provided for queries includes a user-intuitive NoSQL-style JSON query specification format following Oracle SODAQBE syntax. Oracle SODAQBE syntax is described, for example, at docs.oracle.com / en / database / oracle / simple-oracle-document-access / adsdi / overview-soda.html, the entirety of which is incorporated herein by reference. Therefore, the technique presented herein offers the unique advantages of both: storing data within a highly robust RDBMS and indexing the data, and using a REST / application wrapper on top of the PL / SQL API to query general indexes using complex NoSQL-style search queries.

[0031] source

[0032] As used herein, the term "source" refers to anything containing data that a user can search for. Therefore, a source can be "internal" or "external" to a database system. Examples of internal sources include, but are not limited to, tables, materialized views, non-materialized views, etc. Examples of external sources include, but are not limited to, web pages, text documents, containers within other database systems or repositories, etc. The search index described herein is referred to as "general" because it can be used to search across data from all such data sources, as will be described in detail below. Sources indexed using general indexes are referred to herein as "indexed sources."

[0033] Conventional relational tables are organized by rows, where each row is a set of values ​​for the table's columns. However, other types of sources (such as web pages) may not be organized in this way. According to one implementation, for search purposes, all sources are treated as if they were organized by rows. In this embodiment, a "source-row" is any indexable unit of data from an indexed source that is associated with unique identification information.

[0034] System Overview

[0035] Figure 1 This is a block diagram of a database system 100 according to one embodiment, the database system including components for enabling the database system 100 to be used for general searches across both internal and external sources. The database system 100 includes a metadata extractor 130 for extracting metadata from both internal and external sources (represented by external source 102 and internal source 104). In one embodiment, the metadata is extracted on a per-source-row basis. The metadata extractor 130 feeds source-row metadata from the respective sources to a source-row queue 112.

[0036] The SYNC_INDEX module 120 periodically reads entries from the source-row queue 112 and inserts the rows corresponding to these entries into the general index table 110. The general index 150 is built on the UI table 110 and kept synchronized with the contents of the UI table 110. In the illustrated embodiment, the general index 150 is created by the following operations:

[0037] • Retrieve metadata for the target source row from general index table 110.

[0038] • Send metadata to the JSON constructor 140

[0039] • Make JSON constructor 140 obtain the value for the target source row from the appropriate source.

[0040] • Enable JSON constructor 140 to create a JSON object that includes both metadata for the target source row and the value for the target source row.

[0041] • Within the general index 150, the target source row is indexed based on the JSON object used for the target source row.

[0042] In one implementation, the JSON constructor 140 is a component that performs the following operations:

[0043] • Query information from source rows from the indexed source.

[0044] • Construct a virtual JSON document representing the source rows

[0045] • Returns dummy JSON for index building.

[0046] Once all source rows from all sources are indexed via universal index 150, database system 100 uses index 150 to respond to queries against any or all sources from which metadata has been extracted. Depending on the information requested by the query, JSON objects containing data answering the search query can be generated from these sources using JSON constructor 140. Each component of database system 100 will be described in more detail below.

[0047] Source of index building

[0048] Figure 2 This is a block diagram illustrating various types of indexing sources. (Reference) Figure 2 It illustrates a scenario where the sources for indexing include relational database table 200, nonmaterialized view 206, and remote data repository 210.

[0049] Table 200 represents any table within database system 100. For ease of explanation, the source of the index includes a single relational table. However, in a practical implementation, the source of the index can include any number of relational database tables.

[0050] Unmaterialized view 206 is a view created via a join between two relational tables 202 and 204. Because view 206 is unmaterialized, it does not repeat data from tables 202 and 204. The source of indexes can include any number of such views. Furthermore, the logic defining such views can be arbitrarily complex, involving joins between any number of base tables.

[0051] As mentioned above, the remote data repository 210 can take many forms. For example, the remote data repository 210 can be a URL, a file repository, etc. The techniques described herein are not limited to any particular type or number of remote data repositories. For illustrative purposes, only a single remote data repository 210 is illustrated. In the illustrated embodiment, the database system 100 can access the remote data repository 210 via the Internet 240. The external table 212 is a data structure within the database system 100 that internally represents the remote data repository 210. The external table 212 typically contains information about the remote data repository 210, but does not duplicate data stored within the remote data repository 210. For searching and indexing purposes, the database system 100 treats the external table 212 as if it were an internal table. However, to satisfy a query against the external table 212, the database system 100 may ultimately have to retrieve the requested data from the remote data source 210 via the Internet 240.

[0052] Figure 2The source shown for indexing is merely an example of the types of sources that can be indexed using the general index 150. The techniques described herein are not limited to any particular type of source. For example, a source can include both materialized and non-materialized views.

[0053] Metadata extractor

[0054] Metadata extractor 130 generally refers to any mechanism capable of extracting metadata from an indexed source. In one implementation, metadata extractor 130 is implemented against an internally indexed source via a DML trigger / callback created on top of the internally indexed source (e.g., a table, view, or materialized view). For example, in this implementation, table 200 may be associated with a trigger that causes an entry to be added to the source-row queue 112 each time a DML operation is performed on table 200. Similarly, tables 202 and 204 may be associated with triggers that cause an entry to be added to the source-row queue 112 each time a DML operation affecting the content of view 206 is performed on one of the base tables 202 or 204 of view 206.

[0055] While DML triggers can be used to track changes made to source rows by DML operations, the metadata extractor 130 can use a different mechanism for the initial index building operation performed on the source. For example, suppose a user adds an existing table with 10,000 rows to a general index. In this scenario, the metadata extractor can extract metadata from each of these 10,000 rows and store 10,000 corresponding entries in the source-row queue 112. Similarly, when an external source is initially indexed via a general index, the external tool can send metadata for each element to be indexed by the external source to the database server.

[0056] General Index Source Row Queue

[0057] Source-row queue 112 generally represents a repository used for temporary storage of metadata extracted from the indexed sources for source-row storage. In one implementation, each entry in source-row queue 112 corresponds to a single source-row from one of the sources and includes it in JSON format:

[0058] • Source identification information, and

[0059] • Row identification information

[0060] "Source identification information" identifies the source of the row corresponding to the entry. For example, suppose table 200 is a "customers" table owned by "Scott". In these cases, each entry in the source-row queue 112 associated with a row from table 200 could have source identification information {"owner": "Scott", "source": "customers", ....}

[0061] Row identifier information is information that uniquely identifies a row within a source. Because row identifier information uniquely identifies a row, it is similar to a source's primary key. Similar to a primary key in a relational table, a row identifier can be a single value or a combination of key values ​​that collectively and uniquely identify the row. The properties of a primary key can vary based on the properties of the source from which the row is derived.

[0062] For illustrative purposes, we will assume that the primary key of table 200 is "cust_id". Therefore, the row identifier information for a queue entry in table 200 could have the row identifier information "key": {"cust_id" = 4}. In this case, the entry for a row in the source-row queue 112 could be {"owner": "Scott", "source": "customers", "key": {"cust_id" = 4}}.

[0063] It is worth noting that the information in the queue entry of a modified source-row uniquely identifies the source-row corresponding to that entry, but does not actually contain any other values ​​from that source-row. For example, if a new row is inserted into internal source 104, then the entry for that row in source-row queue 112 will have metadata that uniquely identifies the source and the row, but will not have any other values ​​from the newly inserted row.

[0064] Synchronous Index Module

[0065] The SYNC_INDEX module 120 generally represents any mechanism that enables UI table 110 to maintain "eventual consistency" with changes occurring at the source of the index being built. In one implementation, the SYNC_INDEX module 120 is a collection of background jobs within database system 100 (one background job per index partition). Each background job periodically (e.g., every three seconds) performs the following operations on its corresponding index partition:

[0066] • Retrieve the metadata (primary key) of each changed row from the source-row queue 112.

[0067] • Update UI table 110 based on metadata

[0068] • Submit the changes to UI form 110

[0069] • Synchronize the corresponding general JSON search index partitions to reflect updates to UI table 110.

[0070] The structure of UI table 110 and the synchronization process between UI table 110 and general index 150 will be described in more detail below.

[0071] UI Table

[0072] In one implementation, UI table 110 includes one UI table row for each source row. One column of UI table 110 is a JSON-type column used to store metadata that uniquely identifies the source row associated with that UI table row. For illustrative purposes, this document refers to the JSON-type column storing the metadata for each source row as the METADATA column.

[0073] Apart from the metadata required to uniquely identify a specific source row from which an index is created, UI table 110 does not include values ​​for other fields from the corresponding source row. Therefore, data from the indexed source is not duplicated in UI table 110, resulting in significant space and synchronization overhead savings.

[0074] Furthermore, all UI table entries from all indexed sources have the same format. This holds true even if the indexed sources themselves differ from each other in other respects (such as the number and data types of fields, whether the source is internal or external, whether the source is a file, view, or relational table, etc.). Because UI table entries have the same format, database system 100 can index UI table 110 in a manner that allows index searches across all indexed sources (or any subset thereof), regardless of the source.

[0075] For illustrative purposes, assume that UI table 110 includes a UI table-row that corresponds to the source-row in table 200, where cust_id equals "4". Within this UI table-row, the METADATA column can contain the following JSON object: {"owner": "Scott", "source": "customers", "key": {"cust_id" = 4}}. It is worth noting that this is the same information contained in the source-row entries in the source-row queue 112. Therefore, just as queue entries do not duplicate non-primary key values ​​of the source-row, the corresponding UI table-rows are also unique.

[0076] According to one implementation, UI table 110 is partitioned, with each partition corresponding to a different source for indexing. Figure 3 This is a block diagram of this implementation method. (See reference) Figure 3UI table 110 has three partitions 300, 302, and 304, corresponding to table 200, outer table 212, and view 206, respectively. All UI table rows added to table 110 by the source rows of table 200 from the SYNC_INDEX module 120 are stored in partition 300. Similarly, all UI table rows added to table 110 by the source rows of outer table 212 from the SYNC_INDEX module 120 are stored in partition 302. All UI table rows added to table 110 by the source rows of view 206 from the SYNC_INDEX module 120 are stored in partition 304. By partitioning UI table 110 in this way, sources (and their corresponding source-specific indexes) can be easily added and discarded in the general index. Furthermore, the maintenance of partitioning UI table 110 and source-specific indexes facilitates:

[0077] • Parallelism during queries (a single search request allows each source-specific index to be searched in parallel), and

[0078] • Perform query processing on queries that have conditions restricting the indexed sources to be searched.

[0079] We will now describe general indexes and source-specific tables in more detail.

[0080] General Index

[0081] In one implementation, the general index 150 includes a collection of JSON search indexes that appear as a single JSON search index at the application layer. The JSON search index is a flexible index that indexes all content in the JSON document collection for full-text searches (called keyword searches) using the `json_textcontains()` predicate and scalar value range searches using the `json_exists()` and `json_value()` predicates. Furthermore, it can be used to maintain an up-to-date JSON data guide describing the schema of the JSON document collection. The JSON search index is described in detail in various sources, including but not limited to:

[0082] •blogs.oracle.com / database / post / search-indexes-for-json

[0083] •docs.oracle.com / en / database / oracle / oracle-database / 12.2 / adjsn / indexes-for-json-data.html

[0084] •https: / / oracle-base.com / articles / 12c / indexing-json-data-in-oracle-database-12cr1

[0085] The entire content of each of the above is incorporated herein by reference.

[0086] In one implementation, each source-specific search index includes:

[0087] • An inverted text index tokenizes the data in the source where the index is built into words and includes an indication of the “document” in which each word appears (e.g., an index entry for the word “article” could indicate that the word “article” appears in document numbers 1, 5, 7, 8, and 9).

[0088] • Index structures (e.g., B-trees, function-based indexes, binary indexes) used for point lookups or range-based searches (e.g., to answer queries such as "all cameras with a price between $100 and $500").

[0089] It's important to note that these index structures include values ​​from fields in the source row that are not present in the metadata used for the source row. For example, for the SALES table, an index could be built based on the field "date_of_sale," which is not part of the metadata that uniquely identifies the row (and therefore is not in UI table 110). The following section explains how to create index entries for values ​​in fields that do not exist in UI table 110.

[0090] The JSON search index is designed to output the document IDs of documents that satisfy the search query. However, since index 150 is built on top of JSON objects corresponding to UI table rows, database system 100 can use a mapping from doc_id to rowid to convert the doc_id output by index 150 into the corresponding rowid of the UI table row. Therefore, depending on the requirements of any given query, the result set provided by database system 100 could be:

[0091] • doc_id generated from index 150 that meets the search criteria

[0092] • The rowid of the UI table row corresponding to these doc_ids

[0093] • Metadata corresponding to these doc_ids from UI table rows

[0094] • Constructed as a JSON document that includes data from the source rows corresponding to these UI table rows (for queries that are not limited to a single indexed source, a single dynamically constructed query-result JSON document can include content from multiple index sources).

[0095] • Those JSON documents that have undergone further filtering and / or manipulation (e.g., database system 100 can apply JSON operators to the JSON documents constructed by JSON constructor 140 in response to a query).

[0096] Figure 3 The diagram illustrates one implementation of General Index 150. (Reference) Figure 3 The diagram illustrates an implementation where each partition of UI table 110 has its own JSON search index within index 150. Because the partitions of UI table 110 are source-specific, the JSON search index including index 150 is also source-specific.

[0097] In the illustrated embodiment, index 150 includes source-specific indexes 310, 312, and 314. Index 310 is built on partition 300 and therefore indexes the contents of table 200. Index 312 is built on partition 302 and therefore indexes the contents of outer table 212. Index 314 is built on partition 304 and therefore indexes the contents of view 206.

[0098] The general index 150 can have any number of source-specific indexes for each source for which it is indexed. The number and type of indexes for any given source for which an index is indexed can vary based on the type of search that will be supported for that source. According to one implementation, among other possible index types, the general index 150 particularly includes at least one JSON search index for each source for which an index is indexed. The creation and use of such source-specific JSON search indexes will be described in more detail below.

[0099] One advantage of representing source rows as JSON objects is the removal of search limitations imposed by the data type associated with the indexed field. For example, the underlying data type of a "date" field in one source may differ from the underlying data type of a "date" field in another source. However, once indexed as a JSON object, a general index can be used to search for a specific date (or date range) in both sources simultaneously, regardless of the underlying data type in the indexed source.

[0100] JSON constructor: Creates a JSON object for the source row.

[0101] Unfortunately, because the rows in table 110 only contain metadata for the source rows, it doesn't have all the information needed to create an index on the source rows for which the index is created. For example, suppose table 200 includes a first_name column (“FIRST”) and a last_name column (“LAST”) in addition to the cust_id column. To handle searches for all source rows where FIRST = “Fred” or LAST = “Johnson”, one would expect to index the values ​​from these additional columns. Without values ​​from the “FIRST” and “LAST” columns in table 200, index 310 would be unusable for answering such queries.

[0102] Therefore, to obtain the values ​​needed to index the "additional fields" of the source being indexed, the metadata of each source row of the source being indexed is fed into the JSON constructor 140. The JSON constructor 140 uses the metadata for each source row to obtain the values ​​of the additional fields of the source row from the source being indexed (e.g., the values ​​for the "FIRST" column that the source row has), and creates a JSON object for that source row. The JSON object produced by the JSON constructor 140 for each source row includes both the metadata for the source row (also contained in the METADATA column of UI table 110) and the values ​​of the additional fields of the source row retrieved by the JSON constructor 140 from the source being indexed.

[0103] As an example, suppose JSON constructor 140 receives the following metadata for a specific source row in table 200:

[0104] {"owner" : "CUSTOMER", "source" : "CUSTOMERS", "key" : {"cust_id" =1}}.

[0105] In response to receiving this metadata, JSON constructor 140 can use it to retrieve additional values ​​contained in a specific source row (the row where "cust_id" = 1) from table 200. For example, JSON constructor 140 can retrieve the row corresponding to this metadata from table 200 to determine that the row's "FIRST" column has the value "Eric" and the row's "LAST" column has the value "Cartman". Based on this information, JSON constructor 140 can generate the following JSON object:

[0106] {"CUSTOMER":{"CUSTOMERS":{"CUST_ID":1,"FIRST":"Eric","LAST":"Cartman"}}}

[0107] The format of this JSON object is: owner id (i.e., "CUSTOMER"), followed by source id (table name "CUSTOMERS"), and then a series of key / value pairs, where the keys correspond to column names and the values ​​are the values ​​of those columns contained in the source row. In this example, the three key / value pairs are:

[0108] • Key: CUST_ID, Value: 1

[0109] • Key: FIRST, Value: Eric

[0110] • Key: LAST, Value: Cartman

[0111] For any given source table, the type and number of keys indexed can vary. For example, source rows from outer table 212 may not have values ​​for the "FIRST" and "LAST" keys at all, but may instead have keys such as "FILENAME" and "MODIFICATION DATE".

[0112] Once the JSON constructor 140 creates a JSON object for a given source-row from a given source, the database system 100 updates the source-specific index for that given source based on that JSON object. For example, suppose the source-row associated with the JSON object specified above is built as "Document 1" for a JSON search index of the CUSTOMER table. Under these conditions, the JSON search index for the CUSTOMER table can be updated with entries in Document 1 indicating the word "Eric" and entries in Document 1 indicating the word "Cartman". A document-to-row ID mapping can be used to establish an association between "Document 1" and the row within the UI table 110 corresponding to the source-row that produced the JSON object considered as "Document 1".

[0113] Suppose another source row in the CUSTOMER table is assigned "Document ID 4". If that source row also has "Eric" in its firstname field, then the index entry for the word "Eric" (in the source-specific index used for CUSTOMER) will be updated to include both document identifiers (e.g., "Eric 1, 4").

[0114] Path key

[0115] In one implementation, each source row is treated as having an implicit key referred to herein as "PATH". The value of the "PATH" key comes from the metadata of the specified source. In this example, the source associated with the JSON object {"CUSTOMER":{"CUSTOMERS":{"CUST_ID":1,"FIRST":"Eric","LAST":"Cartman"}}} is identified by the owner value "CUSTOMER" and the source id value "CUSTOMERS". Therefore, the value of the "PATH" key used for this source row is "customer.customers".

[0116] In one implementation, instead of indexing the values ​​of the path keys individually, the path values ​​associated with the source row are added before the values ​​of all other keys in that source row, prior to indexing those values. In this example, the key / value pairs to be indexed for the source row would become:

[0117] •Key: CUST_ID, value: customer.customers.1

[0118] • Key: FIRST, Value: customer.customers.Eric

[0119] • Key: LAST, Value: customer.customers.Cartman

[0120] Maintain a universal index based on JSON objects

[0121] Once the JSON constructor 140 has constructed a JSON object for the source row, the general index 150 is updated based on the contents of that JSON object to index that row. For illustrative purposes, it will be assumed that index 310 is a JSON search index built on top of partition 300 (and thus reflects the contents of table 200). In this example, index 300 is updated using the information in this JSON object so that a specific row can be found using index 310.

[0122] Working example of a database system with universal indexes

[0123] For illustrative purposes, it will be assumed that application users have purchase orders for inventory. The database has five tables with the following primary key-foreign key relationships: Customers (primary key: CUST_ID), Orders (primary key: ORDER_ID, foreign key: CUST_ID), Items (ITEM_ID), and Line Items (foreign key: ORDER_ID, ITEM_ID). The use of general indexes will be described using examples given for each of the following five activities:

[0124] • Create a view that joins the five tables into a single view that reflects the format the user wants to use for searching.

[0125] • Create a general search index to facilitate searching.

[0126] • Add the sources to be indexed using the general search index.

[0127] • Execute DML on the source table of the index (or the base table of the source table of the index).

[0128] • Use general search indexes to issue queries that the database system can answer.

[0129] Create the view to be indexed

[0130] As mentioned above, general indexes can be used to index nonmaterialized views, etc. Therefore, if a user wants to run a search on data in a specific format / organization, they can create a nonmaterialized view where the desired data is organized in the desired format. For example, user "CUSTOMER" could create a view as follows:

[0131] CREATE OR REPLACE VIEW SEARCH_VIEW ((cust_id, business_object,

[0132] CONSTRAINT search_view_pk

[0133] PRIMARY KEY (cust_id)

[0134] RELY DISABLE NOVALIDATE,

[0135] CONSTRAINT search_view_fk

[0136] FOREIGN KEY (cust_id) REFERENCES customers(cust_id)

[0137] (Disable NovaLidate) As

[0138] SELECT c.cust_id, JSON_OBJECT (

[0139] 'id' VALUE c.cust_id,

[0140] 'name' VALUE (c.first || ' ' || c.last),

[0141] 'num_orders' VALUE (

[0142] SELECT COUNT(*)

[0143] FROM orders o

[0144] WHERE o.cust_id = c.cust_id),

[0145] 'orders' VALUE (

[0146] SELECT JSON_ARRAYAGG(

[0147] JSON_OBJECT(

[0148] 'order_id' VALUE o.order_id,

[0149] 'items' VALUE (

[0150] SELECT JSON_ARRAYAGG (

[0151] JSON_OBJECT(

[0152] 'id' VALUE l.item_id,

[0153] 'name' VALUE i.name,

[0154] 'quantity' VALUE l.quantity,

[0155] 'single_item_price' VALUEi.price,

[0156] 'total_price' VALUE (i.price* l.quantity)))

[0157] FROM lineitems l, items i

[0158] WHERE l.order_id = o.order_id

[0159] AND i.item_id = l.item_id)))

[0160] FROM orders o

[0161] WHERE o.cust_id = c.cust_id) ABSENT ON NULL RETURNING JSON)business_object

[0162] FROM customers c;

[0163] Create a universal search index

[0164] A universal index must be created before a view can be indexed using a universal index. In one implementation, the user creates a universal search index using the following command:

[0165] EXEC DBMS_SEARCH.CREATE_INDEX('SEARCH_INDEX');

[0166] In the example given above, the "DBMS_SEARCH.CREATE_INDEX" API is used to create a general search index named "SEARCH_INDEX". In response to this command, the database server creates both the UI table and the JSON search index.

[0167] In one implementation, a table (named "SEARCH_INDEX") is created with two JSON columns ("METADATA" and "DATA") and two dummy varchar columns ("OWNER" and "SOURCE"). The OWNER and SOURCE columns are used to partition the UI table. A more detailed explanation of how each column is used follows.

[0168] In one implementation, the JSON search index created in response to the CREATE_INDEX command is created on top of the DATA column using a module such as JSON_CONSTRUCTOR 140, where full-text search and range search are enabled for all fields of the JSON.

[0169] Add source to index

[0170] Once a universal index is created, users can add any number and type of data sources to it. In one implementation, this is done by calling the DBMS_SEARCH.ADD_SOURCE procedure. For illustrative purposes, it will be assumed that the user expects to be able to use the index to search the CUSTOMERS table itself, as well as the SEARCH_VIEW view created in the example given above.

[0171] According to one implementation, the CUSTOMERS table can be added to the index using the following command:

[0172] EXEC DBMS_SEARCH.ADD_SOURCE('SEARCH_INDEX','CUSTOMERS');

[0173] Similarly, you can add a view to the index using the following command:

[0174] EXEC DBMS_SEARCH.ADD_SOURCE('SEARCH_INDEX','SEARCH_VIEW');

[0175] In one implementation, the database system responds to these commands by performing the following operations:

[0176] • Create a partition for source-row queue 112 for each source.

[0177] • Add partitions to the SEARCH_INDEX table for each unique data source (e.g., the OWNER / SOURCE_NANE combination).

[0178] • For each source row of a source when it is added to the index, the database system adds a JSON METADATA entry to the SEARCH_INDEX table in the following form:

[0179] { “OWNER” : <owner>, "SOURCE” : <source> , "KEY” : { <PK_i> : <PK_VAL_i>, …}}

[0180] where PK_i is the i-th column part of the primary key of the data source. For example:

[0181] {"OWNER":"CUSTOMER","SOURCE":"CUSTOMERS","KEY":{"CUST_ID":1}} represents the row from the source table "CUSTOMERS" with cust_id = 1

[0182] {"OWNER":"CUSTOMER","SOURCE":"SEARCH_VIEW","KEY":{"CUST_ID":1}} represents the row from the source "SEARCH_VIEW" with cust_id = 1

[0183] • If the added source is a view, then all the database tables participating in the view definition are detected along with their relationships to each other (using their primary-foreign key graph) and DML triggers are created / set DML callbacks for each of the database tables. Each trigger computes which primary key value of the view is affected by a DML happening on each row of the current table and then inserts this METADATA entry into the source row queue for this data source.

[0184] • The JSON search INDEX (index) is synchronized to update it with the data that is indexed for this data source. The synchronize index API internally calls the JSON_CONSTRUCTOR which takes the METADATA from the SEARCH_INDEX table, the corresponding rows from the actual data sources (in this case "CUSTOMERS" and "SEARCH_VIEW") and then transforms it into a JSON document of the following form:

[0185] { "<SOURCE_OWNER>” :{ "<SOURCE_NAME>” : { … <COLUMN_i> : <COLUMN_i_VALUE>, …}}}

[0186] For example, for the source-row with cust_id = 1 in the customers table data source, the JSON constructor 140 produces the following JSON object:

[0187] {"CUSTOMER":{"CUSTOMERS":{"CUST_ID":1,"FIRST":"Eric","LAST":"Cartman"}}}

[0188] • A background job (e.g., a job in the SYNC_INDEX module 120) is created for this data source that dequeues rows from the source-row queue in batches, flushes the SEARCH_INDEX table by inserting / deleting / updating rows in the table based on the kind of DML to be executed, and then calls SYNC_INDEX on the JSON search index to update the index with the latest changes, thus ensuring eventual consistency.

[0189] Keeping the universal index synchronized

[0190] As explained above, adding a source to the index involves indexing each source as it is added to the universal index. After this process, the universal index will reflect the sources that were indexed when they were added. However, the user can continue to issue regular DML on all the constituent tables. In order to keep the universal index eventually consistent with the indexed data sources, these changes need to be captured and reflected in the universal index.

[0191] According to one implementation, this eventual consistency is achieved by using an internal framework of triggers (metadata extractor 130), queues (source-row queue 112), and jobs (sync module 120) that automatically flush the search index eventually. The components involved in the synchronization process have been described elsewhere in this document.

[0192] Database systems generally do not support triggers on external sources (such as remote data repository 210). The more dynamic the external source, the more important it is to support eventual synchronization. In one implementation, synchronization can be achieved by periodically dropping the external source from the universal index and then re-adding the external source. When re-added, the database system 110 will again pull all source-rows from the external source, store the metadata for each row in the UI table, create JSON objects for each source-row (which will reflect the current state of the external source), and index these JSON objects using the JSON search index.

[0193] In an alternative implementation, a tool can monitor the external source for changes in a manner similar to the monitoring performed by the database system 100 based on the DML triggers. Such a monitoring tool can submit the metadata of all source-rows that undergo changes to the source-row queue 112. Based on the queue entries thus created, the universal index 150 can be updated in a manner similar to the operations described herein for updating the universal index 150 based on triggers created for internal sources.

[0194] Using the universal index to answer queries

[0195] Once a source is added to the universal index, the database system can begin using the universal index to answer queries against that source. According to one embodiment, the database system 100 supports using the universal index to answer many different types of queries, including both SQL queries and NoSQL-style queries.

[0196] With respect to SQL queries, the universal search index can be used to handle SQL using SQL / JSON and CONTAINS queries. An example of a command to perform a universal search across sources using CONTAINS is:

[0197] SELECT METADATA FROM CUSTOMER.SEARCH_INDEX

[0198] WHERE CONTAINS(DATA, 'Alex or Saurabh') > 0;

[0199] (Example 1: Query without source restriction)

[0200] This search has no conditions restricting the source. Thus, the search will be executed across all indexed sources. When a search involves multiple sources, the database system 100 can search the source-specific index for each source involved in the search in parallel. Thus, in this example, this search query can cause the source-specific indexes 310, 312, and 314 to be searched in parallel. After performing the multi-source search, the database system 100 combines the output from each source-specific index. This combined output can be provided to answer the query, or can be further processed based on the needs of the query.

[0201] Additionally, the universal search index can be used to search a particular source using a single partition pruning based on a JSON_TEXTCONTAINS condition, as follows:

[0202] SELECT METADATA FROM CUSTOMER.SEARCH_INDEX WHERE JSON_TEXTCONTAINS(DATA,

[0203] '$.CUSTOMER.SEARCH_VIEW.BUSINESS_OBJECT.name', 'Alex or Saurabh') AND OWNER = 'CUSTOMER' AND SOURCE = 'SEARCH_VIEW';

[0204] (Example 2: Query with both path and source restrictions)

[0205] In this example, the condition "OWNER = 'CUSTOMER' AND SOURCE = 'SEARCH_VIEW'" restricts the search to a single indexed source. Thus, when the database system 100 executes the query, it will only search the source-specific index for that source. Also, in this example, the search is restricted to the specific path '$.CUSTOMER.SEARCH_VIEW.BUSINESS_OBJECT.name'. Thus, unless the source-rows from SEARCH_VIEW have the name "Alex" or the name "Saurabh" associated with the specific path "$.CUSTOMER.SEARCH_VIEW.BUSINESS_OBJECT.name", these rows from SEARCH_VIEW will not satisfy the search condition.

[0206] Similarly, any SQL JSON combination of JSON_EXISTS, JSON_TEXTCONTAINS, JSON_VALUE can be used for a universal search to answer complex queries in a single index access.

[0207] With respect to NoSQL style queries, one implementation provides a DBMS_SEARCH.FIND API that allows the use of universal indexes with NoSQL style Oracle SODA QBE. Oracle SODA QBE is described in docs.oracle.com / en / database / oracle / simple-oracle-document-access / adsdi / overview-soda-filter-specifications-qbes.html, the entirety of which is incorporated herein by this reference.

[0208] Additionally, users can create REST APIs on top of this API for corresponding applications. Further, the database system can provide a REST wrapper on top of this API. In one implementation, the Oracle SODA QBE syntax supported by the DBMS_SEARCH.FIND API is the same as the syntax supported by the CTX_QUERY.RESULT_SET API, which is described in docs.oracle.com / en / database / oracle / oracle-database / 21 / ccref / CTX_QUERY-package.html, the entirety of which is incorporated herein by this reference.

[0209] An example of such a query in this format is as follows:

[0210] SELECT JSON_SERIALIZE(DBMS_SEARCH.FIND('CUSTOMER.SEARCH_INDEX,

[0211] JSON('{

[0212] "$query" : { "CUSTOMER.SEARCH_VIEW.*" : { "$contains" : "Gun orpatrol costume"}},

[0213] "$facet" : [

[0214] { "$sum" : "CUSTOMER.SEARCH_VIEW.BUSINESS_OBJECT.orders.items.total_price"},

[0215] { "$count" :

[0216] { "path" : "CUSTOMER.SEARCH_VIEW.BUSINESS_OBJECT.orders.items.single_item_price",

[0217] "bucket" : [ { "$lt" : 20},{ "$gte" : 20} ]}},

[0218] { "$uniqueCount" : "CUSTOMER.SEARCH_VIEW.BUSINESS_OBJECT.name"}]

[0219] }') PRETTY FROM DUAL;

[0220] (Example 3: Search using SODA QBE syntax)

[0221] SODA QBE syntax allows aggregation, and when used in conjunction with the ubiquitous index 150, allows a single query to aggregate data obtained from source-rows originating from heterogeneous sources. For example, a "sum" can be computed based on price values from source-rows originating from table 200, price values from source-rows originating from view 206, and price values from source-rows originating from remote data store 210.

[0222] Scoring

[0223] Some queries require the database server to "score" the search results. For example, some searches may request that search results be ranked based on relevance, or that only the "top 10" most relevant search results be included. In each of these cases, the database server must score / rank the search results.

[0224] Scoring can become complex when multiple heterogeneous sources are involved. The scoring algorithm used to rank results from one source can differ from the scoring algorithm used to rank results from another source. This makes it difficult to perform relative ranking of results from mixed sources. However, when such heterogeneous sources are indexed into a general index as described in this paper, the scoring is consistent across all sources. That is, the scoring algorithm (typically based on term frequency) used by database system 100 to score search results is source-independent and therefore does not change based on the source of the data.

[0225] Hardware Overview

[0226] According to one embodiment, the techniques described herein are implemented by one or more dedicated computing devices. The dedicated computing device may be hardwired to execute these techniques, or may include digital electronic devices such as one or more application-specific integrated circuits (ASICs) or field-programmable gate arrays (FPGAs) persistently programmed to execute these techniques, or may include one or more general-purpose hardware processors programmed to execute these techniques according to program instructions in firmware, memory, other storage devices, or combinations thereof. Such a dedicated computing device may also combine custom hardwired logic, ASICs, or FPGAs with custom programming to implement these techniques. The dedicated computing device may be a desktop computer system, a portable computer system, a handheld device, a networking device, or any other device that combines hardwired and / or program logic to implement these techniques.

[0227] For example, Figure 4 This is a block diagram illustrating a computer system 400 on which embodiments of the present invention can be implemented. The computer system 400 includes a bus 402 or other communication mechanism for transmitting information, and a hardware processor 404 coupled to the bus 402 for processing information. The hardware processor 404 may be, for example, a general-purpose microprocessor.

[0228] Computer system 400 also includes main memory 406 (such as random access memory (RAM) or other dynamic storage device) coupled to bus 402 for storing information and instructions to be executed by processor 404. Main memory 406 may also be used to store temporary variables or other intermediate information during the execution of instructions executed by processor 404. When these instructions are stored in non-transitory storage media accessible to processor 404, computer system 400 becomes a dedicated machine customized to perform the operations specified in the instructions.

[0229] Computer system 400 also includes a read-only memory (ROM) 408 or other static storage device coupled to bus 402 for storing static information and instructions for processor 404. Storage device 410 (such as a disk, optical disk, or solid-state drive) is provided and coupled to bus 402 for storing information and instructions.

[0230] Computer system 400 can be coupled to display 412 (such as a cathode ray tube (CRT)) via bus 402 for displaying information to the computer user. Input device 414 (including alphanumeric keys and other keys) is coupled to bus 402 for transmitting information and command selections to processor 404. Another type of user input device is cursor control 416 (such as a mouse, trackball, or arrow keys) for transmitting directional information and command selections to processor 404 and for controlling cursor movement on display 412. Such input devices typically have two degrees of freedom on two axes (a first axis (e.g., x) and a second axis (e.g., y)), which allows the device to specify a position in a plane.

[0231] Computer system 400 may implement the techniques described herein using custom hardwired logic, one or more ASICs or FPGAs, firmware and / or program logic (which, in conjunction with the computer system, make computer system 400 a, or program computer system 400 into, a special-purpose machine). According to one embodiment, computer system 400 performs the techniques described herein in response to processor 404 executing one or more sequences of one or more instructions contained in main memory 406. These instructions may be read into main memory 406 from another storage medium, such as storage device 410. Execution of the sequence of instructions contained in main memory 406 causes processor 404 to perform the processing steps described herein. In alternative embodiments, hardwired circuitry may be used instead of or in combination with software instructions.

[0232] As used herein, the term "storage medium" refers to any non-transitory medium that stores data and / or instructions that enable a machine to operate in a particular manner. Such storage media can include non-volatile media and / or volatile media. Non-volatile media include, for example, optical discs, magnetic disks, or solid-state drives, such as storage device 410. Volatile media include dynamic memory, such as main memory 406. Common forms of storage media include, for example, floppy disks, flexible disks, hard disks, solid-state drives, magnetic tape or any other magnetic data storage media, CD-ROMs, any other optical data storage media, any physical media with a perforated pattern, RAM, PROMs and EPROMs, FLASH-EPROMs, NVRAMs, any other memory chips, or magnetic tape cassettes.

[0233] Storage media differ from transmission media but can be used in conjunction with them. Transmission media participate in transferring information between storage media. For example, transmission media include coaxial cables, copper wires, and optical fibers, including conductors containing bus 402. Transmission media can also take the form of sound waves or light waves, such as those generated during radio wave and infrared data communication.

[0234] Various forms of media can be used to transmit one or more sequences of one or more instructions to processor 404 for execution. For example, instructions may initially be carried on a disk or solid-state drive of a remote computer. The remote computer may load the instructions into its dynamic memory and transmit them over a telephone line using a modem. A modem local to computer system 400 may receive data over a telephone line and convert the data into an infrared signal using an infrared transmitter. An infrared detector may receive the data carried in the infrared signal, and appropriate circuitry may place the data on bus 402. Bus 402 carries the data to main memory 406, from which processor 404 retrieves and executes the instructions. Instructions received by main memory 406 may optionally be stored on storage device 410 before or after execution by processor 404.

[0235] Computer system 400 also includes a communication interface 418 coupled to bus 402. Communication interface 418 provides bidirectional data communication coupled to network link 420, which is connected to local network 422. For example, communication interface 418 may be an Integrated Services Digital Network (ISDN) card, a cable modem, a satellite modem, or a modem providing data communication connectivity with a corresponding type of telephone line. As another example, communication interface 418 may be a Local Area Network (LAN) card to provide data communication connectivity with a compatible LAN. A wireless link may also be implemented. In any such implementation, communication interface 418 transmits and receives electrical, electromagnetic, or optical signals carrying streams of digital data representing various types of information.

[0236] Network link 420 typically provides data communication to other data devices via one or more networks. For example, network link 420 may provide a connection via local network 422 to host computer 424 or to data devices operated by Internet Service Provider (ISP) 426. ISP 426, in turn, provides data communication services via a global packet data communication network (now commonly referred to as "the Internet" 428). Both local network 422 and Internet 428 use electrical, electromagnetic, or optical signals carrying digital data streams. Signals through various networks, as well as signals on network link 420 and through communication interface 418 (which carries digital data to and from computer system 400), are example forms of transmission media.

[0237] Computer system 400 can send messages and receive data, including program code, through one or more networks, network links 420, and communication interfaces 418. In the Internet example, server 430 can send requested code to the application through the Internet 428, ISP 426, local network 422, and communication interface 418.

[0238] The received code may be executed by processor 404 upon receipt and / or stored in storage device 410 or other non-volatile storage device for later execution.

[0239] In the foregoing specification, embodiments of the invention have been described with reference to numerous specific details, which may vary depending on the implementation. Therefore, the specification and drawings should be viewed in an illustrative rather than restrictive sense. The sole and exclusive indication of the scope of the invention, and what the applicant intends to define as the scope of the invention, is the literal and equivalent scope of the claims generated in the specific form of the set of claims arising from this application, including any subsequent corrections.< / owner>

Claims

1. A method comprising: The view definition of the non-materialized view is stored in a database managed by the database server; The view definition of a nonmaterialized view specifies the join between two or more base tables; The database does not use any copies of the data in the two or more base tables that are stored separately from the base tables for the nonmaterialized view; Based on the view definition, determine the source row data of the non-materialized view; Create an index within the database that indexes the source rows of a nonmaterialized view based on the data determined for the source rows of the materialized view; as well as Use the index to answer queries for nonmaterialized views; The method is performed by one or more computing devices.

2. The method of claim 1, wherein: Determining the data for the source rows of a nonmaterialized view involves generating a JSON object for each source row of the nonmaterialized view; as well as Creating an index involves creating a JSON search index based on the JSON object generated for the source-row of the non-materialized view.

3. The method of claim 1, further comprising: Create a universal index table that includes UI table rows for each source row of the nonmaterialized view; as well as The UI table row of a given source row in a materialized view includes metadata that uniquely identifies the source / source-row combination of a given row in a non-materialized view.

4. The method of claim 3, wherein creating the index comprises: The JSON constructor generates a JSON object for each source row of a nonmaterialized view by performing the following operations: Obtain metadata that uniquely identifies each source-row combination of a nonmaterialized view; Based on metadata, obtain the values ​​of one or more fields of the source row from the two or more base tables; as well as Generate a JSON object containing both metadata and the values ​​of the one or more fields; and Update the index to reflect the values ​​of one or more fields of the JSON object for each source row.

5. The method of claim 1, further comprising: Create triggers on the two or more base tables; as well as The trigger causes the index to be updated to reflect changes made by data manipulation language (DML) operations performed on the two or more base tables, which affect the content of the nonmaterialized view.

6. A method comprising: Create a general index within the database; Creating a universal index includes: For each of the multiple sources, obtain metadata that uniquely identifies the source-row within that source; Store UI table rows for each source-row of each of the multiple sources in the general index table; The UI table row for a given source row stores metadata that uniquely identifies the given source row. Generate a JSON object for each UI table row, where the JSON object for a given UI table row includes: Metadata that uniquely identifies the row-source corresponding to a UI table row, and Values ​​of one or more fields obtained from a source associated with a UI table row; Update the general index to reflect the values ​​of one or more fields in the JSON object generated for each UI table row; Receive queries targeting one or more of the plurality of sources; and Use a general index to determine the result set of the query; The method is performed by one or more computing devices.

7. The method of claim 6, further comprising partitioning the general index table into a plurality of partitions, wherein each of the plurality of partitions corresponds to a different source among the plurality of sources and stores a UI table row corresponding to the source row of the corresponding source.

8. The method of claim 7, wherein: General indexes include collections of JSON search indexes; and Each JSON search index indexes different partitions among the multiple partitions of the general index table.

9. The method of claim 6, wherein the plurality of sources includes external sources represented as external tables within the database.

10. The method of claim 6, wherein the plurality of sources includes nonmaterialized views.

11. One or more non-transitory computer-readable media storing instructions that, when executed by one or more computing devices, cause: The view definition of the non-materialized view is stored in a database managed by the database server; The view definition of a nonmaterialized view specifies the join between two or more base tables; The database does not use any copies of the data in the two or more base tables that are stored separately from the base tables for the nonmaterialized view; Based on the view definition, determine the source row data of the non-materialized view; Create an index within the database that indexes the source rows of a nonmaterialized view based on the data determined for the source rows of the materialized view; as well as Use the index to answer queries for nonmaterialized views.

12. One or more non-transitory computer-readable media as claimed in claim 11, wherein: Determining the data for the source rows of a nonmaterialized view involves generating a JSON object for each source row of the nonmaterialized view; as well as Creating an index involves creating a JSON search index based on the JSON object generated for the source-row of the non-materialized view.

13. One or more non-transitory computer-readable media as claimed in claim 11, further comprising instructions for: Create a universal index table that includes UI table rows for each source row of the non-materialized view; and The UI table row of a given source row in a materialized view includes metadata that uniquely identifies the source / source-row combination of a given row in a non-materialized view.

14. One or more non-transitory computer-readable media as claimed in claim 13, wherein creating an index comprises: The JSON constructor generates a JSON object for each source row of a nonmaterialized view by performing the following operations: Obtain metadata that uniquely identifies each source-row combination of a nonmaterialized view; Based on metadata, obtain the values ​​of one or more fields of the source row from the two or more base tables; as well as Generate a JSON object containing both metadata and the values ​​of the one or more fields; and Update the index to reflect the values ​​of one or more fields of the JSON object for each source row.

15. One or more non-transitory computer-readable media as claimed in claim 11, further comprising instructions for: Create triggers on the two or more base tables; and The trigger causes the index to be updated to reflect changes made by data manipulation language (DML) operations performed on the two or more base tables, which affect the content of the nonmaterialized view.

16. One or more non-transitory computer-readable media storing instructions that, when executed by one or more computing devices, cause: Create a general index within the database; Creating a universal index includes: For each of the multiple sources, obtain metadata that uniquely identifies the source-row within that source; Store UI table rows for each source-row of each of the multiple sources in the general index table; The UI table row for a given source row stores metadata that uniquely identifies the given source row. Generate a JSON object for each UI table row, where the JSON object for a given UI table row includes: Metadata that uniquely identifies the row-source corresponding to a UI table row, and Values ​​of one or more fields obtained from a source associated with a UI table row; Update the general index to reflect the values ​​of one or more fields in the JSON object generated for each UI table row; Receive queries targeting one or more of the plurality of sources; and Use a general index to determine the result set of the query.

17. The one or more non-transitory computer-readable media of claim 16, further comprising instructions for partitioning a general index table into a plurality of partitions, wherein each of the plurality of partitions corresponds to a different source among the plurality of sources and stores a UI table row corresponding to a source row of the corresponding source.

18. One or more non-transitory computer-readable media as claimed in claim 17, wherein: General indexes include collections of JSON search indexes; and Each JSON search index indexes different partitions among the multiple partitions of the general index table.

19. One or more non-transitory computer-readable media as claimed in claim 16, wherein the plurality of sources includes external sources represented as external tables within a database.

20. One or more non-transitory computer-readable media as claimed in claim 16, wherein the plurality of sources includes dematerialized views.

Images