彻底搞懂oracle的标量子查询 - 数据库编程

(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 a9
9 rows selected.
SQL> @getplanspe
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8rv825dykpx1m, child number 0
-------------------------------------
select a.*,(select name from b where b.id=a.id) from a
Plan 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 statement
22 rows selected.
由上面的执行计划可以知道，b表执行9次，返回2行
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 a9
9 rows selected.
SQL> @getplanspe
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8rv825dykpx1m, child number 0
-------------------------------------
select a.*,(select name from b where b.id=a.id) from a
Plan 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 statement
22 rows selected.
由上面的执行计划可以知道，b表执行2次，返回2行
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 a5 b1
6 a6 b1
7 a7 b1
8 a8 b1
9 a9 b1
9 rows selected.
SQL>

彻底搞懂oracle的标量子查询(二)