# US 11,907,251 B2 · Method and system for implementing distributed lobs > Agents: this data is available as JSON from the patentgrep API and MCP server — see https://patentgrep.com/llms.txt - Number: US 11,907,251 B2 - Document ID: US11907251B2 - Status: Granted - Kind: B2 - Application number: 15/690,894 - Filed: 2017-08-30 - Published: 2018-03-01 - Granted: 2024-02-20 - Earliest claimed: 2016-08-30 - Assignees: Oracle International Corporation - Applicants: Oracle International Corporation - Inventors: Lakshminarayanan Chidambaran; Srividya Tata; Geeta Arora; Rajendra S. Pingte; Sneha Chandrababu; Ramkrishan Kanodia - Web page: https://patentgrep.com/patent/US11907251B2 ## CPC - G06F 16/27: Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor - G06F 16/2219: {Large Object storage; Management thereof} - G06F 16/2282: {Tablespace storage structures; Management thereof} - G06F: ELECTRIC DIGITAL DATA PROCESSING (computer systems based on specific computational models G06N) ## Abstract Upon receiving a query at a local database server from a client directed to a LOB stored at a remote database server, a remote LOB locator is created comprising a local LOB locator indicating the LOB's location and a database link associated with the remote database server. The remote LOB locator is communicated to the client, which submits a request to the local database server to perform an operation directed to the LOB. The request is forwarded to the remote database server using the remote LOB locator, where the operation is performed. This mechanism is extended to work with sharded databases. Character set conversions are minimized when returning data to the client from the remote database server. Remote LOB locators may serve as bind variables for DML statements directed to local or remote tables. Local LOB locators may serve as bind variables for DML statements directed to remote tables. ## Claims ### Claim 1 A method for performing operations on large objects (LOBs) in a distributed database that includes at least a client and one or more database servers, the method comprising: receiving a query from the client at a database server of the one or more database servers, wherein the distributed database is distributed as at least a first portion of the distributed database and at least a second portion of the distributed database, the query specifying access to a column of at least a second portion of the distributed database, and the column comprises a LOB; in response to receipt of the query, creating, at the database server, a remote LOB locator at least by combining a local LOB locator, a database link count, and one or more database links, the one or more database links corresponding to one or more remote database servers of the one or more database servers, the local LOB locator points to a location at which the LOB is stored in the at least the second portion of the distributed database and the database link count specifies a number of the one or more database links in the remote LOB locator; sending the remote LOB locator to the client; after sending the remote LOB locator to the client, receiving the remote LOB locator in a request from the client to perform an operation that accesses data in the LOB; forwarding the request to the at least the second portion of the distributed database based at least in part on the remote LOB locator; and performing the operation that accesses the data in the LOB stored in the at least the second portion of the distributed database. ### Claim 2 (depends on 1) The method of claim 1, wherein the one or more database servers comprise a local database server, a remote database server on which the LOB is stored, and one or more additional remote database servers, the remote LOB locator further comprises one or more additional database links that respectively connect the local database server hosting the at least the first portion of the distributed database to the remote database server through the one or more additional remote database servers that respectively host at least a respective portion of the distributed database, each of the one or more additional database links is respectively associated with each of the one or more additional remote database servers and is appended to the local LOB locator, and the at least the first portion or the at least the second portion comprises the distributed database in its entirety or a smaller partition of the distributed database. ### Claim 3 (depends on 1) The method of claim 1, wherein the request to perform the operation comprises randomly updating the data for the LOB at any offset at least by performing a write operation for a provided number of characters or bytes from a location. ### Claim 4 (depends on 1) The method of claim 1, further comprising: retrieving at least a portion of the LOB from the at least the second portion of the distributed database at a remote database server, wherein the one or more database servers comprise at least the remote database server; and forwarding the at least the portion of the LOB and the remote LOB locator to the client. ### Claim 5 (depends on 1) The method of claim 1, wherein the one or more database servers comprise a local database server corresponding to the client and one or more remote database servers, the distributed database is sharded into multiple sharded tables that are different from each other, and the multiple sharded tables are distributed across the local database server and the one or more remote database servers. ### Claim 6 (depends on 1) The method of claim 1, wherein the one or more database servers comprise a local database server corresponding to the client and a remote database server, and the remote LOB locator further comprises information describing a character set used to store the LOB at the remote database server. ### Claim 7 (depends on 1) The method of claim 1, further comprising: returning a set of results of the query to the client, wherein the one or more database servers comprise a local database server corresponding to the client and a remote database server, and only a single character set conversion is performed to convert the set of results to a character set used by the client, wherein the remote database server that stores at least a part of the LOB uses a plurality of character sets. ### Claim 8 (depends on 1) The method of claim 1, wherein the one or more database servers comprise a local database server corresponding to the client and a plurality of remote database servers that further comprises a remote database server, and the request to perform the operation is forwarded to the remote database server via a connection that is selected from a pool of connections between the local database server and the plurality of remote database servers. ### Claim 9 (depends on 1) The method of claim 1, wherein the request to perform the operation comprises a DML (data manipulation language) statement in which the remote LOB locator or the local LOB locator is passed as a bind variable. ### Claim 10 (depends on 1) The method of claim 1, wherein the one or more database servers comprise a local database server corresponding to the client and a remote database server, and the database link associated with the remote database server comprises one or more of: information identifying a user, information identifying the database link, or information describing a length of the information identifying the database link. ### Claim 11 (depends on 2) The method of claim 2, wherein forwarding the request to perform the operation comprises: forwarding the request to perform the operation from the client to the local database server that stores thereupon the at least the first portion of the distributed database; forwarding the request to perform the operation from the local database server to the one or more additional remote database servers based at least in part on the one or more additional database links comprising the remote LOB locator, wherein the remote LOB locator comprises a database link count that indicate a total number of servers that are to be traversed to reach the LOB; and forwarding the request to perform the operation from the one or more additional remote database servers to a remote database server of the one or more database servers based at least in part on the database link associated with the remote database server, wherein the operation comprises a random access to the data in the column for the LOB. ### Claim 12 (depends on 11) The method of claim 11, wherein forwarding the request to perform the operation comprises: removing, at each of the one or more additional remote database servers, a first database link associated with the remote database server to which the request to perform the operation is being forwarded. ### Claim 13 (depends on 2) The method of claim 2, wherein an order in which the one or more additional database links associated with the one or more additional remote database servers are appended to the local LOB locator is determined based at least in part on a plurality of connections between the one or more additional remote database servers, the local database server, and the remote database server. ### Claim 14 (depends on 13) The method of claim 13, wherein the request to perform the operation comprises an offset-based access or a random access to the LOB and is forwarded to the remote database server based at least in part on the order in which the one or more additional database links associated with the one or more additional remote database servers are appended to the local LOB locator. ### Claim 15 (depends on 3) The method of claim 3, wherein the at least the first portion of the distributed database comprises an instance of the distributed database in its entirety and serves as a primary database, the at least the second portion of the distributed database comprises an identical instance of the distributed database in its entirety and serves as a standby database for the primary database, and wherein performing the operation on the LOB stored in the distributed database at the remote database server comprises: initiating a request to perform a READ operation on the LOB and each of one or more additional LOBs, the request initiated at a last common remote database server on a path from the client to the LOB and the one or more additional LOBs, wherein the one or more database servers comprise at least the last common remote database server and one or more remote database servers; forwarding the READ operation from the last common remote database server to the one or more remote database servers at which the LOB and the one or more additional LOBs are located; processing the request to perform the READ operation on the LOB and each of the one or more additional LOBs at the one or more remote database servers; receiving, at the last common remote database server, a result of the request to perform the READ operation on the LOB and each of the one or more additional LOBs; performing a comparison of the result of the request to perform the READ operation on the LOB and each of the one or more additional LOBs; and returning an additional result of the comparison to the client. ### Claim 16 (depends on 5) The method of claim 5, wherein the local database server communicates the request to perform the operation to a plurality of shards that are determined by sharding the distributed database and are distributed across the local database server and the one or more remote database servers, at least one of the plurality of shards comprising the LOB. ### Claim 17 (depends on 9) The method of claim 9, wherein the one or more database servers comprise a local database server corresponding to the client and a remote database server, and the DML statement is directed to a remote table stored on the remote database server or a local table stored on the local database server. ### Claim 18 A computer program product embodied on a non-transitory computer readable medium having stored thereon a sequence of instructions which, when executed by a processor, causes a set of acts for performing operations on large objects (LOBs) in a distributed database that includes at least a client and one or more database servers, the set of acts comprising: receiving a query from the client at a database server of the one or more database servers, wherein the distributed database is distributed as at least a first portion of the distributed database and at least a second portion of the distributed database, the query specifying access to a column of at least a second portion of the distributed database, and the column comprises a LOB; in response to receipt of the query, creating, at the database server, a remote LOB locator at least by combining a local LOB locator, a database link count, and one or more database links, the one or more database links corresponding to one or more remote database servers of the one or more database servers, the local LOB locator points to a location at which the LOB is stored in the at least the second portion of the distributed database and the database link count specifies a number of the one or more database links in the remote LOB locator; sending the remote LOB locator to the client; after sending the remote LOB locator to the client, receiving the remote LOB locator in a request from the client to perform an operation that accesses data in the LOB; forwarding the request to the at least the second portion of the distributed database based at least in part on the remote LOB locator; and performing the operation that accesses the data in the LOB stored in the at least the second portion of the distributed database. ### Claim 19 (depends on 18) The computer program product of claim 18, wherein the one or more database servers comprise a local database server, a remote database server on which the LOB is stored, and one or more additional remote database servers, the remote LOB locator further comprises one or more additional database links that respectively connect the local database server hosting the at least the first portion of the distributed database to the remote database server through the one or more additional remote database servers that respectively host at least a respective portion of the distributed database, each of the one or more additional database links is respectively associated with each of the one or more additional remote database servers and is appended to the local LOB locator, and the at least the first portion or the at least the second portion comprises the distributed database in its entirety or a smaller partition of the distributed database. ### Claim 20 (depends on 18) The computer program product of claim 18, wherein the set of acts for the request to perform the operation comprise randomly updating the data for the LOB at any offset at least by performing a write operation for a provided number of characters or bytes from a location. ### Claim 21 (depends on 18) The computer program product of claim 18, wherein the set of acts further comprise: retrieving at least a portion of the LOB from the at least the second portion of the distributed database at a remote database server, wherein the one or more database servers comprise at least the remote database server; and forwarding the at least the portion of the LOB and the remote LOB locator to the client. ### Claim 22 (depends on 18) The computer program product of claim 18, wherein the one or more database servers comprise a local database server corresponding to the client and one or more remote database servers, the distributed database is sharded into multiple sharded tables that are different from each other, and the multiple sharded tables are distributed across the local database server and the one or more remote database servers. ### Claim 23 (depends on 18) The computer program product of claim 18, wherein the one or more database servers comprise a local database server corresponding to the client and a remote database server, and the remote LOB locator further comprises information describing a character set used to store the LOB at the remote database server. ### Claim 24 (depends on 18) The computer program product of claim 18, wherein the one or more database servers comprise a local database server corresponding to the client and a plurality of remote database servers that further comprises a remote database server, and the request to perform the operation is forwarded to the remote database server via a connection that is selected from a pool of connections between the local database server and the plurality of remote database servers. ### Claim 25 (depends on 18) The computer program product of claim 18, wherein the request to perform the operation comprises a DML (data manipulation language) statement in which the remote LOB locator or the local LOB locator is passed as a bind variable. ### Claim 26 (depends on 18) The computer program product of claim 18, wherein the one or more database servers comprise a local database server corresponding to the client and a remote database server, and the database link associated with the remote database server comprises one or more of: information identifying a user, information identifying the database link, or information describing a length of the information identifying the database link. ### Claim 27 (depends on 19) The computer program product of claim 19, wherein the set of acts for forwarding the request to perform the operation comprise: forwarding the request to perform the operation from the client to the local database server that stores thereupon the at least the first portion of the distributed database; forwarding the request to perform the operation from the local database server to the one or more additional remote database servers based at least in part on the one or more additional database links comprising the remote LOB locator, wherein the remote LOB locator comprises a database link count that indicate a total number of servers that are to be traversed to reach the LOB; and forwarding the request to perform the operation from the one or more additional remote database servers to a remote database server of the one or more database servers based at least in part on the database link associated with the remote database server, wherein the operation comprises a random access to the data in the column for the LOB. ### Claim 28 (depends on 27) The computer program product of claim 27, wherein the set of acts for forwarding the request to perform the operation further comprise: removing, at each of the one or more additional remote database servers, a first database link associated with the remote database server to which the request to perform the operation is being forwarded. ### Claim 29 (depends on 19) The computer program product of claim 19, wherein an order in which the one or more additional database links associated with the one or more additional remote database servers are appended to the local LOB locator is determined based at least in part on a plurality of connections between the one or more additional remote database servers, the local database server, and the remote database server. ### Claim 30 (depends on 29) The computer program product of claim 29, wherein the set of acts for the request to perform the operation comprise an offset-based access or a random access to the LOB and is forwarded to the remote database server based at least in part on the order in which the one or more additional database links associated with the one or more additional remote database servers are appended to the local LOB locator. ### Claim 31 (depends on 20) The computer program product of claim 20, wherein the at least the first portion of the distributed database comprises an instance of the distributed database in its entirety and serves as a primary database, the at least the second portion of the distributed database comprises an identical instance of the distributed database in its entirety and serves as a standby database for the primary database, and wherein the set of acts for performing the operation on the LOB stored in the distributed database at the remote database server comprise: initiating a request to perform a READ operation on the LOB and each of one or more additional LOBs, the request initiated at a last common remote database server on a path from the client to the LOB and the one or more additional LOBs, wherein the one or more database servers comprise at least the last common remote database server and one or more remote database servers; forwarding the READ operation from the last common remote database server to one or more remote database servers at which the LOB and the one or more additional LOBs are located; processing the request to perform the READ operation on the LOB and each of the one or more additional LOBs at the one or more remote database servers; receiving, at the last common remote database server, a result of the request to perform the READ operation on the LOB and each of the one or more additional LOBs; performing a comparison of the result of the request to perform the READ operation on the LOB and each of the one or more additional LOBs; and returning an additional result of the comparison to the client. ### Claim 32 (depends on 22) The computer program product of claim 22, wherein the local database server communicates the request to perform the operation to a plurality of shards that are determined by sharding the distributed database and are distributed across the local database server and the one or more remote database servers, at least one of the plurality of shards comprising the LOB. ### Claim 33 (depends on 23) The computer program product of claim 23, wherein the set of acts further comprise: returning a set of results of the query to the client, wherein the one or more database servers comprise a local database server corresponding to the client and a remote database server, and only a single character set conversion is performed to convert the set of results to a character set used by the client, wherein the remote database server that stores at least a part of the LOB uses a plurality of character sets. ### Claim 34 (depends on 25) The computer program product of claim 25, wherein the one or more database servers comprise a local database server corresponding to the client and a remote database server, and the DML statement is directed to a remote table stored on the remote database server or a local table stored on the local database server. ### Claim 35 A computer system for performing operations on large objects (LOBs) in a distributed database that includes at least a client and one or more database servers, the computer system comprising: a memory for storing data and instructions; and a processor that executes a sequence of instructions which, when executed by the processor, causes the processor to execute a set of acts, the set of acts comprising: receiving a query from the client at a database server of the one or more database servers, wherein the distributed database is distributed as at least a first portion of the distributed database and at least a second portion of the distributed database, the query specifying access to a column of at least a second portion of the distributed database, and the column comprises a LOB; in response to receipt of the query, creating, at the database server, a remote LOB locator at least by combining a local LOB locator, a database link count, and one or more database links, the one or more database links corresponding to one or more remote database servers of the one or more database servers, the local LOB locator points to a location at which the LOB is stored in the at least the second portion of the distributed database and the database link count specifies a number of the one or more database links in the remote LOB locator; sending the remote LOB locator to the client; after sending the remote LOB locator to the client, receiving the remote LOB locator in a request from the client to perform an operation that accesses data in the LOB; forwarding the request to the at least the second portion of the distributed database based at least in part on the remote LOB locator; and performing the operation that accesses the data in the LOB stored in the at least the second portion of the distributed database. ### Claim 36 (depends on 35) The computer system of claim 35, wherein the one or more database servers comprise a local database server, a remote database server on which the LOB is stored, and one or more additional remote database servers, the remote LOB locator further comprises one or more additional database links that respectively connect the local database server hosting the at least the first portion of the distributed database to the remote database server through the one or more additional remote database servers that respectively host at least a respective portion of the distributed database, each of the one or more additional database links is respectively associated with each of the one or more additional remote database servers and is appended to the local LOB locator, and the at least the first portion or the at least the second portion comprises the distributed database in its entirety or a smaller partition of the distributed database. ### Claim 37 (depends on 35) The computer system of claim 35, wherein the set of acts for the request to perform the operation comprise randomly updating the data for the LOB at any offset at least by performing a write operation for a provided number of characters or bytes from a location. ### Claim 38 (depends on 35) The computer system of claim 35, wherein the set of acts further comprise: retrieving at least a portion of the LOB from the at least the second portion of the distributed database at a remote database server, wherein the one or more database servers comprise at least the remote database server; and forwarding the at least the portion of the LOB and the remote LOB locator to the client. ### Claim 39 (depends on 35) The computer system of claim 35, wherein the one or more database servers comprise a local database server corresponding to the client and one or more remote database servers, the distributed database is sharded into multiple sharded tables that are different from each other, and the multiple sharded tables are distributed across the local database server and the one or more remote database servers. ### Claim 40 (depends on 35) The computer system of claim 35, wherein the one or more database servers comprise a local database server corresponding to the client and a remote database server, and the remote LOB locator further comprises information describing a character set used to store the LOB at the remote database server. ### Claim 41 (depends on 35) The computer system of claim 35, wherein the one or more database servers comprise a local database server corresponding to the client and a plurality of remote database servers that further comprises a remote database server, and the request to perform the operation is forwarded to the remote database server via a connection that is selected from a pool of connections between the local database server and the plurality of remote database servers. ### Claim 42 (depends on 35) The computer system of claim 35, wherein the request to perform the operation comprises a DML (data manipulation language) statement in which the remote LOB locator or the local LOB locator is passed as a bind variable. ### Claim 43 (depends on 35) The computer system of claim 35, wherein the one or more database servers comprise a local database server corresponding to the client and a remote database server, and the database link associated with the remote database server comprises one or more of: information identifying a user, information identifying the database link, or information describing a length of the information identifying the database link. ### Claim 44 (depends on 36) The computer system of claim 36, wherein the set of acts for forwarding the request to perform the operation comprise: forwarding the request to perform the operation from the client to the local database server that stores thereupon the at least the first portion of the distributed database; forwarding the request to perform the operation from the local database server to the one or more additional remote database servers based at least in part on the one or more additional database links comprising the remote LOB locator, wherein the remote LOB locator comprises a database link count that indicate a total number of servers that are to be traversed to reach the LOB; and forwarding the request to perform the operation from the one or more additional remote database servers to a remote database server of the one or more database servers based at least in part on the database link associated with the remote database server, wherein the operation comprises a random access to the data in the column for the LOB. ### Claim 45 (depends on 44) The computer system of claim 44, wherein the set of acts for forwarding the request to perform the operation comprise: removing, at each of the one or more additional remote database servers, a first database link associated with the remote database server to which the request to perform the operation is being forwarded. ### Claim 46 (depends on 36) The computer system of claim 36, wherein an order in which the one or more additional database links associated with the one or more additional remote database servers are appended to the local LOB locator is determined based at least in part on a plurality of connections between the one or more additional remote database servers, the local database server, and the remote database server. ### Claim 47 (depends on 46) The computer system of claim 46, wherein the set of acts for the request to perform the operation comprise an offset-based access or a random access to the LOB and is forwarded to the remote database server based at least in part on the order in which the one or more additional database links associated with the one or more additional remote database servers are appended to the local LOB locator. ### Claim 48 (depends on 37) The computer system of claim 37, wherein the at least the first portion of the distributed database comprises an instance of the distributed database in its entirety and serves as a primary database, the at least the second portion of the distributed database comprises an identical instance of the distributed database in its entirety and serves as a standby database for the primary database, and wherein the set of acts for performing the operation on the LOB stored in the distributed database at the remote database server comprise: initiating a request to perform a READ operation on the LOB and each of one or more additional LOBs, the request initiated at a last common remote database server on a path from the client to the LOB and the one or more additional LOBs, wherein the one or more database servers comprise at least the last common remote database server and one or more remote database servers; forwarding the READ operation from the last common remote database server to one or more remote database servers at which the LOB and the one or more additional LOBs are located; processing the request to perform the READ operation on the LOB and each of the one or more additional LOBs at the one or more remote database servers; receiving, at the last common remote database server, a result of the request to perform the READ operation on the LOB and each of the one or more additional LOBs; performing a comparison of the result of the request to perform the READ operation on the LOB and each of the one or more additional LOBs; and returning an additional result of the comparison to the client. ### Claim 49 (depends on 39) The computer system of claim 39, wherein the local database server communicates the request to perform the operation to a plurality of shards that are determined by sharding the distributed database and are distributed across the local database server and the one or more remote database servers, at least one of the plurality of shards comprising the LOB. ### Claim 50 (depends on 40) The computer system of claim 40, wherein the set of acts further comprise: returning a set of results of the query to the client, wherein the one or more database servers comprise a local database server corresponding to the client and a remote database server, and only a single character set conversion is performed to convert the set of results to a character set used by the client, wherein the remote database server that stores at least a part of the LOB uses a plurality of character sets. ### Claim 51 (depends on 42) The computer system of claim 42, wherein the one or more database servers comprise a local database server corresponding to the client and a remote database server, and the DML statement is directed to a remote table stored on the remote database server or a local table stored on the local database server.