对这个执行计划感觉比较迷茫，到底是要循环多少次？

wqhhust

prodd  有19行数据， nested loop join后的结果可以是20。例如，prodd  中的每一行可以对应prodm中的多条数据。

极端例子:
prodd的object_id 都是1， prodm的object_id 也都是1， prodd中19条数据，prodm中有10条数据，那么结果集就是19*10=190条记录了。

Yong Huang

原帖由 wqhhust 于 2010-4-5 03:35 发表
prodd  有19行数据， nested loop join后的结果可以是20。例如，prodd  中的每一行可以对应prodm中的多条数据。

极端例子:
prodd的object_id 都是1， prodm的object_id 也都是1， prodd中19条数据，prodm中有10条数据，那么结果集就是19*10=190条记录了。

You're right! Sometimes we just missed something obvious. A simple test case is what zabll provided in message #8 except the two tables should be populated as follows:

--create table t1 as select * from dba_objects where object_id<20;
create table t1 as select rownum object_id from dual connect by level < 20;

--insert into t4 select * from dba_objects;
insert into t4 (object_id) select 1 object_id from dual connect by level < 21;

The plan becomes:

---------------------------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |    20 |   160 |    25   (0)| 00:00:01 |       |       |
|   1 |  NESTED LOOPS        |      |    20 |   160 |    25   (0)| 00:00:01 |       |       |
|   2 |   TABLE ACCESS FULL  | T1   |    19 |    57 |     3   (0)| 00:00:01 |       |       |
|   3 |   PARTITION RANGE ALL|      |     1 |     5 |     1   (0)| 00:00:01 |     1 |     6 |
|*  4 |    TABLE ACCESS FULL | T4   |     1 |     5 |     1   (0)| 00:00:01 |     1 |     6 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("T1"."OBJECT_ID"="T4"."OBJECT_ID")

So t1 has 1, 2, ..., 19, and t4 has twenty 1's. The plan says for each of the nineteen numbers (which are all distinct) in T1 (step 2), it tries to find one matching row in T4 (steps 3 and 4), and the end result is that it finds twenty in total (step 1). Actually, I'm not sure how to explain the row count 1 in steps 3 and 4. Anybody?

Yong Huang

Yong Huang

> Actually, I'm not sure how to explain the row count 1 in steps 3 and 4.

I think those two 1's are not to be trusted. Here're more accurate numbers:

SQL> select OPERATION_ID, LAST_STARTS, STARTS, LAST_OUTPUT_ROWS, OUTPUT_ROWS from v$sql_plan_statistics where sql_id = '2qp2ymfgrtq36';

OPERATION_ID  LAST_STARTS       STARTS LAST_OUTPUT_ROWS  OUTPUT_ROWS
------------ ------------ ------------ ---------------- ------------
           1            1            1               20           20
           2            1            1               19           19
           3           19           19               20           20
           4          114          114               20           20

Yong Huang

wqhhust

To Huang Yong

oracle的执行计划中的数据基本正确, 您所说的1， 准确的说应该是 20/19,但是oracle只精确到个位，所以是1. 在下面的第二个例子中，它就变成2了， 因为38/19=2

ORACLE@QIHUA SQL> create table t1 as select rownum a from dual connect by rownum<=19;

Table created.

ORACLE@QIHUA SQL> create table t2 as select rownum a from dual connect by rownum<=19;

Table created.

ORACLE@QIHUA SQL> insert into t2 values(10);

1 row created.

ORACLE@QIHUA SQL> commit;

Commit complete.

ORACLE@QIHUA SQL> exec dbms_stats.gather_table_stats(ownname => null,tabname => 'T1');

PL/SQL procedure successfully completed.

ORACLE@QIHUA SQL> exec dbms_stats.gather_table_stats(ownname => null,tabname => 'T2');

PL/SQL procedure successfully completed.

ORACLE@QIHUA SQL>
ORACLE@QIHUA SQL> set autotrace traceonly
ORACLE@QIHUA SQL> select /*+ leading(t1,t2) use_nl(t2,t1) */ * from t1 ,t2 where t1.a=t2.a;

20 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3811625520

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    20 |   120 |     8   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |      |    20 |   120 |     8   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |    19 |    57 |     2   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T2   |     1 |     3 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------


ORACLE@QIHUA SQL> set autotrace off
ORACLE@QIHUA SQL>
ORACLE@QIHUA SQL> drop table t1;

Table dropped.

ORACLE@QIHUA SQL> drop table t2;

Table dropped.

ORACLE@QIHUA SQL> create table t1 as select rownum a from dual connect by rownum<=19;

Table created.

ORACLE@QIHUA SQL> create table t2 as select rownum a from dual connect by rownum<=19;

Table created.

ORACLE@QIHUA SQL> insert into t2 select * from t2;

19 rows created.

ORACLE@QIHUA SQL> commit;

Commit complete.

ORACLE@QIHUA SQL> exec dbms_stats.gather_table_stats(ownname => null,tabname => 'T1');

PL/SQL procedure successfully completed.

ORACLE@QIHUA SQL> exec dbms_stats.gather_table_stats(ownname => null,tabname => 'T2');

PL/SQL procedure successfully completed.

ORACLE@QIHUA SQL> set autotrace traceonly
ORACLE@QIHUA SQL> select /*+ leading(t1,t2) use_nl(t2,t1) */ * from t1 ,t2 where t1.a=t2.a;

38 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3811625520

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    38 |   228 |     8   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |      |    38 |   228 |     8   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |    19 |    57 |     2   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T2   |     2 |     6 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------

Yong Huang

> 您所说的1， 准确的说应该是 20/19

Then what does the ratio 20/19 mean here? The total rows returned is 20. The top table contributes 19 rows. The number 20/19 for the bottom table means, literally, how much the top table (in ratio or percentage) has contributed to the total rows. It's still hard to associate it with some kind of row count for the bottom table.

Yong Huang

zabll

原帖由 wqhhust 于 2010-4-7 10:36 发表
To Huang Yong

oracle的执行计划中的数据基本正确, 您所说的1， 准确的说应该是 20/19,但是oracle只精确到个位，所以是1. 在下面的第二个例子中，它就变成2了， 因为38/19=2

Table dropped.

ORACLE@QIHUA SQL> create table t1 as select rownum a from dual connect by rownum create table t2 as select rownum a from dual connect by rownum insert into t2 select * from t2;

19 rows created.

ORACLE@QIHUA SQL> commit;

Commit complete.

ORACLE@QIHUA SQL> exec dbms_stats.gather_table_stats(ownname => null,tabname => 'T1');

PL/SQL procedure successfully completed.

ORACLE@QIHUA SQL> exec dbms_stats.gather_table_stats(ownname => null,tabname => 'T2');

PL/SQL procedure successfully completed.

ORACLE@QIHUA SQL> set autotrace traceonly
ORACLE@QIHUA SQL> select /*+ leading(t1,t2) use_nl(t2,t1) */ * from t1 ,t2 where t1.a=t2.a;

38 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3811625520

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    38 |   228 |     8   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |      |    38 |   228 |     8   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |    19 |    57 |     2   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T2   |     2 |     6 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------

Execution Plan
----------------------------------------------------------
Plan hash value: 3811625520

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    38 |   228 |     8   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |      |    38 |   228 |     8   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |    19 |    57 |     2   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T2   |     2 |     6 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------



   这个执行计划看的有点不理解，T1有19笔资料，T1中每笔资料在T2中对应有2比，但是NESTED LOOPS对应的38是什么意思？难道是循环了38次？按照道理说应该只是循环19次。

wqhhust

==》这个执行计划看的有点不理解，T1有19笔资料，T1中每笔资料在T2中对应有2比，但是NESTED LOOPS对应的38是什么意思？难道是循环了38次？按照道理说应该只是循环19次。
38是说NESTED LOOPS  的结果集是38行， TABLE ACCESS FULL返回19行， 当然会loop19次， 那么每次loop，能找到 2=38/19条记录。

wqhhust

==》Then what does the ratio 20/19 mean here?
我是说 20/19=1.0526315789473684=1. oracle总共返回20条， 循环19次，所以每次平均返回20/19=1行