Introduction to oracle Standard Quantum query and table connection Rewriting

Ainemo has heard of scalar quantum queries before, but its understanding of the details is far from enough. It makes a simple analysis and introduction to scalar quantum queries through some materials and self-testing. Oracle allows a single-row subquery to be included in the select clause. This is the oracle scalar quantum query. The scalar quantum query is similar to an external connection. When an external connection is used

Ainemo has heard of scalar quantum queries before, but its understanding of the details is far from enough. It makes a simple analysis and introduction to scalar quantum queries through some materials and self-testing. Oracle allows a single-row subquery to be included in the select clause. This is the oracle scalar quantum query. The scalar quantum query is similar to an external connection. When an external connection is used

Ainemo has heard of scalar quantum queries before, but its understanding of the details is far from enough. It makes a simple analysis and introduction to scalar quantum queries through some materials and self-testing.

Oracle allows a single-row subquery to be included in the select clause. This is the oracle scalar query. scalar queries are similar to external queries, when using external connections, we can flexibly convert them into scalar queries.

SQL> create table t1 as select * from all_users;

Table created.

SQL> create table t2 as select * from all_objects;

Table created.

SQL> select a.object_id,(select b.username from t1 b where a.owner=b.username) f
rom t2 a;

49812 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1364172329

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 53276 | 1560K| 152 (1)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 17 | 2 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T2 | 53276 | 1560K| 152 (1)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("B"."USERNAME"=:B1)

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
4282 consistent gets
0 physical reads
0 redo size
1176699 bytes sent via SQL*Net to client
37012 bytes received via SQL*Net from client
3322 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
49812 rows processed

The scalar query is actually a subquery, so how is it queried? First, external queries, such as the Execution Plan of the last SQL statement, are used to scan the entire table T2 a first, then, take each row of data in table T2 a and filter it out with table T1 B. The filter condition is. owner = B. username. If yes, the value of the subquery is returned. If not, null is used. Of course, there is also a similar filter deduplication operation at this time. For repeated data rows in t2 a, there is no need to filter with t1 B.

The SQL statement of the above scalar quantum query is actually equivalent to the SQL statement of the Outer Join below:
SQL> select a. object_id, B. username from t2 a, t1 B
2 where a. owner = B. username (+ );

49812 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 535089106

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 53276 | 2445K| 155 (2)| 00:00:02 |
|* 1 | HASH JOIN RIGHT OUTER| | 53276 | 2445K| 155 (2)| 00:00:02 |
| 2 | TABLE ACCESS FULL | T1 | 23 | 391 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | T2 | 53276 | 1560K| 152 (1)| 00:00:02 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("A"."OWNER"="B"."USERNAME"(+))

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
9 recursive calls
0 db block gets
4043 consistent gets
0 physical reads
0 redo size
1176659 bytes sent via SQL*Net to client
37012 bytes received via SQL*Net from client
3322 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
49812 rows processed

However, in the scalar quantum query, if a row in the primary query returns multiple values for the subquery, this is not allowed.
SQL> select a. username, B. object_id from t1 a, t2 B
2 where a. username = B. owner (+ );

29742 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1823443478

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 53276 | 2445K| 155 (2)| 00:00:02 |
|* 1 | HASH JOIN OUTER | | 53276 | 2445K| 155 (2)| 00:00:02 |
| 2 | TABLE ACCESS FULL| T1 | 23 | 391 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 53276 | 1560K| 152 (1)| 00:00:02 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("A"."USERNAME"="B"."OWNER"(+))

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
2726 consistent gets
0 physical reads
0 redo size
654542 bytes sent via SQL*Net to client
22294 bytes received via SQL*Net from client
1984 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
29742 rows processed

SQL> select a.username,(select b.object_id from t2 b where a.username=b.owner) f
rom t1 a;
select a.username,(select b.object_id from t2 b where a.username=b.owner) from t
1 a
*
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row

Because. username = B. owner, where B. the owner has multiple identical values, so B is returned here. object_id may have multiple values, where the above ora-01427 error occurs.

The appearance of Aggregate functions can also be found in scalar queries:
SQL> set autotrace traceonly;
SQL> select a. username, max (B. object_id) from t1 a, t2 B
2 where a. username = B. owner (+)
3 group by a. username;

23 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 577572187

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 53276 | 2445K| 159 (4)| 00:00:02 |
| 1 | HASH GROUP BY | | 53276 | 2445K| 159 (4)| 00:00:02 |
|* 2 | HASH JOIN OUTER | | 53276 | 2445K| 155 (2)| 00:00:02 |
| 3 | TABLE ACCESS FULL| T1 | 23 | 391 | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| T2 | 53276 | 1560K| 152 (1)| 00:00:02 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("A"."USERNAME"="B"."OWNER"(+))

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
92 recursive calls
0 db block gets
777 consistent gets
685 physical reads
0 redo size
1169 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
23 rows processed

SQL> select a.username,(select max(b.object_id) from t2 b where b.owner=a.userna
me) from t1 a;

23 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 367820

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23 | 391 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
|* 2 | TABLE ACCESS FULL| T2 | 533 | 15990 | 152 (1)| 00:00:02 |
| 3 | TABLE ACCESS FULL | T1 | 23 | 391 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("B"."OWNER"=:B1)

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
9 recursive calls
0 db block gets
15929 consistent gets
0 physical reads
0 redo size
1206 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
23 rows processed

However, we have noticed that the above-mentioned scalar quantum query has a problem, that is, the subquery cannot be expanded as a table connection, in other words, the flexible hash join outer association method cannot be used.

Performance of scalar queries and table Association:
If the primary query returns a large amount of data and the subquery does not have an efficient index, the primary query table corresponding to the correlated column does not have many duplicate values, therefore, the execution cost of this scalar query is very high. As shown in the preceding Standard Quantum query and the SQL statement of external connections, the I/O cost of external connections is significantly lower than that of the standard quantum query.

However, the standard quantum query of oracle is indeed optimized internally, and the optimizer caches intermediate results. If the result set is not large, the subquery contains efficient indexes, this scalar query may be more efficient than conventional table join queries.

Ainemo lists several SQL rewriting statements that often involve scalar queries and table connections:
1. Simplest scalar Query
Table: a (a1, a2), B (a1, b2)
Select a2, (select b2 from B where B. a1 = a. a1) from

Table join:
Select a2, b2 from a, B where a. a1 = B. a1 (+ );

2. Restrict the return of a row of data in the subquery
Table: a (a1, a2), B (a1, b2)
Select a2, (select b2 from B where B. a1 = a. a1 and rownum = 1) from

Table join:
SELECT a2, c. b2
FROM,
(SELECT b2, ROW_NUMBER () OVER (partition by a1 order by a1) cn
FROM B
WHERE cn = 1) c
WHERE a. a1 = c. b2 (+ );

3 subqueries with aggregate Functions
Table: a (a1, a2), B (a1, b2)
Select a2, (select sum (b2) from B where B. a1 = a. a1) from

Table join 1:
SELECT a2, bb. sum_value
FROM,
(Select sum (b2) sum_value, a1
FROM B
Group by a1) bb
WHERE a. a1 = B. a1 (+ );
Table join 2:
SELECT a2, SUM (b2)
FROM a, B
WHERE a. a1 = B. a1 (+)
Group by a2;

4. the query includes several scalar queries.
Table: a (a1, a2), B (a1, b2), c (a1, b2, c2)
SELECT a. a2,
(SELECT c2
FROM B, c
WHERE B. a1 = a. a1 AND B. b2 = c. b2 AND ROWNUM = 1 ),
(SELECT b2
FROM B
WHERE B. a1 = a. a1 AND ROWNUM = 1 ),
(SELECT c2
FROM c
WHERE c. a1 = a. a1 and rownum = 1)
FROM

Table join:
SELECT a. a2,
Bb. c2,
Cc. b2,
Dd. c2
FROM (SELECT c2,
B. a1,
ROW_NUMBER () OVER (partition by B. a1 order by B. a1) cnt
FROM B, c
WHERE B. b2 = c. b2 AND cnt = 1) bb,
(SELECT b2, ROW_NUMBER () OVER (partition by a1 order by a1) cnt
FROM B
WHERE cnt = 1) cc,
(SELECT c2, ROW_NUMBER () OVER (partition by a1 order by a1) cnt
FROM c
WHERE cnt = 1) dd,
A
WHERE a. a1 = bb. a1 (+) AND a. a1 = cc. a1 (+) AND a. a1 = dd. a1 (+ );

SELECT a2,
(Select sum (c2)
FROM B, c
WHERE B. a1 = a. a1 AND B. b2 = c. b2 ),
(Select sum (b2)
FROM B
WHERE B. a1 = a. a1 ),
(Select sum (c2)
FROM c
WHERE c. a1 = a. a1)
FROM
Table join:
SELECT a2,
Bb. sum1,
Cc. sum2,
Dd. sum3
FROM (select sum (c2) sum1, B. a1
FROM B, c
WHERE B. b2 = c. b2
Group by B. a1) bb,
(Select sum (b2) sum2, a1
FROM B
Group by a1) cc,
(Select sum (c2) sum3, a1
FROM c
Group by a1) dd,
A
WHERE a. a1 = bb. a1 (+) AND a. a1 = cc. a1 (+) AND a. a1 = dd. a1 (+ );


Original article address: Introduction to oracle Standard Quantum query and table connection rewriting. Thank you for sharing it with me.