Oracle Scalar Quantum Query

Sql> Conn scott/scottconnected.sql> CREATE TABLE A (ID int,name varchar2 (10)); Table created. Sql> CREATE TABLE B (ID int,name varchar2 (10)); Table created. Sql> INSERT into a values (1, ' A1 '); 1 row created. Sql> INSERT into a values (2, ' A2 '); 1 row created. sql> INSERT into B values (1, ' B1 '); 1 row created. sql> INSERT into B values (2, ' B2 '); 1 row created. Sql> commit; Commit complete.

Sql> select * from table (Dbms_xplan.display_cursor (null,null, ' ALLSTATS LAST ')); Plan_table_ OUTPUT------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------sql_id 8rv825dykpx1m ,  child number 1-------------------------------------select a.*, (select name  from b where b.id=a.id)  from aplan hash value: 2657529235------------- -----------------------------------------------------------------------| id  | operation    | name | starts | e-rows | a-rows | a-time   | buffers |----------------------------------------------------------------------------- -------|   0 | select statement  |  | 1 | & NBsp;  |   2 |00:00:00.01 |  8 | | *  1 |  table access full| b  | 2 |  1  |   2 |00:00:00.01 | 14 | |    2 |  TABLE ACCESS FULL| A  | 1 |   2 |   2 |00:00:00.01 |  8 |-------------------------------- ----------------------------------------------------predicate information  (identified by  Operation id):---------------------------------------------------   1 - filter (" B "." ID "=:b1) Note-----   - dynamic sampling used for this statement   (level=2) 23 rows selected.

Table B is executed 2 times and returns 2 data.

Sql> INSERT into a values (3, ' A3 '); 1 row created. Sql> commit; Commit complete.

Sql> select * from table (Dbms_xplan.display_cursor (null,null, ' ALLSTATS LAST ')); Plan_table_ OUTPUT------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------SQL_ID 9RUFVG18A2VFQ ,  child number 0-------------------------------------select a.*, (select name  from b where b.id=a.id)  from aplan hash value: 2657529235------------- -----------------------------------------------------------------------| id  | operation    | name | starts | e-rows | a-rows | a-time   | buffers |----------------------------------------------------------------------------- -------|   0 | select statement  |  | 1 | & NBsp;  |   3 |00:00:00.01 |  8 | | *  1 |  table access full| b  | 3 |  1  |   2 |00:00:00.01 | 21 | |    2 |  TABLE ACCESS FULL| A  | 1 |   3 |   3 |00:00:00.01 |  8 |-------------------------------- ----------------------------------------------------predicate information  (identified by  Operation id):---------------------------------------------------   1 - filter (" B "." ID "=:b1) Note-----   - dynamic sampling used for this statement   (level=2) 23 rows selected.

The

B table is executed 3 times and returns 2 data.

Sql> insert into a values (4, ' A4 '); 1 row created. Sql> insert into a values (5, ' A5 '); 1 row created. Sql> insert into a values (6, ' A6 '); 1 row created. Sql> insert into a values (7, ' A7 '); 1 row created. Sql> insert into a values (8, ' A8 '); 1 row created. Sql> insert into a values (9, ' A9 '); 1 row created. sql> commit; Commit complete. Sql> select a.*, (select name from b where b.id=a.id)  from a;  ID NAME        (Selectnam---------- ---------- -------- --  1 a1       b1  2 a2        b2  3 a3  4 a4  5 a5  6 a6   7 a7  8  a8  9 a99 rows selected. Sql> select * from table (Dbms_xplan.display_cursor (null,null, ' ALLSTATS LAST ')); Plan_table_ OUTPUT------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------sql_id 8rv825dykpx1m ,  child number 1-------------------------------------select a.*, (select name  from b where b.id=a.id)  from aplan hash value: 2657529235------------- -----------------------------------------------------------------------| id  | operation    | name | starts | e-rows | a-rows | a-time   | buffers |----------------------------------------------------------------------------- -------|   0 | select statement  |  | 1 |    |   9 |00:00:00.01 |   8 | | *  1 |  table access full| b  | 9 |  1  |   2 |00:00:00.01 | 63 | |    2 |  TABLE ACCESS FULL| A  | 1 |   2 |   9 |00:00:00.01 |  8 |-------------------------------- ----------------------------------------------------predicate information  (identified by  Operation id):---------------------------------------------------   1 - filter (" B "." ID "=:b1) Note-----   - dynamic sampling used for this statement   (level=2) 23 rows selected.

The

B table is executed 9 times, returning 2 rows of data, indicating that the a table to the B value, can match on the return, the match does not return null

Sql> update b set name= ' B1 '; 2 rows updated. sql> commit; Commit complete. Sql> select a.*, (select name from b where b.id=a.id)  from a;  ID NAME        (Selectnam---------- ---------- -------- --  1 a1       b1  2 a2        b1  3 a3  4 a4  5 a5  6 a6   7 a7  8 a8  9 a99 rows selected. Sql> select * from table (Dbms_xplan.display_cursor (null,null, ' ALLSTATS LAST ')); Plan_table_ OUTPUT------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------sql_id 8rv825dykpx1m ,  chilD number 1-------------------------------------select a.*, (select name from b  where b.id=a.id)  from aplan hash value: 2657529235------------------------ ------------------------------------------------------------| id  | operation    | name | starts | e-rows | a-rows | a-time  |  buffers |------------------------------------------------------------------------------------|    0 | SELECT STATEMENT  |  | 1 |     |   9 |00:00:00.01 |  8 | | *  1 |  table access full| b  | 9 |  1  |   2 |00:00:00.01 | 63 | |    2 |  table access full| a  | 1 |   2 |   9 |00:00:00.01 |  8 |------------------------------- -----------------------------------------------------predicate information  (identified by  Operation id):---------------------------------------------------   1 - filter (" B "." ID "=:b1) Note-----   - dynamic sampling used for this statement   (level=2) 23 rows selected.

Ideally, a.id is the primary key, there is no duplicate value, then a table returns how many rows, and B will be executed how many times.

Scalar quantum query rewrite:

1sql> select * from A; ID NAME--------------------1 A1 2 a2sql> select * from B; ID name--------------------1 B1 2 b2sql> select Name, (select name from B where b.id=a.id) from A;name (Selectnam --------------------A1 b1a2 B2

Rewrite:

Sql> Select A.name,b.name from a b where a.id=b.id (+); name Name--------------------A1 b1a2 B2

Oracle Scalar Quantum Query