1. Using Oracle Sharding
  2. Query and DML Processing
  3. Supported Query Constructs and Example Query Shapes

Supported Query Constructs and Example Query Shapes

Oracle Sharding supports single-shard and multi-shard query shapes with some restrictions.

The following are restrictions on query constructs in Oracle Sharding.

  • CONNECT BY Queries CONNECT BY queries are not supported.

  • MODEL Clause The MODEL clause is not supported.

  • User-Defined PL/SQL in the WHERE Clause User-defined PL/SQL is allowed in multi-shard queries only in the SELECT clause. If it is specified in the WHERE clause then an error is thrown.

  • XLATE and XML Query type XLATE and XML Query type columns are not supported.

  • Object types You can include object types in SELECT lists, WHERE clauses, and so on, but custom constructors and member functions of type object type are not permitted in WHERE clauses.

    Furthermore, for duplicated tables, non-final types, that is, object types that are created with the NOT FINAL keyword, cannot be used as a column data type. For sharded tables, non-final types can be used as a column data type but the column must be created with keywords NOT SUBSTITUTABLE AT ALL LEVELS.

Note:

Queries involving only duplicated tables are run on the coordinator.

The following topics show several examples of query shapes supported in Oracle Sharding.

Queries on Sharded Tables Only

For a single-table query, the query can have an equality filter on the sharding key that qualifies a shard. For join queries, all of the tables should be joined using equality on the sharding key.

The following examples show queries where only sharded tables participate.

Example 9-1 Inner Join

SELECT … FROM s1 INNER JOIN s2 ON s1.sk=s2.sk 
WHERE any_filter(s1) AND any_filter(s2)

Example 9-2 Left Outer Join

SELECT … FROM s1 LEFT OUTER JOIN s2 ON s1.sk=s2.sk

Example 9-3 Right Outer Join

SELECT … FROM s1 RIGHT OUTER JOIN s2 ON s1.sk=s2.sk

Example 9-4 Full Outer Join

SELECT … FROM s1 FULL OUTER JOIN s2 ON s1.sk=s2.sk
WHERE any_filter(s1) AND any_filter(s2)

Queries Involving Both Sharded and Duplicated Tables

A query involving both sharded and duplicated tables can be either a single-shard or multi-shard query, based on the predicates on the sharding key. The only difference is that the query contains a non-sharded table.

Note:

Joins between a sharded table and a duplicated table can be on any column, using any comparison operator, = < > <= >=, or arbitrary join expressions.

Example 9-5 Inner Join

SELECT … FROM s1 INNER JOIN r1 ON any_join_condition(s1,r1) 
WHERE any_filter(s1) AND any_filter(r1)

Example 9-6 Left or Right Outer Join

In this case, the sharded table is the first table in LEFT OUTER JOIN. 

SELECT … FROM s1 LEFT OUTER JOIN r1 ON any_join_condition(s1,r1) 
WHERE any_filter(s1) AND any_filter(r1)

SELECT … FROM r1 LEFT OUTER JOIN s1 ON any_join_condition(s1,s2) 
AND any_filter(r1) AND filter_one_shard(s1)

In this case, the sharded table is the second table in RIGHT OUTER JOIN. 

SELECT … FROM r1 RIGHT OUTER JOIN s1 ON any_join_condition(s1,r1) 
WHERE any_filter(s1) AND any_filter(r1)

SELECT … FROM s1 RIGHT OUTER JOIN r1 ON any_join_condition(s1,s2) 
AND filter_one_shard(s1) AND any_filter(r1)

In some cases, the duplicated table is the first table in LEFT OUTER JOIN, or the sharded table is first and it maps to a single shard, based on filter predicate on the sharding key. 

SELECT … FROM r1 LEFT OUTER JOIN s1 ON any_join_condition(s1,s2) 
AND any_filter(r1) AND any_filter(s1)

In some cases, the duplicated table is the second table in RIGHT OUTER JOIN, or the sharded table is second and it maps to a single shard based on filter predicate on sharding key. 

SELECT … FROM s1 RIGHT OUTER JOIN r1 ON any_join_condition(s1,s2) 
AND any_filter (s1) AND any_filter(r1)

Example 9-7 Full Outer Join

SELECT … FROM s1 FULL OUTER JOIN r1 ON s1.sk=s2.sk
WHERE any_filter(s1) AND any_filter(s2)

In this case, the sharded table requires access to multiple shards:

SELECT … FROM s1 FULL OUTER JOIN r1 ON s1.non_sk=s2.non_sk
WHERE any_filter(s1) AND any_filter(s2)

Example 9-8 Semi-Join (EXISTS) 

SELECT … FROM s1 EXISTS 
(SELECT 1 FROM r1 WHERE r1.anykey=s1.anykey)

SELECT … FROM r1 EXISTS 
(SELECT 1 FROM s1 WHERE r1.anykey=s1.anykey and filter_one_shard(s1))

In this case, the sharded table is in a subquery that requires the participation of multiple shards.

SELECT … FROM r1 EXISTS 
(SELECT 1 FROM s1 WHERE r1.anykey=s1.anykey)

Example 9-9 Anti-Join (NOT EXISTS) 

SELECT … FROM s1 NOT EXISTS 
(SELECT 1 FROM r1 WHERE r1.anykey=s1.anykey)

In this case, the sharded table is in the sub-query.

SELECT … FROM r1 NOT EXISTS 
(SELECT 1 FROM s1 WHERE r1.anykey=s1.anykey

Aggregate Functions Supported by Oracle Sharding

The following aggregations are supported by proxy routing in Oracle Sharding.

  • COUNT

  • SUM

  • MIN

  • MAX

  • AVG

Queries with User-Defined Types

User-defined SQL object types and user-defined SQL collection types are referred to as user-defined types. Oracle Sharding supports queries with user-defined types.

Example 9-10 Create Table with User-Defined Types

The following example creates an all-shard type and type body, then creates a sharded table referencing the type.

ALTER SESSION ENABLE SHARD DDL;

CREATE OR REPLACE TYPE person_typ AS OBJECT (
    first_name   VARCHAR2(20),
    last_name    VARCHAR2(25),
    email        VARCHAR2(25),
    phone        VARCHAR2(20),
    MEMBER FUNCTION details (
    self IN person_typ
    ) RETURN VARCHAR2
);
/

CREATE OR REPLACE TYPE BODY person_typ AS
    MEMBER FUNCTION details (
    self IN person_typ
    ) RETURN VARCHAR2 IS
        result VARCHAR2(100);
    BEGIN
        result := first_name || ' ' || last_name || ' ' || email || ' ' || phone;
        RETURN result;
    END;
END;
/

CREATE SHARDED TABLE Employees
( Employee_id      NUMBER NOT NULL
, person      person_typ
, signup_date DATE NOT NULL
, CONSTRAINT RootPK PRIMARY KEY(CustNo)
)
PARTITION BY CONSISTENT HASH (CustNo)
PARTITIONS AUTO
TABLESPACE SET ts1
;

Example 9-11 Insert Data Using Type Constructor 

INSERT INTO Employees values ( 1, person_typ('John', 'Doe', 'jdoe@example.com', '123-456-7890'), to_date('24 Jun 2020', 'dd Mon YYYY'));

Example 9-12 Multi-Shard Query of a User-Defined Type Column

SELECT e.person FROM Employees e;


SELECT e.person.first_name, e.person.last_name FROM Employees e;


SELECT e.person.details() FROM Employee e where e.person.first_name = 'John’;

 
SELECT signup_date from Employees e where e.person = person_typ('John', 'Doe', 'jdoe@example.com', '123-456-7890’);

Execution Plans for Proxy Routing

In a multi-shard query, each shard produces an independent execution plan which is optimized for the data size and compute resources available on the shard.

You do not need to connect to individual shards to see the explain plan for SQL fragments. Interfaces provided in dbms_xplan.display_cursor() display on the coordinator the plans for the SQL segments run on the shards, and [V/X]$SHARD_SQL uniquely maps a shard SQL fragment of a multi-shard query to the target shard database.

SQL Segment Interfaces for dbms_xplan.display_cursor()

Two interfaces can display the plan for a SQL segment run on shards. The interfaces take shard IDs as the argument to display the plans from the specified shards. The ALL_SHARDS format displays the plans from all of the shards.

To print all of the plans from all shards use the format value ALL_SHARDS as shown here. 

select * from table(dbms_xplan.display_cursor(sql_id=>:sqlid,
                                              cursor_child_no=>:childno,
                                              format=>'BASIC +ALL_SHARDS‘,
                                              shard_ids=>shard_ids))

To print selective plans from the shards, pass shard IDs in the display_cursor() function. For plans from multiple shards, pass an array of numbers containing shard IDs in the shard_ids parameter as shown here. 

select * from table(dbms_xplan.display_cursor(sql_id=>:sqlid, 
                                               cursor_child_no=>:childno,
                                               format=>'BASIC',
                                               shard_ids=>ids))

To return a plan from one shard pass the shard ID directly to the shard_id parameter, as shown here. 

select * from table(dbms_xplan.display_cursor(sql_id=>:sqlid,
                                              cursor_child_no=>:childno,
                                              format=>'BASIC',
                                              shard_id=>1))

V$SQL_SHARD

V$SQL_SHARD uniquely maps a shard SQL fragment of a multi-shard query to the target shard database. This view is relevant only for the shard coordinator database to store a list of shards accessed for each shard SQL fragment for a given multi-shard query. Every time a multi-shard query runs, it can run a shard SQL fragment on different set of shards, so the shard IDs update each time it is runs. This view maintains the SQL ID of a shard SQL fragment for each REMOTE node and the SHARD IDs on which the shard SQL fragment was run. 

Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
 SQL_ID                                            VARCHAR2(13)
 CHILD_NUMBER                                      NUMBER
 NODE_ID                                           NUMBER
 SHARD_SQL_ID                                      VARCHAR2(13)
 SHARD_ID                                          NUMBER
 SHARD_CHILD_NUMBER                                NUMBER

  • SQL_ID – SQL ID of a multi-shard query on coordinator

  • CHILD_NUMBER – cursor child number of a multi-shard query on coordinator

  • NODE_ID – ID of REMOTE node for a shard SQL fragment of a multi-shard query

  • SHARD_SQL_ID – SQL ID of the shard SQL fragment for given remote NODE ID

  • SHARD_ID – IDs of shards where the shard SQL fragment was run

  • SHARD _CHILD_NUMBER– cursor child number of a shard SQL fragment on a shard (default 0)

The following is an example of a multi-shard query on the sharded database and the execution plan.

SQL> select count(*) from departments a where exists (select distinct department_id
 from departments b where b.department_id=60);
------------------------------------------------
| Id  | Operation          | Name              |
------------------------------------------------
|   0 | SELECT STATEMENT   |                   |
|   1 |  SORT AGGREGATE    |                   |
|   2 |   FILTER           |                   |
|   3 |    VIEW            | VW_SHARD_377C5901 |
|   4 |     SHARD ITERATOR |                   |
|   5 |      REMOTE        |                   |
|   6 |    VIEW            | VW_SHARD_EEC581E4 |
|   7 |     SHARD ITERATOR |                   |
|   8 |      REMOTE        |                   |
------------------------------------------------

A query of SQL_ID on the V$SQL_SHARD view. 

SQL> Select * from v$sql_shard where SQL_ID = ‘1m024z033271u’;
SQL_ID        NODE_ID   SHARD_SQL_ID  SHARD_ID
------------- -------  -------------- --------
1m024z033271u       5   5z386yz9suujt        1
1m024z033271u       5   5z386yz9suujt       11 
1m024z033271u       5   5z386yz9suujt       21 
1m024z033271u       8   8f50ctj1a2tbs 	    11

See Also:

Oracle Database PL/SQL Packages and Types Reference

Oracle Database Reference