Get a thorough understanding of Oracle's scalar quantum queries

Oracle scalar queries and their own defined functions are sometimes easier to use, and are often used by developers. Small amount of data does not matter. The amount of data is large and there are often performance problems.
Here is a test to help you understand the scalar quantum query thoroughly. 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> @getlvallSession altered.        Sql> Select A.*, (select name from B where b.id=a.id) from A;                   ID NAME (selectnamefrombwher--------------------------------------------------1 A1 B1 2 A2 b2sql> @getplanspePLAN_TABLE_OUTPUT-------------------------------------------- --------------------------------------------------------------------------------------------------------------- ---------------------------------------------sql_id 8rv825dykpx1m, child number 0--------------------------------- ----Select A.*, (select name from B whereb.id=a.id) from Aplan hash value:2657529235----------------------------------------------------------------------- -------------| Id | Operation | Name | Starts | E-rows |   A-rows | A-time |  buffers |------------------------------------------------------------------------------------|* 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 statement22 rows selected.

<strong>--to be known by the above run schedule, B table runs 2 times. Returns 2 rows of </strong> 

Sql> INSERT into a values (3, ' A3 '); 1 row created. Sql> commit; Commit complete.        Sql> Select A.*, (select name from B where b.id=a.id) from A;                   ID NAME (selectnamefrombwher--------------------------------------------------1 A1 B1 2 A2 B2 3 a3sql> @getplanspePLAN_TABLE_OUTPUT------------------------------- --------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------sql_id 8rv825dykpx1m, 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 |------------------------------------------------------------------------------------|* 1 | TABLE ACCESS Full|      B |      3 |      1 |      2 |00:00:00.01 |   21 | |  2 | TABLE ACCESS full|      A |      1 |      2 |       3 |00:00:00.01 | 8 |------------------------------------------------------------------------------------predicate information ( identified by Operation ID):---------------------------------------------------1-filter ("B". " ID "=:b1) Note------Dynamic sampling used for this statement22 rows selected.

<strong>--to be known by the above run schedule, B table runs 3 times. Returns 2 rows of </strong> 

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 (selectnamefrombwher--------------------------------------------------1 A1         B1 2 A2 B2 3 A3 4 A4 5 A5 6 a6 7 A7 8 A8 9 A99 rows selected. Sql> @getplanspePLAN_TABLE_ OUTPUT------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------sql_id 8rv825dykpx1m, 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 |------------------------------------------------------------------------------------|* 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 statement22 rows selected.

<strong>--by the above run plan to know, B table run 9 times, return 2 rows </strong> 
 

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 (selectnamefrombwher--------------------------------------------------1 A1         B1 2 A2 B1 3 A3 4 A4 5 A5 6 a6 7 A7 8 A8 9 A99 rows selected. Sql> @getplanspePLAN_TABLE_ OUTPUT------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------sql_id 8rv825dykpx1m, child Number 0-------------------------------------Select A.*, (select name from B where b.id=a.id) from Aplan hash value:265752 9235------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-rows |   A-rows | A-time | Buffers |------------------------------------------------------------------------------------|* 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 statement22 rows selected.

<strong>--by the above run plan to know, B table Run 2 times, return 2 rows </strong> 
 

sql> INSERT into B values (3, ' B1 '); 1 row created. sql> INSERT into B values (4, ' B1 '); 1 row created. sql> INSERT into B values (5, ' B1 '), 1 row created.insert into B values (6, ' B1 '); B1 '); 1 row created. sql> INSERT into B values (7, ' B1 '); 1 row created. sql> INSERT into B values (8, ' B1 '); 1 row created. sql> INSERT into B values (9, ' B1 '); 1 row created. Sql> commit; Commit complete.        Sql> Select A.*, (select name from B where b.id=a.id) from A;                   ID NAME (selectnamefrombwher--------------------------------------------------1 A1 B1 2 A2 B1 3 A3 B1 4 A4 B1 5 A                   5 B1 6 A6 B1 7 A7 B1 8 A8 B1 9 A9 b19 rows selected. Sql> @getplanspePLAN_TABLE_ OUTPUT-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------sql_id 8rv825dykpx1m, child number 0-------------------------------------the 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 |------------------------------------------------------------------------------------|* 1 | TABLE ACCESS full|      B |      9 |      1 |      9 |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 statement22 rows selected.

<strong>--b.name fields are all ' B1 '. Can be known by the above running plan. Table B runs 9 times. Returns 9 rows of </strong> 

sql> update a set id=1;9 rows updated. Sql> commit; Commit complete.        Sql> select * from A;         ID NAME------------------------------1 A1 1 A2 1 A3 1 A4 1 A5 1 a6 1 A7 1 a8 1 a99 rows selected.        Sql> SELECT * from B;         ID NAME------------------------------1 B1 2 B1 3 B1 4 B1 5 B1 6 B1 7 B1 8 B1 9 B19 rows selected.        Sql> Select A.*, (select name from B where b.id=a.id) from A;                   ID NAME (selectnamefrombwher--------------------------------------------------1 A1 B1 1 A2 B1 1 A3 B1 1 A4 B1 1 A                   5 B1 1 A6 B1 1 A7 B1 1 A8 B1 1 A9 b19 rows selected. Sql> @getplanspePLAN_TABLE_OUTPUT---------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------sql_id 8rv825dykpx1m, child Number 0-------------------------------------Select A.*, (select name from B where b.id=a.id) from Aplan hash value:265752 9235------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-rows |   A-rows | A-time |  buffers |------------------------------------------------------------------------------------|* 1 | TABLE ACCESS full|      B |      1 |      1 |       1 |00:00:00.01 |   7 | |  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 statement22 rows selected. sql> 

</pre><pre name= "code" class= "SQL" ><strong><span style= "font-size:18px;" > Association fields a.id all 1. A table has 9 lines, and the scalar quantum query is equivalent to running 9 times select name from B where b.id=1, Oracle is not stupid, starts=1. The description runs only 1 times. Summary: Ideally, a.id is the primary key, no repetition value. So how many rows a table returns, and how many times B will be run. In special cases, the distinct value of a.id is only n, then the B table only runs n times.
</span></strong>

Get a thorough understanding of Oracle's scalar quantum queries