彻底搞懂oracle的标量子查询
来源：互联网 发布：算法的效率类型 编辑：程序博客网 时间：2024/06/14 01:57
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> @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 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 | 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>--由上面的执行计划可以知道，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 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>--由上面的执行计划可以知道，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 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>--由上面的执行计划可以知道，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 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>--由上面的执行计划可以知道，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 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: 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字段全部为‘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 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 a5 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: 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 statement22 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>
1 0
- 彻底搞懂oracle的标量子查询
- oracle标量子查询
- [Oracle]高效的PL/SQL程序设计(二)--标量子查询
- [Oracle]高效的PL/SQL程序设计(二)--标量子查询
- [Oracle]高效的PL/SQL程序设计(二)--标量子查询
- 经典的标量子查询
- SQL优化-标量子查询的改写
- 数据仓库的隐患-标量子查询
- 彻底搞懂Oracle字符集
- 彻底搞懂Oracle字符集
- 彻底搞懂Oracle字符集
- 标量子查询
- 标量子查询
- 标量子查询
- 标量子查询
- sql 标量子查询
- 标量子查询
- 标量子查询改写
- POJ 2524 Ubiquitous Religions (幷查集)
- Android中RelativeLayout各个属性的含义
- 算法竞赛入门经典（第2版）习题3-12 浮点数 Floating-Point Numbers UVa11809
- MyEclipse里WEB-INF显示classes附Web开发目录结构
- 加沙问题的相关概念
- 彻底搞懂oracle的标量子查询
- 集群、分布式计算、负载均衡的区别
- 使用ngrok快速地将本地Web服务映射到外网
- mybatis对数字的处理
- getRealPath()和getContextPath()的区别
- Converting Between YUV and RGB
- ubuntu 13.04 安装qpid和openmama
- storm入门教程 第一章 前言
- Speed Limit
