oracle递归查询性能优化,关于子查询，递归循环的效率问题

本帖最后由 gongcheng28 于 2012-4-10 14:48 编辑

-- Create table

create table master_info

(

masterid       varchar2(32),

parent         varchar2(32)

);

alter table master_info

add constraint UK01_master_info unique (masterid);

-- Create/Recreate indexes

create index I01_master_info on master_info (parent);

-- Create table

create table detail_info

(

masterid       varchar2(32),

sons           varchar2(32)

);

-- Create/Recreate indexes

create index I01_detail_info on detail_info (masterid);

create index I02_detail_info on detail_info (sons);

第一种写法&＃xff1a;

select *

from (select b.sons,

c.parent

from detail_info b inner join master_info c

on c.masterid &＃61; b.masterid

) m

start with m.parent &＃61; &＃39;1&＃39;

connect by m.parent &＃61; prior m.sons

第二种写法&＃xff1a;

select b.sons,

c.parent

from detail_info b inner join master_info c

on c.masterid &＃61; b.masterid

start with c.parent &＃61; &＃39;1&＃39;

connect by c.parent &＃61; prior b.sons

两种写法在数据量少的时候的执行计划是一样的。

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 1480042955

--------------------------------------------------------------------------------

| Id  | Operation                      | Name            | Rows  | Bytes | Cost

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT               |                 |     2 |    72 |     4

|*  1 |  CONNECT BY WITH FILTERING     |                 |       |       |

|   2 |   NESTED LOOPS                 |                 |       |       |

|   3 |    NESTED LOOPS                |                 |     1 |    72 |     1

|   4 |     TABLE ACCESS BY INDEX ROWID| MASTER_INFO     |     1 |    36 |     1

|*  5 |      INDEX RANGE SCAN          | I01_MASTER_INFO |     1 |       |     1

|*  6 |     INDEX RANGE SCAN           | I01_DETAIL_INFO |     1 |       |     0

|   7 |    TABLE ACCESS BY INDEX ROWID | DETAIL_INFO     |     1 |    36 |     0

|   8 |   NESTED LOOPS                 |                 |     1 |    90 |     1

|   9 |    NESTED LOOPS                |                 |     1 |    54 |     1

|  10 |     CONNECT BY PUMP            |                 |       |       |

|  11 |     TABLE ACCESS BY INDEX ROWID| MASTER_INFO     |     1 |    36 |     0

|* 12 |      INDEX RANGE SCAN          | I01_MASTER_INFO |     1 |       |     0

|  13 |    TABLE ACCESS BY INDEX ROWID | DETAIL_INFO     |     1 |    36 |     0

|* 14 |     INDEX RANGE SCAN           | I01_DETAIL_INFO |     1 |       |     0

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("C"."PARENT"&＃61;PRIOR "B"."SONS")

5 - access("C"."PARENT"&＃61;&＃39;1&＃39;)

6 - access("C"."MASTERID"&＃61;"B"."MASTERID")

12 - access("C"."PARENT"&＃61;"connect$_by$_pump$_004"."prior b.sons  ")

14 - access("C"."MASTERID"&＃61;"B"."MASTERID")

Note

-----

- dynamic sampling used for this statement (level&＃61;2)

但是当数据量有几十万的时候&＃xff0c;第一种写法的执行计划&＃xff0c;直接变成&＃xff0c;三张表都是full_scan了。

这个数据量小的时候&＃xff0c;和数据量大的时候的执行计划&＃xff0c;差别也太大了。