Thoroughly understand oracle Standard Quantum queries and oracle Standard Quantum queries
Oracle scalar quantum queries and user-defined functions are sometimes more convenient to use, and developers often use them. It doesn't matter if the data volume is small. Large data volumes often cause performance problems.
The following tests help you fully understand standard quantum queries.
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> @ getlvall
Session altered.
SQL> select a. *, (select name from B where B. id = a. id) from;
Id name (SELECTNAMEFROMBWHER
--------------------------------------------------
1 a1 b1
2 a2 b2
SQL> @ getplanspe
PLAN_TABLE_OUTPUT
Certificate --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL _ID 8rv825dykpx1m, child number 0
-------------------------------------
Select a. *, (select name from B where B. id = a. id) from
Plan hash value: 2657529235
Bytes ------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Time | Buffers |
Bytes ------------------------------------------------------------------------------------
| * 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 |
Bytes ------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
1-filter ("B". "ID" =: B1)
Note
-----
-Dynamic sampling used for this statement
22 rows selected.
The preceding execution plan shows that table B is executed twice and two rows are returned.
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;
Id name (SELECTNAMEFROMBWHER
--------------------------------------------------
1 a1 b1
2 a2 b2
3 a3
SQL> @ getplanspe
PLAN_TABLE_OUTPUT
Certificate --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL _ID 8rv825dykpx1m, child number 0
-------------------------------------
Select a. *, (select name from B where B. id = a. id) from
Plan hash value: 2657529235
Bytes ------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Time | Buffers |
Bytes ------------------------------------------------------------------------------------
| * 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 |
Bytes ------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
1-filter ("B". "ID" =: B1)
Note
-----
-Dynamic sampling used for this statement
22 rows selected.
The preceding execution plan shows that table B executes three times and returns two rows.
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;
Id name (SELECTNAMEFROMBWHER
--------------------------------------------------
1 a1 b1
2 a2 b2
3 a3
4 a4
5 a5
6 a6
7 a7
8 a8
9 a9
9 rows selected.
SQL> @ getplanspe
PLAN_TABLE_OUTPUT
Certificate --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL _ID 8rv825dykpx1m, child number 0
-------------------------------------
Select a. *, (select name from B where B. id = a. id) from
Plan hash value: 2657529235
Bytes ------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Time | Buffers |
Bytes ------------------------------------------------------------------------------------
| * 1 | table access full | B | 9 | 1 | 2 | 00:00:00. 01 | 63 |
| 2 | TABLE accesskey FULL | A | 1 | 2 | 9 | 00:00:00. 01 | 8 |
Bytes ------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
1-filter ("B". "ID" =: B1)
Note
-----
-Dynamic sampling used for this statement
22 rows selected.
According to the preceding execution plan, table B executes nine times and returns two rows.
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;
Id name (SELECTNAMEFROMBWHER
--------------------------------------------------
1 a1 b1
2 a2 b1
3 a3
4 a4
5 a5
6 a6
7 a7
8 a8
9 a9
9 rows selected.
SQL> @ getplanspe
PLAN_TABLE_OUTPUT
Certificate --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL _ID 8rv825dykpx1m, child number 0
-------------------------------------
Select a. *, (select name from B where B. id = a. id) from
Plan hash value: 2657529235
Bytes ------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Time | Buffers |
Bytes ------------------------------------------------------------------------------------
| * 1 | table access full | B | 9 | 1 | 2 | 00:00:00. 01 | 63 |
| 2 | TABLE accesskey FULL | A | 1 | 2 | 9 | 00:00:00. 01 | 8 |
Bytes ------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
1-filter ("B". "ID" =: B1)
Note
-----
-Dynamic sampling used for this statement
22 rows selected.
The preceding execution plan shows that table B is executed twice and two rows are returned.
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;
Id name (SELECTNAMEFROMBWHER
--------------------------------------------------
1 a1 b1
2 a2 b1
3 a3 b1
4 a4 b1
5 a5 b1
6 a6 b1
7 a7 b1
8 a8 b1
9 a9 b1
9 rows selected.
SQL> @ getplanspe
PLAN_TABLE_OUTPUT
Certificate --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL _ID 8rv825dykpx1m, child number 0
-------------------------------------
Select a. *, (select name from B where B. id = a. id) from
Plan hash value: 2657529235
Bytes ------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Time | Buffers |
Bytes ------------------------------------------------------------------------------------
| * 1 | table access full | B | 9 | 1 | 9 | 00:00:00. 01 | 63 |
| 2 | TABLE accesskey FULL | A | 1 | 2 | 9 | 00:00:00. 01 | 8 |
Bytes ------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
1-filter ("B". "ID" =: B1)
Note
-----
-Dynamic sampling used for this statement
22 rows selected.
B. The name field is all 'b1 '. It is known from the preceding execution plan that table B executes nine times and nine rows are returned.
SQL> update a set id = 1;
9 rows updated.
SQL> commit;
Commit complete.
SQL> select * from;
ID NAME
------------------------------
1 a1
1 a2
1 a3
1 a4
1 a5
1 a6
1 a7
1 a8
1 a9
9 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 b1
9 rows selected.
SQL> select a. *, (select name from B where B. id = a. id) from;
Id name (SELECTNAMEFROMBWHER
--------------------------------------------------
1 a1 b1
1 a2 b1
1 a3 b1
1 a4 b1
1 a5 b1
1 a6 b1
1 a7 b1
1 a8 b1
1 a9 b1
9 rows selected.
SQL> @ getplanspe
PLAN_TABLE_OUTPUT
Certificate --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL _ID 8rv825dykpx1m, child number 0
-------------------------------------
Select a. *, (select name from B where B. id = a. id) from
Plan hash value: 2657529235
Bytes ------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Time | Buffers |
Bytes ------------------------------------------------------------------------------------
| * 1 | table access full | B | 1 | 1 | 1 | 00:00:00. 01 | 7 |
| 2 | TABLE accesskey FULL | A | 1 | 2 | 9 | 00:00:00. 01 | 8 |
Bytes ------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
1-filter ("B". "ID" =: B1)
Note
-----
-Dynamic sampling used for this statement
22 rows selected.
SQL>
Associated field. all IDS are 1, and table a has nine rows. The scalar quantum query is equivalent to executing nine select names from B where B. id = 1, oracle is not silly, starts = 1, it indicates that only one execution is performed.
Summary:
Ideally, if a. id is the primary key with no duplicate values, the number of rows returned by Table a and the number of times that table B will be executed.
In special cases, there are only n distinct values for a. id, so table B is only executed n times.
Concept of Oracle scalar Query
Here is an example. Take a closer look.
Blog.csdn.net/..3.aspx
In oracle, is a scalar variable a system variable?
@ The variable name is different from the variable in VS. For example, if you wrote the variable, the TBusersTP variable declared above is actually completely different from the @ TBusersTP in the SQL statement, add this cmd. params. add (, qbEdtw
