oracle 自连接层次查询,使用自连接查询居然比直接查询效率高很多，何解？

请newkid帮忙分析分析

自连接的：

[php]

20:14:16 SQL> select t1.ind_cd f1,t2.ic f2

20:14:37   2  from places t1,

20:14:37   3       (select '0'||sys_connect_by_path(pid,'-') ic,pid

20:14:37   4        from places connect by prior pid=parentid

20:14:37   5                    start with pid =100) t2

20:14:37   6  where t1.pid = t2.pid;

已选择15818行。

已用时间:  00: 00: 01.04

Execution Plan

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

0      SELECT STATEMENT Optimizer=CHOOSE

1    0   NESTED LOOPS

2    1     VIEW

3    2       CONNECT BY (WITH FILTERING)

4    3         NESTED LOOPS

5    4           INDEX (UNIQUE SCAN) OF 'PK_PLACES' (UNIQUE)

6    4           TABLE ACCESS (BY USER ROWID) OF 'PLACES'

7    3         NESTED LOOPS

8    7           BUFFER (SORT)

9    8             CONNECT BY PUMP

10    7           TABLE ACCESS (BY INDEX ROWID) OF 'PLACES'

11   10             INDEX (RANGE SCAN) OF 'IDX_PLACES_PARENTID' (NON

-UNIQUE)

12    1     TABLE ACCESS (BY INDEX ROWID) OF 'PLACES'

13   12       INDEX (UNIQUE SCAN) OF 'PK_PLACES' (UNIQUE)

Statistics

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

0  recursive calls

0  db block gets

52838  consistent gets

0  physical reads

0  redo size

1485740  bytes sent via SQL*Net to client

12097  bytes received via SQL*Net from client

1056  SQL*Net roundtrips to/from client

13  sorts (memory)

0  sorts (disk)

15818  rows processed

20:14:40 SQL> /

已选择15818行。

已用时间:  00: 00: 01.05

Execution Plan

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

0      SELECT STATEMENT Optimizer=CHOOSE

1    0   NESTED LOOPS

2    1     VIEW

3    2       CONNECT BY (WITH FILTERING)

4    3         NESTED LOOPS

5    4           INDEX (UNIQUE SCAN) OF 'PK_PLACES' (UNIQUE)

6    4           TABLE ACCESS (BY USER ROWID) OF 'PLACES'

7    3         NESTED LOOPS

8    7           BUFFER (SORT)

9    8             CONNECT BY PUMP

10    7           TABLE ACCESS (BY INDEX ROWID) OF 'PLACES'

11   10             INDEX (RANGE SCAN) OF 'IDX_PLACES_PARENTID' (NON

-UNIQUE)

12    1     TABLE ACCESS (BY INDEX ROWID) OF 'PLACES'

13   12       INDEX (UNIQUE SCAN) OF 'PK_PLACES' (UNIQUE)

Statistics

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

0  recursive calls

0  db block gets

52838  consistent gets

0  physical reads

0  redo size

1485740  bytes sent via SQL*Net to client

12097  bytes received via SQL*Net from client

1056  SQL*Net roundtrips to/from client

13  sorts (memory)

0  sorts (disk)

15818  rows processed

-[/php]

直接查询的：

[php]

20:14:45 SQL> select ind_cd f1,'0'||sys_connect_by_path(pid,'-') f2

20:14:52   2  from places connect by prior pid=parentid

20:14:52   3              start with pid =100;

已选择15818行。

已用时间:  00: 00: 03.08

Execution Plan

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

0      SELECT STATEMENT Optimizer=CHOOSE

1    0   CONNECT BY (WITH FILTERING)

2    1     NESTED LOOPS

3    2       INDEX (UNIQUE SCAN) OF 'PK_PLACES' (UNIQUE)

4    2       TABLE ACCESS (BY USER ROWID) OF 'PLACES'

5    1     NESTED LOOPS

6    5       BUFFER (SORT)

7    6         CONNECT BY PUMP

8    5       TABLE ACCESS (BY INDEX ROWID) OF 'PLACES'

9    8         INDEX (RANGE SCAN) OF 'IDX_PLACES_PARENTID' (NON-UNI

QUE)

Statistics

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

0  recursive calls

47434  db block gets

20146  consistent gets

813  physical reads

0  redo size

1485740  bytes sent via SQL*Net to client

12097  bytes received via SQL*Net from client

1056  SQL*Net roundtrips to/from client

12  sorts (memory)

1  sorts (disk)

15818  rows processed

20:14:57 SQL> /

已选择15818行。

已用时间:  00: 00: 04.03

Execution Plan

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

0      SELECT STATEMENT Optimizer=CHOOSE

1    0   CONNECT BY (WITH FILTERING)

2    1     NESTED LOOPS

3    2       INDEX (UNIQUE SCAN) OF 'PK_PLACES' (UNIQUE)

4    2       TABLE ACCESS (BY USER ROWID) OF 'PLACES'

5    1     NESTED LOOPS

6    5       BUFFER (SORT)

7    6         CONNECT BY PUMP

8    5       TABLE ACCESS (BY INDEX ROWID) OF 'PLACES'

9    8         INDEX (RANGE SCAN) OF 'IDX_PLACES_PARENTID' (NON-UNI

QUE)

Statistics

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

0  recursive calls

47434  db block gets

20146  consistent gets

813  physical reads

0  redo size

1485740  bytes sent via SQL*Net to client

12097  bytes received via SQL*Net from client

1056  SQL*Net roundtrips to/from client

12  sorts (memory)

1  sorts (disk)

15818  rows processed

-[/php]