oracle 10g 树状查询新特性（二）

查询文档，想要解决树状查询的根的问题。9i的文档翻完，特意要翻了10g的文档，还找到了两个新的伪列，可是居然偏偏就漏掉了我最想要找的ROOT特性，真可谓捡了芝麻丢了西瓜。最另我郁闷的是，Oracle这个功能的名称居然就包含我自己杜撰的ROOT，当时看到CONNECT_BY_ISLEAF和CONNECT_BY_ISCYCLE伪列后，我就在想，怎么没有CONNECT_BY_ROOT伪列。没想到Oracle在10g中确实提供了这个功能，而且名称居然也就是CONNECT_BY_ROOT，真不知道是自己有先见之明还是粗心大意到了一定的地步。

感谢fuyuncat的提醒，不然的话，不知道什么时候才能发现这个问题。




Oracle提供的并不是一个伪列，伪列只能返回ROOT的ID，Oracle提供了更为方便的功能，CONNECT_BY_ROOT是一个操作，使用这个操作符可以返回ROOT记录的任何一列。

而且这个操作的效率十分的高，比起我前面写的那个垃圾SQL，效率不知道强了多少倍。

把前面例子中的数据量加大后对比一些，就可以看出二者的效率差别有多大了。

SQL> DROP TABLE TEST PURGE;

表已删除。

已用时间: 00: 00: 00.62
SQL> CREATE TABLE TEST (ID NUMBER PRIMARY KEY, FID NUMBER, NAME VARCHAR2(30));

表已创建。

已用时间: 00: 00: 00.09
SQL> INSERT INTO TEST VALUES (1, 0, 'OBJECT');

已创建 1 行。

已用时间: 00: 00: 00.00
SQL> INSERT INTO TEST VALUES (2, 1, 'TABLE');

已创建 1 行。

已用时间: 00: 00: 00.01
SQL> INSERT INTO TEST VALUES (3, 1, 'INDEX');

已创建 1 行。

已用时间: 00: 00: 00.01
SQL> INSERT INTO TEST VALUES (4, 1, 'VIEW');

已创建 1 行。

已用时间: 00: 00: 00.00
SQL> INSERT INTO TEST VALUES (5, 1, 'SYNONYM');

已创建 1 行。

已用时间: 00: 00: 00.00
SQL> INSERT INTO TEST VALUES (6, 1, 'SOURCE');

已创建 1 行。

已用时间: 00: 00: 00.00
SQL> INSERT INTO TEST SELECT 200000+ROWNUM, 2, TABLE_NAME FROM DBA_TABLES WHERE OWNER = 'SYS';

已创建702行。

已用时间: 00: 00: 00.32
SQL> INSERT INTO TEST SELECT 300000+ROWNUM, 3, INDEX_NAME FROM DBA_INDEXES WHERE OWNER = 'SYS';

已创建807行。

已用时间: 00: 00: 00.26
SQL> INSERT INTO TEST SELECT 400000+ROWNUM, 4, VIEW_NAME FROM DBA_VIEWS;

已创建3701行。

已用时间: 00: 00: 00.11
SQL> INSERT INTO TEST SELECT 500000+ROWNUM, 5, SYNONYM_NAME FROM DBA_SYNONYMS;

已创建20107行。

已用时间: 00: 00: 00.67
SQL> INSERT INTO TEST SELECT 600000+ROWNUM, 6, NAME FROM (SELECT DISTINCT NAME FROM DBA_SOURCE);

已创建3244行。

已用时间: 00: 00: 00.70
SQL> INSERT INTO TEST SELECT 2000000+ROWNUM, B.ID, A.COLUMN_NAME FROM DBA_TAB_COLUMNS A, TEST B
2 WHERE OWNER = 'SYS' AND A.TABLE_NAME = B.NAME AND B.FID = 2;

已创建6910行。

已用时间: 00: 00: 00.42
SQL> INSERT INTO TEST SELECT 3000000+ROWNUM, B.ID, SUBSTR(A.COLUMN_NAME, 1, 30) FROM DBA_IND_COLUMNS
A, TEST B
2 WHERE TABLE_OWNER = 'SYS' AND A.INDEX_NAME = B.NAME AND B.FID = 3;

已创建1626行。

已用时间: 00: 00: 00.29
SQL> COMMIT;

提交完成。

已用时间: 00: 00: 00.01
SQL> CREATE INDEX IND_TEST_FID ON TEST(FID);

索引已创建。

已用时间: 00: 00: 00.14
SQL> SELECT COUNT(*) FROM TEST;

COUNT(*)
----------
37103

已用时间: 00: 00: 00.03
SQL> SET AUTOT TRACE
SQL> SELECT CONNECT_BY_ROOT(ID), ID FROM TEST
2 CONNECT BY PRIOR ID = FID;

已选择119838行。

已用时间: 00: 00: 02.03

执行计划
----------------------------------------------------------
Plan hash value: 1738124912

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 34495 | 875K| 43 (3)| 00:00:01 |
| 1 | CONNECT BY WITHOUT FILTERING| | | | | |
| 2 | TABLE ACCESS FULL | TEST | 34495 | 875K| 43 (3)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement

统计信息
----------------------------------------------------------
7 recursive calls
0 db block gets
251 consistent gets
0 physical reads
0 redo size
2440065 bytes sent via SQL*Net to client
88264 bytes received via SQL*Net from client
7991 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
119838 rows processed

SQL> SELECT
2 (
3 SELECT ID FROM TEST
4 WHERE LEVEL = A.LV
5 START WITH ID = A.ID
6 CONNECT BY ID = PRIOR FID
7 ) ROOT_ID, ID
8 FROM (SELECT ID, LEVEL LV FROM TEST CONNECT BY PRIOR ID = FID) A;

已选择119838行。

已用时间: 01: 00: 03.53

执行计划
----------------------------------------------------------
Plan hash value: 916527755

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 34495 | 875K| 43 (3)| 00:00:01 |
|* 1 | FILTER | | | | | |
|* 2 | CONNECT BY WITH FILTERING | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| TEST | | | | |
|* 4 | INDEX UNIQUE SCAN | SYS_C007300 | 1 | 13 | 1 (0)| 00:00:01 |
|* 5 | HASH JOIN | | | | | |
| 6 | CONNECT BY PUMP | | | | | |
| 7 | TABLE ACCESS FULL | TEST | 34495 | 875K| 2 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | TEST | 34495 | 875K| 2 (0)| 00:00:01 |
| 9 | VIEW | | 34495 | 875K| 43 (3)| 00:00:01 |
| 10 | CONNECT BY WITHOUT FILTERING| | | | | |
| 11 | TABLE ACCESS FULL | TEST | 34495 | 875K| 43 (3)| 00:00:01 |
----------------------------------------------------------------------

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

1 - filter(LEVEL=:B1)
2 - filter("ID"=:B1)
4 - access("ID"=:B1)
5 - access("ID"=NULL)

Note
-----
- dynamic sampling used for this statement

统计信息
----------------------------------------------------------
19 recursive calls
0 db block gets
49565765 consistent gets
0 physical reads
0 redo size
2440053 bytes sent via SQL*Net to client
88264 bytes received via SQL*Net from client
7991 SQL*Net roundtrips to/from client
513485 sorts (memory)
0 sorts (disk)
119838 rows processed