彻底搞懂oracle的标量子查询

最新推荐文章于 2024-06-07 16:44:00 发布

gdmzlhj1

最新推荐文章于 2024-06-07 16:44:00 发布

阅读量8k

点赞数 2

分类专栏： sql优化文章标签：优化 sql oracle 执行计划数据库

本文链接：https://blog.csdn.net/gdmzlhj1/article/details/37763825

版权

sql优化专栏收录该内容

18 篇文章 0 订阅

订阅专栏

oracle标量子查询和自定义函数有时用起来比较方便，而且开发人员也经常使用，数据量小还无所谓，数据量大，往往存在性能问题。
以下测试帮助大家彻底搞懂标量子查询。

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 a;
        ID NAME                 (SELECTNAMEFROMBWHER
---------- -------------------- --------------------
         1 a1                   b1
         2 a2                   b2
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    |      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
22 rows selected.

<strong>--由上面的执行计划可以知道，b表执行2次，返回2行</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 a3
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    |      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 statement
22 rows selected.

<strong>--由上面的执行计划可以知道，b表执行3次，返回2行</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 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.

<strong>
--由上面的执行计划可以知道，b表执行9次，返回2行</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 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.

<strong>--由上面的执行计划可以知道，b表执行2次，返回2行</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 a5                   b1
         6 a6                   b1
         7 a7                   b1
         8 a8                   b1
         9 a9                   b1
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 |      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 statement
22 rows selected.

<strong>--b.name字段全部为‘b1’，由上面的执行计划可以知道，b表执行9次，返回9行</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 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 a;
        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
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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    |      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 statement
22 rows selected.
SQL>

</pre><pre name="code" class="sql"><strong><span style="font-size:18px;">关联字段a.id全部为1，a表有9行，标量子查询相当于执行9次select name from b where b.id=1 ，oracle也不傻，starts=1，说明只执行了1次。
总结：
理想状态下，a.id为主键，没有重复值，那么a表返回多少行，b表就要被执行多少次。
特殊情况下，a.id的distinct值只有n个，那么b表只执行n次。</span></strong>

gdmzlhj1

关注

2
点赞
踩
6

收藏

觉得还不错? 一键收藏
0
评论
彻底搞懂oracle的标量子查询

在oracle中，标量子查询和自定义函数用的比较多，而且开发人员也比较常用，但经常会引起性能问题，特别表比较大的时候。下面的案例将教大家彻底搞懂标量子查询：SQL> create table a (id int,name varchar2(10));Table created.SQL> create table b (id int,name varchar2(10));
复制链接

扫一扫

专栏目录