Shulou(Shulou.com)06/01 Report--

SQL > conn scott/scottConnected.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 row created.SQL > insert into a values); 1 row created.SQL > insert into b values (1); 1 row created.SQL > insert into b values (2); 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 aPlan hash value: 2657529235 Id-| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |-| 0 | SELECT STATEMENT | | 1 | | 2 | 00SELECT STATEMENT 00.01 | 8 | | * 1 | TABLE ACCESS FULL | B | 2 | 1 | 2 | 00 00.01 | 14 | 2 | TABLE ACCESS FULL | A | 1 | 2 | 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.

Table B is executed twice and returns 2 pieces of data.

SQL > insert into a values; 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 aPlan hash value: 2657529235 Id-| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |-| 0 | SELECT STATEMENT | | 1 | | 3 | 0000 SELECT STATEMENT 00.01 | 8 | | * 1 | TABLE ACCESS FULL | B | 3 | 1 | 2 | 00 00.01 | 21 | 2 | TABLE ACCESS FULL | A | 1 | 3 | 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.

Table B is executed 3 times and returns 2 pieces of data.

SQL > insert into a values, 1 row created.SQL > insert into a values, 1 row created.SQL > insert into a values, 6 row created.SQL > insert into a values, 1 row created.SQL > commit;Commit complete.SQL > select, (select name from b where b.id=a.id) from a ID NAME (SELECTNAM- 1 a1 b1 2a2 b2 3a3 4 a4 5 a5 6 a6 7 a7 8 a8 9 a99 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 aPlan hash value: 2657529235 Id-| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |-| 0 | SELECT STATEMENT | | 1 | | 9 | 00SELECT STATEMENT 00.01 | 8 | | * 1 | TABLE ACCESS FULL | B | 9 | 1 | 2 | 00 00.01 | 63 | 2 | TABLE ACCESS FULL | A | 1 | 9 | 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.

Table B is executed 9 times and returns 2 rows of data, indicating that table a sends a value to b, returns if it can match, and returns null if it doesn't match.

SQL > update b set name='b1';2 rows updated.SQL > commit;Commit complete.SQL > select a.mom, (select name from b where b.id=a.id) from a ID NAME (SELECTNAM- 1 a1 b1 2 a2 b1 3 a3 4 5 a5 6 a6 7 a7 8 a8 9 a99 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 aPlan hash value: 2657529235 Id-| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |-| 0 | SELECT STATEMENT | | 1 | | 9 | 00SELECT STATEMENT 00.01 | 8 | | * 1 | TABLE ACCESS FULL | B | 9 | 1 | 2 | 00 00.01 | 63 | 2 | TABLE ACCESS FULL | A | 1 | 9 | 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.

Ideally, if a.id is the primary key and there are no duplicate values, then table b will be executed as many rows as table a returns.

Scalar quantum query rewriting:

1SQL > select * from a; ID NAME--1 a1 2 a2SQL > select * from b; ID NAME--1 b1 2 b2SQL > select name, (select name from b where b.id=a.id) from a from name (SELECTNAM--A1 b1a2 b2)

Rewrite:

SQL > select a.name from b where a.id=b.id (+); NAME NAME--A1 b1a2 b2

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.