Relational database organization for sharding

Abstract

The present disclosure relates to sharded relational database organization. Techniques for creating, organizing, and maintaining a sharded database are provided. A sharded database can be created using user-defined shards, system-managed shards, or composite shards. The sharded database is implemented with relational database technology. The described techniques provide improvements in load distribution, organization, query processing, and schema propagation in a sharded database.

Description

[0001] This application is a divisional application of the application for invention patent application number 201680067124.9, filed on September 30, 2016, entitled "Relational Database Organization for Sharding." TECHNICAL FIELD

[0002] The present disclosure relates to database systems. More specifically, the present disclosure relates to relational database organization for sharding. BACKGROUND

[0003] Database systems that store increasingly large amounts of data are becoming more prevalent. For example, online transaction processing (OLTP) systems such as e-commerce, mobile, social, and software as a service (SaaS) systems often require large database storage. Example applications of OLTP systems include, but are not limited to, large billing systems, ticketing systems, online financial services, media companies, online information services, and social media companies. Given the large amounts of data that these database systems store, it is impractical to store all of the data in a single database instance because the amount of data can utilize a large amount of computing resources such as processors, memory, and storage devices.

[0004] Furthermore, non-relational databases (sometimes referred to as NoSQL databases) do not provide the full featured benefits of relational databases. For example, NoSQL databases do not provide the same schema control, atomicity, consistency, isolation, durability, or fully featured structured query language (SQL) of relational databases.

[0005] The approaches described in this section are approaches that can be pursued, but they are not necessarily approaches that have been previously conceived or pursued. Therefore, unless otherwise indicated herein, it should not be assumed that any of the approaches described in this section qualify as prior art merely by virtue of their inclusion in this section. SUMMARY

[0006] According to one aspect of the present disclosure, a method is provided, comprising: selecting one or more columns from a set of tables as partition keys; establishing a plurality of key value sets, wherein each key value set in the plurality of key value sets comprises values of the partition keys that do not belong to any key value set in the other key value sets; for each table in the set of tables, generating partitions for each key value set in the plurality of key value sets; creating a sharded database comprising a plurality of shards; wherein each shard in the plurality of shards is associated with a different key value set in the plurality of key value sets; for each table in the set of tables, distributing the partitions of the table among the plurality of shards of the sharded database; wherein, for each table, the partitions associated with each key value set are distributed to the shards in the plurality of shards that are associated with that key value set; and storing mapping data that maps each key value set in the plurality of key value sets to the shard in the plurality of shards that is associated with that key value set.

[0007] According to yet another aspect of the present disclosure, a method is provided, comprising: storing, by a shard coordinator, a plurality of mapping entries; wherein each mapping entry in the plurality of mapping entries maps a different shard key value set to a shard in a plurality of shards of a sharded database; wherein each shard in the plurality of shards comprises one or more database servers; receiving, at the shard coordinator, a first query from a client application, wherein the first query comprises a first shard key value; in response to receiving the first query, performing: identifying a particular mapping entry in the plurality of mapping entries that corresponds to a shard key value set that includes the first shard key value; using the particular mapping entry to identify a target shard from the plurality of shards; generating a first result for the first query by using the target shard that corresponds to the shard key value set that includes the first shard key value; and sending, to the client application, a response to the first query that is based on the first result.

[0008] According to yet another aspect of the present disclosure, a method is provided, comprising: performing a first operation that involves data that resides in both a first shard and a second shard of a sharded database by causing a client application to send a first request to the first shard to cause the first shard to perform a first portion of the first operation that involves data in the first shard, and to cause the client application to send a second request to the second shard to cause the second shard to perform a second portion of the first operation that involves data in the second shard; performing a second operation that involves data that resides in both the first shard and the second shard of the sharded database by causing the client application to send a third request to a shard coordinator; and in response to the third request, the shard coordinator causing: the first shard to perform a first portion of the second operation that involves data in the first shard, and the second shard to perform a second portion of the second operation that involves data in the second shard.

[0009] According to yet another aspect of the disclosure, a method is provided, comprising: maintaining, by a shard catalog, schema data indicating a schema used by each shard of a plurality of shards of a sharded database; receiving a schema modification instruction to modify the schema; in response to receiving the schema modification instruction, automatically performing: updating, at the shard catalog, the schema data based on the schema modification instruction; and causing all shards of the plurality of shards to modify the schema by performing, for each particular shard of the plurality of shards: creating a database connection to the particular shard; sending the schema modification instruction to the particular shard; and executing the schema modification instruction on the particular shard.

[0010] According to yet another aspect of the disclosure, a method is provided, comprising: maintaining a sharded database comprising a plurality of shards; wherein the plurality of shards are grouped into a plurality of shard spaces; wherein each shard space of the plurality of shard spaces comprises at least one shard of the plurality of shards; performing one or more levels of partitioning for a table using one or more levels of partitioning criteria to produce a first plurality of partitions; receiving a user-specified code from a user; selecting, based at least in part on the user-specified code, a shard space of the plurality of shard spaces for each partition of the first plurality of partitions; and distributing each partition of the first plurality of partitions to the shard space selected for that partition based on the user-specified code.

[0011] According to yet another aspect of the disclosure, one or more non-transitory computer-readable media storing instructions that, when executed by one or more hardware processors, cause performance of a method of the disclosure are provided.

[0012] According to yet another aspect of the disclosure, an apparatus comprising one or more devices configured to perform a method of the disclosure is provided. BRIEF DESCRIPTION OF DRAWINGS

[0013] Example embodiments of the invention(s) are illustrated by way of example, and not by way of limitation, in the figures of the accompanying drawings and in which like reference numerals refer to similar elements, and in which:

[0014] Figure 1 An example of an unsharded database and a sharded database according to one embodiment is shown.

[0015] Figure 2 is a block diagram of a system for a sharded database according to one embodiment.

[0016] Figure 3A and Figure 3B An example of a table family according to one embodiment is shown.

[0017] Figure 4A andFigure 4B An example of a table with repetitions is shown, according to one embodiment.

[0018] Figure 5 An example of a shard as a distributed partition form is shown, according to one embodiment.

[0019] Figure 6A and Figure 6B An example of a block configuration in a sharded database is shown, according to one embodiment.

[0020] Figure 7 An example of using consistent hashing to assign a range of values to a block is shown, according to one embodiment.

[0021] Figure 8 An example of a system managed shard is shown, according to one embodiment.

[0022] Figure 9 An example of a partition to table space and shard mapping is shown, according to one embodiment.

[0023] Figure 10 An example of a sharded database using composite shards is shown, according to one embodiment.

[0024] Figure 11 An example of a sharded database of sub-partitions, according to one embodiment.

[0025] Figure 12A , Figure 12B and Figure 12C An example of a re-sharding is shown, according to one embodiment.

[0026] Figure 13 A flowchart of a process for re-sharding, according to one embodiment.

[0027] Figure 14 A flowchart of a process for proxy-based query routing, according to one embodiment.

[0028] Figure 15A and Figure 15B A flowchart of a process for proxy-based query routing, according to one embodiment.

[0029] Figure 16 A flowchart of a query routing process, according to one embodiment.

[0030] Figure 17 is a block diagram of a computing device in which example embodiment(s) of the present application can be implemented.

[0031] Figure 18 is a block diagram of a basic software system for controlling the operation of a computing device. DETAILED DESCRIPTION

[0032] In the following description, for the purposes of explanation, numerous specific details are set forth in order to provide a thorough understanding of the one or more example embodiments. It will be recognized, however, that the one or more example embodiments can be practiced without some or all of these specific details. In other instances, well-known structures and devices are shown in block diagram form to avoid obscuring the one or more example embodiments.

[0033] OVERALL SUMMARY

[0034] Horizontal partitioning is a technique that breaks a single large table into smaller, more manageable subsets of information, referred to as "partitions." Sharding is a data layer architecture in which data is horizontally partitioned across independent database instances, where each independent database instance is referred to as a "shard." A collection of shards together make up a single logical database referred to as a "sharded database" ("SDB"). Logically, a sharded database can be accessible to client applications just like a traditional non-sharded database. However, tables in a sharded database are horizontally partitioned across shards.

[0035] Figure 1 An example of an unsharded database 100 and a sharded database 110 is shown. The unsharded database 100 is a relational database and includes a table 102. All of the contents of the table 102 are stored in the same unsharded database 100 and, thus, use the same computing resources, such as processors, memory, and disk space.

[0036] However, the sharded database 110 depicts an alternative configuration that uses sharding technology. The sharded database 110 includes three shards 112, 114, and 116. Each of the shards 112, 114, and 116 is its own database instance and includes its own table 113, 115, and 117, respectively. However, in the sharded database 110, the table 102 has been horizontally partitioned across the shards 112, 114, and 116 into the tables 113, 115, and 117. Horizontal partitioning in a sharded database involves splitting a database table, such as the table 102, across shards so that each shard contains a subset of rows of the table 102. In this example, the tables 113, 115, and 117 each contain a subset of rows of the table 102. Collectively, the tables 113, 115, and 117 can be referred to as "sharded tables." The data stored in the tables 113, 115, and 117 collectively is equivalent to the data stored in the table 102. The sharded database 110 is logically viewed as a single database and, thus, can be accessible to client applications just like the unsharded database 100.

[0037] In one embodiment, the shards are nothing-shared database architecture, as the shards 112, 114, and 116 do not need to share physical resources such as processors, memory, and / or disk storage devices. The shards 112, 114, and 116 are loosely coupled in software and do not need to run clusterware. From the perspective of a database administrator, the sharded database 110 is composed of multiple database instances that can be co-managed or managed separately. However, from the perspective of a client application, the sharded database 110 appears logically like a single database. Thus, the number of shards included in the sharded database 110 and the distribution of data across the shards is completely transparent to the client application.

[0038] The configuration of the sharded database 110 provides various benefits. For example, in an embodiment, the sharded database 110 improves scalability by adding additional shards and distributing load across the shards, thus eliminating performance bottlenecks and making it possible to increase the performance and capacity of the system. The sharded database 110 can be implemented in a nothing-shared architecture, thus, each shard in the sharded database is its own database instance, and the shards do not need to share hardware such as processors, memory, and / or disk storage.

[0039] In an embodiment, the sharded database 110 provides fault containment, as it eliminates single points of failure such as shared disks, shared storage area networks, clusterware, shared hardware, etc. In contrast, the shards provide strong fault isolation, as the failure of a single shard does not affect the availability of other shards.

[0040] In an embodiment, the sharded database 110 provides enhanced access to global data distribution. The shards make it possible to store particular data in a location that is physically close to its customers. When data must be located within a particular jurisdiction by law, it can be necessary to store the data physically close to the customer by physically locating the shard for that particular data within that jurisdiction to meet regulatory requirements. Storing data physically close to the customer can also provide performance benefits by improving the latency between the customer and the underlying data stored in the shard.

[0041] In an embodiment, the sharded database 110 allows for rolling upgrades of the system. In a sharded data architecture, changes made to one shard do not affect the contents of other shards in the sharded database, thus allowing a database administrator to first attempt changes to a small subset of data stored in a single shard, and then roll those changes to the remaining shards in the sharded database.

[0042] In embodiments, sharded databases 110 provide simplicity in cloud deployments. Given that the size of shards can be made arbitrarily small, database administrators easily deploy sharded databases in clouds composed of low-end commodity servers with local storage.

[0043] In embodiments, sharded databases 110 are most effective in applications that are well partitioned, access data primarily within a single shard, and do not have stringent performance and consistency requirements for cross-shard operations. Thus, sharding is particularly suitable for OLTP systems, such as e-commerce, mobile, social, and SaaS.

[0044] In embodiments, sharded databases 110 provide improvements to automatic propagation of database schema changes across shards. Rather than requiring a database administrator to manually apply database schema changes to each individual shard, sharded databases 110 can automatically propagate such schema changes to shards from a single entry point.

[0045] In embodiments, sharded databases 110 support traditional structured query language (SQL), and thus can utilize all of the full SQL syntax and keywords already available. Moreover, given that sharded databases 110 support SQL, it can easily integrate with existing client applications configured to access relational databases via SQL.

[0046] In embodiments, sharded databases 110 provide the benefits of full features of relational databases, including schema control, atomicity, consistency, isolation, and durability.

[0047] In some embodiments, sharded databases 110 provide direct routing of queries to shards, without the need for intermediate components to route queries. Such direct routing improves system latency by reducing the number of network hops required to process queries.

[0048] General Architecture

[0049] Figure 2 is a block diagram of a database system according to one embodiment. Client applications 210 are any type of client application that requires access to data stored in a database. In one embodiment, client applications 210 can be clients in an OLTP setting, such as e-commerce, mobile, social, or SaaS. Client applications 210 are communicatively coupled to sharded databases 250.

[0050] Sharded database 250 is a logical database in which data is horizontally partitioned across independent database instances. Specifically, data stored in sharded database 250 is horizontally partitioned and stored in shards 260A, 260B, and 260C. A sharded database can include any number of shards, and the number of shards in a sharded database can vary over time. According to one embodiment, each of shards 260A, 260B, and 260C is its own database instance that needs to share physical resources, such as processors, memory, and / or storage devices, with other shards in sharded database 250.

[0051] Shard catalog

[0052] Sharded database 250 includes a shard catalog 230. Shard catalog 230 is a special database used to store configuration data for sharded database 250. In one embodiment, shard catalog can be replicated to provide improved availability and scalability. Configuration data stored in shard catalog 230 can include: a routing table that maps which shard stores data blocks corresponding to a given value, range of values, or set of values of a shard key; shard topology data that describes the overall configuration of sharded database 250, information about the configuration of shards 260A, 260B, and 260C; information about the configuration of shard director 220, information about client applications 210, information about the schema of data that is horizontally partitioned across shards 260A, 260B, and 260C, a history log of pending and completed schema modification instructions for shards 260A, 260B, and 260C, and all other information related to the configuration of sharded database 250.

[0053] In one embodiment, the shard catalog maintains a routing table that stores mapping data comprising a plurality of mapping entries. Each mapping entry of the plurality of mapping entries maps a different set of key values of one or more shard keys to a shard of a plurality of shards in a sharded database. In another embodiment, each mapping entry of the plurality of mapping entries maps a different set of key values of one or more shard keys to a data block on a shard of a plurality of shards in a sharded database. In another embodiment, each mapping entry of the plurality of mapping entries maps a different set of key values of one or more shard keys to a shard space comprising one or more shards in a sharded database. In one embodiment, the set of key values can be a range of partition key values. In another embodiment, the set of key values can be a list of partition key values. In another embodiment, the set of key values can be a set of hash values.

[0054] Thus, for a query that needs to access data for a particular shard key value, the routing table can be used to find which shard in the sharded database contains the data block needed to process the query.

[0055] Shard director

[0056] Shard database 260 includes a shard director 220. Shard director 220 coordinates various functions across shards of shard database 250, including but not limited to: routing database requests to shards, propagating database schema changes to shards, monitoring the status of shards, receiving status updates from shards, receiving notifications from client applications, sending notifications to shards, sending notifications to client applications, and / or coordinating various operations that affect the configuration of shard database 260, such as re-sharding operations. Shard director 220 is communicatively coupled to shard catalog 230, client applications 210, and shards 260A, 260B, and 260C.

[0057] While depicted as a single shard director 220, in one embodiment, shard database 260 can include multiple shard directors 220. For example, in one embodiment, shard database 260 can include three shard directors 220. Having multiple shard directors 220 can allow for load balancing of the coordination tasks performed by shard directors 220, thereby improving performance. In the case of multiple shard directors 220, in one embodiment, one of the shard directors 220 can be selected as a manager of the shard directors 220, which is responsible for managing the remaining shard directors 220, including load balancing.

[0058] Table family

[0059] To obtain the benefits of sharding, the schema of the shard database should be designed in a way that maximizes the number of data requests that can be performed on a single shard without requiring multiple shards. By performing queries on a single shard, the need for joins across shards is eliminated, thereby improving efficiency.

[0060] Parent-child relationships often exist between database tables. For example, a reference constraint can exist between a parent table and a child table. Specifically, in the case of a foreign key constraint, a column of a child table can be constrained to only store values that exist in a primary key of a parent table. Multiple tables linked by such relationships often form a tree-like hierarchy in which each child has a single parent. A "table family" is a representation of the hierarchy of related tables. A table in a table family that has no parent is referred to as a "root table." There is only one root table in a table family.

[0061] For example, Figure 3AAn example of a table family 310 is shown. The table family 310 includes tables 312, 314, and 316. The tables 312, 314, and 316 show a classic Customers / Orders / LineItems pattern. The table 312 is a table that includes customer information. The primary key of the customer table 312 is the "CustNo" field. The customer table 312 is the root table of the table family 310.

[0062] The Orders table 314 is a table that includes order information. The primary key of the orders table 314 is the "OrderNo" field. The foreign key of the orders table 314 is the "CustNo" field that references the primary key of the customer table 312, which indicates that the "CustNo" field of the orders table 314 is only allowed to store values that exist in the "CustNo" field of the customer table 312. Thus, the orders table 314 is a child of the customer table 312.

[0063] The LineItems table 316 is a table that includes line item information. The primary key of the line items table 316 is the "LineNo" field. The foreign key of the line items table 316 is the "OrderNo" field that references the primary key of the orders table 314, which indicates that the "OrderNo" field of the table 316 is only allowed to store values that exist in the "OrderNo" field of the orders table 314. Thus, the line items table 316 is a child of the orders table 314.

[0064] According to one embodiment, when a table belonging to a table family is sharded, all tables of the table family are partitioned by inheriting the partition key from the root table of the table family. Thus, the partition key of the root table is used as the partition key for all tables in the table family. In one embodiment, the partition key is equivalent to the shard key in the sharded database. In the example of the table family 310, the "CustNo" field can be used as the partition key and the shard key for all tables in the table family 310. The corresponding partitions from all tables of the table family are always stored in the same shard. Since join operations in the sharded database are typically performed on related data from tables in the table family, storing the corresponding partitions from the table family in the same shard can improve the performance of such joins because the join operation can be performed on a single shard and does not need to query across shards.

[0065] To illustrate, Figure 3BAn example is shown of how data from table family 310 can be sharded into three shards 320, 330, and 340. Shard 320 includes tables 322, 324, and 326 that have been partitioned based on the "Customer Number" shard key. Specifically, tables 322, 324, and 326 each contain corresponding data from the customer table, the order table, and the line item table, respectively, where Customer Number = 123. Shard 330 includes tables 332, 334, and 336. Shard 340 includes tables 342, 344, and 346 that have also been partitioned based on the "Customer Number" shard key. Specifically, tables 332, 334, and 336 each contain corresponding data from the customer table, the order table, and the line item table, respectively, where Customer Number = 456. Specifically, tables 342, 344, and 346 each contain corresponding data from the customer table, the order table, and the line item table, respectively, where Customer Number = 999.

[0066] In general, tables 322, 332, and 342 are sharded table representations of customer table 312, but, now the contents are spread across three shards. Similarly, tables 324, 334, and 344 collectively contain the same data as order table 314, but, now the contents are spread across three shards. Further, tables 326, 336, and 346 collectively contain the same data as line item table 316, but, now the contents are spread across three shards. By sharding the contents of table family 310 using the same shard key for all tables 312, 314, and 316, each shard will be able to perform joins on relevant data for a given table family individually, thereby improving system performance and reducing the frequency of cross-shard queries.

[0067] Table 1 gives example code for creating sharded tables for the customer table, the order table, and the line item table:

[0068] Table 1

[0069] CREATE SHARDED TABLE Customers

[0070] (CustNo NUMBER NOT NULL

[0071] ,Name VARCHAR2(50)

[0072] ,Address VARCHAR2(250)

[0073] ,CONSTRAINT RootPK PRIMARY KEY(CustNo) )

[0075] PARTITION BY CONSISTENT HASH(CustNo)

[0076] PARTITIONS AUTO

[0077] TABLESPACE SET ts1 ;

[0079] CREATE SHARDED TABLE Orders

[0080] (OrderNo NUMBER NOT NULL

[0081] ,CustNo NUMBER NOT NULL

[0082] ,OrderDate DATE

[0083] ,CONSTRAINT OrderPK PRIMARY KEY(OrderNo)

[0084] ,CONSTRAINT CustFK FOREIGN KEY(CustNo)REFERENCES Customers(CustNo) )

[0086] PARTITION BY REFERENCE(CustFK) ;

[0088] CREATE SHARDED TABLE LineItems

[0089] (CustNo NUMBER NOT NULL

[0090] ,LineNo NUMBER(2)NOT NULL

[0091] ,OrderNo NUMBER(5)NOT NULL

[0092] ,StockNo NUMBER(4)

[0093] ,Quantity NUMBER(2)

[0094] ,CONSTRAINT LinePK PRIMARY KEY(CustNo,OrderNo,LineNo)

[0095] CONSTRAINT LineFK FOREIGN KEY (CustNo, OrderNo) REFERENCES Orders (CustNo, OrderNo) )

[0097] PARTITION BY REFERENCE (LineFK) ;

[0099] In the above syntax example, the Customers table is partitioned across shards by using the CustNo (customer number) field as a partition key. The Customers table is the root table in a table family. The Orders table and the LineItems table are the child and grandchild tables, respectively, of the Customers table. The Orders table and the LineItems table are partitioned by references to foreign keys that indirectly point back to the partition key of the Customers table. Thus, the Orders table and the LineItems table are also partitioned based on the same partition key of the Customers table. Thus, the Customers table, the Orders table, and the LineItems table are a table family that are similarly partitioned across shards for the same partition key.

[0100] In another embodiment, even if the schema of the tables does not explicitly articulate constraints that define the parent-child relationships between the tables of a table collection, such as foreign keys, the table collection is partitioned across shards as if they are a table family. In this case, the database administrator can recognize that the tables are related to each other even if they are not explicitly a "table family." The database administrator can identify one or more partition keys to use to partition the collection of tables across shards such that the partitions of the collection of tables include related data that would likely be used to satisfy a single query and such partitions across the collection of tables would be included on the same shard. Thus, the database administrator can essentially simulate a logical grouping of a collection of tables even when there is no explicit table family based on constraints in the schema. For example, in one embodiment, the collection of tables includes a customers table and an orders table. The customers table and the orders table can each include a "customer number" column, but there is no reference constraint in the schema that links the customers table to the orders table. In this example, the database administrator can partition the collection of tables using the customer number field as at least one partition key even though there is no formal table family. Thus, the logical grouping of the collection of tables can be used to create data blocks as will be described later.

[0101] Duplicate tables

[0102] Some reference tables are relatively small, infrequently changed, and / or are often accessed with sharded tables. An example of a reference table can be a table of U.S. zip codes. Such zip code information is relatively small and static, but can be frequently accessed with other sharded tables that involve addresses. Reference tables are good candidates to be implemented as "duplicated tables" that are replicated across all shards in a sharded database. By storing a copy of the duplicated table on multiple shards, the system can ensure that more queries can be processed via a single shard, without requiring cross-shard queries, thereby improving system efficiency.

[0103] Figure 4A An example of an unsharded database 410 is shown. The unsharded database 410 includes a customers table 412, an orders table 414, and a line item table 416, which again form a table family. The unsharded database 410 also includes a products (Products) table 418, which is an example of a reference table.

[0104] Figure 4B An example of a sharded database that includes shards 420, 430, and 440 is shown. The shards of tables 412, 414, and 416 are similar to those made in Figure 3B

[0105] Table 2 gives example code for creating a duplicated table:

[0106] Table 2

[0107] CREATE DUPLICATED TABLE Products(

[0108] SKU NUMBER PRIMARY KEY,

[0109] Product VARCHAR2(20), ) ;

[0112] ​In one embodiment, materialized view replication is used to synchronize the replicated tables on each shard. In one embodiment, the replicated tables on each shard are read-only materialized views. The master table for the materialized view is located in the shard catalog. In one embodiment, the materialized views on all shards are automatically refreshed at a configurable frequency. In one embodiment, the CREATE DUPLICATED TABLE command automatically creates the master table, the materialized view, and other objects required for materialized view replication.

[0113] Shard as a distributed partition

[0114] Although a shard database appears to a client application and a client application developer as a single database from the perspective of a database administrator, it is a collection of shards, where each shard is a discrete database instance. A shard table is partitioned across all shards of a shard database. Thus, a table partition on each shard of a shard database is no different than a regular partition used in a non-sharded database.

[0115] Figure 5 An example is shown of a shard as a distributed partition. An unsharded database 500 includes a single table that is broken into partitions 1-20. A shard database 520 has these same partitions 1-20 distributed as partitions across shards 521, 522, 523, 524, and 525. From the perspective of a client application, the structure and format of the partitions is the same, but, in the case of shard database 520, the partitions are distributed across five different shards, thus, the computational load is distributed across the computational resources of each shard.

[0116] Tablespace and blocks

[0117] A "tablespace" is a collection of storage containers (e.g., data files) used to store data for database objects in a database. A database object is an object managed by a database system. Examples of database objects include tables, indexes, and code modules that can be executed by a database system. When a tablespace holds data for a database object, the database object can be said to be in a particular tablespace. Each partition of a shard table is stored in a separate tablespace. Thus, a tablespace is a physical unit of data distribution in a shard database. A "tablespace set" is a collection of tablespaces.

[0118] It is possible to create different tables from a table collection or table family in different tablespace sets. For example, a customer table can be created in tablespace set TS1 and an order table can be created in tablespace set TS2. However, to minimize the number of cross-shard queries required, it must be guaranteed that the tablespace storing partition_1 of the customer table and the tablespace storing the corresponding partition_1 of the order table always reside in the same shard. To achieve this goal, a "chunk" can be defined as a set of tablespaces storing the corresponding partitions of a table. For example, in one embodiment, a chunk can be defined as a set of tablespace sets storing the corresponding partitions of all tables in a table family. A chunk contains a single partition from each table in the relevant table collection or table family.

[0119] Figure 6A An example of a chunk 601 is shown, according to one embodiment. Chunk 601 stores corresponding partitions of a customer table, an order table, and a line item table. Specifically, chunk 601 stores a partition Customers_P1 covering customers 1 to 1,000,000 (1M) in the customer table based on a range of values of a partition key. Chunk 601 also stores a partition Orders_P1 including corresponding rows in the order table for customers in Customers_P1 using the same partition key. Chunk 601 also stores a partition LineItems_P1 including corresponding rows in the line item table for orders in Orders_P1 for the same partition key. Thus, partitions Customers_P1, Orders_P1, and LineItems_P1 are all partitioned based on the same partition key. Thus, a join operation requiring data from the customer table, the order table, and the line item table for customers falling within a set of values of the partition key can be serviced by a single shard containing chunk 601 without requiring a cross-shard query.

[0120] Figure 6BAn example of a shard 620 is shown. The shard 620 includes chunks 601, 606, and 611, which include partitions from a table family that includes a Customers table, an Orders table, and a LineItems table. Based on different ranges of values of the partition key, each of the chunks 601, 606, and 611 contains a single partition from each of the tables of the table family. In this example, the partition key is the CustomerNumber column of the Customers table. For example, the chunk 601 includes data from the Customers table, the Orders table, and the LineItems table for CustomerNumbers in the range of 1 to 1,000,000. The chunk 606 covers data from the Customers table, the Orders table, and the LineItems table for CustomerNumbers in the range of 5,000,001 to 6,000,000. The chunk 611 includes data from the Customers table, the Orders table, and the LineItems table for CustomerNumbers in the range of 10,000,001 to 11,000,000. The shard 620 also includes a table 630, which is a duplicate copy of the Products table that is a reference table. Thus, a query that requires data from corresponding rows of Customers, Orders, or LineItems that share the same partition key value can be serviced by a single shard that contains chunks for the partitions of the three tables. Such a query does not need to join across shards.

[0121] System managed sharding

[0122] In one embodiment, system managed sharding is a technique for sharding in which the system automatically determines the mapping of data to shards in a sharded database. As will be described herein, using partitioning by a hashing strategy, data is automatically distributed across shards. Thus, system managed sharding distributes data evenly and randomly across shards. Because data is distributed randomly and evenly across shards, system managed sharding reduces the likelihood of creating hotspots that require handling a disproportionate amount of data requests relative to other chunks in the sharded database. Moreover, because data is distributed randomly and evenly across shards, system managed sharding provides uniform performance across shards. In one embodiment, a sharded database using system managed sharding can maintain an even distribution of data when shards are added or removed from the sharded database.

[0123] In one embodiment, in system managed sharding, data is assigned to shards based on a hash function applied to the shard key. For example, in one embodiment, a traditional hash is a partitioning strategy that can be used in system managed sharding. In a traditional hash, a hash function is applied to the shard key as follows to determine the chunk to which to assign data: hash(key) % N. Here, hash() is the hash function and N is a constant that represents the number of chunks. Thus, by applying a traditional hash to the shard key, the system can determine to which chunk to assign data associated with the shard key.

[0124] In one embodiment, consistent hashing is a partitioning strategy that can be used in a system-managed sharding. In consistent hashing, a hash function is applied to a shard key to obtain a hash value. The possible range of values of the hash function (e.g., from 0 to 2 32 ) is divided into a set of N contiguous intervals, each representing a different bin. Thus, by applying consistent hashing to a shard key, the system can determine to which bin the data associated with the shard key is assigned.

[0125] For example, Figure 7 An example of the range of values assigned to a bin is shown. In this example, the shard database contains 1024 bins, and each bin is assigned a range of 2 22 hash values. Thus, bin 710A is assigned a range of hash values from 0 to 4194304 (e.g., 2 22 ), bin 710B is assigned a range from 419304 (e.g., 2 22 ) to 8388608 (e.g., 2*2 22 ), and so on, until bin 710N is assigned a range from 4290772992 (e.g., 1023*2 22 ) to 4294967296 (e.g., 1024*2 22 ). Thus, partitioning by consistent hashing is essentially partitioning by ranges of hash values.

[0126] Assuming all shards have the same computational capacity, the same number of bins is assigned to each shard in the shard database. For example, if 1024 bins are created in a shard database containing 16 shards, then each shard will contain 64 bins. In one embodiment, the bins are randomly allocated to the shards to provide a random and uniform distribution of data across the shards.

[0127] In the case of resharding, when a shard is added to or removed from the shard database, a certain number of bins are moved between the shards to maintain a uniform distribution of bins across the shards. During this resharding process, the contents of the bins do not change, so no rehashing is required.

[0128] The number of bins in a shard database with system-managed sharding can be specified in the CREATE SHARDCATALOG command. Once the shard database is deployed, the number of bins can only be changed by splitting bins. When a bin is split, its range of hash values is divided into two ranges, but nothing needs to be done for the rest of the bins. Any bin can be independently split at any time.

[0129] A chunk can contain only one tablespace from a given tablespace set. However, the same tablespace set can be used for multiple tables belonging to the same table family. In this case, each tablespace in the tablespace set will store multiple partitions, one for each table. Alternatively, each table in the table family can be stored in a separate tablespace set. In this case, the chunk contains multiple tablespaces: one for each table, and each tablespace stores a single partition.

[0130] Figure 8 An example of system managed sharding is shown, which shows the relationship between partitions, tablespaces, and shards for the simple case of a single sharded table. In this case, each chunk contains a single tablespace, and each tablespace stores a single partition. The sharded database includes shards 820, 830, 840, and 850. Partitions P_1 through P_480 are located in tablespaces tbs1-1 through tbs1-480. Tablespace set 810 includes tablespaces tbs1-1 through tbs1-480.

[0131] Table 3 gives an example code to create a sharded table using system managed sharding:

[0132] Table 3

[0133] CREATE SHARDED TABLE customers

[0134] (cust_id NUMBER NOT NULL

[0135] , name VARCHAR2(50)

[0136] , address VARCHAR2(250)

[0137] , location_id VARCHAR2(20)

[0138] , class VARCHAR2(3)

[0139] , signup DATE

[0140] CONSTRAINT cust_pk PRIMARY KEY(cust_id) )

[0142] PARTITION BY CONSISTENT HASH(cust_id)

[0143] PARTITIONS AUTO

[0144] TABLESPACE SET ts1 ;

[0146] In this example, a sharded table "customers" is created that uses consistent hashing across the cust_id shard key. The statement contains "PARTITIONS AUTO", which indicates that the number of partitions will be automatically set to the number of tablespaces in the tablespace set ts1. Thus, the number of chunks will also equal the number of tablespaces in the tablespace set ts1.

[0147] User-defined sharding

[0148] In one embodiment, user-defined sharding is a technique for sharding that allows a user, such as a database administrator, to explicitly specify a user-defined mapping of data to shards. User-defined sharding can be used when there is a need to store certain data on a particular shard and the database administrator needs full control over moving data between shards. For example, user-defined sharding can be beneficial when performance constraints require that certain data be located on a particular shard. In another example, user-defined sharding can be necessary when regulatory requirements need certain data to be stored on a particular shard. Another benefit of user-defined sharding is that in the event of planned or unplanned outages of a shard, the database administrator knows exactly which data is unavailable due to the outage. A disadvantage of user-defined sharding is that the database administrator needs to monitor the shards and manually maintain an even distribution of data and workloads across the shards.

[0149] With user-defined sharding, a sharded table can be partitioned by range or list. The CREATE TABLE syntax for a sharded table is similar to the syntax for creating a regular table, except for the additional requirement that each partition should be stored in a separate tablespace. Table 4 gives an example code for creating a table using user-defined sharding for the accounts table:

[0150] Table 4

[0151] CREATE SHARDED TABLE accounts

[0152] (id NUMBER

[0153] ,account_number NUMBER

[0154] ,customer_id NUMBER

[0155] ,branch_id NUMBER

[0156] ,state VARCHAR(2)

[0157] , status VARCHAR2(1) )

[0159] PARTITION BY LIST(state)

[0160] (PARTITION p_northwest VALUES('OR','WA') TABLESPACE tbs1

[0161] , PARTITION p_southwest VALUES('AZ','UT','NM') TABLESPACE tbs2

[0162] , PARTITION p_northcentral VALUES('SD','WI') TABLESPACE tbs3

[0163] , PARTITION p_southcentral VALUES('OK','TX') TABLESPACE tbs4

[0164] , PARTITION p_northeast VALUES('NY','VR','NJ') TABLESPACE tbs5

[0165] , PARTITION p_southeast VALUES('FL','GA') TABLESPACE tbs6 ) ;

[0168] In the example shown in Table 4, the accounts table is created as a sharded table and partitioned by list using the state as the partition key. The user is defining which partitions based on the partition key are assigned to specific table spaces. There are no table spaces set for the user-defined shards. Each table space must be created explicitly. A "shard space" is a collection of shards that store data corresponding to a range or list of key values. Specifically, in the case of user-defined shards, a shard space consists of a shard or a collection of fully replicated shards. Table 5 gives an example code for creating table spaces for the accounts table:

[0169] Table 5

[0170] CREATE TABLESPACE tbs1 IN SHARDSPACE west;

[0171] CREATE TABLESPACE tbs2 IN SHARDSPACE west;

[0172] CREATE TABLESPACE tbs3 IN SHARDSPACE central;

[0173] CREATE TABLESPACE tbs4 IN SHARDSPACE central;

[0174] CREATE TABLESPACE tbs5 IN SHARDSPACE east;

[0175] CREATE TABLESPACE tbs6 IN SHARDSPACE east;

[0176] Thus, in the example shown in Table 5 above, the user manually defines the location of each tablespace in the shard space. Table 6 gives example code for creating the shard space and populating shards for the accounts table:

[0177] Table 6

[0178] ADD SHARDSPACE - SHARDSPACE west, central, east;

[0179] ADD SHARD - CONNECT shard-1 - SHARDSPACE west;

[0180] ADD SHARD - CONNECT shard-2 - SHARDSPACE central;

[0181] ADD SHARD - CONNECT shard-3 - SHARDSPACE east;

[0182] Thus, in the example shown in Table 6 above, the user manually defines what shard space populates each shard.

[0183] Figure 9 An example is shown of mapping partitions to tablespaces and mapping tablespaces to shards of the accounts table. The accounts table is sharded across shards 920, 930, and 940. Shard 920 is associated with the west shard space, shard 930 is associated with the central shard space, and shard 940 is associated with the east shard space.

[0184] In one embodiment, user-defined sharding can be used to segregate data onto specific shards based on performance or other constraints. For example, in one embodiment, by using user-defined sharding, a first data set can be assigned to a first set of shards accessible to a cloud environment, and a second data set can be assigned to a second set of shards inaccessible to the cloud environment. Segregating data can be helpful due to privacy or regulatory concerns requiring data to be inaccessible via the cloud environment. In other embodiments, segregating data can be helpful for archival purposes, as data being archived is segregated onto shards with lower performance capabilities, while data being actively used is segregated onto shards with higher performance capabilities.

[0185] Composite Sharding

[0186] In one embodiment, composite sharding is an algorithm for combining the features of user-defined sharding with the features of system-managed sharding.

[0187] System-managed sharding distributes data randomly across shards, which provides better load balancing compared to user-defined sharding that partitions by range or list. However, system-managed sharding does not give the database administrator any control over the assignment of data to shards.

[0188] User-defined sharding provides the ability to assign specific data to specific shards, which can be necessary to meet various requirements. For example, regulatory requirements can require that certain data be stored in computer hardware located in a specific geographic location. Also, shards can be implemented on different computing devices with different hardware performance characteristics, including but not limited to processors, memory, and storage. Furthermore, data can be differentiated based on a classification of the data, where the classification of the data is determined based on a particular column in a table. For example, in the case of customer-related data, some customers can be classified as “GOLD” customers, while other customers can be classified as “SILVER” customers. In this example, the data for GOLD customers can need to be stored in one set of shards, while the data for SILVER customers can need to be stored in a different set of shards.

[0189] With composite sharding, data can be partitioned across more than one level of partitioning. For example, data can first be partitioned by list or range across multiple shard spaces, and then further partitioned by consistent hashing across multiple shards in each shard space. Given user-defined mapping data, the multi-level sharding provided by composite sharding allows the system to automatically maintain an even distribution of data across shards in each shard space, while partitioning data across shard spaces. Composite sharding can be performed using data definition language (DDL).

[0190] For example, assume that a database administrator wishes to allocate three shards hosted on faster servers to GOLD customers and four shards hosted on slower machines to SILVER customers. Within each shard set, the customers must be distributed using partitioning by consistent hashing on customer_id. Thus, in this example, the data is partitioned by range or list across shard space using a first shard key that indicates the customer class. The data is further partitioned by consistent hashing across shards within the shard space using a second shard key for customer_id. In one embodiment, the first shard key can be referred to as a super shard key. A super shard key is a type of shard key that is used for higher level partitioning in a compound shard configuration. In one embodiment, a routing table stores mapping data that maps for a combination of a value set of the first shard key and a value set of the second shard key to a location of a chunk containing data in a particular shard in a particular shard space. Thus, the routing table can be used to determine a location of a chunk containing data for a given key combination on a particular shard in a particular shard space using a combination of the first shard key and the second shard key.

[0191] Figure 10 An example of a sharded database 1000 including shard space 1001 and shard space 1002 is shown. Shard space 1001 is a shard space dedicated to GOLD customers and includes shards 1020, 1030, and 1040 of table space set 1010. Shard space 1002 is a shard space dedicated to SILVER customers and includes shards 1050, 1060, 1070, and 1080 of table space set 1011.

[0192] Table 7 gives example code for creating shard spaces 1001 and 1002:

[0193] Table 7

[0194] ADD SHARDSPACE - SHARDSPACE shspacel, shspace2;

[0195] ADD SHARD - CONNECT shardl - SHARDSPACE shspacel;

[0196] ADD SHARD - CONNECT shard2 - SHARDSPACE shspacel;

[0197] ADD SHARD - CONNECT shard3 - SHARDSPACE shspacel;

[0198] ADD SHARD - CONNECT shard4 - SHARDSPACE shspace2;

[0199] ADD SHARD - CONNECT shard5 - SHARDSPACE shspace2;

[0200] ADD SHARD - CONNECT shard6 - SHARDSPACE shspace2;

[0201] ADD SHARD - CONNECT shard7 - SHARDSPACE shspace2;

[0202] In Table 7 above, the user provides a mapping of multiple shards (shardl, shard 2, shard3, shard4, shard5, shard6, and shard7) to multiple shard spaces (shspace 1 and shspace2). Table 8 gives example code for creating table space sets 1010 and 1011:

[0203] Table 8

[0204] CREATE TABLESPACE SET tbsl IN SHARDSPACE shspace 1 ;

[0205] CREATE TABLESPACE SET tbs2 IN SHARDSPACE shspace2;

[0206] In the example of Table 8 above, the user provides a mapping of table space sets (tbsl and tbs2) to shard spaces (shspace 1 and shspace2). Once the shard spaces and table space sets are created, shard tables can be created, partitioned into two partition sets: GOLD and SILVER, based on service class. Table 9 gives example code for creating shard tables for shard database 1000:

[0207] Table 9

[0208] CREATE SHARDED TABLE customers

[0209] (cust_id NUMBER NOT NULL

[0210] , name VARCHAR2(50)

[0211] , address VARCHAR2(250)

[0212] , location_id VARCHAR2(20)

[0213] , class VARCHAR2(3)

[0214] , signup_date DATE

[0215] , CONSTRAINT cust_pk PRIMARY KEY (cust_id))

[0216] PARTITION SET BY LIST (class)

[0217] PARTITION BY CONSISTENT HASH (cust_id)

[0218] PARTITIONS AUTO

[0219] (PARTITION SET gold VALUES ('gld') TABLESPACE SET tbsl, PARTITION SET silver VALUES ('slv') TABLESPACE SET tbs2) ;

[0221] In the example of Table 9 above, the customer table is created as a sharded table. The customer table is first partitioned using the "class" column as the partition key. In Table 9, the mapping provided by the user maps GOLD customers to the tablespace set tbsl and SILVER customers to the tablespace set tbs2. This is an example of user-defined sharding. The customer table is further partitioned using the "cust_id" as the partition key. This further partitioning uses consistent hashing and is an example of system-managed sharding. Thus, the end result of Table 9 is a composite shard that combines the features of user-defined sharding and system-managed sharding.

[0222] The composite shard provides the benefits of user-defined sharding in that it allows the user to customize the allocation of data to the shard space based on user-defined constraints. Such user-defined constraints can vary based on the application and can include constraints such as regulatory restrictions on the physical location of data, differentiation based on data categories, or allocation of data on different hardware resources. The composite shard also provides the benefits of system-managed sharding by providing automatic and randomization of distribution of data within a shard within the same shard space. Thus, the composite shard is a multi-level sharding approach that combines the benefits of user-defined sharding and system-managed sharding.

[0223] In one embodiment, multi-level composite sharding is a strategy for distributing data in a sharded database that can include any number of N-level partitions and is not limited to two-level partitions. For example, in one embodiment, the first N-1 levels are each partitioned based on a range or list and can each be partitioned using a different partition key. Further, in one embodiment, the Nth level is partitioned based on a hash function such as a consistent hash function. Thus, such multi-level composite sharding can be used to simulate a multi-level hierarchical organization of data across one or more shards, tablespace sets, and / or sharding. Such multi-level composite sharding strategies provide the benefits of user-defined sharding and the benefits of system-managed sharding.

[0224] In one embodiment, when using composite sharding, there is a shard key for each level of sharding. Multiple shard key values can be stored in a key-value store in order to maintain mapping data allowing the location of a particular piece of data residing on a shard in a multi-level composite sharded database. In one embodiment, in order to determine which shard contains data for a particular database request, a client application will need to include the values of all shard keys within the database request.

[0225] Sub-Partitioning

[0226] In one embodiment, sub-partitioning techniques can be combined with sharding techniques to allow the partitions included in a shard to be further sub-partitioned. Sub-partitioning splits each partition into smaller parts. Sub-partitioning provides various benefits. For example, sub-partitioning allows for multi-level organization of data across a shard. For example, within a shard, data can be organized into sub-partitions based on date, customer class, or some other criteria from the data. Sub-partitioning is beneficial for efficient parallel execution within a shard, especially in cases where the number of partitions per shard can be small and the shard is partitioned by range or list. Data that is sub-partitioned can be partitioned by list, range, or hash function such as consistent hash or traditional hash.

[0227] Table 10 gives an example code for system-defined sharding by consistent hash and sub-partitioning by range:

[0228] Table 10

[0229] CREATE SHARDED TABLE customers

[0230] (cust_id NUMBER NOT NULL

[0231] , name VARCHAR2(50)

[0232] , address VARCHAR2(250)

[0233] ,location_id VARCHAR2(20)

[0234] ,class VARCHAR2(3)

[0235] ,signup_date DATE

[0236] ,CONSTRAINT cust_pk PRIMARY KEY(cust_id,signup_date) )

[0238] TABLESPACE SET tbs1

[0239] PARTITION BY CONSISTENT HASH(cust_id)

[0240] SUBPARTITION BY RANGE(signup_date)

[0241] SUBPARTITION TEMPLATE

[0242] (SUBPARTITION per1 VALUES LESS THAN(TO_DATE('01 / 01 / 2000','DD / MM / YYYY'))

[0243] ,SUBPARTITION per2 VALUES LESS THAN(TO_DATE('01 / 01 / 2010','DD / MM / YYYY'))

[0244] ,SUBPARTITION per3 VALUES LESS THAN(TO_DATE('01 / 01 / 2020','DD / MM / YYYY'))

[0245] ,SUBPARTITION future VALUES LESS THAN(MAXVALUE)) )

[0247] PARTITIONS AUTO ;

[0249] In this example, the Customers partitioned table is created. The Customers partitioned table is first partitioned by consistent hash using cust_id and then subpartitioned into four subpartitions by signup_date. Figure 11An example showing the results of applying this code is shown. The partitions are all created in tablespace set tbs1 1110. The partitions are partitioned across shards 1120, 1130, and 1140 by consistent hashing. Each of these partitions is in its own tablespace. In addition, each partition includes four subpartitions, which are partitioned by signup_date and stored in the father's tablespace.

[0250] The subpartitions can be combined with system-managed shards, user-defined shards, or composite shards. If the subpartitions are combined with composite shards, then the data in the table will end up organized in three levels: partition set, partition, and subpartition. The partition keys for each level of partitioning can be different, and the routing table can be updated to include mapping data for all three partition keys.

[0251] An example application area for subpartitions can be storing data about customers that meets regulatory and archival requirements. The regulatory requirements can require that data about a particular customer be stored in a shard that is physically located within the customer's geographic jurisdiction. The archival requirements can require that data for older customers be stored separately from data for newer customers. For example, data about older customers can need to be stored separately in order to be archived to backup storage. User-defined shards can be used to store the creation partitions of the shard table such that each shard contains data for customers that are geographically located within the same jurisdiction as the shard and subpartitions to create subpartitions that group customers based on a service-based signup date to meet these requirements. Thus, subpartitions allow for multi-level organization of data in a shard database.

[0252] Replication

[0253] Since each of the shards in a shard database is its own database instance, the present system can use existing replication techniques to replicate data stored in the shards. For example, in active / active replication, two peer database instances can store the same set of data that can be changed and accessed by client applications. Changes made to one of the peer database instances are replicated to the other, and vice versa. Under active / active replication, there is no master copy, and the peer database instances remain identical.

[0254] In master / slave replication, there is a master database instance and multiple slave database instances. The master copy is the source of truth and can accept read and write transactions. Changes made to the master database instance are replicated to the slave database instances. The slave database instances are read-only.

[0255] Thus, in one embodiment, data stored in a chunk in a first shard can be replicated to one or more additional shards. This replication of data in a chunk can be performed using active / active replication or master / slave replication.

[0256] Re-sharding

[0257] Re-sharding is the process of redistributing data among shards in a sharded database. Re-sharding can be triggered by various conditions. Re-sharding involves moving chunks or portions of chunks to new shards. As previously described, chunks are the unit of data migration between shards. By storing related data from different shard tables in the same chunk and moving the chunk as an atomic unit between shards, the re-sharding process can ensure that related data from different shard tables is moved together.

[0258] In one embodiment, the re-sharding process can be triggered when a new shard is added to the sharded database. In this case, the re-sharding process can be automatic and will automatically redistribute one or more chunks in the sharded database to move the chunks from the old shards to the new shard.

[0259] In one embodiment, the re-sharding process can be triggered when a shard is removed from the sharded database. In this case, the re-sharding process can be automatic and will automatically redistribute one or more chunks from the shard being removed from the sharded database to another shard in the sharded database that is not being removed.

[0260] In one embodiment, the re-sharding process can be triggered when a database administrator uses a command to manually break up or split a particular chunk. This can occur, for example, when the database administrator recognizes that a particular chunk is oversized and needs to be split into smaller chunks and redistributed. The oversized chunk can be split into two or more smaller chunks, which are then redistributed to shards in the sharded database.

[0261] In one embodiment, the re-sharding process can be triggered when the sharded database detects significant load on a particular shard. To balance the load across the sharded database, the sharded database can identify one or more chunks on the particular shard to be redistributed to other shards in the sharded database in response to detecting significant load on the particular shard. In one embodiment, the identification of the one or more chunks for redistribution is random.

[0262] In one embodiment, the ability to perform re-sharding by allowing the sharded database to reconfigure the distribution of data in response to triggering events, such as newly added or removed shards, manual intervention by a database administrator, or detection of significant load on a particular shard, provides the sharded database with resilient scalability.

[0263] Figure 12A 、 Figure 12B and Figure 12CAn example of a re-sharding of a sharded database when a new shard is added to the database is depicted. In Figure 12A In the example, the sharded database includes shards 1220, 1230, 1240, and 1250. Currently, shard 1220 includes chunks 1201, 1202, 1203, and 1204. Shard 1230 includes chunks 1205, 1206, 1207, and 1208. Shard 1240 includes chunks 1209, 1210, 1211, and 1212. In addition, shard 1250 has just been added to the sharded database. However, since shard 1250 is new, it does not yet have any chunks.

[0264] In Figure 12B In the example, the sharded database includes shards 1220, 1230, 1240, and 1250. Currently, shard 1220 includes chunks 1201, 1202, 1203, and 1204. Shard 1230 includes chunks 1205, 1206, 1207, and 1208. Shard 1240 includes chunks 1209, 1210, 1211, and 1212. In addition, shard 1250 has just been added to the sharded database. However, since shard 1250 is new, it does not yet have any chunks.

[0265] Figure 12C An example of the final result of the re-sharding process is shown. After the re-sharding is complete, the chunks in the sharded database are redistributed as follows: shard 1220 includes chunks 1201, 1202, and 1203; shard 1230 includes chunks 1205, 1206, and 1207; shard 1240 includes chunks 1209, 1210, and 1211; and new shard 1250 includes chunks 1204, 1208, and 1212, which were redistributed from other shards in the sharded database. Thus, the sharded database has automatically redistributed data in the sharded database in response to the addition of new shard 1250. This automatic redistribution of data in response to the addition of a new shard provides load balancing in the sharded database.

[0266] Figure 13 A flowchart of a process 1300 for re-sharding according to one embodiment is shown. In step 1302, the shard director identifies a source chunk to move from a source shard to a destination shard. As previously described, this can be done automatically in response to detecting significant load on the source shard or in response to detecting a new shard added to the sharded database. In this case, the source chunk to be moved can be identified based on a random selection from among all chunks located on the source shard. In another embodiment, the source chunk can be identified in response to a database command provided by a database administrator, such as a command to split a chunk or move a chunk. Process 1300 can then proceed to step 1304.

[0267] In step 1304, the shard director can optionally determine whether the sharded database uses active / active replication. If the sharded database uses active / active replication, then a duplicate copy of the source chunk will exist in the replica that can be used to service database queries that need to access data in the source chunk while the re-sharding process continues. If the sharded database does not use active / active replication, then a duplicate copy of the source chunk that can be used to service database queries that need to access data in the source chunk while the re-sharding process continues can not exist in the replica. If the sharded database uses active / active replication, then the process 1300 can proceed to step 1306. If the sharded database does not use active / active replication, then the process 1300 can proceed to step 1312.

[0268] In step 1306, since the shard director knows that a copy of the source chunk is available from the replicated source, the shard director updates the routing table to indicate that the source chunk is not available on the source, but that the data contained in the source chunk is available from the replicated shard that contains a copy of the source chunk. The process 1300 can proceed to step 1308.

[0269] In step 1308, the shard director notifies the client applications of the update to the routing table. The notification will also include mapping data for the new mapping to indicate that the data contained in the source chunk is now available from the replicated shard. This will thus let the client applications know that for queries that need to access the source chunk while the source chunk is not available on the source shard, they can continue to route the queries directly to the replicated shard. This ensures that the client applications do not experience downtime when they need to access the data they require from the source chunk while the source chunk is being migrated to the new destination shard. The process 1300 can then proceed to step 1310.

[0270] In step 1310, the shard director instructs the shard to copy the source chunk from the source shard to the destination shard. The process 1300 can then proceed to step 1320.

[0271] In step 1312, the shard director instructs the destination shard to store a fuzzy copy of the source chunk in the destination shard. The fuzzy copy is a copy that is generated from the source chunk while the source chunk is still available to process further queries. Thus, if the source chunk is modified during the copying process, the fuzzy copy of the source chunk can not be identical to the source chunk. Any additional modifications or updates made to the source chunk will not be reflected in the fuzzy copy. The process 1300 can then proceed to step 1314.

[0272] In step 1314, process 1300 determines whether there are additional changes to the source block on the source shard that have not yet been reflected in the fuzzy copy of the source block stored in the destination shard. If there are additional changes that have not yet been reflected in the fuzzy copy, then process 1300 can proceed to step 1316. If there are no additional changes that have not yet been reflected in the fuzzy copy, then the fuzzy copy of the source block and the source block are now identical, and process 1300 proceeds to step 1318.

[0273] In step 1316, process 1300 copies any additional changes made to the source block but not yet reflected in the fuzzy copy to the fuzzy copy. Process 1300 then returns to step 1314.

[0274] In step 1318, the fuzzy copy of the source block and the source block are now identical, so the shard director makes the source block on the source shard read-only. Thus, the source shard can still be used for read queries, but cannot be updated. The process then proceeds to step 1320.

[0275] In step 1320, the shard director updates the routing table to indicate that the source block is now available on the destination shard and is no longer available at the source shard. Process 1300 then proceeds to step 1322.

[0276] In step 1322, the shard director notifies the client application that an update has been made to the routing table. The shard director sends the updated mapping data from the routing table to indicate that the source block is now available at the destination shard, not the source shard. Thus, if a query requires access to data stored in the block, the client application can use this updated mapping data included in the notification to route subsequent queries directly to the destination shard. Process 1300 can then proceed to step 1324.

[0277] In step 1324, the shard director can instruct the source shard to delete the source block from the source shard, thereby freeing up storage space on the source shard. Process 1300 can then end.

[0278] Routing Queries

[0279] Most queries in a typical OLTP client application are short and must be processed with millisecond latency. During the routing of a query to the appropriate shard, additional network hops and resolution can introduce latency that is unacceptable to an OLTP client application. This disclosure provides techniques for minimizing latency when routing queries sent from a client application.

[0280] Return Figure 2In some cases, a data request from a client application 210 will require data from a single shard. Such a data request is referred to as a single-shard query. Single-shard queries will represent the majority of data requests for a typical OLTP client application, as shards 260A, 260B, and 260C have been configured such that the chunks in each shard contain corresponding partitions from tables of a table family. Thus, most queries that rely on data from a table family will likely be serviced by a single shard, as the relevant data for that table family is collocated on the same shard. Likewise, the use of replicated tables for relatively small and / or static reference tables improves the likelihood that queries are processed as single-shard queries.

[0281] In other cases, a data request from a client application 210 will require data from multiple shards. Such a data request is referred to as a cross-shard query. Processing a cross-shard query is typically slower than processing a single-shard query, as it requires joining data from multiple shards. Cross-shard queries are typically used to generate reports and collect statistics that require data from multiple shards. Such cross-shard queries are typically not performance critical.

[0282] As previously described, shard directory 230 maintains a routing table that maps a list of chunks hosted by each shard to a range of hash values associated with each chunk. Thus, the routing table can be used to determine which shard contains a chunk that includes data for a shard key value or set of shard key values. In one embodiment, where a database is sharded via compound sharding, the routing table can also include mapping information for combinations of shard keys and super-shard keys. Thus, in the case of a compound-sharded database, for a given set of shard key values, the routing table can be used to determine which shard contains a chunk that includes data for the set of shard key values.

[0283] In one embodiment, the routing table maintained by the shard directory 230 is copied and accessible by a shard coordinator. The shard coordinator is a component that helps route queries to the appropriate shard. In one embodiment, the shard coordinator is implemented as part of the shard director 220. In another embodiment, the shard coordinator is implemented in the client application 210. In another embodiment, the shard coordinator is implemented on one or more of each individual shard 260 in the sharded database 250. In another embodiment, the shard coordinator is implemented as a component separate from the shard director 220 and the individual shards 260. For example, the shard coordinator can be implemented as a software component external to the shard director 220 and the individual shards 260. In one embodiment, the software component can be part of the sharded database 250. In another embodiment, the software component can be external to the sharded database 250. In one embodiment, the software component can be external to the sharded database 250 and the client application 210.

[0284] In one embodiment, the shard coordinator functionality can be distributed across multiple software components SI through SN that exist between the client application 210 and the sharded database 250. The software components SI through SN can have different accessibility to the client application 210 and / or the sharded database 260. This accessibility reflects various communication characteristics, including but not limited to physical proximity, bandwidth, availability of computing resources, workload, and other characteristics that will affect the accessibility of the software components SI through SN.

[0285] In one embodiment, software component SI can be more readily accessible to client application 210 than software component S2. Likewise, software component S2 can be more readily accessible to client application 210 than software component S3, and so on. Thus, software component SI is considered closest to client application 210 because it is most readily accessible to client application 210, and software component SN is considered farthest from client application 210 because it is least accessible to client application 210. In one embodiment, when a database request requiring processing by a shard coordinator is created at client application 210, the software component closest to client application 210 that is available is used as the shard coordinator to attempt to process the database request. If the software component closest to client application 210 that is available cannot process the database request, then the next closest software component is attempted, and so on, until the database request is successfully processed by a shard coordinator. For example, a software component can not be able to process a database request if it does not have sufficient mapping data to properly route the database request. By using the software component closest to client application 210 that is available to act as the shard coordinator that will process the database request, the system can provide improved performance in processing requests because the closest available software component has improved accessibility compared to other software components.

[0286] Proxy-based query routing for single-shard queries with known keys

[0287] In one embodiment, client application 210 cannot directly route a query in a database request to a shard because it cannot independently determine which destination shard(s) contain the data needed to process the query. Client application 210 can use a shard coordinator to perform proxy-based routing to the destination shard(s).

[0288] Figure 14 A flowchart of a process 1400 for proxy-based query routing for single-shard queries is shown, according to one embodiment. In step 1402, client application 210 sends a database request including a single-shard query to a shard coordinator. The database request includes a shard key, and optionally, a super-shard key as well. Process 1400 then proceeds to step 1404.

[0289] In step 1404, the shard coordinator can use the key and a routing table to determine which destination shard contains the data needed to process the single-shard query of the database request. Process 1400 then proceeds to step 1406.

[0290] In step 1406, the shard coordinator redirects the client application 210 directly to the target shard containing the data needed to process the single-shard query. By redirecting the client application 210 to the target shard rather than merely returning the identity of the target shard to the client application 210, the process 1400 can remove unnecessary hops in processing single-shard queries, thereby improving system latency. The process 1400 then proceeds to step 1408.

[0291] In step 1408, the client application 210 can execute the single-shard query directly on the target shard and receive the results of the query directly from the target shard. The process 1400 then proceeds to step 1410.

[0292] In step 1410, the target shard can also optionally return mapping data identifying all key ranges stored by the particular shard. Such mapping data can be cached directly by the client application 210 or in a connection pool accessible to the client application 210. The mapping data will allow the client application 210 to route subsequent queries having shard keys matching the cached mapping data directly to the target shard without needing to consult the shard coordinator. This will improve the performance of subsequent database requests to the target shard. The process 1400 can then end.

[0293] Proxy-based query routing for unknown shard keys

[0294] FIG. 15 illustrates a flowchart of a process 1500 for proxy-based query routing when a database request sent by the client application 210 does not include a shard key. This process 1500 can be used, for example, when a legacy client application is being used to access a sharded database and the database requests have not yet been updated to include shard keys. Alternatively, this process 1500 can be used when the client application 210 is performing a cross-shard query that requires data from multiple shards.

[0295] In step 1502, the client application 210 sends a database request including a query to a shard coordinator that does not include a shard key. The process 1500 then proceeds to step 1504.

[0296] In step 1504, the shard coordinator parses the query in the database request and determines the value of the shard key for the query. The shard coordinator can compare the shard key to a routing table to determine the target shard(s) containing the data needed to process the query. The process 1500 then proceeds to step 1506.

[0297] In step 1506, the shard coordinator sends the query to each shard identified in step 1504. The process 1500 then proceeds to step 1508.

[0298] In step 1508, each of the shard(s) processes the query and returns the results of processing the query to the shard coordinator. Processing then proceeds to step 1510.

[0299] In step 1510, the shard coordinator aggregates and filters the results received from the shard(s) in step 1508. In addition, the shard coordinator returns the aggregated results to the client application 210. Processing 1500 can then end.

[0300] In another embodiment, Figure 15B A flowchart of processing 1501 for proxy-based query routing is shown when the database request sent by the client application 210 does not include a shard key. In step 1522, the client application 210 sends a database request including a query to the shard coordinator that does not include a shard key. Processing 1501 then proceeds to step 1524.

[0301] In step 1524, the shard coordinator parses the query in the database request and determines the value of the shard key for the query. The shard coordinator can then compare the shard key to a routing table to determine the shard(s) that contain the data needed to process the query. Processing 1501 then proceeds to step 1526.

[0302] In step 1526, the shard coordinator sends the predicates of the query to each of the shards identified in step 1524. Processing 1501 then proceeds to step 1528.

[0303] In step 1528, each of the shard(s) processes the predicates and sends the predicate results to the shard coordinator. Processing then proceeds to step 1530.

[0304] In step 1530, the shard coordinator receives the predicate results from the shard(s) and then applies the query to the predicate results. The final results of applying the query to the predicate results can then be returned to the client application 210. Processing 1501 can then end.

[0305] Direct routing to shards using cached mapping data

[0306] In one embodiment, the client application 210 can use cached mapping data to route queries directly to a particular shard 260. In this example, the client application 210 can use mapping data cached in the client application 210 or cached in a connection pool accessible to the client application 210 to determine which particular shard 260 contains data for a particular query. For example, when a client connection pool for the client application 210 first connects to a particular shard, it can need to use a shard coordinator to route a first query because the client application 210 does not know which particular shard is needed to process the first query. The particular shard can process the first query and return a response. In addition to providing a response to the first query, the particular shard also returns mapping data that identifies all key ranges stored by the particular shard. The client application 210 or connection pool can cache the key range mapping for the particular shard.

[0307] Thus, for subsequent queries that require data stored on the particular shard, the client application 210 can use the cached mapping data to route the query directly to the particular shard without needing to access an intermediary shard coordinator. Thus, in the case of a single shard query, the client application 210 can route the subsequent query directly to the particular shard and return the results directly to the client application 210. Directly routing to a shard using a known key can provide improved latency because the client application 210 can directly access the shard without needing to consult an intermediary shard coordinator in order to process the query.

[0308] In one embodiment, when a query is routed directly to a shard, the predicate of the query can be automatically added to the query to limit the query output by the shard to the provided key value(s). For example, if "cust_id = 123" is specified when establishing a database connection, and then "SELECT * FROM customers" is executed through the database connection, then the query can be rewritten as "SELECT * FROM customers where cust_id = 123". The query can be rewritten by the client application, connection pool, or shard coordinator. This behavior is optional and can be controlled by a user-defined parameter for the sharded database.

[0309] Further details regarding direct routing of queries between client applications 210 and shards can be found in U.S. Patent Application No. 15 / 133,972, filed April 20, 2016, SYSTEM AND METHOD FOR PROVIDING DIRECT ACCESS TO A SHARDED DATABASE, and U.S. Patent Application No. 15 / 133,979, filed April 20, 2016, SYSTEM AND METHOD FOR PROVIDING ACCESS TO A SHARDED DATABASE USING A CACHE AND A SHARD TECHNOLOGY, the contents of each of which are incorporated by reference herein as if fully set forth herein.

[0310] Direct routing to shards based on client-resolved queries

[0311] In one embodiment, client applications 210 can resolve queries in order to determine the shard(s) that need to be used to process the query without consulting a shard coordinator. In one instance, a query that includes a "SELECT *" statement across a sharded table can be resolved by a client application 210. Based on the resolution, the client application 210 can identify that the "SELECT *" statement will require data from all shards in the sharded database. Accordingly, the client application 210 can route the query directly to all shards in the sharded database rather than using an intermediary shard coordinator to route the query. For example, the client application 210 can route the query directly to all shards identified in the cached mapping data accessible to the client application 210.

[0312] Combined processing for query routing

[0313] In one embodiment, various proxy-based routing techniques and direct routing techniques can be combined into a combined processing for query routing that uses direct routing when the client application 210 is able to perform direct routing and proxy-based routing when the client application 210 is unable to perform direct routing. This combined processing provides the benefit of improved latency of direct routing while retaining the ability to perform cross-shard queries using proxy-based routing and the ability to access shard(s) when the client application 210 does not know which shard(s) contain data necessary to process a query.

[0314] Figure 16An example flowchart for combined processing of query routing is shown. In step 1601, client application 210 determines if it has access to the shard key. If the client application has access to the shard key, processing 1600 can proceed to step 1602. If the client application 210 does not have access to the shard key, processing 1600 can proceed to step 1612.

[0315] In step 1602, client application 210 determines if it has access to cached mapping data for the shard key. Cached mapping data is mapping data that includes key value ranges for a particular shard(s) in the sharded database. If client application 210 can use the shard key to determine from the cached mapping data which shard(s) contain data necessary to process the query, processing 1600 can proceed to step 1604. If client application 210 cannot use the shard key to determine from the cached mapping data which shard(s) contain data necessary to process the query, processing 1600 can proceed to step 1606.

[0316] In step 1604, client application 210 can directly connect to the shard(s) containing data necessary to process the query and directly execute the query on the shard(s). The shard(s) return the results of the query directly to client application 210. Processing 1600 can then end.

[0317] In step 1606, client application 210 can determine if the query is simple enough to determine which shard(s) contain data necessary to process the query. For example, if the query includes a "SELECT * " statement, client application 210 can determine that the query needs to be processed by all shard(s) in the sharded database. If client application 210 determines that the query is simple enough, processing 1600 can proceed to step 1608, otherwise processing 1600 can proceed to step 1612.

[0318] In step 1608, client application 210 can analyze the query to determine which target shard(s) contain data necessary to process the query. Processing 1600 can then proceed to step 1610.

[0319] In step 1610, client application 210 can directly connect to the shard(s) containing data necessary to process the query and directly execute the query on the shard(s). The shard(s) return the results of the query directly to client application 210. Processing 1600 can then end.

[0320] In step 1612, the client application 210 can send the query to the shard coordinator to assist in proxy-based routing of the query. The process 1600 can then proceed to step 1614.

[0321] In step 1614, the shard coordinator can analyze the query to determine the shard key of the query. The shard coordinator can use the shard key and a routing table to determine which shard(s) contain the data necessary to process the query. The process 1600 can proceed to step 1616.

[0322] In step 1616, the shard coordinator can determine whether the query is a single-shard query. If the query is a single-shard query, then the process 1600 can proceed to step 1620. If the query is a cross-shard query, then the process 1600 can proceed to step 1618.

[0323] In step 1618, the shard coordinator facilitates query processing for cross-shard queries. In one embodiment, the shard coordinator will send the query to each of the target shard(s), and the target shard(s) will process the query. The target shard(s) will return the results to the shard coordinator, which will aggregate and filter the results. The shard coordinator sends the final aggregated results of the cross-shard query back to the client application 210. The process 1600 can then end.

[0324] In step 1620, the shard coordinator can redirect the client application 210 to the target shard that contains the data necessary to process the single-shard query. The process 1600 can then proceed to step 1622.

[0325] In step 1622, the client application 210 can directly connect to the target shard and execute the query on the target shard. The results of executing the query are returned directly from the target shard to the client application 210. The process 1600 can then end.

[0326] Automatic propagation of mode changes

[0327] Changes to database schema can be made via a data definition language (DDL). In the case of a sharded database with a large number of shards, it is important to have an efficient way to propagate schema changes across the sharded database. In some conventional sharded databases, database schema changes across the sharded database are done by scripts written by customers in a scripting language or application. The application code or scripting language is used to propagate schema changes across all shards in the sharded database by manually connecting to each shard and applying the code or scripting language to each shard. Such application code or scripting language is not as uniform or robust as DDL. Moreover, such code or scripting language is prone to user error. In other sharded databases, database schema changes across the sharded database are done manually by a database administrator applying DDL for schema changes to each shard individually. This approach is undesirable because some sharded databases include hundreds or thousands of shards, and this manual work by the database administrator is time consuming and prone to error.

[0328] The present system provides automatic propagation of database schema changes using DDL to shards in a sharded database. In one embodiment, the shard directory 230 can maintain a history of schema modification instructions that have been received. Each schema modification instruction is written as a DDL request. These DDL requests can be entered directly at the shard directory 230 by a database administrator.

[0329] The shard directory 230 can maintain a history log that indicates the status of applying DDL requests to each particular shard in the sharded database. Thus, the history logic indicates which DDL requests have been applied to a shard and which DDL requests are pending to be applied to a shard. In some cases, DDL requests can have been applied to some, but not all, shards. In one embodiment, a database administrator can indicate a schedule of when pending DDL requests should be applied to the sharded database. The schedule can be sent to the shard director 220, and the shard director 220 can delay executing pending DDL requests until the time specified in the schedule.

[0330] In one embodiment, the shard directory 230 can push pending DDL requests to the shard director 220. The shard director 220 is responsible for applying the pending DDL requests at the shards 260. The shard director 220 can access a history log that indicates which shards have applied any historical DDL requests and pending DDL requests.

[0331] The shard director 220 can act as a database client with respect to the shards 260. Thus, the shard director 220 can receive the pending DDL requests from the shard catalog 230, connect to each of the shards 260, and execute the pending DDL requests on each of the shards 260. In one embodiment, the execution of the schema changes on each shard proceeds in parallel. In one embodiment, the shards 260 can return status messages to the shard director 220 to indicate whether the pending DDL requests were successfully executed on a given shard. The shard director 220 can relay the received status messages to the shard catalog 230 so that the database administrator can view them. Likewise, a history log can be updated to indicate whether the DDL requests were successfully applied to a particular shard.

[0332] In one embodiment, the shard director 220 can act as a listener that can receive notifications providing status information from the shards 260. The status information can include whether a shard is available or unavailable. In one embodiment, the shard director 220 can delay applying pending DDL requests to a shard until the shard is available. For example, if a shard database has three shards A, B, and C. Shards A and B are available, but shard C is unavailable. The shard director 220 can immediately apply the DDL requests to shards A and B and update the history log accordingly. The shard director can then wait until shard C is available, as the shard director will receive a status update from shard C when it becomes available. Once shard C becomes available, the shard director 220 can use the history log to determine all pending DDL requests that have not yet been applied to shard C and apply them in turn until the schema of shard C is fully updated with all outstanding schema modification instructions.

[0333] In another embodiment, the status information can include an indication about the workload that the shard is experiencing. In one embodiment, the shard director 220 can delay applying pending DDL requests to a shard until all shards in the shard database have a workload that is less than a threshold setting. By delaying the application of the pending DDL, the shard director 220 can ensure that the DDL is only applied to the shards in the shard database when the performance impact is acceptable.

[0334] When a shard director 220 executes a given DDL request on a shard in a sharded database, the shard has sufficient metadata stored on it to recognize that the shard is only one database instance in a larger sharded database. Thus, the shard can execute the DDL request by only executing the portion of the DDL request that will affect the shard. For example, assume the DDL request is a "CREATE SHARDED TABLE" request. In a normal database instance, a "CREATE TABLE" request would create a fully-formed standalone database table along with the associated metadata for the table data object. However, in a shard of the present system, when it receives a "CREATE SHARDED TABLE" DDL request, instead of creating a fully-formed standalone database table, the shard can use the stored metadata about the configuration of the sharded database as a whole to create a partition of the shard table that will be stored in the chunk found on the shard itself, without having to create the remainder of the table that will be stored in chunks located in different shards. In this way, DDL propagation can improve system performance by allowing shards to only execute the portion of a DDL request that affects their own shard, without having to execute the full contents of the DDL request.

[0335] This system allows a database administrator to make schema changes across a large set of shards in a sharded database automatically. The database administrator need only provide the shard catalog with a DDL specifying the schema changes, and these changes will automatically propagate to each shard in the sharded database for execution. This is not an application code or script for propagating DDL requests, nor does it require the database administrator to manually connect to each individual shard to propagate DDL schema changes.

[0336] Syntax

[0337] While the present disclosure provides various examples of syntax for how to create, manage, and manipulate a sharded database, these examples are merely illustrative. The present system can be implemented using an existing relational database coding language or query language, such as Structured Query Language (SQL). This means that legacy systems can be easily upgraded, migrated, or connected to a system that includes the sharded database teachings described herein, as no significant changes to SQL will be required. The use of Data Manipulation Language (DML) can take advantage of the benefits of the present system without any changes. Furthermore, the use of DDL requires only minor changes to support the keywords necessary to implement the shard organization of a sharded database.

[0338] Database Overview

[0339] Embodiments of the present invention are used in the context of a database management system (DBMS). Thus, a description of an example DBMS is provided.

[0340] Generally, a server such as a database server is a combination of integrated software components and allocation of computing resources such as memory, nodes, and processes on the nodes to execute the integrated software components, where the combination of software and computing resources is dedicated to providing a particular type of functionality on behalf of clients of the server. A database server governs and facilitates access to a particular database, processing requests by clients to access the database.

[0341] A database includes data and metadata stored on persistent storage mechanisms such as a collection of hard disks. The data and metadata can be logically stored in the database according to a relational and / or object-relational database structure, for example.

[0342] Users interact with a database server of a DBMS by submitting commands to the database server, which cause the database server to perform operations on data stored in the database. A user can be one or more applications running on a client computer that interacts with the database server. Multiple users can also be collectively referred to herein as users.

[0343] A database command can be in the form of a database statement. For a database server to process a database statement, the database statement must conform to a database language supported by the database server. One non-limiting example of a database language supported by many database servers is SQL, including proprietary forms of SQL supported by database servers such as Oracle (e.g., Oracle Database 1 lg). SQL data definition language ("DDL") instructions are issued to a database server to create or configure database objects such as tables, views, or complex types. Data manipulation language ("DML") instructions are issued to a DBMS to manage data stored within a database structure. For example, SELECT, INSERT, UPDATE, and DELETE are common examples of DML instructions found in some SQL implementations. SQL / XML is a common extension of SQL used when manipulating XML data in an object-relational database.

[0344] Generally, data is stored in a database in one or more data containers, each container containing records, and the data within each record is organized into one or more fields. In a relational database system, the data containers are commonly referred to as tables, the records are referred to as rows, and the fields are referred to as columns. In an object-oriented database, the data containers are commonly referred to as object classes, the records are referred to as objects, and the fields are referred to as attributes. Other database architectures can use other terminology. Systems implementing the present invention are not limited to any particular type of data container or database architecture. However, for the purposes of explanation, the examples and terminology used herein will be those commonly associated with a relational or object-relational database. Thus, the terms "table," "row," and "column" are used herein to refer to data containers, records, and fields, respectively.

[0345] A multi-node database management system is composed of interconnected nodes that share access to the same database. Typically, the nodes are interconnected via a network and share access to shared storage devices to varying degrees, e.g., sharing access to a set of disk drives and data blocks stored thereon. The nodes in a multi-node database system can be in the form of a set of computers (e.g., workstations, personal computers) interconnected via a network. Alternatively, the nodes can be nodes of a grid, the grid composed of nodes in the form of server blades interconnected with other server blades on a rack.

[0346] Each node in a multi-node database system hosts a database server. A server, such as a database server, is a combination of an integrated software component and an allocation of computing resources (such as memory, a node, and a process on the node for executing the integrated software component on a processor), where the combination of software and computing resources is dedicated to performing a particular function on behalf of one or more clients.

[0347] Resources from multiple nodes in a multi-node database system can be allocated to run the software of a particular database server. Each combination of software and allocation of resources from a node is a server referred to herein as a "server instance" or "instance." A database server can include multiple database instances, some or all of which run on separate computers, including separate server blades.

[0348] Query optimization and execution plan

[0349] Query optimization generates one or more different candidate execution plans for a query, which the query optimizer evaluates to determine which execution plan should be used to compute the query.

[0350] An execution plan can be represented by a graph of interconnected nodes (referred to herein as operators or row sources), each of which corresponds to a step of the execution plan (referred to herein as an execution plan operation). The hierarchy of the graph (i.e., a directed tree) represents the order of execution of the execution plan operations and how data flows between each of the execution plan operations in the execution plan. Execution plan operators generate a collection of rows (which can be referred to as a table) as output, and execution plan operations include, for example, table scans, index scans, sort-merge joins, nested-loop joins, filters, and importantly, outer joins.

[0351] A query optimizer can optimize a query by transforming the query. In general, transforming a query involves rewriting the query as another semantically equivalent query that should produce the same results and potentially can be executed more efficiently, i.e., for which a potentially more efficient and less costly execution plan can be generated. Examples of query transformations include view merging, subquery unnesting, predicate movement and pushdown, common subexpression elimination, outer to inner join conversion, materialized view rewriting, and star transformation.

[0352] Hardware Overview

[0353] Reference will now be made to Figure 17 , Figure 17 is a block diagram illustrating a basic computing device 1700 in which one or more example embodiments of the application can be implemented. The computing device 1700 and its components, including their connections, relationships, and functions, is meant to be exemplary only, and is not meant to limit implementations of the one or more example embodiments of the application. Other computing devices suitable for implementing the one or more example embodiments of the application can have different components, including components with different connections, relationships, and functions.

[0354] The computing device 1700 can include a bus 1702 or other communication mechanism for addressing the main memory 1706 and for communicating data between the various components of the device 1700.

[0355] The computing device 1700 can also include one or more hardware processors 1704 coupled with the bus 1702 for processing information. The hardware processor(s) 1704 can be a general purpose microprocessor, a system on a chip (SoC), or other processor.

[0356] The main memory 1706, such as a random access memory (RAM) or other dynamic storage device, also can be coupled to the bus 1702 for storing information and instructions to be executed by the processor(s) 1704. The main memory 1706 also can be used for storing temporary variables or other intermediate information during execution of software instructions to be executed by the processor(s) 1704.

[0357] Software instructions, when stored in a storage medium accessible to the processor(s) 1704, cause the computing device 1700 to become a special-purpose machine programmed to perform the operations specified in the software instructions. The terms "software," "software instructions," "computer program," "computer-executable instructions," and "processor-executable instructions" should be construed broadly to cover any machine-readable information (whether human-readable or not) used to direct the computing device to perform particular operations, and include, but are not limited to, application software, desktop applications, scripts, binary files, operating systems, device drivers, boot loaders, shells, utilities, system software, JAVASCRIPT, web pages, web applications, plug-ins, embedded software, microcode, compilers, debuggers, interpreters, virtual machines, linkers, and text editors.

[0358] The computing device 1700 can also include a read-only memory (ROM) 1708 or other static storage device coupled to the bus 1702 for storing static information and software instructions for the processor(s) 1704.

[0359] One or more mass storage devices 1710 can be coupled to the bus 1702 for persistently storing information and software instructions (e.g., programs) that are appropriate for the operation of the computing device. The mass storage devices 1710 can be implemented, for example, with a magnetic, optical, solid-state, magnetic-optical, flash, or any other available mass storage technology, and can be fixed or removable. The mass storage devices 1710 can be shared with other devices, or can be dedicated to the computing device. Typically, at least one of the mass storage devices 1710 (e.g., a device's main hard disk) stores an operating system and programs implementing various aspects of the present disclosure, as well as various data files.

[0360] The computing device 1700 can be coupled via the bus 1702 to a display 1712, such as a liquid crystal display (LCD) or other electronic visual display, for displaying information to a computer user. In some configurations, a touch-sensitive surface that incorporates touch detection technology (e.g., resistive, capacitive, etc.) can be overlaid on the display 1712 to form a touch-sensitive display for communicating touch gestures (e.g., finger or stylus) input to the processor(s) 1704.

[0361] An input device 1714, including alphanumeric and other keys, can be coupled to the bus 1702 for communicating information and command selections to the processor(s) 1704. In addition to or alternatively with alphanumeric and other keys, the input device 1714 can include one or more physical buttons or switches, such as, for example, a power (on / off) button, a "home" button, a volume control button, etc.

[0362] Another type of user input device can be a cursor control 1716 (such as a mouse, trackball, or cursor direction keys) for communicating direction information and command selections to the processor 1704 and for controlling cursor movement on the display 1712. This input device typically has two degrees of freedom in two axes, a first axis (e.g., x) and a second axis (e.g., y), that allow the device to specify positions in a plane.

[0363] While in some configurations (such as the depicted configuration), one or more of the display 1712, the input device 1714, and the cursor control 1716 are external components (i.e., peripherals) of the computing device 1700, some or all of the display 1712, the input device 1714, and the cursor control 1716 are integrated as part of the form factor of the computing device 1700 in other configurations. Figure 17

[0364] The functions of the disclosed systems, methods, and modules can be performed by the computing device 1700 in response to the processor(s) 1704 executing one or more programs of software instructions contained in the main memory 1706. Such software instructions can be read into the main memory 1706 from another storage medium, such as the storage device(s) 1710. Execution of the software instructions contained in the main memory 1706 causes the processor(s) 1704 to perform the functions of the example embodiment(s).

[0365] While the functions and operations of the example embodiment(s) can be entirely implemented in software instructions, hardwired or programmable circuitry (e.g., ASICs, FPGAs, etc.) of the computing device 1700 can be used in other embodiments to perform the functions, either instead of or in conjunction with software instructions, depending on at hand particular implementation requirements.

[0366] As used herein, the term “storage medium” means any non-transitory medium that stores data and / or software instructions that cause a computing device to operate in a specific manner. Such storage media can include non-volatile media and / or volatile media. Non-volatile media includes, for example, non-volatile random-access memory (NVRAM), flash memory, optical disks, magnetic disks, or solid-state drives such as the storage device 1710. Volatile media includes dynamic memory, such as the main memory 1706. Common forms of storage media include, for example, a floppy disk, a flexible disk, a hard disk, a solid-state drive, a magnetic tape, or any other magnetic data storage medium, a CD-ROM, any other optical data storage medium, any physical medium with patterns of holes, a RAM, a PROM, and EPROM, a FLASH-EPROM, NVRAM, flash memory, any other memory chip or cartridge. ​

[0367] Storage media and computer readable media differ in that storage media typically are passive data storage devices that do not store data on their own, active component parts, but that require a power source, e.g., various

[0368] Carrying one or more sequences of software instructions to (one or more) processor(s) 1704 for execution can involve various forms of transmission involving electrical, optical, or electromagnetic signals carried in the air or through a variety of different media, such as coaxial cables, copper wire, and fiber optic cables, or wires comprising bus 1702. As used herein, a "signal" can be a waveform, a sequence of waveforms, or any other form of electromagnetic or optical energy that can be used to convey information.

[0369] Computing device 1700 can also include one or more communication interfaces 1718 coupled to bus 1702. Communication interfaces 1718 provide a two-way data communication coupling to wired or wireless network links 1720 that are connected to a local network 1722 (e.g., Ethernet, wireless LAN, cellular telephone network, Bluetooth wireless network, etc.). Communication interfaces 1718 send and receive electrical, electromagnetic or optical signals that carry digital data streams representing various types of information. For example, communication interfaces 1718 can be a wired network interface card, a wireless network interface card with an integrated radio antenna, or a modem (e.g., ISDN, DSL, or cable modem).

[0370] The network link(s) 1720 typically provide data communication through one or more networks to other data devices. For example, a network link 1720 can provide a connection to a host computer 1724 through a local network 1722 to a data equipment operated by an Internet Service Provider (ISP) 1726. The ISP 1726 in turn provides data communication services through the world wide packet data communication network now commonly referred to as the “Internet” 1728. The local network(s) 1722 and the Internet 1728 use electrical, electromagnetic, or optical signals that carry digital data streams. The signals through the various networks and the signals on or

[0371] The computing device 1700 can send messages and receive data, including program code, through the network(s), network link(s) 1720, and communication interface(s) 1718. In the Internet example, a server 1730 might transmit a requested code for an application program through the Internet 1728, ISP 1726, local network(s) 1722 and communication interface(s) 1718.

[0372] The received code can be executed by processor 1704 as it is received, and / or stored in storage device 170 or other non-volatile storage for later execution.

[0373] Software Overview

[0374] Figure 18 is a block diagram of a basic software system 1800 that can be used to control the operation of the computing device 1700. The software system 1800 and its components, including their connections, relationships, and functions, is meant to be exemplary only and not meant to limit implementations of the example embodiment(s). Other software systems suitable for implementing the example embodiment(s) can have different components, including components with different connections, relationships, and functions.

[0375] The software system 1800 is provided to direct the operation of the computing device 1700. The software system 1800 includes a kernel or operating system (OS) 1810, which can be stored in the system memory (RAM) 1706 and / or the fixed (hard drive or flash memory) 1710 of the computing device 1700. Among other things, the OS 1810 is employed by the computing device 1700 to manage the hardware components of the computing device 1700 and to provide common services and a platform for its other software.

[0376] OS 1810 manages low-level aspects of computer operation, including managing execution of processes, memory allocation, file input and output (I / O), and device I / O. One or more applications, represented as 1802A, 1802B, 1802C... 1802N, can be "loaded" (e.g., transferred from fixed storage 1710 into memory 1706) for execution by system 1800. The applications or other software intended for use on device 1800 can also be stored as downloadable computer

[0377] Software system 1800 includes graphical user interface (GUI) 1815, for receiving user commands and data in a graphical (e.g., "point and click" or "touch gesture") fashion. These inputs, in turn, can be acted upon by system 1800 in response to instructions from operating system 1810 and / or application(s) 1802. GUI 1815 also serves to display results of operation from

[0378] OS 1810 can execute directly on the bare hardware 1820 of device 1700, such as the processor(s) 1704. Alternatively, a hypervisor or virtual machine monitor (VMM) 1830 can intervene between the bare hardware 1820 and OS 1810. In this configuration, VMM 1830 acts as a software "cushion" or virtualization layer between OS 1810 and bare hardware 1820 of device 1700.

[0379] VMM 1830 instantiates and runs one or more virtual machine instances ("guest machines"). Each guest machine includes a "guest" operating system (such as OS 1810) and one or more applications (such as application(s) 1802) designed to execute on the guest operating system. VMM 1830 presents the guest operating system with a virtual operating platform and manages execution of the guest operating system.

[0380] In some cases, VMM 1830 can allow a guest operating system to run as if it were running directly on the bare hardware 1820 of device 1700. In these cases, the same version of the guest operating system configured to execute directly on bare hardware 1820 can also execute on VMM 1830 without modification or reconfiguration. In other words, in some cases, VMM 1830 can provide full hardware and CPU virtualization to the guest operating system.

[0381] In other cases, the guest operating system can be specially designed or configured to execute on the VMM 1830 to improve efficiency. In these cases, the guest operating system "knows" that it is executing on a virtual machine monitor. In other words, in some cases, the VMM 1830 can provide para-virtualization to the guest operating system.

[0382] The basic computer hardware and software described above is presented to illustrate the basic underlying computer components that can be used to implement the example embodiment(s). However, the example embodiment(s) are not necessarily limited to any particular computing environment or computer device configuration. Rather, the example embodiment(s) can be implemented in any type of system architecture or processing environment that will support the features and functionality of the example embodiment(s) presented herein, as would be known to those skilled in the art in light of this disclosure.

[0383] Extensions and Alternatives

[0384] While some of the figures described in the foregoing specification include a flow diagram, the steps of such a flow diagram can be executed in any order, unless otherwise specified in the figure. Further, some steps can be optional, can be performed in the background, and / or can be performed by different components than those depicted in the figures. All steps, operations, and functions of the flow diagrams described herein are intended to be indicative of operations performed in programming a special purpose computer or general computer to perform the described functions. In other words, each flow diagram in this disclosure, when taken in conjunction with the related text, is a guide to the programmer in programming a computer to perform the described functions, and is a plan or specification for performing the algorithms described in the flow diagrams. The level of skill in the field associated with this disclosure is known to be high, and therefore the flow diagrams and related text in this disclosure have been prepared with the sufficiency and detail that is normally expected in this field when technicians communicate with each other about programs, algorithms, and their implementation.

[0385] In the foregoing specification, the example embodiment(s) of the application have been described with reference to numerous specific details that can vary from implementation to implementation. Thus, the example embodiment(s) of the application should not be construed as limited to the specific examples set forth herein, but included as being among a full set of equivalent alternatives that can be claimed.

Claims

1. A method for sharded databases, comprising: The sharding directory maintains pattern data indicating the patterns used by each of the multiple shards in the sharding database; The sharding directory is a special database used to store the configuration data of the sharding database; Receive mode modification instructions for modifying the mode; In response to receiving the mode modification instruction, the following is executed automatically: The pattern data is updated at the shard directory based on the pattern modification instruction; as well as By performing the following operations on each specific shard among the plurality of shards, the modifier mode of all shards in the plurality of shards is made: Create a database connection to a specific shard; Send the mode modification instruction to the specific fragment; as well as The mode modification instruction is executed on the specific slice.

2. The method of claim 1, wherein each of the plurality of shards is its own relational database instance and does not share processors, memory, or disk storage devices with the other shards in the plurality of shards.

3. The method of claim 1, wherein the mode modification instruction is written in Data Definition Language (DDL).

4. The method of claim 1, further comprising sending the mode modification instruction from the shard directory to the shard directory.

5. The method of claim 4, further comprising: For each specific fragment among the plurality of fragments: After the mode modification instruction is executed on the specific fragment, a notification indicating the status of the execution of the mode modification instruction on the specific fragment is sent from the specific fragment to the fragment director; as well as The pattern data is updated by the fragment guide to indicate the status of executing the pattern modification instruction on the specific fragment.

6. The method of claim 5, further comprising: In response to the fragmentation guide detecting that the first fragment is unavailable, the execution of the mode modification instruction on the first fragment is postponed.

7. The method of claim 6, further comprising: In response to the fragmentation guide detecting that a first fragment is available, the mode modification instruction on the first fragment is executed.

8. The method of claim 1, wherein the schema modification instruction includes instructions for creating a shard table; and wherein executing the schema modification instruction on the particular shard includes creating a partition of the shard table in a data block to be stored on the particular shard, without creating a complete shard table on the particular shard.

9. The method of claim 1, further comprising: The sharding director receives notification status information about the workload of each specific shard from the plurality of shards; as well as For each specific fragment among the plurality of fragments: Perform the following steps: create a database connection, send the schema modification instruction to the specific shard, and execute the schema modification instruction only when the workload of each specific shard is below the threshold workload setting.

10. The method of claim 1, wherein the operation of modifying all the shards in the plurality of shards is performed in parallel for each of the plurality of shards.

11. A computing device, comprising: One or more hardware processors; as well as One or more storage media storing instructions that, when executed by the one or more hardware processors, cause the execution of the method as described in any one of claims 1 to 10.

12. A non-transitory computer-readable medium storing one or more instructions, which, when executed by one or more hardware processors, cause to perform the method as described in any one of claims 1 to 10.

13. An apparatus comprising one or more devices configured to perform the method as described in any one of claims 1 to 10.

Images