Oracle樹形查詢功能，也就是connect by語句使用起來非常方便。樹形結構是很常見的，比如組織機構樹，產品目錄樹等。我們這裡不講述connect by 如何使用，只是提出在使用樹形查詢時需要考慮的一個有關於性能方面的問題。
這裡提到的問題，主要是Oracle優化器在評估connect by 語句的cardinality時，存在的缺陷，下面將舉例說明。
在這個例子中所使用的表，是一個真實的生產系統中的表，BSS_ORG：
SQL> desc bss_org
名稱 是否為空? 類型
---------------------------- -------- --------------
BSS_ORG_ID NOT NULL NUMBER(9)
NAME NOT NULL VARCHAR2(64)
BSS_PARENT_ORG_ID NUMBER(9)
BSS_ORG_LEVEL_ID NOT NULL NUMBER(3)
STATE NOT NULL VARCHAR2(3)
STATE_DATE DATE
BSS_ORG_CODE VARCHAR2(15)
在這個BSS_ORG表中，BSS_ORG_ID是主鍵，BSS_PARENT_ORG_ID與BSS_ORG_ID形成上下層級關系。這個表的統計信息如下：
Table Number Empty Average Chain Average Global
Name of Rows Blocks Blocks Space Count Row Len Stats
--------------- -------------- -------- ------------ ------- -------- ------- ------
BSS_ORG 5,739 52 0 0 0 53 YES
Column Column Distinct Number
Name Details Values Density Buckets
------------------------- ------------------------ ------------ --------- ------- --
BSS_ORG_ID NUMBER(9,0) NOT NULL 5,739 .000174 1
NAME VARCHAR2(64) NOT NULL 5,034 .000361 200
BSS_PARENT_ORG_ID NUMBER(9,0) 905 .002189 200
BSS_ORG_LEVEL_ID NUMBER(3,0) NOT NULL 6 .000087 6
STATE VARCHAR2(3) NOT NULL 2 .000087 2
STATE_DATE DATE 1,624 .001434 200
BSS_ORG_CODE VARCHAR2(15) 5,639 .000179 200
Table Number Empty Average Chain Average Global
Name of Rows Blocks Blocks Space Count Row Len Stats
--------------- -------------- -------- ------------ ------- -------- ------- ------
BSS_ORG 5,739 52 0 0 0 53 YES
Column Column Distinct Number
Name Details Values Density Buckets
------------------------- ------------------------ ------------ --------- ------- --
BSS_ORG_ID NUMBER(9,0) NOT NULL 5,739 .000174 1
NAME VARCHAR2(64) NOT NULL 5,034 .000361 200
BSS_PARENT_ORG_ID NUMBER(9,0) 905 .002189 200
BSS_ORG_LEVEL_ID NUMBER(3,0) NOT NULL 6 .000087 6
STATE VARCHAR2(3) NOT NULL 2 .000087 2
STATE_DATE DATE 1,624 .001434 200
BSS_ORG_CODE VARCHAR2(15) 5,639 .000179 200
下面的測試，是在Oracle 11.1.0.6 for Windows版本下進行的測試。在Oracle 9i、Oracle 10g下測試的結果與Oracle 11g下測試的結果是相符的。當然這裡談到的問題是cardinality，因此在三個版本下，SQL的執行計劃可能有所不同，但最終的結論是一致的。(BTW：從10g開始，connect by語句有一個新的執行步驟，稱為CONNECT BY NO FILTERING，對應的Hint是no_connect_by_filtering)。
Table Number Empty Average Chain Average Global
Name of Rows Blocks Blocks Space Count Row Len Stats
--------------- -------------- -------- ------------ ------- -------- ------- ------
BSS_ORG 5,739 52 0 0 0 53 YES
Column Column Distinct Number
Name Details Values Density Buckets
------------------------- ------------------------ ------------ --------- ------- --
BSS_ORG_ID NUMBER(9,0) NOT NULL 5,739 .000174 1
NAME VARCHAR2(64) NOT NULL 5,034 .000361 200
BSS_PARENT_ORG_ID NUMBER(9,0) 905 .002189 200
BSS_ORG_LEVEL_ID NUMBER(3,0) NOT NULL 6 .000087 6
STATE VARCHAR2(3) NOT NULL 2 .000087 2
STATE_DATE DATE 1,624 .001434 200
BSS_ORG_CODE VARCHAR2(15) 5,639 .000179 200
Table Number Empty Average Chain Average Global
Name of Rows Blocks Blocks Space Count Row Len Stats
--------------- -------------- -------- ------------ ------- -------- ------- ------
BSS_ORG 5,739 52 0 0 0 53 YES
Column Column Distinct Number
Name Details Values Density Buckets
------------------------- ------------------------ ------------ --------- ------- --
BSS_ORG_ID NUMBER(9,0) NOT NULL 5,739 .000174 1
NAME VARCHAR2(64) NOT NULL 5,034 .000361 200
BSS_PARENT_ORG_ID NUMBER(9,0) 905 .002189 200
BSS_ORG_LEVEL_ID NUMBER(3,0) NOT NULL 6 .000087 6
STATE VARCHAR2(3) NOT NULL 2 .000087 2
STATE_DATE DATE 1,624 .001434 200
BSS_ORG_CODE VARCHAR2(15) 5,639 .000179 200
上面的2條SQL，第1條實際應該返回的行數為5739，第2條SQL實際應該返回的行數為4，但是從執行計劃上看，Oracle優化器評估的行數均為5739。
Table Number Empty Average Chain Average Global
Name of Rows Blocks Blocks Space Count Row Len Stats
--------------- -------------- -------- ------------ ------- -------- ------- ------
BSS_ORG 5,739 52 0 0 0 53 YES
Column Column Distinct Number
Name Details Values Density Buckets
------------------------- ------------------------ ------------ --------- ------- --
BSS_ORG_ID NUMBER(9,0) NOT NULL 5,739 .000174 1
NAME VARCHAR2(64) NOT NULL 5,034 .000361 200
BSS_PARENT_ORG_ID NUMBER(9,0) 905 .002189 200
BSS_ORG_LEVEL_ID NUMBER(3,0) NOT NULL 6 .000087 6
STATE VARCHAR2(3) NOT NULL 2 .000087 2
STATE_DATE DATE 1,624 .001434 200
BSS_ORG_CODE VARCHAR2(15) 5,639 .000179 200
Table Number Empty Average Chain Average Global
Name of Rows Blocks Blocks Space Count Row Len Stats
--------------- -------------- -------- ------------ ------- -------- ------- ------
BSS_ORG 5,739 52 0 0 0 53 YES
Column Column Distinct Number
Name Details Values Density Buckets
------------------------- ------------------------ ------------ --------- ------- --
BSS_ORG_ID NUMBER(9,0) NOT NULL 5,739 .000174 1
NAME VARCHAR2(64) NOT NULL 5,034 .000361 200
BSS_PARENT_ORG_ID NUMBER(9,0) 905 .002189 200
BSS_ORG_LEVEL_ID NUMBER(3,0) NOT NULL 6 .000087 6
STATE VARCHAR2(3) NOT NULL 2 .000087 2
STATE_DATE DATE 1,624 .001434 200
BSS_ORG_CODE VARCHAR2(15) 5,639 .000179 200
這裡在BSS_PARENT_ORG_ID列上建一個索引，是為了使執行計劃與9i、10g下的一致。
這2條SQL返回的結果行數，與前面的2條SQL一樣，分別是5739和4，但是從執行計劃上看，Oracle優化器評估出來的行數都是6。
從前面的兩個測試來看，優化器評估出來的SQL返回的行數要麼是5739(表BSS_ORG的總行數)，要麼是6(總行數/BSS_PARENT_ORG_ID的Disctint Values)。但無論如何，隨著不同的start with條件，這個行數(cardinality)與實際返回的結果行數可能會存在非常大的差異。如果僅僅是測試中這樣一個簡單的SQL，實際上不會有什麼問題，很容易出現問題的地方在於，一個復雜的SQL中，有類似於測試SQL這樣的子查詢，這樣使得表連接的評估出現很大的偏差，這樣容易引起非常大的性能問題。
在9i下，如果BSS_PARENT_ORG_ID上如果沒有索引，那麼最後一個測試SQL的執行計劃如下：
SQL> explain plan for
2 SELECT *
3 FROM bss_org t
4 START WITH bss_org_id = 832044754
5 CONNECT BY bss_parent_org_id =
6 PRIOR bss_org_id
7 ;
已解釋。
SQL> @showplan
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5739 | 297K| 9 |
|* 1 | CONNECT BY WITH FILTERING | | | | |
| 2 | TABLE Access BY INDEX ROWID| BSS_ORG | | | |
|* 3 | INDEX UNIQUE SCAN | PK_BSS_ORG | 1 | 7 | 1 |
| 4 | HASH JOIN | | | | |
| 5 | CONNECT BY PUMP | | | | |
| 6 | TABLE Access FULL | BSS_ORG | 5739 | 297K| 9 |
| 7 | TABLE Access FULL | BSS_ORG | 5739 | 297K| 9 |
----------------------------------------------------------------------------
Predicate Information (identifIEd by Operation id):
---------------------------------------------------
1 - filter("T"."BSS_ORG_ID"=832044754)
3 - Access("T"."BSS_ORG_ID"=832044754)
SQL> explain plan for
2 SELECT *
3 FROM bss_org t
4 START WITH bss_org_id = 832044754
5 CONNECT BY bss_parent_org_id =
6 PRIOR bss_org_id
7 ;
已解釋。
SQL> @showplan
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5739 | 297K| 9 |
|* 1 | CONNECT BY WITH FILTERING | | | | |
| 2 | TABLE Access BY INDEX ROWID| BSS_ORG | | | |
|* 3 | INDEX UNIQUE SCAN | PK_BSS_ORG | 1 | 7 | 1 |
| 4 | HASH JOIN | | | | |
| 5 | CONNECT BY PUMP | | | | |
| 6 | TABLE Access FULL | BSS_ORG | 5739 | 297K| 9 |
| 7 | TABLE Access FULL | BSS_ORG | 5739 | 297K| 9 |
----------------------------------------------------------------------------
Predicate Information (identifIEd by Operation id):
---------------------------------------------------
1 - filter("T"."BSS_ORG_ID"=832044754)
3 - Access("T"."BSS_ORG_ID"=832044754)
這裡9i的優化器評估出來的cardinality為5739，而11g與此同樣的執行計劃，評估的cardinality是6。
前段時間就遇上由於connect by語句引起的性能問題。數據庫為Oracle 9208，開始由於bss_org表的bss_parent_org_id列上沒有索引，導致connect by部分得到的cardinality為5739，結果SQL性能非常差，在bss_parent_org_id上建索引後，執行計劃改變，connect by 部分得到的cardinality為6，SQL性能大幅提升。
對於Oracle優化器不能准確評估connect by 語句的cardinality，目前沒有比較好的解決辦法。必要的時候只有考慮使用Hint了。
