oracle標(biāo)量子查詢
SQL> conn scott/scott Connected. 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> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8rv825dykpx1m, child number 1
-------------------------------------
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 |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 |00:00:00.01 | 8 |
|* 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 (level=2)
23 rows selected.B表被執(zhí)行2次，返回2條數(shù)據(jù)。
創(chuàng)新互聯(lián)專注于企業(yè)全網(wǎng)整合營銷推廣、網(wǎng)站重做改版、陽東網(wǎng)站定制設(shè)計、自適應(yīng)品牌網(wǎng)站建設(shè)、HTML5、商城網(wǎng)站建設(shè)、集團公司官網(wǎng)建設(shè)、外貿(mào)網(wǎng)站制作、高端網(wǎng)站制作、響應(yīng)式網(wǎng)頁設(shè)計等建站業(yè)務(wù)，價格優(yōu)惠性價比高，為陽東等各大城市提供網(wǎng)站開發(fā)制作服務(wù)。
SQL> insert into a values(3,'a3'); 1 row created. SQL> commit; Commit complete.
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 9rufvg18a2vfq, 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 |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 3 |00:00:00.01 | 8 |
|* 1 | TABLE ACCESS FULL| B | 3 | 1 | 2 |00:00:00.01 | 21 |
| 2 | TABLE ACCESS FULL| A | 1 | 3 | 3 |00:00:00.01 | 8 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("B"."ID"=:B1)
Note
-----
- dynamic sampling used for this statement (level=2)
23 rows selected.B表被執(zhí)行3次，返回2條數(shù)據(jù)。
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 (SELECTNAM
---------- ---------- ----------
1 a1 b1
2 a2 b2
3 a3
4 a4
5 a5
6 a6
7 a7
8 a8
9 a9
9 rows selected.
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8rv825dykpx1m, child number 1
-------------------------------------
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 |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 9 |00:00:00.01 | 8 |
|* 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 (level=2)
23 rows selected.B表被執(zhí)行9次，返回2行數(shù)據(jù)，說明a表向b傳值，能匹配上就返回，匹配不上就返回null
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 (SELECTNAM
---------- ---------- ----------
1 a1 b1
2 a2 b1
3 a3
4 a4
5 a5
6 a6
7 a7
8 a8
9 a9
9 rows selected.
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8rv825dykpx1m, child number 1
-------------------------------------
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 |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 9 |00:00:00.01 | 8 |
|* 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 (level=2)
23 rows selected.理想狀態(tài)下，a.id為主鍵，沒有重復(fù)值，那么a表返回多少行，b表就要被執(zhí)行多少次。
標(biāo)量子查詢改寫:
1 SQL> select * from a; ID NAME ---------- ---------- 1 a1 2 a2 SQL> select * from b; ID NAME ---------- ---------- 1 b1 2 b2 SQL> select name,(select name from b where b.id=a.id) from a; NAME (SELECTNAM ---------- ---------- a1 b1 a2 b2
改寫：
SQL> select a.name,b.name from a,b where a.id=b.id(+); NAME NAME ---------- ---------- a1 b1 a2 b2
文章標(biāo)題：oracle標(biāo)量子查詢
網(wǎng)址分享：http://m.jiaotiyi.com/article/jeesjd.html